The Azure SQL Database Architecture

Azure SQL Database is a highly scalable multi-tenant and a highly available Platform-as-a-Service (PaaS) or Database-as-a-Service (DBaaS) offering from Microsoft.

Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.

Azure SQL Database has the following deployment options:

• Single
• Elastic pool
• Managed instance

Azure SQL Database allows users to focus only on managing data, and is divided into four layers that work together to provide users with relational database functionality, as shown in the following diagram:

Figure 1.1: The four layers of Azure SQL Database

Note

If you were to compare it to the on-premise SQL Server architecture, other than the Service Layer, the rest of the architecture is pretty similar.

Client Layer

The client layer acts as an interface for applications to access a SQL database. It can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between a SQL database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, and Java to easily connect to Azure SQL Database without any additional requirements.

Service Layer

The service layer acts as a gateway between the client and platform layers. It is responsible for:

• Provisioning a SQL database
• User authentication and SQL database validation
• Enforcing security (firewall rules and denial-of-service attacks)
• Billing and metering for a SQL database
• Routing connections from the client layer to the physical server hosting the SQL database in the platform layer

Platform Layer

The platform layer consists of physical servers hosting SQL databases in data centers. Each SQL database is stored on one physical server and is replicated across two different physical servers:

As shown in Figure 1.1, the Platform Layer has two other components: Azure Fabric and Management Services. Azure Fabric is responsible for load balancing, automatic failover, and the automatic replication of a SQL database between physical servers. Management Services takes care of an individual server's health monitoring and patch updates.

Infrastructure Layer

This layer is responsible for the administration of the physical hardware and the OS.

Note

Dynamic routing allows us to move a SQL database to different physical servers in the event of any hardware failures or for load distribution.

Azure SQL Database Request Flow

The following diagram shows the Platform layer:

Figure 1.2: Platform layer – nodes

The application sends a TDS request (login, DML, or DDL queries) to the SQL database. The TDS request is not directly sent to the Platform layer. The request is first validated by the SQL Gateway Service at the Service layer.

The Gateway Service validates the login and firewall rules, and checks for denial-of-service attacks. It then dynamically determines the physical server on which the SQL database is hosted and routes the request to that physical server in the Platform layer. Dynamic routing allows the SQL database to be moved across physical servers or SQL instances in the event of hardware failures.

Note

Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.

Dynamic routing refers to routing the database request to the physical server that hosts an Azure SQL database. This routing is done internally and is transparent to the user. If one physical server hosting the database fails, dynamic routing will route the requests to the next available physical server hosting the Azure SQL database.

The internals of dynamic routing are out of the scope of this book.

As shown in Figure 1.2, the Platform layer has three nodes: Node 1, Node 2, and Node 3. Each node has a primary replica of a SQL database and two secondary replicas of two different SQL databases from two different physical servers. The SQL database can fail over to the secondary replicas if the primary replica fails. This ensures the high availability of the SQL database.

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.

Differences between Azure SQL Database and SQL Server

Azure SQL Database is a PaaS offering and therefore some of its features differ from the on-premises SQL Server. Some of the important features that differ are as follows:

Backup and Restore

Conventional database backup and restore statements aren't supported. Backups are automatically scheduled and start within a few minutes of the database provisioning. Backups are consistent, transaction-wise, which means that you can do a point-in-time restore.

There is no additional cost for backup storage until it goes beyond 200% of the provisioned database storage.

You can reduce the backup retention period to manage backup storage costs. You can also use the long-term retention period feature to store backups in the Azure vault for a much lower cost for a longer duration.

Apart from automatic backups, you can also export the Azure SQL Database bacpac or dacpac file to Azure storage.

Recovery Model

The default recovery model of an Azure SQL database is FULL and it can't be modified to any other recovery model as in on-premises recovery models.

The recovery model is set when the master database is created, meaning when an Azure SQL server is provisioned, the recovery model can't be modified because the master database is read-only.

To view the recovery model of an Azure SQL database, execute the following query:

SELECT name, recovery_model_desc FROM sys.databases;

Note

You can use either of the two methods discussed earlier in the lesson to run the query – the Azure portal or SSMS.

You should get the following output:

Figure 1.29: Recovery model of an SQL database

SQL Server Agent

Azure SQL Server doesn't have SQL Server Agent, which is used to schedule jobs and send success/failure notifications. However, you can use the following workarounds:

• Create a SQL Agent job on an on-premise SQL server or on an Azure SQL VM SQL Agent to connect and run on the Azure SQL database.
• Azure Automation allows users to schedule jobs in Microsoft Azure to automate manual tasks. This topic is covered in detail later in the book.
• Elastic Database Jobs is an Azure Cloud service that allows the scheduled execution of ad hoc tasks. This topic is covered in detail later in the book.
• Use PowerShell to automate a task and schedule PowerShell script execution with Windows Scheduler, on-premises, or Azure SQL VM SQL Agent.

Change Data Capture

Change Data Capture (CDC) allows you to capture data modifications to CDC-enabled databases and tables. The CDC feature is important in incremental load scenarios, such as incrementally inserting changed data to the data warehouse from an OLTP environment. The CDC requires SQL Server Agent, and therefore isn't available in Azure SQL Database. However, you can use the temporal table, SSIS, or Azure Data Factory to implement CDC.

Auditing

The auditing features, such as C2 auditing, system health extended events, SQL default trace, and anything that writes alerts or issues into event logs or SQL error logs, aren't available. This is because it's a PaaS offering and we don't have access to or control of event logs or error logs.

However, there is an auditing and threat-detection feature available out of the box for Azure SQL Database.

Mirroring

You can't enable mirroring between two Azure SQL databases, but you can configure Azure SQL Database as a mirror server. You can also set up a readable secondary for an Azure SQL database, which is better than mirroring.

Table Partitioning

Table partitioning using a partition scheme and partition function is allowed in Azure SQL Database; however, because of the PaaS nature of the SQL database, all partitions should be created on a primary filegroup. You won't get a performance improvement by having partitions on different disks (spindles); however, you will get a performance improvement with partition elimination.

Replication

Conventional replication techniques, such as snapshot, transactional, and merge replication, can't be done between two Azure SQL databases. However, an Azure SQL database can be a subscriber to an on-premise or Azure VM SQL Server.

However, this too has limitations. It supports one-way transactional replication, not peer-to-peer or bi-directional replication; it supports only push subscription.

Note that you should have SQL Server 2012 or above at on-premises. Replication and distribution agents can't be configured on Azure SQL Database.

Multi-Part Names

Three-part names (databasename.schemaname.tablename) are only limited to tempdb, wherein you access a temp table as tempdb.dbo.#temp. For example, if there is a temporary table, say #temp1, then you can run the following query to select all the values from #temp1:

SELECT * FROM tempdb.dbo.#temp1

You can't access the tables in different SQL databases in Azure on the same Azure SQL server using three-part names. Four-part (ServerName.DatabaseName.SchemaName.TableName) names aren't allowed at all.

You can use an elastic query to access tables from different databases from an Azure SQL server. Elastic queries are covered in detail later in the book. You can access objects in different schemas in the same Azure SQL database using two-part (Schemaname.Tablename) names.

To explore other T-SQL differences, visit https://docs.microsoft.com/en-us/azure/sql-database/sql-database-transact-sql-information.

Unsupported Features

Some features not supported by Azure SQL Database or Azure SQL Server are:

• SQL Browser Service: SQL Browser is a Windows service and provides instance and post information to incoming connection requests. This isn't required because Azure SQL Server listens to port 1433 only.
• Filestream: Azure SQL Database doesn't support FileStream or filetable, just because of the PaaS nature of the service. There is a workaround to use Azure Storage; however, that would require a re-work on the application and the database side.
• Common Language Runtime (SQL CLR): SQL CLR allows users to write programmable database objects such as stored procedures, functions, and triggers in managed code. This provides a significant performance improvement in some scenarios. SQL CLR was initially supported and then the support was removed due to concerns about security issues.
• Resource Governor: Resource Governor allows you to throttle/limit resources (CPU, memory, and I/O) for different SQL Server workloads. This feature is not available in Azure SQL Database.

  Azure SQL Database comes with different service tiers, each suitable for different workloads. You should evaluate the performance tier your application workload will fit into and accordingly provision the database for that performance tier.

• Global Temporary Tables: Global temporary tables are defined by ## and are accessible across all sessions. These are not supported in Azure SQL Database. Local temporary tables are allowed. Global temporary tables created with ## are accessible across all sessions for a particular database. For example, a global temporary table created in database DB1 will be accessible to all sessions connecting to database DB1 only.
• Log Shipping: Log shipping is the process of taking log backups on a primary server and copying and restoring them on a secondary server. Log shipping is commonly used as a high-availability or disaster-recovery solution, or to migrate a database from one SQL instance to another. Log shipping isn't supported by Azure SQL Database.
• SQL Trace and Profiler: SQL Trace and Profiler can't be used to trace events on Azure SQL Server. As of now, there isn't any direct alternate other than using DMVs, monitoring using the Azure portal, and extended events.
• Trace Flags: Trace flags are special switches used to enable or disable a particular SQL Server functionality. These are not available in Azure SQL Server.
• System Stored Procedures: Azure SQL Database doesn't support all the system stored procedures supported in the on-premises SQL Server. System procedures such as sp_addmessage, sp_helpuser, and sp_configure aren't supported. In a nutshell, procedures related to features unsupported in Azure SQL Database aren't supported.
• USE Statement: The USE statement is used to switch from one database context to another. This isn't supported in Azure SQL Database.

Introduction to Managed Instance

SQL managed instance is a fully managed SQL Server instance offering announced in May 2017 and made generally available from October 1, 2018.

SQL managed instance provides nearly 100% surface area compatibility with on-premises SQL Server instances and the DBaaS benefits available with Azure SQL Database, such as automatic backups, updates, automatic performance tuning, Intelligent Insights, and so on.

Note that SQL managed instance is not a replacement for Azure SQL Database, rather a new deployment option for Azure SQL Database with near 100% compatibility with on-premises SQL Server instances.

Managed instance supports most of the features of an on-premise deployment, which were earlier not available in Azure SQL Database. It therefore provides easy lift and shift migration from an on-premises environment to the cloud.

When you migrate to managed instance on Azure, you don't only migrate databases, you migrate licenses too.

Note

You can save up to 55% on managed instance, when migrating from SQL Server Enterprise or Standard edition with software assurance. For more details, please visit https://azure.microsoft.com/en-us/blog/migrate-your-databases-to-a-fully-managed-service-with-azure-sql-database-managed-instance/ or contact Azure Support.

Some of the important features supported by managed instance that are not available in Azure SQL Database are as follows:

• Native backup and restore
• Global temporary tables
• Cross-database queries and transactions
• Linked servers
• CLR modules
• SQL agent
• Database mail
• Transactional replication (Azure SQL Database can only be a subscriber)

DTC is not supported in managed instance.

These and other features of managed instance make it 100% compatible with an on-premise SQL Server.

Purchasing Model

SQL Server managed instance follows a vCore based purchasing model. The vCore model gives you the flexibility to choose the compute, memory, and storage based on different workload requirements.

The vCore model supports two hardware generations, Gen4 and Gen5:

• Gen4 has Intel E5-2673 v3 (Haswell) 2.4 GHz processors (1 vCore is 1 Physical Core), 7 GB per vCore Memory with 3 GB per vCore In-Memory, and max storage size of 8 TB.
• Gen5 has intel E5-2673 v4 (Broadwell) 2.3 GHz processors, fast NVMe SSD, (1 vCore = 1 Logical Processor (hyper-thread), 5.1 GB memory per vCore, 2.6 GB per vCore In-Memory, and a max storage size of 8 TB.

The vCore model comes with two service tiers: General Purpose and Business Critical.

The General Purpose service tier is designed for SQL Server workloads with typical performance requirements and is suitable for the majority of applications.

The Business Critical service tier, as the name suggests, supports high-performance, low I/O latency environments. The Business Critical service tier provides 1-2 millisecond (approximately) I/O latency, 48 MB/s per instance of log throughput, and 24-48 MB/s of data throughput per vCore.

Connecting to SQL Managed Instance

A SQL managed instance is a set of services hosted on one or more isolated virtual machines inside a virtual network subnet.

When we provision a managed instance, a virtual cluster is created. A virtual cluster can have one or more managed instances.

Applications connect to databases via an endpoint, <mi_name>.<dns_zone>.database.windows.net, and should be inside a virtual network, a peered virtual network, or an on-premise network connected via VPN or Azure ExpressRoute.

Unlike Azure SQL Database, SQL managed instance supports Azure Virtual Network (VNet). An Azure Virtual Network is a logical boundary or isolation that groups resources within a specified Azure region and enables secure communication between resources, the internet, and on-premise networks.

Figure 1.30: High-level connectivity architecture for SQL managed instances

The preceding diagram shows a high-level connectivity architecture for SQL managed instances. Let's go through it:

• All managed instances are part of a virtual cluster and are in a managed instance subnet in virtual network vNet1
• Web and other applications in vNet1 connect to the managed instance using an endpoint, for example, sqlinstance3.dnszone.database.windows.net.
• Applications in any other virtual network connect using the same endpoint; however, the two virtual networks are peered to allow connectivity between them.
• On-premise applications connect using the same endpoint via VPN or an ExpressRoute gateway.

Exercise: Provisioning a SQL-Managed Instance Using the Azure Portal

In this exercise, we'll provision and connect to a managed instance. We'll also learn about VNet support in SQL-managed instances.

To provision a SQL-managed instance, perform the following steps:

1. Log in to https://portal.azure.com using your Azure credentials.
2. In the top search box, type SQL Managed Instance and select SQL managed instances from the dropdown:
   

   Figure 1.31: Searching for SQL Managed Instance.

3. In the SQL managed instances window, select Add:
   

   Figure 1.32: The SQL managed instances pane

4. In the SQL managed instance window, provide the information shown in the following screenshot:
   

   Figure 1.33: Information required to add the SQL managed instance

   Note

   Figure 1.31, Figure 1.32, and Figure 1.33 are all a part of one SQL managed instance window. The window is split into three images for clarity.

   In the Subscription box, provide your Azure subscription type. SQL managed instances currently support the following subscription types: Enterprise Agreement (EA), Pay-As-You-Go, Cloud Service Provider (CSP), Enterprise Dev/Test, and Pay-As-You-Go Dev/test.

   If you have a different subscription, you won't be able to create a SQL managed instance.

   The Managed instance name box is for the name of the managed instance you plan to create. It can be any valid name, in accordance with the naming rules at https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions.

   The Managed instance admin login box is for any valid login name, as long as it fits the naming conventions at https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions.

   The password can be any valid password that follows these rules:

   

   Figure 1.34: Password requirements

   The Time zone box denotes the time zone of the managed instance. The preferred time zone is UTC; however, this will differ from business to business.

   Collation is the SQL Server collation that the managed instance will be in.

   

   Figure 1.35: Server collation information

   In the Location box, enter the Azure location the managed instance will be created in.

   The Virtual network box is for setting the virtual network that the managed instance will be a part of. If no network is provided, a new virtual network is created.

   Note

   A detailed explanation of the networking requirements is beyond the scope of the book. For more details, please visit https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-connectivity-architecture.

   For the Connection type box, SQL managed instances support two connection types, Redirect and Proxy. Redirect is the recommended connection type because the client directly connects to the node hosting the database, and therefore it offers low latency and high throughput.

   In Proxy connection type, requests to the database are proxied through the Azure SQL Database gateways.

   Resource group sets the resource group the SQL managed instance will be part of. It can be a new or existing one.

   To use this managed instance as a secondary instance in a failover group, check I want to use this managed instance as an Instance Failover Group secondary and specify the managed instance to share an instance failover group within DnsZonePartner managed instance.

   The DnsZonePartner managed instance box is left blank because we don't have any other managed instances.

   

   Figure 1.36: SQL managed instance pricing information

   For Pricing tier, select the optimal pricing tier that suits your business needs. However, for demo purposes, select General Purpose: 32 GB, 8 vCore for a lower price.

   The first managed instance in a subnet may take up 6 hours to complete and a warning pops up to inform you of this.

5. Click Create to validate and provision the SQL-managed instance.

   To monitor the progress, click the Notifications (bell) icon in the top-left corner:

Figure 1.37: Notification icon in the instance window

As we can see, the deployment is in progress:

Figure 1.38: The Notifications pane

After the deployment is complete, a deployment complete notification will come up in the notification window.

Activity: Provisioning an Azure SQL Server and SQL Database Using PowerShell

This section discusses provisioning an Azure SQL server and SQL database using PowerShell. To understand the process, let's take the example of Mike, who is the newest member of the Data Administration team at ToyStore Ltd., a company that manufactures toys for children. ToyStore has an e-commerce web portal that allows customers to purchase toys online. ToyStore has migrated the online e-commerce portal to Microsoft Azure and is therefore moving to Azure SQL Database from the on-premises SQL Server. Mike is asked to provision the Azure SQL database and other required resources as his initial assignment. This can be achieved by following these steps:

Note

If you are short of time, you can refer to the C:\code\Lesson01\ProvisionAzureSQLDatabase.ps1 file. You can run this file in the PowerShell console instead of typing the code as instructed in the following steps. Open a PowerShell console and enter the full path to execute the PowerShell script. You'll have to change the Azure Resource Group name, the Azure SQL server, and the Azure SQL database name in the script before executing it.

1. Save the Azure profile details into a file for future reference. Press Windows + R to open the Run command window.
2. In the Run command window, type powershell and then press Enter. This will open a new PowerShell console window:
   

   Figure 1.39: Opening up PowerShell

3. In the PowerShell console, run the following command:

   Add-AzureRmAccount

   You'll have to enter your Azure credentials into the pop-up dialog box. After a successful login, the control will return to the PowerShell window.

4. Run the following command to save the profile details to a file:

   Save-AzureRmProfile -Path C:\code\MyAzureProfile.json

   The Azure subscription details will be saved in the MyAzureProfile.json file in JSON format. If you wish to explore the JSON file, you can open it in any editor to review its content:

   

   Figure 1.40: The PowerShell command window

   Note

   Saving the profile in a file allows you to use the file to log in to your Azure account from PowerShell instead of providing your credentials every time in the Azure authentication window.

5. Press Window + R to open the Run command window. Type PowerShell_ISE.exe in the Run command window and press Enter. This will open a new PowerShell ISE editor window. This is where you'll write the PowerShell commands:
   

   Figure 1.41: Run command window

6. In the PowerShell ISE, select File from the top menu, and then click Save. Alternatively, you can press Ctrl + S to save the file. In the Save As dialog box, browse to the C:\Code\Lesson01\ directory. In the File name textbox, type Provision-AzureSQLDatabase.ps1, and then click Save to save the file:
   

   Figure 1.42: Saving the PowerShell ISE file

7. Copy and paste the following lines in the Provision-AzureSQLDatabase.ps1 file one after another. The code explanation, wherever required, is given in the comments within the code snippet.
8. Copy and paste the following code to define the parameters:

   param (

   [parameter(Mandatory=$true)] [String] $ResourceGroup, [parameter(Mandatory=$true)] [String] $Location, [parameter(Mandatory=$true)] [String] $SQLServer, [parameter(Mandatory=$true)] [String] $UserName, [parameter(Mandatory=$true)] [String] $Password,

   [parameter(Mandatory=$true)] [String] $SQLDatabase, [parameter(Mandatory=$true)] [String] $Edition="Basic", [parameter(Mandatory=$false)] [String] $AzureProfileFilePath

   )

   The preceding code defines the parameters required by the scripts:

   ResourceGroup: The resource group that will host the logical Azure SQL server and Azure SQL database.

   Location: The resource group location. The default is East US 2.

   SQLServer: The logical Azure SQL server name that will host the Azure SQL database.

   UserName: The Azure SQL Server admin username. The default username is sqladmin. Don't change the username; keep it as the default.

   Password: The Azure SQL Server admin password. The default password is Packt@pub2. Don't change the password; keep it as the default.

   SQLDatabase: The Azure SQL database to create.

   Edition: The Azure SQL Database edition. This is discussed in detail in Lesson 2, Migrating a SQL Server Database to an Azure SQL Database.

   AzureProfileFilePath: The full path of the file that contains your Azure profile details. You created this earlier under the Saving Azure Profile Details to a File section.

9. Copy and paste the following code to log in to your Azure account from PowerShell:

   Start-Transcript -Path .\log\ProvisionAzureSQLDatabase.txt -Append if([string]::IsNullOrEmpty($AzureProfileFilePath))

   {

   $AzureProfileFilePath="..\..\MyAzureProfile.json"

   }

   if((Test-Path -Path $AzureProfileFilePath))

   {

   $profile = Import-AzureRmContext-Path $AzureProfileFilePath

   $SubscriptionID = $profile.Context.Subscription.SubscriptionId

   }

   else

   {

   Write-Host "File Not Found $AzureProfileFilePath"

   -ForegroundColor Red

   $profile = Login-AzureRmAccount

   $SubscriptionID = $profile.Context.Subscription.

   SubscriptionId

   }

   Set-AzureRmContext -SubscriptionId $SubscriptionID | Out-Null

   The preceding code first checks for the profile details in the Azure profile file. If found, it retrieves the subscription ID of the profile; otherwise, it uses the Login-AzureRmAccount command to pop up the Azure login dialog box. You have to provide your Azure credentials in the login dialog box. After a successful login, it retrieves and stores the subscription ID of the profile in the $SubscriptionID variable.

   It then sets the current Azure subscription to yours for the PowerShell cmdlets to use in the current session.

10. Copy and paste the following code to create the resource group if it doesn't already exist:

    # Check if resource group exists

    # An error is returned and stored in the notexists variable if the resource group exists

    Get-AzureRmResourceGroup -Name $ResourceGroup -Location $Location -ErrorVariable notexists -ErrorAction SilentlyContinue

    #Provision Azure Resource Group

    if($notexists)

    {

     

    Write-Host "Provisioning Azure Resource Group $ResourceGroup" -ForegroundColor Green

    $_ResourceGroup = @{

      Name = $ResourceGroup;

      Location = $Location;

    }

    New-AzureRmResourceGroup @_ResourceGroup;

    }

    else

    {

    Write-Host $notexists -ForegroundColor Yellow

    }

    The Get-AzureRmResourceGroup cmdlet fetches the given resource group. If the given resource group doesn't exist, an error is returned. The error returned is stored in the notexists variable.

    The New-AzureRmResourceGroup cmdlet provisions the new resource group if the notexists variable isn't empty.

11. Copy and paste the following code to create a new Azure SQL server if one doesn't exist:

    Get-AzureRmSqlServer -ServerName $SQLServer -ResourceGroupName

    $ResourceGroup -ErrorVariable notexists -ErrorAction SilentlyContinue

    if($notexists)

    {

    Write-Host "Provisioning Azure SQL Server $SQLServer"

    -ForegroundColor Green

    $credentials = New-Object -TypeName System.Management.Automation. PSCredential -ArgumentList $UserName, $(ConvertTo-SecureString

    -String $Password -AsPlainText -Force)

    $_SqlServer = @{

    ResourceGroupName = $ResourceGroup; ServerName = $SQLServer;

    Location = $Location; SqlAdministratorCredentials = $credentials; ServerVersion = '12.0';

    }

    New-AzureRmSqlServer @_SqlServer;

    }

    else

    {

    Write-Host $notexists -ForegroundColor Yellow

    }

    The Get-AzureRmSqlServer cmdlet gets the given Azure SQL server. If the given Azure SQL server doesn't exist, an error is returned. The error returned is stored in the notexists variable.

    The New-AzureRmSqlServer cmdlet provisions the new Azure SQL server if the notexists variable isn't empty.

12. Copy and paste the following code to create the Azure SQL database if it doesn't already exist:

    # Check if Azure SQL Database Exists

    # An error is returned and stored in the notexists variable if the resource group exists

    Get-AzureRmSqlDatabase -DatabaseName $SQLDatabase -ServerName

    $SQLServer -ResourceGroupName $ResourceGroup -ErrorVariable notexits -ErrorAction SilentlyContinue

    if($notexists)

    {

    # Provision Azure SQL Database

    Write-Host "Provisioning Azure SQL Database $SQLDatabase"

    -ForegroundColor Green

    $_SqlDatabase = @{

    ResourceGroupName = $ResourceGroup; ServerName = $SQLServer; DatabaseName = $SQLDatabase; Edition = $Edition;

    };

    New-AzureRmSqlDatabase @_SqlDatabase;

    }

    else

    {

    Write-Host $notexists -ForegroundColor Yellow

    }

    Get-AzureRmSqlDatabase gets the given Azure SQL database. If the given Azure SQL database doesn't exist, an error is returned. The error returned is stored in the notexists variable.

    New-AzureRmSqlDatabase provisions the new Azure SQL database if the notexists variable isn't empty.

13. Copy and paste the following code to add the system's public IP address to the Azure SQL Server firewall rule:

    $startip = (Invoke-WebRequest http://myexternalip.com/ raw --UseBasicParsing -ErrorVariable err -ErrorAction SilentlyContinue).Content.trim()

    $endip=$startip

    Write-host "Creating firewall rule for $azuresqlservername with StartIP: $startip and EndIP: $endip " -ForegroundColor Green

    $NewFirewallRule = @{ ResourceGroupName = $ResourceGroup; ServerName = $SQLServer; FirewallRuleName = 'PacktPub'; StartIpAddress = $startip; EndIpAddress=$endip;

    };

    New-AzureRmSqlServerFirewallRule @NewFirewallRule;

    The preceding code first gets the public IP of the system (running this PowerShell script) by calling the http://myexternalip.com/raw website using the Invoke-WebRequest command. The link returns the public IP in text format, which is stored in the $startip variable.

    The IP is then used to create the firewall rule by the name of PacktPub using the New-AzureRmSqlServerFirewallRule cmdlet.

14. To run the PowerShell script, perform the following steps: Press Window + R to open the Run command window. Type PowerShell and hit Enter to open a new PowerShell console window.
15. Change the directory to the folder that has the shard-toystore.ps1 script. For example, if the script is in the C:\Code\Lesson01\ directory, then run the following command to switch to this directory:

    cd C:\Code\Lesson01

16. In the following command, change the parameter values. Copy the command to the PowerShell console and hit Enter:

    .\ProvisionAzureSQLDatabase.ps1 -ResourceGroup toystore -SQLServer toyfactory -UserName sqladmin -Password Packt@pub2 -SQLDatabase toystore -AzureProfileFilePath C:\Code\MyAzureProfile.json

    The preceding command will create the toystore resource group, the toyfactory Azure SQL server, and the toystore Azure SQL database. It'll also create a firewall rule by the name of PacktPub with the machine's public IP address.

Exercise: Provisioning a Managed Instance

To provision a managed instance using a PowerShell script, perform the following steps:

1. Create a file called ProvisionSQLMI.ps1 and add the following code:

   <#

   Managed Instance is not supported in Visual Studio Enterprise subscription.

   If you are using Pay-as-you-go subscription, do check the managed instance cost

   #>

   param(

   [string]$ResourceGroup="Packt-1",

   [string]$Location="centralus",

   [string]$vNet="PackvNet-$(Get-Random)",

   [string]$misubnet="PackSubnet-$(Get-Random)",

   [string]$miname="Packt-$(Get-Random)",

   [string]$miadmin="miadmin",

   [string]$miadminpassword,

   [string]$miedition="General Purpose",

   [string]$mivcores=8,

   [string]$mistorage=32,

   [string]$migeneration = "Gen4",

   [string]$milicense="LicenseIncluded",

   [string]$subscriptionid="f0193880-5aca-4fbd-adf4-953954e4fdd7"

   )

2. Add the following code to log in to Azure:

   # login to azure

   $Account = Connect-AzAccount

   if([string]::IsNullOrEmpty($subscriptionid))

   {

      $subscriptionid=$Account.Context.Subscription.Id

   }

   Set-AzContext $subscriptionid

3. Add the following code snippet to verify that the resource group exists:

   # Check if resource group exists

   # An error is returned and stored in notexists variable if resource group exists

   Get-AzResourceGroup -Name $ResourceGroup -Location $location -ErrorVariable notexists -ErrorAction SilentlyContinue

4. Provision a resource group:

   #Provision Azure Resource Group

   if(![string]::IsNullOrEmpty($notexists))

   {

   Write-Host "Provisioning Azure Resource Group $ResourceGroup" -ForegroundColor Green

   $_ResourceGroup = @{

     Name = $ResourceGroup;

     Location = $Location;

     }

   New-AzResourceGroup @_ResourceGroup;

   }

   else

   {

   Write-Host $notexists -ForegroundColor Yellow

   }

   Write-Host "Provisioning Azure Virtual Network $vNet" -ForegroundColor Green

   $obvnet = New-AzVirtualNetwork -Name $vNet -ResourceGroupName $ResourceGroup -Location $Location -AddressPrefix "10.0.0.0/16"

   Write-Host "Provisioning Managed instance subnet $misubnet" -ForegroundColor Green

   $obmisubnet = Add-AzVirtualNetworkSubnetConfig -Name $misubnet -VirtualNetwork $obvnet -AddressPrefix "10.0.0.0/24"

   $misubnetid = $obmisubnet.Id

   $_nsg = "mi-nsg"

   $_rt = "mi-rt"

   Write-Host "Provisioning Network Security Group" -ForegroundColor Green

   $nsg = New-AzNetworkSecurityGroup -Name $_nsg -ResourceGroupName $ResourceGroup -Location $Location -Force

   <#

   Routing table is required for a managed instance to connect with 

   Azure Management Service. 

   #>

   Write-Host "Provisioning Routing table" -ForegroundColor Green

   $routetable = New-AzRouteTable -Name $_rt -ResourceGroupName $ResourceGroup -Location $Location -Force

5. Assign a network security group to the managed instance subnet:

   #Assign network security group to managed instance subnet

   Set-AzVirtualNetworkSubnetConfig '

   -VirtualNetwork $obvnet -Name $misubnet '

   -AddressPrefix "10.0.0.0/24" -NetworkSecurityGroup $nsg '

   -RouteTable $routetable | Set-AzVirtualNetwork

6. Configure the network rules in the network security group by adding the following code:

   #Configure network rules in network security group

   Get-AzNetworkSecurityGroup -ResourceGroupName $ResourceGroup -Name $_nsg '

    | Add-AzNetworkSecurityRuleConfig '

                         -Priority 100 '

                         -Name "allow_management_inbound" '

                         -Access Allow '

                         -Protocol Tcp '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix * '

                         -DestinationPortRange 9000,9003,1438,1440,1452 '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 200 '

                         -Name "allow_misubnet_inbound" '

                         -Access Allow '

                         -Protocol * '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix "10.0.0.0/24" '

                         -DestinationPortRange * '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 300 '

                         -Name "allow_health_probe_inbound" '

                         -Access Allow '

                         -Protocol * '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix AzureLoadBalancer '

                         -DestinationPortRange * '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 1000 '

                         -Name "allow_tds_inbound" '

                         -Access Allow '

                         -Protocol Tcp '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix VirtualNetwork '

                         -DestinationPortRange 1433 '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 1100 '

                         -Name "allow_redirect_inbound" '

                         -Access Allow '

                         -Protocol Tcp '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix VirtualNetwork '

                         -DestinationPortRange 11000-11999 '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 4096 '

                         -Name "deny_all_inbound" '

                         -Access Deny '

                         -Protocol * '

                         -Direction Inbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix * '

                         -DestinationPortRange * '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 100 '

                         -Name "allow_management_outbound" '

                         -Access Allow '

                         -Protocol Tcp '

                         -Direction Outbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix * '

                         -DestinationPortRange 80,443,12000 '

                         -DestinationAddressPrefix * '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 200 '

                         -Name "allow_misubnet_outbound" '

                         -Access Allow '

                         -Protocol * '

                         -Direction Outbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix * '

                         -DestinationPortRange * '

                         -DestinationAddressPrefix "10.0.0.0/24" '

   | Add-AzNetworkSecurityRuleConfig '

                         -Priority 4096 '

                         -Name "deny_all_outbound" '

                         -Access Deny '

                         -Protocol * '

                         -Direction Outbound '

                         -SourcePortRange * '

                         -SourceAddressPrefix * '

                         -DestinationPortRange * '

                         -DestinationAddressPrefix * '

   | Set-AzNetworkSecurityGroup                    

7. Update the routing table configuration:

   #update the routing table configuration.

   Get-AzRouteTable '

       -ResourceGroupName $ResourceGroup '

       -Name $_rt '

       | Add-AzRouteConfig '

       -Name "ToManagedInstanceManagementService" '

       -AddressPrefix 0.0.0.0/0 '

       -NextHopType Internet '

       | Add-AzRouteConfig '

       -Name "ToLocalClusterNode" '

       -AddressPrefix "10.0.0.0/24" '

       -NextHopType VnetLocal '

       | Set-AzRouteTable

8. Add the following code to provision a managed instance:

   # Provision managed instance

    $creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $miadmin, (ConvertTo-SecureString -String $miadminpassword -AsPlainText -Force)

   New-AzSqlInstance -Name $miname -ResourceGroupName $ResourceGroup -Location $Location -SubnetId $misubnetid '

                         -AdministratorCredential $creds '

                         -StorageSizeInGB $mistorage -VCore $mivcores -Edition $miedition '

                         -ComputeGeneration $migeneration -LicenseType $milicense

   <#

   Clean-Up : Remove managed instance

   Remove-AzSqlInstance -Name $miadmin -ResourceGroupName $ResourceGroup -Force

   #>

   Note

   The PowerShell script is self-explanatory. Review the comments in the script to understand what each command is used for.

9. Open a new PowerShell console window. Set the directory to the one containing the ProvisionSQLMI.ps1 file.
10. Copy and paste the following command in the PowerShell window:

    .\ProvisionSQLMI.ps1 -ResourceGroup Packt1 -Location westus2 -vNet mi-vnet -misubnet mi-subnet -miname packtmi -miadmin miadmin -miadminpassword Thisismypassword$12345 -miedition "General Purpose" -mivcores 8 -mistorage 32 -migeneration Gen4 -milicense LicenseIncluded

    You may change the parameter values if you wish to.

    Note

    If you have more than one subscription, specify the subscription ID in the preceding command for the parameter subscription ID.

    This will create a new SQL managed instance with all the required network specifications.

    Note

    It may take more than 3 hours to provision the first SQL managed instance.

11. Once you are done with the managed instance, execute the following command to delete it:

    Remove-AzSqlInstance -Name $miadmin -ResourceGroupName $ResourceGroup -Force

    This command expects the managed instance name and the resource group to delete that managed instance.

Summary

This lesson was an introduction to the SQL DBaaS offering from Microsoft. We learned about the Azure SQL Database architecture and the different layers that make up the Azure SQL Database infrastructure.

We also learned about the request flow through the different layers when a user connects to and queries an Azure SQL database. We learned how to connect to and query a database from SQL Server Management Studio and the Azure portal.

We learned about the latest Azure SQL Database offering, Azure managed instance, and also learned how to provision a managed instance using PowerShell.

Most importantly, the lesson covered the differences between an on-premises SQL database and an Azure SQL database, along with the unsupported features on an Azure SQL database.

In the next lesson, we will discuss how to migrate data from an on-premises system to an Azure SQL database.