Setup And Connect To Azure SQL

Introduction

Microsoft Azure SQL Database (formerly known as SQL Azure) is a managed cloud database i.e. Software as a Service (SaaS) provided by Microsoft Azure. In simple words, Microsoft Azure SQL is a cloud database that runs on a cloud computing platform and access to it is provided as Service.

Managed database services supports backup, HA (high availability), and scalability of the database. Azure SQL uses SQL Server 2016 codebase and is compatible with SQL Server 2014 and most of the features in SQL 2016.

Pricing

Azure SQL database can be setup as Standalone database or an Elastic database pool (allows multiple databases to share storage and compute the resources). It is priced in three tiers – Basic, Standard, Premium.

• Basic - Can be used for small database. It supports single active operation at a given time. Best suited for development, testing applications
• Standard - More frequently used option. Supports multiple concurrent queries. Mostly used along with web applications
• Premium - Supports many concurrent users at a time. Designed for mission critical applications.

Create Azure SQL (Azure Portal)

Creating Azure SQL from Azure Portal is few simple clicks and helps to setup your Azure SQL in a few minutes.

Please note, the old portal does not support creation of any Data Services now a days.

Creating Azure SQL is only supported from New Azure Portal.

1. Login to New Azure Portal.
   
   
2. Click "New" button from top left corner.
   
   
3. Select "Databases".
   
   
4. Select "SQL Database".
   
   
   

5. Fill in the SQL Database details as below,
   
   
   1. Database name - Name of your Azure SQL Database. E.g. sampleSQLDB
   2. Subscription - Select any of your MSDN subscription
   3. Resource Group - Create a new resource group or use any of the existing.
   4. Source - You may choose to create a blank database of any predefined database (e.g. Adventure Works)
      
      
      

6. Click Create a new server
   
   
   

7. Fill in the SQL server (logical server) form as below,
   
   
   1. Server Name - Any globally unique name
   2. Server admin login - Any valid name (e.g. user1)
   3. Password - Any valid password
   4. Location - Select any location for hosting SQL Server. Preferably select any location near to your location or same location where your web application is deployed.
      
      
8. When finished, click Select.
   
   
9. Click Pricing tier to specify the service tier and performance level for your new database. Use the slider to select 20 DTUs and 250 GB of storage. (or as per your need)
   
   
   
   
10. Click "Apply" to save your selection.
    
    
11. SQL Database form is now complete; click "Create" to provision your database.
    
    
12. In the toolbar, click "Notifications" to see the deployment progress.

Create Server level Firewall rule

1. Once the deployment finishes, click "SQL databases" from menu.
   
   
2. Click the database created in the above process. E.g. sampleSQLDB.
   
   
3. Click "Set server firewall".
   
   
   

4. Click "Add client IP" to add your current IP address to new firewall rule. The firewall rule will open port 1433 to a single IP address or range of IP addresses.
   
   
   
   
5. Click "Save".

Query SQL Database

1. On SQL Database page, click Tools
   
   
2. Click Query editor (preview)
   
   
3. Click Preview terms checkbox, and then click OK.
   
   
   
   
4. Click Login
   
   
5. Select SQL server authentication.
   
   
6. Enter server admin login and password created earlier.
   
   
   
   
7. Click OK
   
   
8. Once Authenticated you can type in and run your SQL queries.

Connect to Azure SQL using SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio (SSMS)
   
   
2. In the connect to server dialog
   
   
3. Enter server name as <server-name>.database.windows.net
   
   
4. <server-name> is value while sever creation
   
   
5. Enter Login name as usename@<server-name>
   
   
6. Enter password
   
   
7. Click Connect to proceed.
   
   
   

Get Connection String for SQL Azure

1. On SQL Database page, click Show database connection strings
   
   
   

2. This will show the connection string on next page.
   
   
3. You are ready to use this connection string in your web applications.