Chapter 5
Provisioning Azure SQL Database

This chapter delves into Microsoft Azure SQL Database, the Microsoft SQL Server–compatible relational database offering in the Microsoft Azure cloud. Azure SQL Database is designed so that cloud applications can take advantage of relational database services without the overhead of managing the actual database engine. Azure SQL Database is also designed to meet requirements from hosting a single database associated with an organization’s line-of-business application to hosting thousands of databases for a global software-as-a-service offering. In this chapter, we look at basic Azure SQL Database concepts and how to provision databases and manage them.

We first introduce fundamental concepts of Azure and database-as-a-service. Next, you’ll learn how to create your first server and database. These sections include thorough coverage of the available options and why each one matters. Also covered are the current limitations of Azure SQL Database compared to SQL Server 2017. You will find there aren’t many left, but, still, a successful deployment requires planning for missing features.

Security must be on your checklist when deploying any database, and perhaps even more so in the cloud. This chapter includes coverage of all the security features specific only to Azure SQL Database. For security features common to SQL Server, refer to Chapter 6 and Chapter 7. This chapter then reviews features designed to prepare your cloud-hosted database for disaster recovery. Finally, we present an overview of different methods that you can use to move on-premises SQL Server databases to Azure SQL Database.

Throughout the chapter, you will also find many PowerShell samples to complete tasks. This is important because the flexibility of cloud computing offers quick setup and teardown of resources. Automation through scripting becomes a must-have skill—unless you prefer to work overtime clicking around in the web GUI. If you need an introduction to PowerShell, a solid place to start is https://docs.microsoft.com/powershell/scripting/getting-started/getting-started-with-windows-powershell.

One area that is beyond the scope for this book is creating an Azure subscription. There are many options for creating an Azure subscription and there are ways to obtain free monthly Azure credits for developers or IT professionals. You can consult the resources at https://azure.microsoft.com/get-started/ to begin your journey to the cloud.

Under some Azure subscription models, you are incentivized for committing to a minimum annual spend in return for a reduced service cost. It is important to note that OpEx might not be cheaper than CapEx overall—that depends on how efficiently services are provisioned and used. Those considerations are beyond the scope of this text, but we strongly encourage you to plan for optimizing your resource allocation early.

The second concept in cloud computing is elasticity. Elasticity means that the resources you provision are not fixed in terms of capacity. In on-premises environments, you would provision hardware and software (licensing) sufficient to accommodate peak demand. In Azure, elasticity gives you the ability to scale up and down or out and in as needed to accommodate demand at any given moment.

Finally, control also becomes a discussion topic. With on-premises deployments of SQL Server, the DBA team decides which hardware to select, when to apply patches, and when to upgrade to a major new release. With Azure SQL Database, it’s the team at Microsoft that makes these decisions. The team announces major changes and updates using a variety of channels, and, as a cloud DBA, one of your tasks will include regularly reviewing these announcements. You will need to thoroughly understand your Azure environment to determine which changes or updates will affect your application(s).

There is an alternative way to host SQL Server databases in the cloud, which is using Azure VM images, which can come with a SQL Server version preinstalled. In that case, you are using IaaS. With IaaS, you gain increased control and complete feature parity with on-premises deployments. IaaS also introduces more responsibility for sizing the VM specifications appropriately and managing software updates for both the operating system (OS) and SQL Server.

We encourage you to consider the implications of choosing between IaaS and PaaS for hosting your databases in the cloud. A discussion of these implications is available in Chapter 3.

For managing Azure and Azure SQL Database using PowerShell, you should always use the latest Azure PowerShell module. The module is updated frequently, so be sure to check for updates regularly. You can install the PowerShell module using the following PowerShell command, run with Administrator privileges:

Install-Module AzureRM

If you need to update the module, use the following:

Update-Module AzureRM

If you prefer a GUI to install or update the Azure PowerShell module, use the Web Platform Installer (WebPI) from http://www.microsoft.com/web/downloads/platform.aspx. If you use the WebPI to install the module, you also need to use WebPI to update it. The PowerShell Update-Module cmdlet will not be able to update the module if it was installed using the WebPI.

After installing or updating the module, you might want to test whether you can successfully connect to your Azure subscription. Use the following command to sign in to Azure using a Microsoft account or an Azure Active Directory account:

Login-AzureRmAccount

The Login-AzureRmAccount cmdlet will output the active subscription. If you need to switch the subscription, you can use the Get-AzureRmSubscription cmdlet to see a list of all subscriptions your account can manage. You can then use the Select-AzureRmSubscription cmdlet to change the active subscription to another one. This is illustrated using the commands that follow and assumes that you have a subscription with the name “Pay-As-You-Go.”

Click here to view code image

Get-AzureRmSubscription
Select-AzureRmSubscription -SubscriptionName 'Pay-As-You-Go'

Azure resources are organized in a hierarchy of containers. The container at the top level is the subscription. The subscription is primarily a billing boundary—all resources in a single subscription appear on a single bill and have the same billing cycle. There are also life cycle consequences: should a subscription be discontinued, all resources within the subscription will stop. (Eventually, the subscription will be deleted.) Security configuration is also associated with the subscription: a subscription trusts a single Azure Active Directory (Azure AD) instance. This means that all user accounts used to manage resources within the subscription must exist within the trusted Azure AD instance. Microsoft accounts or user accounts from other Azure AD instances can be added as external users to the trusted instance. An organization can choose to have multiple Azure subscriptions that trust the same Azure AD instance.

A single subscription can have many resources of several types, of which Azure SQL Database is just one. To allow organizing these resources by life cycle and to provide a security boundary, resource groups exists. Resource groups are logical containers that have a name and a little metadata. The resources in a resource group are deleted if the resource group itself is deleted, hence the life cycle relationship between the resources and the resource group. Using Role-Based Access Control, permissions can be granted on a resource group and those permissions will apply to the resources within the group. Configuring permissions this way can be a huge timesaver and increase visibility into permission assignments. This is discussed in more detail in the section “Security in Azure SQL Database” later in the chapter.

Figure 5-1 illustrates the relationship between the subscription, resource group, and resource.

In addition to missing several features of an on-premises SQL Server, there are also features that are unique to logical SQL servers: firewall configuration and elastic pools to name just two.

You can find more information about firewall configuration later in this chapter. We covered elastic pools in Chapter 3.

For now, you should consider that your logical SQL server determines the geographic region where your data will be stored. When a single server hosts multiple databases, these databases are collocated in the same Azure region, but they might not be hosted on the same hardware. That is of no consequence to you when using the databases, but the point serves to illustrate the purely logical nature of the concept.

A DTU is a blended measure of hardware resources that are provided for the database. This blend includes CPU, memory, and data and transaction log I/O. An increase in DTU results in a linear increase in each of the hardware resources. Thus, when doubling the DTUs for a database, you are effectively doubling how much CPU, memory, and I/O is assigned to your database. The relative mix of these hardware measures was determined by Microsoft using a benchmark developed for this purpose. This benchmark is called the Azure SQL Database Benchmark. It is designed to be representative of common Online Transaction Processing (OLTP) workloads.

To read a detailed description of the benchmark, go to https://docs.microsoft.com/azure/sql-database/sql-database-benchmark-overview.

As you’ll learn in the section on provisioning Azure SQL Database, when creating a database, you specify the number of DTUs for that database by specifying the pricing tier and service objective. Additional differences between the pricing tiers are also discussed in that section.

When managing many databases, each with potentially many different scaling needs, you should also consider running these databases in an elastic pool. An elastic pool is a grouping of databases on a single logical server that share the DTUs available within the pool. We discuss elastic pools in depth in the section “Provisioning an elastic pool” later in the chapter.

Elastic scale in Azure SQL Database is also achieved using the elastic database tools client library which is available for .NET and Java applications. Applications developed using this library can save their data in different Azure SQL databases based on data-dependent routing rules while retaining the ability to run SELECT queries across all shards. This is a popular model for SaaS applications because you can assign each SaaS customer its own database.

You can find more information on the elastic database tools at https://docs.microsoft.com/azure/sql-database/sql-database-elastic-scale-get-started.

You’ll be interested to know that provisioning a server does not incur usage charges. Azure SQL Database is billed based on the DTUs assigned to each database or elastic pool. The server acts only as a container for databases and provides the connection endpoint. This is also why there are no performance or sizing specifications attached to a server.

• Server name. The server name becomes the DNS name of the server. The domain name is fixed to database.windows.net. This means that your server name must be globally unique and lowercase. There are also restrictions as to which characters are allowed, though those restrictions are comparable to on-premises server names.

• Server admin login. This user name is best compared to the “sa” account in SQL Server. However, you cannot use “sa” or other common SQL Server identifiers for this user name; they are reserved for internal purposes. You should choose a generic name rather than a name derived from your name because you cannot change the server admin login later.

• Password. Unlike on-premises SQL Server, it’s not possible to turn off SQL Server Authentication. Therefore, the password associated with the server admin login should be very strong and carefully guarded. Unlike the login itself, Azure users with specific roles can change it.

You can read more about Role-Based Access Control to your Azure SQL Database resources later in the chapter.

• Subscription. The subscription in which you create the server determines which Azure account will be associated with databases on this server. Database usage charges are billed to the subscription.

• Resource group. The resource group where this server will reside. Review the section on Azure governance earlier in the chapter to learn about the importance of resource groups.

• Location. This is the Azure region where the database(s) are physically located. Azure SQL Database is available in most regions worldwide. You should carefully consider the placement of your servers and, by consequence, databases. Your data should be in the same region as the compute resources (Azure Web Apps, for example) that will read and write the data. When you locate the applications that connect to the server in the same region, you can expect latency in the order of single-digit milliseconds.

When creating a new server using the Azure portal, the new server’s firewall allows connections from all Azure resources. In the current GUI, the check box indicating this setting is unavailable. You can read more about the firewall in the section “Server and database-level firewall” later in the chapter. We recommend that you configure the firewall to allow connections from known IP addresses only before deploying any databases.

Click here to view code image

$resourceGroupName = 'SSIO2017'
$serverName = $resourceGroupName.ToLower()
$Cred = Get-Credential -UserName dbadmin -Message "Pwd for server admin"
New-AzureRmSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location -SqlAdministratorCredentials $Cred

In this script, the Get-Credential cmdlet is used to obtain the password for the dbadmin server administrator. This cmdlet opens a dialog box that asks for the password. All values needed to create a server are provided as parameters to the New-AzureRmSqlServer cmdlet.

Using SQL Server Management Studio 17 as an example to connect to the newly created server, Figure 5-2 shows the different values entered in the Connect To Server dialog box. When you first establish the connection, you will be prompted by SQL Server Management Studio to create a firewall rule to allow this connection (see Figure 5-3). You will need to sign in with your Azure account to create the firewall rule.

Connections to Azure SQL Database are always encrypted, even if it is not specified in the connection string. For older client libraries, you might need to specify encryption explicitly in the connection string because these libraries might not support the automatic upgrade of the connection. If you neglect to specify it explicitly, you will receive an error message. Use Encrypt=True in the connection string if needed.

You might be tempted to look up the IP address of your server and use the IP address instead of the FQDN to connect. This is not recommended, because the IP address for the server is really the IP address of a connection gateway. This IP address is subject to change at any time as the Azure fabric conducts updates or failovers.

Because there is no cost associated with maintaining a server, likely the only reason to delete one is when you have reached the limit of servers permitted in a single subscription. As of this writing, that limit is six servers, but you can request an increase by contacting Azure Support.

You can create a database from one of three sources: Blank, Sample, or Backup. A Blank database is just that: there are no user database objects. If you choose Sample (AdventureWorksLT), the new database will have the lightweight Adventure Works schema and data. If you choose Backup, you can restore the most recent daily backup of another Azure SQL Database in the subscription. The sections that follow discuss the process of provisioning a database using the Azure portal, PowerShell, Azure CLI, and Transact-SQL (T-SQL).

• Database name. The database name must be unique within the server and meet all requirements for a database name in SQL Server.

• Subscription. Select the subscription that will be used to bill the charges for this database. The subscription you select here will narrow down the list of server choices later. This parameter is not shown when the process is started from a server.

• Resource group. Here, you will choose to create a new resource group or use an existing one. If you choose to create a new resource group, you will also need to create a new server. But note that choosing an existing resource group will not narrow the list of server choices later.

• Collation. The collation selected here becomes the database’s default collation. Unlike on-premises, there is no GUI to set the new database’s collation name. You will need to correctly type the collation name from memory or refer to a list of valid SQL Server collation names.

• Source. You select one of three values that match the aforementioned options: Blank database, Sample (AdventureWorksLT), or Backup.

• Backup. You will be prompted to provide this only when you’ve selected Backup as the source of the new database. The database you select will be restored to its most recent daily backup, which means it might be up to 24 hours old.

You can read more about options for restoring database backups in the section “Understanding default disaster recovery features” later in the chapter.

• Server. You can select an existing server in the selected subscription or create a new server. The existing servers that are listed are filtered to the subscription you selected earlier, but not to the resource group. If you select a server in a different resource group than the group you selected earlier, the resource group value will be updated automatically to reflect the correct selection. That is because the life cycle of a database is tied to the life cycle of the server, and the life cycle of the server is tied to the resource group. Therefore, a database cannot exist in a different resource group than its server. This server value is locked when the process is started from a logical SQL server.

• Pricing tier. When creating a standalone database, you need to select a pricing tier. The pricing tier determines the hourly usage charges and several architectural aspects of your database. We discuss pricing tiers in a later section. It is possible to mix pricing tiers within a server, underscoring the notion that the server is a mere logical container for databases and has no relationship to any performance aspects. While selecting the pricing tier, you also can set a maximum database size. Your database will not be able to run INSERT or UPDATE T-SQL statements when the maximum size is reached.

• Elastic database pool. You can make this selection only when adding this database to an existing or new elastic database pool. We discuss elastic pools in detail in the section “Provisioning an elastic pool” later in the chapter.

Click here to view code image

$resourceGroupName = 'SSIO2017'
$serverName = $resourceGroupName.ToLower()
$databaseName = 'Contoso'
New-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -DatabaseName $databaseName -Edition Standard `
    -RequestedServiceObjectiveName "S0" -CollationName Latin1_General_CI_AS

Other optional parameters include the following:

• CatalogCollation. This collation parameter determines the collation of character data in the database’s metadata catalog. Note that you cannot set this database property in the GUI. This value defaults to SQL_Latin1_General_CP1_CI_AS, which is different from SQL Server, where it defaults to DATABASE_DEFAULT.

• ElasticPoolName. When specified, this database will be added to the existing elastic pool on the server. The next section covers elastic pools.

• MaxSizeBytes. Sets the maximum database size in bytes. You cannot set just any value here; there is a list of supported maximum sizes. The available maximum sizes depend on the selected pricing tier.

• SampleName. Specify AdventureWorksLT if you want the database to have the available sample schema and data.

• Tags. This parameter is common to many Azure cmdlets. You can use it to specify an arbitrary number of name–value pairs. Tags are used to add custom metadata to Azure resources. You can use both the Azure portal and PowerShell to filter resources based on tag values. You can also obtain a consolidated billing view for resources with the same tag values.

  A usage example is -Tags @{"Tag1"="Value 1";"Tag 2"="Value 2"}, which would associate two name–value pairs to the database. The name of the first tag is Tag1 with Value 1, and the name of the second tag is Tag 2 with Value 2.

After creating the database, you can retrieve information about it by using the
Get-AzureRmSqlDatabase cmdlet, as shown here:

Click here to view code image

Get-AzureRmSqlDatabase -ResourceGroupName SSIO2017 -ServerName ssio2017 `
    -DatabaseName Contoso

The Azure CLI command that follows creates a database with the same parameters as those found in the preceding PowerShell script. After creating the database, the new database’s properties are retrieved.

You can find the full list of supported CLI commands for Azure SQL Database at https://docs.microsoft.com/cli/azure/sql/db.

Click here to view code image

az sql db create --resource-group SSIO2017 --server ssio2017
    --name Contoso --collation Latin1_General_CI_AS
    --edition Standard --service-objective S0
az sql db list --resource-group SSIO2017 --server ssio2017
    --name Contoso

Click here to view code image

CREATE DATABASE Contoso COLLATE Latin1_General_CI_AS
    (EDITION = 'standard', SERVICE_OBJECTIVE = 'S0');

Because the T-SQL command is run in the context of a server, you do not need to, nor can you, provide a server name or resource group name. You cannot use T-SQL to create a database based on the AdventureWorksLT sample, but you can use it to restore a database from a backup using the AS COPY OF clause, as shown here:

Click here to view code image

CREATE DATABASE Contoso_copy AS COPY OF Contoso;

The Basic tier provides the lowest available DTUs. For giving up some availability guarantees and performance, you also pay significantly less. This tier is suitable for development purposes and perhaps very small-scale applications.

The Standard and Premium tiers are the two main choices for production databases. At first glance, you will notice that the Premium tier provides considerably more DTUs and does so at a higher cost per DTU compared to Standard. This is because of architectural differences between these tiers. The database files in Standard tier databases are stored in Azure blob storage. This means that the files are not local to the database engine. In the Premium tier, they are stored on local solid-state drives (SSDs). This difference in locality of the database files has performance implications, as you might expect. Further, there is also a difference in how intraregion high availability (HA) is handled. HA for Standard tier databases is ensured by using replication of the Azure blobs. In the Premium tier, HA is achieved by using Always On features.

Finally, the Premium RS tier provides the same performance objectives as the Premium tier but at a lower cost. This is due to a less-stringent Service-Level Agreement (SLA) for the Premium RS tier. You should consider using this tier for scenarios in which performance trumps availability, such as development and test or during migration activities.

Scaling down might not be possible if the database size exceeds the maximum allowed size for the lower service objective or pricing tier. If you know that you will likely scale down your database, you should set the maximum database size to a value equal to or less than the maximum database size for the service objective to which you might scale down.

Scaling is always initiated by an administrator. Unlike some Azure services, there is no autoscale functionality in Azure SQL Database. You could, however, consider deploying databases to an elastic pool (discussed in the next section) to achieve automatic balancing of resource demands for a group of databases. Another option to scale without administrator intervention would be to use Azure Automation to monitor DTU usage and within defined limits initiate scaling. You can use the PowerShell Set-AzureRmSqlDatabase cmdlet to set a new pricing tier by using the -Edition parameter, and a new service objective by using the -RequestedServiceObjectiveName parameter.

You can create elastic pools in the Azure portal, by using PowerShell, the Azure CLI, or the REST API. After an elastic pool is created, you can create new databases directly in the pool. You also can add existing databases to the pool or move them out of it.

In most of the next sections, no distinction is made between standalone databases or elastic pool databases. Management of standalone databases is not different from management of databases in elastic pools. Also, whether a database is in an elastic pool or standalone makes no difference for establishing a connection.

To create a new elastic pool on the ssio2017 server and move the existing Contoso database to the pool, use the following PowerShell script:

Click here to view code image

$resourceGroupName = 'SSIO2017'
$serverName = $resourceGroupName.ToLower()
$databaseName = 'Contoso'
$poolName = 'Contoso-Pool'
# Create a new elastic pool
New-AzureRmSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName `
    -Edition 'Standard' -Dtu 50 `
    -DatabaseDtuMin 10 -DatabaseDtuMax 20
# Now move the Contoso database to the pool
Set-AzureRmSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -DatabaseName $databaseName `
    -ElasticPoolName $poolName

This script creates a new pool named Contoso-Pool in the Standard tier and provides 50 total eDTUs. A single database will be assigned no less than 10 DTU and no more than 20 DTU. The parameters -Dtu, -DatabaseDtuMin and -DatabaseDtuMax have a list of valid values depending on the selected tier and one another.

To provide predictable performance for Azure SQL Database, there are limits to the number of concurrent requests, concurrent logins, and concurrent sessions. These limits differ by service tier and service objective. If any limit is reached, the next connection or query attempt will fail with error code 10928.

You can find an exhaustive list of these operational limits online at https://docs.microsoft.com/azure/sql-database/sql-database-resource-limits.

One final limitation to be aware of is that a single server has an upper limit on the total DTUs it can host as well as on the total number of databases. For a large deployment, this might require distributing databases across servers. We recommend against operating at or near this limit because overall performance can become suboptimal. As of this writing, you should limit the number of databases per server to around 1,000.

• SQL Server Agent. To schedule recurring tasks for Azure SQL Database instances, DBAs should consider using Azure Automation. Azure Automation is a service that makes it possible for you to reliably run potentially long-running PowerShell scripts. You can use Azure Automation to automate management of any Azure or third-party cloud service, including Azure SQL Database. In addition, there is a gallery of reusable scripts available.

You can find more information about using Azure Automation with Azure SQL Database at https://docs.microsoft.com/azure/sql-database/sql-database-manage-automation. You can find an introduction to Azure Automation at https://docs.microsoft.com/azure/automation/automation-intro.

• SQL Server Integration Services. Instead of SQL Server Integration Services, you would use Azure Data Factory to perform tasks such as extracting data from various sources, transforming it by using a range of services, and finally publish it to data stores for consumption by business intelligence tools or applications. We should note that you can use SQL Server Integration Services to extract data from and load data to Azure SQL Database, and that you can use Data Factory to extract data from and load data to on-premises data stores. The decision about which service to use depends largely on where most of your data resides, which services you plan on using for transforming the data, and whether you allow a cloud service to connect to your on-premises environment using a gateway service. Recently, Microsoft also announced the Azure-SSIS integration runtime in Data Factory, which you can use to deploy SQL Server Integration Services packages.

You can learn more about Data Factory at https://azure.microsoft.com/services/data-factory/.

• SQL Server Reporting Services. Several years ago, Microsoft offered a reporting service in Azure that was highly compatible with SQL Server Reporting Services: SQL Reporting Services. It was discontinued, however, and instead Microsoft Power BI is now recommended. Power BI is a powerful tool to create interactive visualizations using data from various sources. You can embed Power BI dashboards and reports in applications. You can also access them directly using a web browser or mobile app.

You can learn more about Power BI at https://powerbi.microsoft.com.

• SQL Server Analysis Services. To replace SQL Server Analysis Services, there are several alternative Azure services. Foremost, there is Azure Analysis Services. It is built on SQL Server Analysis Services and, as such, existing tabular models can be migrated from on-premises SQL Server Analysis Services deployments to the cloud.

You can learn more about Azure Analysis Services at https://docs.microsoft.com/azure/analysis-services/analysis-services-overview.

Second, for data warehousing terabytes or petabytes of relational data, there is Azure SQL Data Warehouse. SQL Data Warehouse is based on SQL Server and provides a compatible interface. Compared with Azure SQL Database, however, SQL Data Warehouse can scale storage and compute independently. This makes SQL Data Warehouse suitable for storing enormous amounts of data that might only occasionally need to be processed.

To learn more about Azure SQL Data Warehouse go to https://azure.microsoft.com/services/sql-data-warehouse/.

Another alternative to SQL Server Analysis Services is Azure Data Lake. Data Lake is much more extensive than SQL Server Analysis Services, but we mention it here because of its ability to capture data and prepare it for analytical workloads. Unlike SQL Server Analysis Services, though, Data Lake can store unstructured data in addition to relational data.

You can learn more about Data Lake at https://docs.microsoft.com/azure/data-lake-store/data-lake-store-overview.

In terms of achieving near 100 percent compatibility with SQL Server, a managed instance provides features not available in standard Azure SQL databases, including the following:

• SQL CLR

• SQL Server Agent

• Cross-database and cross-instance queries and transactions

• FILESTREAM and FileTable

Most notable in terms of added functionality, managed instances run in an Azure Virtual Network. This Virtual Network can act as a network boundary between the Azure resources within the Virtual Network and those outside (including other Azure customers’ resources). You also can use a Virtual Network to create a LAN-like network environment between your on-premises environment and the Azure cloud.

Other aspects of security must be managed by you, the cloud DBA. Some of these aspects, such as Transparent Data Encryption (TDE) are shared with on-premises SQL Server 2017. Others are specific to Azure SQL Database and include firewall configuration, access control, and auditing and threat detection. We discuss these features of Azure SQL Database in the upcoming sections. Microsoft’s commitment regarding Azure SQL Database is to not differentiate the tiers with security features. All the features discussed in this section are available in all pricing tiers, though some require additional charges.

TDE is on by default for any new database. This hasn’t always been the case, so if your database has been around for a long time, you should verify whether it is turned on. When TDE is on for a database, not only are the database files encrypted, but the geo-replicated backups are also encrypted. You will learn more about backups in the section “Preparing Azure SQL Database for disaster recovery” later in the chapter. TDE is covered in Chapter 7.

Other security features shared with SQL Server 2017 are dynamic data masking, row-level security, and Always Encrypted. Chapter 7 looks at these features in detail.

When creating a new server using the Azure portal, by default any Azure resource is permitted through the server-level firewall. This might appear convenient, but it leaves the server open to unauthorized connection attempts from an attacker who merely needs to create an Azure service such as a web app. Servers created using other methods—for example, PowerShell—do not have any default firewall rules, which means any connection attempt is refused until at least one firewall rule is created.

Database-level firewall rules take precedence over server firewall rules. After you have created database firewall rules, you can remove the server firewall rule(s) and still connect to the database. However, if you will be hosting several databases that need to accept connections from the same IPs on a single server, keeping the firewall rules at the server level might be more sensible. It is also convenient to keep server-level firewall rules in place for administrative access.

You can find server-level firewall rules in the virtual master database in the sys.firewall_rules catalog view. Database-level firewall rules are in the user database in the sys.database_firewall_rules catalog view. This makes the database more portable, which can be advantageous in combination with contained users. Especially when using geo-replication, which we discuss in the section “Preparing Azure SQL Database for disaster recovery” coming up later in the chapter, having portable databases avoids unexpected connection issues when failing-over databases to another server.

You can learn more about contained databases in Chapter 6.

• Rule name. The rule name has no impact on the operation of the firewall; it exists only to create a human-friendly reminder about the rule. The rule name is limited to 128 characters. The name must be unique in the server.

• Start IP address. The first IPv4 address of the range of allowed addresses.

• End IP address. The end IPv4 address can be the same as the start IP address to create a rule that allows connections from exactly one address. The end IP address cannot be lower than the start IP address.

If you want to create a server-level firewall rule that allows access from any Azure resource, you would create a rule using 0.0.0.0 as both the start and end IP address of the rule. Using PowerShell, the New-AzureRmSqlServerFirewallRule cmdlet provides the -AllowAllAzureIPs parameter as a shortcut: you do not need to provide a rule name, start, or end IP address.

You can create and manage database-level firewall rules only by using T-SQL. Azure SQL Database provides the following stored procedures to manage the rules:

• sp_set_database_firewall_rule. This stored procedure creates a new firewall rule or updates an existing firewall rule.

• sp_delete_database_firewall_rule. This stored procedure deletes an existing databaselevel firewall rule using the name of the rule.

The following T-SQL script creates a new database-level firewall rule allowing a single (fictitious) IP address and then updates the rule by expanding the single IP address to a range of addresses, and finally deletes the rule:

Click here to view code image

EXEC sp_set_database_firewall_rule N'Headquarters', '1.2.3.4', '1.2.3.4';
EXEC sp_set_database_firewall_rule N'Headquarters', '1.2.3.4', '1.2.3.6';
SELECT * FROM sys.database_firewall_rules;
EXEC sp_delete_database_firewall_rule N'Headquarters';

For cases in which the Azure AD domain is federated with an Active Directory Domain Services domain, you can achieve true SSO comparable to an on-premises experience. The latter case would exclude any external users or Microsoft accounts that have been added to the directory; only federated identities can take advantage of this. Furthermore, this also requires a client that supports it, which, as of this writing, is only SQL Server Management Studio.

To set an Active Directory admin for a server, you can use the Azure portal, PowerShell, or Azure CLI. You use the PowerShell Set-AzureRmSqlServerActiveDirectoryAdministrator cmdlet to provision the Azure AD admin. The -DisplayName parameter references the Azure AD principal. When you use this parameter to set a user account as the administrator, the value can be the user’s display name or user principal name (UPN). When setting a group as the administrator, only the group’s display name is supported.

After you set an Azure AD principal as the Active Directory admin for the server, you can create contained users in the server’s databases. Contained users for Azure AD principals must be created by other Azure AD principals. Users authenticated with SQL authentication cannot validate the Azure AD principal names, and, as such, even the server administrator login cannot create contained users for Azure AD principals. Contained users are created by using the T-SQL CREATE USER statement with the FROM EXTERNAL PROVIDER clause. The following example statements create an external user for an Azure AD user account with UPN [email protected] and for an Azure AD group Sales Managers:

Click here to view code image

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
CREATE USER [Sales Managers] FROM EXTERNAL PROVIDER;

By default, these newly created contained users will be members of the PUBLIC database role and will be granted CONNECT permission. You can add these users to additional roles or grant them additional permissions directly like any other database user. Chapter 6 has further coverage on permissions and roles.

You also can add Azure AD application identities as external users. For an application identity to access a database, a certificate must be installed on the system running the application.

You can find more information and a sample on the SQL Server Security Blog at https://blogs.msdn.microsoft.com/sqlsecurity/2016/02/09/token-based-authentication-support-for-azure-sql-db-using-azure-ad-auth/.

RBAC includes several built-in roles to which you can add Azure AD principals. The built-in roles have a fixed set of permissions. You also can create custom roles if the built-in roles do not meet your needs.

You can find a comprehensive list of built-in roles and their permissions at https://docs.microsoft.com/azure/active-directory/role-based-access-built-in-roles.

Three of the built-in roles relate specifically to Azure SQL Database:

• SQL DB Contributor. This role can primarily create and manage Azure SQL databases but not any security-related settings. For example, this role can create a new database on an existing server and create alert rules.

• SQL Security Manager. This role can primarily manage security settings of databases and servers. For example, this role can create auditing policies on an existing database but cannot create a new database.

• SQL Server Contributor. This role can primarily create and manage servers but not databases or any security-related settings.

Note that the permissions do not relate to server or database access; instead, they relate to managing the resources in Azure. Indeed, users assigned to these RBAC roles are not granted any permissions in the database, not even CONNECT permission.

You can find more details about support for extended events at https://docs.microsoft.com/azure/sql-database/sql-database-xevent-db-diff-from-svr.

Azure SQL Database auditing creates a record of activities that have taken place in the database. The types of activities that can be audited include permission changes, T-SQL batch execution, and auditing changes themselves. As of this writing, there is no official list of supported audit actions available.

Audit actions are grouped in audit action groups, and a list of audit action groups is available in the PowerShell reference for the Set-AzureRmSqlDatabaseAuditing cmdlet at https://docs.microsoft.com/powershell/module/azurerm.sqlset-azurermsqldatabaseauditing.

Auditing and threat detection are separate but related features: threat detection is possible only if auditing is turned on. The features are configured on the same blade in the Azure portal. Auditing is available at no charge, but there is a monthly fee per server for activating threat detection.

You can turn on both features at the server and database level. When auditing is turned on at the server level, all databases hosted on that server are audited. After you turn on auditing on the server, you can still turn it on at the database level, as well. This will not override any server-level settings; rather, it creates two separate audits. This is not usually desired.

Auditing logs server and database events to an Azure storage account. The Azure storage account must be in the same region as the server. This is a sensible requirement; you would not want to incur data transfer charges for the audit data or deal with the latency of such a transfer.

You also need to decide on an audit log retention period. You can choose to keep the audit logs indefinitely or you can select a retention period. The retention period can be at most nine years with a daily granularity.

The following PowerShell script sets up auditing for the Contoso database on the ssio2017 server:

Click here to view code image

$resourceGroupName = 'SSIO2017'
$location = "southcentralus"
$serverName = $resourceGroupName.ToLower()
$databaseName = 'Contoso'
$storageAccountName = "azuresqldbaudit"
# Create a new storage account
$storageAccount = New-AzureRmStorageAccount -ResourceGroupName $resourceGroupName `
    -Name $storageAccountName -Location $location -Kind Storage `
    -SkuName Standard_LRS -EnableHttpsTrafficOnly $true
# Use the new storage account to configure auditing
$auditSettings = Set-AzureRmSqlDatabaseAuditing `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -DatabaseName $databaseName `
    -StorageAccountName $storageAccountName -StorageKeyType Primary `
    -RetentionInDays 365 -State Enabled

The first cmdlet in the script creates a new storage account with the name azuresqldbaudit. Note that this name must be globally unique, so you will need to update the script with a name of your choosing before running the script. Storage account names can contain only lowercase letters and digits. (For more details on the New-AzureRmStorageAccount cmdlet, see https://docs.microsoft.com/powershell/module/azurerm.storage/new-azurermstorageaccount.) The second cmdlet, Set-AzureRmSqlDatabaseAuditing, configures and turns on auditing on the database using the newly created storage account. The audit log retention period is set to 365 days.

By default, all actions are audited. You cannot use the Azure portal to customize which events are audited; instead, you use PowerShell (or the REST API) to indicate specific action groups that you want to audit. The -AuditActionGroup parameter takes an array of strings that identify the groups to audit.

You can find the list of group names at https://docs.microsoft.com/powershell/module/azurerm.sql/set-azurermsqldatabaseauditingpolicy#optional-parameters.

You can find a full list of methods to access the audit logs at https://docs.microsoft.com/azure/sql-database/sql-database-auditing#subheading-3.

If your goal is to quickly review recent audit events, you can see the audit logs in the Azure portal. In the Auditing & Threat Detection blade for a database, click View Audit Logs to open the Audit Records blade. This blade shows the most recent audit logs, which you can filter to restrict the events shown by latest event time or show only suspect SQL injection audit records. This approach is rather limited because you cannot aggregate audit logs from different databases and the filtering capabilities are minimal.

A more advanced approach is to use SQL Server Management Studio. SQL Server Management Studio 17 and later support opening the audit logs directly from Azure storage. Alternatively, you can use the Azure Storage Explorer to download the audit logs and open them using older versions of SQL Server Management Studio or third-party tools. The audit logs are stored in the sqldbauditlogs blob container in the selected storage account. The container follows a hierarchical folder structure: logicalservernameDatabaseNameSqlDbAuditing_AuditNameyyyymmdd. The blobs within the date folder are the audit logs for that date (in Coordinated Universal Time [UTC]). The blobs are binary extended event files (.xel).

After you have obtained the audit files, you can open them in SQL Server Management Studio. On the File menu, click Open, and then click Merge Audit Files to open the Merge Audit Files dialog box, as shown in Figure 5-4.

A third way of examining audit logs is by using the sys.fn_get_audit_file T-SQL function. You can use this to perform programmatic evaluation of the audit logs. The function can work with locally downloaded files or you can obtain files directly from the Azure storage account. To obtain logs directly from the Azure storage account, you must run the query using a connection to the database whose logs are being accessed. The following T-SQL script example queries all audit events logged to the azuresqldbaudit storage account from August 29, 2017, for the Contoso database on the ssio2017 server:

Click here to view code image

SELECT * FROM sys.fn_get_audit_file ('https://azuresqldbaudit.blob.core.windows.net/sqldbauditlogs/ssio2017/Contoso/SqlDbAuditing_Audit/2017-08-29/', default, default);

You can find more information on the sys.fn_get_audit_file function at https://docs.microsoft.com/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql.

• SQL injection. This threat type detects the possible occurrence of a SQL injection attack.

• SQL injection vulnerability. This type detects the possible existence of a SQL injection vulnerability in the queries that are run.

• Anomalous client login. This type detects logins that are unusual, such as from a geographic location from which a user has not previously signed in.

You can turn off these threat types individually if you do not want to detect them.

Regularly scheduled backups are also configured by default. A full backup is scheduled weekly, differential backups take place every few hours, and transaction log backups every 5 to 10 minutes. The exact timing of backups is managed by the Azure fabric based on overall system workload and the database’s activity levels. These backups are retained for a specified period, which depends on the pricing tier.

You can use these backups to restore the database to a point-in-time within the retention period. You also can restore a database that was accidentally deleted to the same server from which it was deleted. Remember: deleting a server irreversibly deletes all databases and backups. You should generally not delete a server until the backup retention period has expired, just in case. After all, there is no cost associated with a server without databases.

You also can restore databases to another Azure region. This is referred to as a geo-restore. This restores databases from backups that are geo-replicated to other regions using Azure Storage replication. If your database has TDE turned on, the backups are also encrypted.

Although these default features provide valuable DR options, they are likely not adequate for production workloads. For example, the Estimated Recovery Time (ERT) for a geo-restore is less than 12 hours with a Recovery Point Objective (RPO) of less than one hour. Further, the maximum backup retention period is 35 days for the Standard, Premium, and Premium RS tiers, and only seven days for the Basic tier. Some of these values are likely unsuitable for mission-critical databases, so you should review the optional DR features in the next sections and configure those as needed to achieve an acceptable level of risk for your environment.

The term “backup” is somewhat inappropriate insomuch as the method to create a manual backup is exporting the database to a BACPAC file. (You can read more about BACPAC files in Chapter 4. A significant difference between a database backup and an export is that the export is not transactionally consistent. During the data export, Data Manipulation Language (DML) statements in a single transaction might have completed before and after the data in different tables was extracted. This can have unintended consequences and can even prevent you from restoring the export without dropping foreign key constraints.

Azure SQL Database can, however, provide you with a transactionally consistent export using a three-step procedure: first, make a copy of the database. The copy is guaranteed to be transactionally consistent. Then, export the copy. Because no applications are accessing this copy, no data manipulation is taking place during the export. Finally, delete the copy to avoid incurring continued charges. You can perform this procedure by using the Azure portal, but because it involves multiple steps, and some steps can be long-running, it lends itself perfectly to using a PowerShell script.

A database export’s destination is an Azure blob, so a storage account is required. The following script determines a name for the database copy based on the existing database and the current time:

Click here to view code image

# Set variables
$resourceGroupName = 'SSIO2017'
$location = "southcentralus"
$serverName = $resourceGroupName.ToLower()
$databaseName = 'Contoso'
# Create a name for the database copy
$d = (Get-Date).ToUniversalTime()
$databaseCopyName = "$databaseName-Copy-" + ($d.ToString("yyyyMMddHHmmss"))
$storageAccountName = 'azuresqldbexport'
# Ask interactively for the server admin login username and password
$cred = Get-Credential
# Create a new Azure storage account
$storAcct = New-AzureRmStorageAccount -ResourceGroupName $resourceGroupName `
    -Name $storageAccountName -Location $location `
    -SkuName Standard_LRS

# Get the access keys for the newly created storage account
$storageKey = Get-AzureRmStorageAccountKey -ResourceGroupName $resourceGroupName `
    -Name $storageAccountName
# Create a database copy - this copy will have the same tier as the original
$newDB = New-AzureRmSqlDatabaseCopy -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -DatabaseName $databaseName `
    -CopyDatabaseName $databaseCopyName
# Prepare additional variables to use as the storage location for the BACPAC
$containerName = "mydbbak"
$container = New-AzureStorageContainer -Context $storAcct.Context -Name
$bacpacUri = $container.CloudBlobContainer.StorageUri.PrimaryUri.ToString() + "/" + `
    $databaseCopyName + ".bacpac"
# Initiate a database export of the database copy - see Firewall troubleshooting
$exportRequest = New-AzureRmSqlDatabaseExport –ResourceGroupName $resourceGroupName `
    –ServerName $NewDB.ServerName –DatabaseName $databaseCopyName `
    –StorageKeytype StorageAccessKey –StorageKey $storageKey[0].Value `
    -StorageUri $bacpacUri `
    –AdministratorLogin $cred.UserName –AdministratorLoginPassword $cred.Password
# Run a loop while the export is progressing
Do {
    $exportStatus = Get-AzureRmSqlDatabaseImportExportStatus `
        -OperationStatusLink $ExportRequest.OperationStatusLink
    Write-Host "Exporting… sleeping for 5 second…"
    Start-Sleep -Seconds 5
} While ($exportStatus.Status -eq "InProgress")
# Delete the copied database to avoid further charges
Remove-AzureRmSqlDatabase –ResourceGroupName $resourceGroupName `
    –ServerName $serverName –DatabaseName $databaseCopyName

First, a new storage account is created in the same Azure region as the database server to avoid cross-region traffic charges. The script then creates the database copy on the same database server. Next, the new storage account is used for the export of the database copy. The export operation is asynchronous, so a loop is used to wait for completion of the export. Finally, when the export is completed, the database copy is deleted. As in previous scripts, the storage account name you create must be globally unique, so change the value of the variable in the script before running it.

This script produces several lines of output. At the end of the script, if everything was successful, you can use the Azure Storage Explorer to access the new storage account and download the BACPAC file for further use. Alternatively, you can leave the BACPAC file in Azure Storage and use related commands to import the database file later, should a restore become necessary.

You can configure geo-replication in any pricing tier and any region. To configure georeplication, you will need to provision a server in another region, though you can do this as part of the configuration process if you are using the Azure portal.

In the event of a disaster, you would be alerted via the Azure portal of reliability issues in the datacenter hosting your primary database. You would need to manually failover to a secondary database—using geo-replication only, there is no automatic failover (but keep reading to learn about failover groups, which do provide automatic failover capability). Failover is accomplished by selecting (one of) the secondary database(s) to be the primary.

Because the replication from primary to secondary is asynchronous, an unplanned failover can lead to data loss. The RPO for geo-replication, which is an indicator of the maximum amount of data loss expressed as a unit of time, is five seconds. Although no more than five seconds of data loss during an actual disaster is a sound objective, when conducting DR drills, no data loss is acceptable. A planned change to another region, such as during a DR drill or to migrate to another region permanently, can be initiated as a planned failover. A planned failover will not lead to data loss because the selected secondary will not become primary until replication is completed.

Unfortunately, a planned failover cannot be initiated from the Azure portal. The PowerShell cmdlet Set-AzureRmSqlDatabaseSecondary with the -Failover parameter and without the -AllowDataLoss parameter will initiate a planned failover. If the primary is not available due to an incident, you can use the portal (see Figure 5-5) or PowerShell to initiate a failover with the potential for some data loss, as just described. If you have multiple secondaries, after a failover, the new primary will begin replicating to the remaining available secondaries without a need for further manual configuration.

For each secondary database, you will be charged the same hourly charges as a primary database, with the same pricing tier and service objective. A secondary database must have the same pricing tier as its primary, but it does not need to have the same service objective or performance level. For example, a primary database in the Standard tier with service objective S2 can be geo-replicated to a secondary in the Standard tier with service objective S1 or S3, but it cannot be geo-replicated to a secondary in the Basic or Premium tier.

To decide whether your service objective for secondaries can be lower than that of the primary, you will need to consider the read-write activity ratio. If the primary is write-heavy—that is, most database operations are writes—the secondary will likely need the same service objective to be able to keep up with the primary. However, if the primary’s utilization is mostly toward read operations, you could consider lowering the service objective for the secondary. You can monitor the replication status in the Azure portal or use the PowerShell Get-AzureRmSqlDatabaseReplicationLink cmdlet to ensure that the secondary can keep up with the primary.

As of this writing, geo-replication introduces a limitation on the scalability of databases. When a primary database is in a geo-replication relationship, its pricing tier cannot be upgraded (for example, from Standard to Premium) without first upgrading all secondaries. To downgrade, you must downgrade the primary before any secondaries can be downgraded. As a best practice, when scaling up or down, you should make sure that the secondary database has the higher service objective longer than the primary. In other words, when scaling up, first scale up secondary databases; when scaling down, scale down secondary databases second.

Failover groups, currently in public preview, build on top of geo-replication to address these shortcomings. Configured at the server level, a failover group can include one, multiple, or all databases hosted on that server. All databases in a group are recovered simultaneously. By default, failover groups are set to automatically recover the databases in case of an outage, though you can turn this off. With automatic recovery turned on, you need to configure a grace period. This grace period offers a way to direct the Azure infrastructure to emphasize either availability or data guarantees. By increasing the grace period, you are emphasizing data guarantees because the automatic failover will not occur if it would result in data loss until the outage has lasted as long as the grace period. By decreasing the grace period, you are emphasizing availability. As of this writing, the minimum grace period is at least one hour, although this is expected to change to allow much shorter grace periods. In practical terms, this means that if the secondary database in the failover group is not up to date one hour after the outage, the failover will occur, resulting in data loss.

When you configure a failover group, two new DNS CNAME records are created. The first CNAME record refers to the read-write listener and it points to the primary server URL. During a failover, this record is updated automatically so that it always points to the writable replica. The read-write listener’s FQDN is the name of the failover group prepended to database.windows.net. This means that your failover group name must be globally unique. The second CNAME record points to the read-only listener, which is the secondary server’s URL. The read-only listener’s DNS name is the name of the failover group prepended to secondary.database.windows.net. If the failover group name is ssio2017, the FQDN of the read-write listener will be ssio2017.database.windows.net and the FQDN of the secondary will be ssio2017.secondary.database.windows.net.

You can create failover groups with existing geo-replication already in place. If the failover group’s secondary server is in the same region as an existing geo-replication secondary, the existing secondary will be used for the failover group. If you select a region for the failover secondary server where no replica is yet configured, a new secondary server and database will be created during the deployment process. If a new secondary database is created, it will be created in the same tier and with the same service objective as the primary.

Unlike with geo-replication, the Azure portal supports initiating a planned failover for failover groups. You can also initiate a planned failover by using PowerShell. Planned failovers will not cause data loss. Both interfaces also support initiating a forced failover, which, as with geo-replication’s unplanned failover, can lead to data loss within the five-second RPO window.

Long-term backup retention is configured at the server level, but databases on the server can be selectively included or excluded. Before you can set up long-term backup retention, you must create a Recovery Services vault in the same resource group and region. When a vault is available, you must create or select a retention policy. As its name indicates, the retention policy determines how long the weekly backups are retained in the vault. After you configure long-term backup retention, the next scheduled full backup will be the first backup added. In other words, existing backups are not added to the vault.

Each database in a single server can have a separate retention policy, but all backups from a single server are kept in the same vault, as illustrated in Figure 5-6. Pricing for Azure Backup is based on the number of protected “nodes” and the storage consumed. In the case of Azure SQL Database, a “node” is a single database, and the storage consumed is the size of the database backup file. When a database is deleted, you will continue to be charged for the vault’s contents; however, the charges will decrease over time as backup files older than the retention period are deleted.

You can configure long-term backup retention by using the Azure portal or PowerShell. Although only primary or standalone databases are backed up and will therefore be the only databases that have backups to be added to the vault, you should configure long-term backup retention on geo-replicated secondaries, as well. This ensures that in case of a failover, backups from the new primary database will be added to its vault, without further intervention. After a failover, a full backup is immediately taken and that backup is added to the vault. Until a failover takes place, no additional costs are incurred for configuring retention on the secondary server.

Among the options for migration, here are some of the most common ones:

• BACPAC. BACPAC files have been mentioned previously in this chapter for possible on-premises use. You also can use them in the reverse direction. When a BACPAC file has been generated for an on-premises database, it needs to be uploaded to a standard-tier Azure storage account. From there, you can import the BACPAC file to an existing server using the Azure portal or PowerShell. Although this migration method is simple and scalable to many databases, for large databases, the entire process might take longer than is acceptable. The Microsoft SQL Server CAT offers a lengthy article on using BACPAC for moving SQL Server to Azure SQL Database, which you can read at https://blogs.msdn.microsoft.com/sqlcat/2016/10/20/migrating-from-sql-server-to-azure-sql-database-using-bacpac-files/.

  TROUBLESHOOTING

  BACPAC imports require a firewall rule for all Azure Services

  The Azure SQL Database Import Service, which is used for the import of BACPAC files, can run anywhere in the Azure region of the destination server. Because the IP address of the host running the service is not known, you will need to open the server firewall to allow all Azure IP addresses to access the server. We discuss this in the section “Server and database-level firewall” earlier in the chapter.

  ---

• Transactional replication. Setting up replication makes it possible to move schema and data with minimal downtime. If using replication, it is also possible to migrate only a subset of the data from the source. The downside is that replication requires changing the schema of the database that is to be moved. Also, you will need to allow direct communication from your SQL Server to Azure. Chapter 12 has detailed coverage on replication with SQL Server.

• Custom development. You can transfer schema and data by using custom scripts or SQL Server Integration Services. With increasing schema complexity, the complexity of the custom development also increases. SQL Server Data Tools or third-party schema and data migration tools can help in this effort.

• Microsoft Data Migration Assistant (DMA). This option is the newest available. You can use the DMA tool to assess the schema of the source database and verify compatibility with Azure SQL Database. If issues are detected, detailed guidance on how to resolve them is provided. Finally, the tool can prepare and run a migration workflow. The tool should be at least version 3.2; earlier versions did not have the migration workflow. You can also find references to another tool: the SQL Azure Migration Wizard. This tool, however, was created for the previous iteration of Azure SQL Database (v11) and is no longer developed. It still works, but it will apply Azure SQL Database v11 compatibility rules, which are much more restrictive than the current v12.

• Azure Database Migration Service. As of this writing, this service is in preview. It is a comprehensive service that supports Azure SQL Database, managed instances, and SQL Server on Azure VMs. You can learn more about it at https://azure.microsoft.com/services/database-migration/.

When considering moving databases, you should review schema compatibility as early as possible. You can use the SQL Server Data Tools to find incompatible object definitions. By importing the database schema in SQL Server Data Tools and then changing the destination platform to Microsoft Azure SQL Database v12, SQL Server Data Tools will list the incompatible schema elements. Unlike DMA, SQL Server Data Tools does not provide guidance on resolution, though.