carloscastilla - Fotolia

Problem solve Get help with specific problems with your technologies, process and projects.

Migrating SQL Server to Microsoft Azure SQL Database as a service

Microsoft Azure SQL Database compatibility problems disappeared in V12, clearing the path for a SQL database migration to the cloud. Here's how to make the move.

While there is some understandable reluctance to move resource-intensive and latency-sensitive databases to cloud platforms such as Microsoft Azure SQL Database, there are good reasons why outsourcing to the cloud makes sense for many applications.

Microsoft Azure SQL Database provides a SQL Server-based, multi-tenant database as a service in the Azure cloud, removing IT hardware and server management tasks from your data center.

Microsoft Azure SQL Database grows up

Azure SQL Database, until recently, suffered from compatibility limitations that acted as hurdles to implementation. But, as of June 2016, new databases created on Azure SQL Database V12 now have their compatibility level start at 130, which matches Microsoft SQL Server 2016, according to the Microsoft Azure blog.

Azure platform as a service (PaaS) allows you to dynamically resize your databases and increases their scalability. In addition, Azure SQL Database provides automatic administration and data protection. With Azure SQL Database, Microsoft provides all of the infrastructure support, security and software updates, as well as automatic backups and disaster recovery failover.

Moving SQL Server to Microsoft Azure SQL Database

If you're looking to move your on-premises SQL Server databases to Microsoft's PaaS, the process essentially requires you to first create an Azure SQL Database using the Azure Portal and then test if your current database is compatible with Azure SQL Database. You need to fix any compatibility issues you might find before performing the migration.

There are a number of ways to test if your database is compatible with Azure SQL Database. One of the easiest ways is to use the Export Data-tier Application function in SQL Server Management Studio (SSMS). Since this option is built directly into SSMS, start by opening SSMS and navigating to your source database with Object Explorer. Then, right-click on the source database and select Tasks from the pop-up menu. Next, under Tasks, select Export Data-tier Application (as shown in Figure 1).

Testing Azure SQL Database compatibility
Figure 1. Testing Azure SQL Database compatibility using the Export Data-tier Application Wizard

This will open the Export Data-tier Application wizard. Click Next on the introduction dialog to display the export settings dialog. You will have the option to save the BACPAC file to either the local disk or to Microsoft Azure. To check compatibility, it's best to select the save to local disk option. Since you are only performing a compatibility test, it's more efficient to skip actually exporting the data. To do that, click on the Advanced tab and then clear all of the checkboxes (as shown in Figure 2).

Export Data-tier Application Wizard
Figure 2. Validating database using the Export Data-tier Application Wizard

Click Next, then click Finish on the Summary dialog. The wizard will then perform a number of compatibility checks. A red X will appear to indicate any errors that must be fixed before migration. To get more information on an error, click on the link in the Results column. If there are no errors, all of the tests will display green checkmarks (as shown in Figure 3). This indicates that there are no compatibility issues and that you can safely move forward with your SQL database migration. 

Successful database validation
Figure 3. Successful database validation using the Export Data-tier Application Wizard

After you perform the compatibility tests, click the Close button to exit the Export Data-tier Application wizard and then proceed with the database migration.

Migrating databases with SQL Server Migration Wizard

There are several methods available that you can use to migrate your on-premises database to Azure SQL Database. The most direct method is probably to use the Deploy Database to Microsoft Azure SQL Database wizard option from SSMS, which migrates a compatible SQL Server database directly to Azure SQL Database.

To begin the migration, open SSMS and use Object Explorer to navigate to the database you want to migrate. Then, right-click the database and select Tasks. Next, click Deploy Database to Microsoft Azure SQL Database (as shown in Figure 4).

Deploying the database
Figure 4. Using the Deploy Database to Microsoft Azure SQL Database wizard

This will start the Deploy Database to Microsoft Azure SQL Database wizard. Click through the introduction screen to display the Deployment Settings dialog. The database name will be displayed under New database name and the Temporary file name will specify the local directory and file to use for the temporary BACPAC file. Clicking the Connect button displays the Connect to Server dialog (as shown in Figure 5).

Deploy Database Connect to Server dialog
Figure 5. Deploy Database Connect to Server dialog

To connect, you first need to know the server name for the Azure SQL Database and its login information. If this is the first time you've used the database, the login information is the same as what you entered when you created the Azure SQL Database.

To find the server name, open your Azure portal, log in and click the SQL Database's blade to display your existing Azure SQL Database deployments (as shown in Figure 6).

Find your Azure SQL Database name
Figure 6. Using the Azure Portal to find your Azure SQL Database name

Select the Overview link to display your Azure SQL Database server connection name. If you click on the Server name link, you can copy it to the clipboard and paste it into the Connect to Server dialog.

Before you can connect to the server from your client system, you will probably need to set up an Azure SQL Database firewall rule. The firewall works by creating exceptions for one or more client IP addresses, allowing these addresses to have access to the Azure SQL Database server. After you create the firewall, complete the Deployment Settings dialog by filling it with your Azure SQL Database settings (as shown in Figure 7).

Completed Deployment Settings
Figure 7. The completed Deployment Settings

Click Next to display the Summary dialog and then click Finish to begin the database migration process. The actual migration will take a variable amount of time depending on the size of the database and the activity level of your system. You can see the completed dialog in Figure 8.

Azure SQL Database migration
Figure 8. The Azure SQL Database migration

Post-SQL database migration

After the migration is complete, you can manage the Azure SQL Database using standard SQL Server management tools, such as SSMS. In Figure 9, you can see SSMS 2016 managing the migrated Azure SQL Database. To connect to the Azure SQL Database instance from SSMS, use the same connection information from the earlier migration connection in Figure 5.

Managing Azure SQL Database
Figure 9. Managing Azure SQL Database with SSMS

Next Steps

Microsoft cloud gets MPP with Azure SQL Data Warehouse

Microsoft makes it easier to set up a SQL Server on Azure Virtual Machines

Why should I set up SQL Server Express on Azure?

Dig Deeper on SQL Server Migration Strategies and Planning