Book Image

Professional Azure SQL Database Administration - Second Edition

By : Ahmad Osama
Book Image

Professional Azure SQL Database Administration - Second Edition

By: Ahmad Osama

Overview of this book

Despite being the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. This book shows you how to administer Azure SQL Database to fully benefit from its wide range of features and functionalities. Professional Azure SQL Database Administration begins by covering the architecture and explaining the difference between Azure SQL Database and the on-premise SQL Server to help you get comfortable with Azure SQL Database. You’ll perform common tasks such as migrating, backing up and restoring a SQL Server database to an Azure database. As you progress, you’ll understand how you can reduce costs, and manage and scale multiple SQL databases using elastic pools. You’ll also implement a disaster recovery solution using standard and active geo-replication. Whether it is learning different techniques to monitor and tune an Azure SQL Database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL database features and functionality for data management solutions. By the end of this book, you’ll be well-versed with key aspects of an Azure SQL Database instance, such as migration, backup restorations, performance optimization, high availability, and disaster recovery.
Table of Contents (11 chapters)

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
    Figure 1.3: Azure pane
  3. On the New page, under Databases, select SQL Database:
    Figure 1.4: Azure panel
    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
    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
    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
    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: Configure pane
    Figure 1.8: The Configure pane
  9. Click Review + create to continue:
    Figure 1.9: SQL pane provisioning panel
    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
    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
    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
    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
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
    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
    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
    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
    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
    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
    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
    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
    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
    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
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
    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
    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
    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
    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.