Moving your SQL Server database to Azure SQL Database is a three part process - you first prepare, then export, and then import the database. In this tutorial, you learn to:
- Prepare a database in a SQL Server for migration to Azure SQL Database using the Data Migration Assistant (DMA)
- Export the database to a BACPAC file
- Import the BACPAC file into an Azure SQL Database
If you don't have an Azure subscription, create a free account before you begin.
To complete this tutorial, make sure the following prerequisites are completed:
- Installed the newest version of SQL Server Management Studio (SSMS). Installing SSMS also installs the newest version of SQLPackage, a command-line utility that can be used to automate a range of database development tasks.
- Installed the Data Migration Assistant (DMA).
- You have identified and have access to a database to migrate. This tutorial uses the SQL Server 2008R2 AdventureWorks OLTP database on an instance of SQL Server 2008R2 or newer, but you can use any database of your choice. To fix compatibility issues, use SQL Server Data Tools
Prepare for migration
You are ready to prepare for migration. Follow these steps to use the Data Migration Assistant to assess the readiness of your database for migration to Azure SQL Database.
Open the Data Migration Assistant. You can run DMA on any computer with connectivity to the SQL Server instance containing the database that you plan to migrate, you do not need to install it on the computer hosting the SQL Server instance.
In the left-hand menu, click + New to create an Assessment project. Fill in the form with a Project name (all other values should be left at their default values) and click Create. The Options page opens.
On the Options page, click Next. The Select sources page opens.
On the Select sources page, enter the name of SQL Server instance containing the server you plan to migrate. Change the other values on this page if necessary. Click Connect.
In the Add sources portion of the Select sources page, select the checkboxes for the databases to be tested for compatibility. Click Add.
Click Start Assessment.
When the assessment completes, first look to see if the database is sufficiently compatible to migrate. Look for the checkmark in a green circle.
Review the results. The SQL Server feature parity results shown are the default results to review. Specifically review the information about unsupported and partially supported features, and the provided information about recommended actions.
Review the Compatibility issues by clicking that option in the upper left. Specifically review the information about migration blockers, behavior changes, and deprecated features for each compatibility level. For the AdventureWorks2008R2 database, review the changes to Full-Text Search since SQL Server 2008 and the changes to SERVERPROPERTY('LCID') since SQL Server 2000. For details on these changes, links for more information is provided. Many search options and settings for Full-Text Search have changed
After you migrate your database to Azure SQL Database, you can choose to operate the database at its current compatibility level (level 100 for the AdventureWorks2008R2 database) or at a higher level. For more information on the implications and options for operating a database at a specific compatibility level, see ALTER DATABASE Compatibility Level. See also ALTER DATABASE SCOPED CONFIGURATION for information about additional database-level settings related to compatibility levels.
Optionally, click Export report to save the report as a JSON file.
- Close the Data Migration Assistant.
Export to BACPAC file
A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure blob storage or in local storage for archiving or for migration - such as from SQL Server to Azure SQL Database. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export.
Follow these steps to use the SQLPackage command-line utility to export the AdventureWorks2008R2 database to local storage.
Open a Windows command prompt and change your directory to a folder in which you have the 130 version of SQLPackage - such as C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin.
Execute the following SQLPackage command at the command prompt to export the AdventureWorks2008R2 database from localhost to AdventureWorks2008R2.bacpac. Change any of these values as appropriate to your environment.
sqlpackage.exe /Action:Export /ssn:localhost /sdn:AdventureWorks2008R2 /tf:AdventureWorks2008R2.bacpac
Once the execution is complete the generated BACPAC file is stored in the directory where the sqlpackage executable is located. In this example C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin.
Log in to the Azure portal
Log in to the Azure portal. Logging on from the computer from which you are running the SQLPackage command-line utility eases the creation of the firewall rule in step 5.
Create a SQL server logical server
A SQL server logical server acts as a central administrative point for multiple databases. Follow these steps to create a SQL server logical server to contain the migrated Adventure Works OLTP SQL Server database.
Click the New button found on the upper left-hand corner of the Azure portal.
Type sql server in the search window on the New page, and select SQL database (logical server) from the filtered list.
On the Everything page, click SQL server (logical server) and then click Create on the SQL Server (logical server) page.
Fill out the SQL server (logical server) form with the following information, as shown on the preceding image:
Setting Suggested value Description Server name Enter any globally unique name For valid server names, see Naming rules and restrictions. Server admin login Enter any valid name For valid login names, see Database Identifiers. Password Enter any valid password Your password must have at least 8 characters and must contain characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters. Subscription Select a subscription For details about your subscriptions, see Subscriptions. Resource group Choose an existing resource group or create a new group, such as myResourceGroup For valid resource group names, see Naming rules and restrictions. Location Enter any valid location for the new server For information about regions, see Azure Regions.
Click Create to provision the logical server. Provisioning takes a few minutes.
Remember your server name, server admin login name, and password. You need these values later in this tutorial.
Create a server-level firewall rule
The SQL Database service creates a firewall at the server-level that prevents external applications and tools from connecting to the server or any databases on the server unless a firewall rule is created to open the firewall for specific IP addresses. Follow these steps to create a SQL Database server-level firewall rule for the IP address of the computer from which you are running the SQLPackage command-line utility. This enables SQLPackage to connect to the SQL serverDatabase logical server through the Azure SQL Database firewall.
Click All resources from the left-hand menu and click your new server on the All resources page. The overview page for your server opens and provides options for further configuration.
Click Firewall in the left-hand menu under Settings on the overview page. The Firewall settings page for the SQL Database server opens.
Click Add client IP on the toolbar to add the IP address of the computer you are currently using and then click Save. A server-level firewall rule is created for this IP address.
You can now connect to all databases on this server using SQL Server Management Studio or another tool of your choice from this IP address using the server admin account created previously.
SQL Database communicates over port 1433. If you are trying to connect from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 1433.
Import a BACPAC file to Azure SQL Database
The newest versions of the SQLPackage command-line utility provide support for creating an Azure SQL database at a specified service tier and performance level. For best performance during import, select a high service tier and performance level and then scale down after import if the service tier and performance level is higher than you need immediately.
Follow these steps use the SQLPackage command-line utility to import the AdventureWorks2008R2 database to Azure SQL Database. While you can use SQL Server Management Studio for this task, SQLPackage is the preferred method for most production environments for maximum flexibility and best performance. See Migrating from SQL Server to Azure SQL Database using BACPAC Files.
Execute the following SQLPackage command at the command prompt to import the AdventureWorks2008R2 database from local storage to the SQL server logical server that you previously created to a new database, a service tier of Premium, and a Service Objective of P6. Replace the values in angle brackets with appropriate values for your SQL server logical server and specify a name for the new database (also replace the angle brackets). You can also choose to change the values for database edition and service objectgive as appropriate to your environment. For the purpose of this tutorial, the migrated database is called myMigratedDatabase.
SqlPackage.exe /a:import /tcs:"Data Source=<your_server_name>.database.windows.net;Initial Catalog=<your_new_database_name>;User Id=<change_to_your_admin_user_account>;Password=<change_to_your_password>" /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6
A SQL server logical server listens on port 1433. If you are attempting to connect to a SQL server logical server from within a corporate firewall, this port must be open in the corporate firewall for you to successfully connect.
Connect using SQL Server Management Studio (SSMS)
Use SQL Server Management Studio to establish a connection to your Azure SQL Database server and newly migrated database, called myMigratedDatabase in this tutorial. If you are running SQL Server Management Studio on a different computer from which you ran SQLPackage, create a firewall rule for this computer using the steps in the previous procedure.
Open SQL Server Management Studio.
In the Connect to Server dialog box, enter the following information:
- Server type: Specify Database engine
- Server name: Enter your fully qualified server name, such as mynewserver20170403.database.windows.net
- Authentication: Specify SQL Server Authentication
- Login: Enter your server admin account
Password: Enter the password for your server admin account
Click Connect. The Object Explorer window opens.
In Object Explorer, expand Databases and then expand myMigratedDatabase to view the objects in the sample database.
Change database properties
You can change the service tier, performance level, and compatibility level using SQL Server Management Studio. During the import phase, we recommend that you import to a higher performance tier database for best performance, but that you scale down after the import completes to save money until you are ready to actively use the imported database. Changing the compatibility level may yield better performance and access to the newest capabilities of the Azure SQL Database service. When you migrate an older database, its database compatibility level is maintained at the lowest supported level that is compatible with the database being imported. For more information, see Improved query performance with compatibility Level 130 in Azure SQL Database.
In Object Explorer, right-click myMigratedDatabase and click New Query. A query window opens connected to your database.
Execute the following command to set the service tier to Standard and the performance level to S1.
ALTER DATABASE myMigratedDatabase MODIFY ( EDITION = 'Standard' , MAXSIZE = 250 GB , SERVICE_OBJECTIVE = 'S1' );
Execute the following command to change the database compatibility level to 130.
ALTER DATABASE myMigratedDatabase SET COMPATIBILITY_LEVEL = 130;
In this tutorial you prepared, exported and imported your database. You learned to:
- Prepare a database in a SQL Server for migration to Azure SQL Database
- Export the database to a BACPAC file
- Import the BACPAC file into an Azure SQL Database
Advance to the next tutorial to learn how to secure your database.