Provisioning an Azure SQL Database

Provisioning an Azure SQL database refers to creating a new and blank Azure SQL database.

In this section, we'll create a new SQL database in Azure using the Azure portal:

1. Open a browser and log in to the Azure portal using your Azure credentials: https://portal.azure.com.
2. On the left-hand navigation pane, select Create a resource:
   

   Figure 1.3: Azure pane

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

   Figure 1.4: Azure panel

4. On the SQL Database page, under the PROJECT DETAILS heading, provide the Subscription and the Resource group. Click the Create new link under the Resource group textbox. In the pop-up box, set the Resource group name as toystore.

   Note

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

   For example, the toystore retail web application uses different Azure resources such as Azure SQL Database, Azure VMs, and Azure Storage. All of these resources can be grouped in a single resource group, say, toystore.

   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/architecture/best-practices/naming-conventions.

   

   Figure 1.5: SQL database panel

5. Under the DATABASE DETAILS heading, enter the Database name and Server.
6. To create a new server, click on Create new under the Server textbox.

   On the New server page, provide the following details and click Select at the bottom of the page: Server name, Server admin login, Password, Confirm password, and Location.

   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/architecture/best-practices/naming-conventions.

   

   Figure 1.6: Server pane

7. Under the Want to use SQL elastic pool? option, select No.
8. In Compute + storage, click Configure database and then select Standard:
   

   Figure 1.7: The Configure window

   Note that you will have to click the Looking for basic, standard, premium? link for the standard option to be available:

   

   Figure 1.8: The Configure pane

9. Click Review + create to continue:
   

   Figure 1.9: SQL pane provisioning panel

10. On the TERMS page, read through the terms and conditions and the configuration settings made so far:
    

    Figure 1.10: The TERMS page

11. Click Create to provision the SQL database.

    Provisioning may take 2-5 minutes. Once the resources are provisioned, you'll get a notification, as shown in the following screenshot:

    

    Figure 1.11: Notification after provision completion

12. Click Go to resource to go to 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:

1. On the toystore pane, select Query editor (preview):
   

   Figure 1.12: Toystore pane

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

   Figure 1.13: The Query Editor pane

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

3. Open C:\Code\Lesson01\sqlquery.sql in Notepad. Copy and paste the query from the notepad into the Query 1 window in the Query editor on the Azure portal.

   The query creates a new table (orders), populates it with sample data, and returns the top 10 rows from the orders table:

   -- create a new orders table 

   CREATE TABLE orders

   (

   orderid INT IDENTITY(1, 1) PRIMARY KEY,

   quantity INT, sales MONEY

   );

   --populate Orders table with sample data

   ;

   WITH t1 

        AS (SELECT 1 AS a 

            UNION ALL 

            SELECT 1), 

        t2 

        AS (SELECT 1 AS a 

            FROM t1 

                   CROSS JOIN t1 AS b), 

        t3 

        AS (SELECT 1 AS a 

            FROM t2 

                   CROSS JOIN t2 AS b), 

        t4 

        AS (SELECT 1 AS a 

            FROM t3 

                   CROSS JOIN t3 AS b), 

        t5 

        AS (SELECT 1 AS a 

            FROM t4 

                   CROSS JOIN t4 AS b), 

        nums 

        AS (SELECT Row_number() 

                     OVER ( 

                       ORDER BY (SELECT NULL)) AS n 

            FROM t5) 

   INSERT INTO orders SELECT n,

   n * 10

   FROM   nums;

   GO

   SELECT TOP 10 * from orders;

4. Select Run to execute the query. You should get the following output:

Figure 1.14: Expected output

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

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

1. Open SQL Server Management Studio. In the Connect to Server dialog box, set the Server type as Database Engine, if not already selected.
2. Under the Server name, provide the Azure SQL server name. You can find the Azure SQL server in the Overview section of the Azure SQL Database pane on the Azure portal:
   

   Figure 1.15: Overview pane of the toystore database

3. Select SQL Server Authentication as the Authentication Type.
4. Provide the login and password for Azure SQL Server and select Connect:
   

   Figure 1.16: Login panel of SQL Server

   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:

   

   Figure 1.17: New Firewall Rule pane

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

   Open the toystore SQL database Overview pane, if it's not already open.

   From the Overview pane, select Set server firewall:

   

   Figure 1.18: Set the server firewall in the Overview pane

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

   Figure 1.19: The Add client IP option on the Firewall settings pane

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

   If you wish to rename the rule, you can do so by 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 toyfactory server.

   Note

   The virtual network can be used to add a SQL database in Azure to a given network. A detailed explanation of virtual networks is out of the scope of this book.

   

   Figure 1.20: The Firewall settings pane

   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. Press F8 to open Object Explorer, if it's not already open:
   

   Figure 1.21: Object Explorer pane

8. You can view and modify the firewall settings using T-SQL in the master database. Press Ctrl + N to open a new query window. Make sure that the database is set to master.

   Note

   To open a new query window in the master database context, in Object Explorer, expand Databases, then expand System Databases. Right-click the master database and select New Query.

9. Enter the following query to view the existing firewall rules:

   SELECT * FROM sys.firewall_rules

   You should get the following output:

   

   Figure 1.22: Existing firewall rules

   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.

10. To query the toystore SQL database, change the database context of the SSMS query window to toystore. You can do this by selecting the toystore database from the database dropdown in the menu:
    

    Figure 1.23: Dropdown to select the toystore database

11. Copy and paste the following query into the query window:

    SELECT COUNT(*) AS OrderCount FROM orders;

    The query will return the total number of orders from the orders table. You should get the following output:

Figure 1.24: Total number of orders in the "orders" table

Deleting Resources

To delete an Azure SQL database, an Azure SQL server, and Azure resource groups, perform the following steps:

Note

All resources must be deleted to successfully complete the activity at the end of this lesson.

1. Switch to the Azure portal and select All resources from the left-hand navigation pane.
2. From the All resources pane, select the checkbox next to toyfactory and the Azure SQL server that is to be deleted, and then select Delete from the top menu:
   

   Figure 1.25: Deleting the toyfactory SQL Server

3. In the Delete Resources window, type yes in the confirmation box and click the Delete button to delete the Azure SQL server and Azure SQL database:
   

   Figure 1.26: Confirming to delete the selected resource

   Note

   To only delete an Azure SQL database, check the Azure SQL database checkbox.

4. To delete the Azure resource group, select Resource groups from the left-hand navigation pane:
   

   Figure 1.27: Resource groups

5. In the Resource groups pane, click the three dots next to the toystore resource group, and then select Delete resource group from the context menu:
   

   Figure 1.28: Delete resource group option

6. In the delete confirmation pane, type the resource under the TYPE THE RESOURCE GROUP NAME section, and then click Delete.