Provisioning a First Azure SQL Database

In this section, we'll learn:

• How to provision an Azure SQL database
• How to Connecting and Querying the SQL Database from the Azure Portal
• How to Connecting to and Querying the SQL Database from SQL Server Management Studio

Provisioning Azure SQL database: 

Provisioning an Azure SQL database refer to creating a new and blank Azure SQL database. We'll create new SQL database in Azure using the Azure portal:

1. Open a browser and log in to the Azure portal: https://portal.azure.com

2. On the left-hand navigation pane, select Create a resource:

3. On the New page, under Databases, select SQL Database:

4. On the below SQL Database page, select the Subscription and the Resource group. Click the Create new link under the Resource group and set the Resource group name (in my case i.e. rg-sportstore).

Note: A resource group is a logical container that is used to group Azure resources required to run an application.

5. Under the DATABASE DETAILS, enter the Database name (in my case database name: db-sportstore).

Note: The SQL database name should be unique across Microsoft Azure and should follow the

following naming rules and conventions: 

https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/ready/azure-best-practices/naming-and-tagging

6. To create a new server, click on Create new link under the Server textbox .On the New

Server page, enter the following details and click OK.

 Note: The server name should be unique across Microsoft Azure and should follow the following naming rules and conventions: 

https://docs.microsoft.com/en-us/azure/cloud-adoption-framework/ready/azure-best-practices/naming-and-tagging 

7. Under the Want to use SQL elastic pool? option, select No.

8. In Compute + storage, click Configure database and then select Standard:

Now Click on Looking for basic, standard, premium? link for the standard option to be available:

Click Apply.

9. Click Review + create to continue:

10. On the TERMS page, read the terms and conditions and the basic configuration settings:

11. Click Create to provision the SQL database.

It may take 2-5 minutes. Once the resources are provisioned, you'll get a notification, as

below:

12. Click Go to resource to check the newly created SQL database.

Connecting and Querying the SQL Database from the Azure Portal

In this section, we'll learn how to connect and query the SQL database from the Azure portal.

Click on resource group (rg-sportstore) and click on database (in my case db-sportstore).

1. On the (db-sportsore) database pane, select Query editor (preview):

2. On the Query editor (preview) pane, under SQL server authentication, provide the login and password:

Select OK to authenticate and return to the Query editor (preview) pane:

If you receive above error message, then click on Set server firewall (vm-sportstore). It will redirect you to Firewall Setting. Click on “Add Client IP” and “Save” it.

Note: If you have already added client IP in Firewall Setting then you will not receive below
error message.

Now go back to Query editor (preview) pane, under SQL server authentication, provide the username and password:

Select OK to authenticate and return to the Query editor (preview) pane:

3. Now run the below query or any sample query to check.

Connecting to and Querying the SQL Database from SQL Server Management Studio

In this section, we'll learn how to connect and query the SQL database from SQL Server Management Studio (SSMS):

1. Open SQL Server Management Studio.

2. Under the Server name, provide the Azure SQL server name. You can find the Azure SQL server name in the Overview section of the Azure SQL Database pane on the Azure portal:

3. Select SQL Server Authentication as the Authentication Type.

4. Provide the login and password for Azure SQL Server and select Connect: 

You'll get an error saying Your client IP address does not have access to the server. To connect to

Azure SQL Server, you must add the IP of the system you want to connect from under the firewall

rule of Azure SQL Server. You can also provide a range of IP addresses to connect from:

Note: If you have already added client IP in Firewall Rule (after SQL database creation) then

you will not receive below error message.

To add your machine's IP to the Azure SQL Server firewall rule, switch to the Azure portal.

Open the db-sportstore SQL database Overview pane, if it's not already open.

From the Overview pane, select Set server firewall:

   5. In the Firewall settings pane, select Add client IP:

6. The Azure portal will automatically detect the machine's IP and add it to the firewall rule.

If you want to rename the rule, you can providing a meaningful name in the RULE NAME

column. All machines with IPs between START IP and END IP are allowed to access all of the

databases on the vm-sportstore server.

Click Save to save the firewall rule.

7. Switch back to SQL Server Management Studio (SSMS) and click Connect. You should now be able to connect to Azure SQL Server. 

8. You can view the firewall settings using T-SQL in the master database.

  

   SELECT * FROM sys.firewall_rules

       You should get the following output:

The AzureAllWindowsAzureIps firewall is the default firewall, which allows resources within Microsoft to access Azure SQL Server.

The rest are user-defined firewall rules. The firewall rules for you will be different from what is shown here. 

You can use sp_set_firewall_rule to add a new firewall rule and sp_delete_ firewall_rule to delete an existing firewall rule.

9. Now run the below query or any sample query to check.

Conclusion

Now you will be able to provision the SQL Database and query the the SQL database from the Azure Portal and SSMS (SQL Server Management Studio).

                                                             Stay Tuned!!!!!

               

--By Satish K....

Stay In Touch: