ver

Migrating a SQL Server database to Azure SQL PaaS

Microsoft SQL Server and Azure SQL PaaS service are compatible with each other, meaning that a database can be moved from a SQL server running on a physical or virtual machine to Azure SQL service and vice versa.

Prerequisites and Constraints

However, there are some limitations, as certain editions of Azure SQL server are not compatible with older versions of SQL Server.

2.1 Alignment of SQL Versions to Azure SQL Compatibility Levels

Starting in mid-June 2016, in Azure SQL Database, the default compatibility level is 130 instead of 120 for newly created databases.

Databases created before mid-June 2016 are not affected, and will maintain their current compatibility level (100, 110, or 120). Databases that migrate from Azure SQL Database version V11 to V12 will not have their compatibility level changed either.

Compatibility level affects the parser and some other features. That does not mean the creating the script and moving the data is not possible provided that there is no deprecated code or unsupported features.

The alignment of SQL versions to default compatibility levels are as follows:

Table 3: Alignment of SQL to compatibility levels

The upgrade with backup and restore or detach and attach. If you Script the SQL 2005 objects and there are no deprecated code or discontinued features you can run it to recreate the Objects in SQL 2016.

Overview of migration options to Azure SQL: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-cloud-migrate/

Example of how upgrading from 2005 to 2014 is affected by compatibility levels:

https://blogs.msdn.microsoft.com/ialonso/2015/04/17/sql-server-2005-upgrade-to-sql-server-2014-and-compatibility-levels/

Altering Compatibility Level

Compatibility level of a SQL database can be altered. It sets certain database behaviours to be compatible with the specified version of SQL Server. Compatibility levels apply to both SQL Server and Azure SQL.

Syntax to change:

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = { 130 | 120 | 110 | 100 | 90 }

Arguments:

database_name: Is the name of the database to be modified.

SET COMPATIBILITY_LEVEL = { 130 | 120 | 110 | 100 | 90 }: Is the version of SQL Server with which the database is to be made compatible. The following compatibility level values can be configured

Table 4: SQL Server Compatibility Levels

2.3 SQL Server and Azure SQL PaaS Compatibility Matrix

The below compatibility matrix does not always work, depending on the Compatibility value of the SQL PaaS and features used in the SQL Server.

In RED is the best answer considering the general scenario but as explained above this may well change depending on Code/Features and type of migration. For example, 2008 is not compatible with 2008 R2, because you cannot backup a SQL 2008 R2 and restore it on a SQL 2008 Instance. More generally you can always backup an older version to a new never the other way around as we bump the internal version of the database.

More details:

Table 5: SQL Server and Azure SQL Compatibility Matrix

It is also recommended that SQL Server Management Studio (SSMS) is being used for the migration process from and to the Azure SQL.

SSMS can be downloaded from the below link:

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Installation guide for SSMS (applies to all newest versions as well):

https://blogs.msdn.microsoft.com/bethmassi/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010/

Migrating a Microsoft Database

Provided that compatible versions of the two SQL offerings are being used, a database created in the SQL server can be exported as a BACPAC (.bacpac extension) file that contains both the database schema and data and can be imported to another SQL Server database or Azure SQL. The process can be performed both ways.

Export SQL Server to a BACPAC file

It is recommended that the latest version of SSMS is always used. Process to export database to a BACPAC file is as follows:

1. Open Management Studio and connect to your source database in Object Explorer.

Figure 10: Connecting to Database

1. Right-click the source database in the Object Explorer, point to Tasks, and click Export Data-Tier Application.

Figure 11: Exporting Database BACPAC file

1. In the export wizard, configure the export to save the BACPAC file to either a local disk location or to an Azure blob. The exported BACPAC always includes the complete database schema and, by default, data from all the tables.

5. Import a Database from SQL Server to Azure SQL using SSMS

This process assumes that the previous steps of exporting the database to BACPAC file have been successfully completed.

Process is as follows:

1. Connect to your Azure SQL Database server, right-click the Databases folder and click Import Data-tier Application

Figure 12: Importing database to Azure SQL through SSMS

1. To create the database (in an Azure SQL Server that Exists) in Azure SQL Database, import a BACPAC file from your local disk or select the Azure storage account and container to which you uploaded your BACPAC file.

Figure 13: Import BACPAC from local disk

1. Provide the New database name for the database on Azure SQL DB, set the Edition of Microsoft Azure SQL Database (service tier), Maximum database size, and Service Objective (performance level).

Figure 14: Create the database in Azure SQL

1. Click Next and then click Finish to import the BACPAC file into a new database in the Azure SQL Database server.
2. Using Object Explorer, connect to your migrated database in your Azure SQL Database server.
3. Using the Azure portal, view your database and its properties.

6. Import a BACPAC file to create an Azure SQL database, using the Azure Portal

After an Azure SQL server to host the database has been created, the process is as follows:

1. Access Azure Portal
2. On the left hand pane, click SQL Servers
3. Select the server to restore the database into
4. In the SQL Server blade click Import database to open the Import database blade:

Figure 15: Import Database BACPAC file from inside the portal

1. Click Storage and select your storage account, blob container, and .bacpac file and click OK.

Figure 16: Selecting the BACPAC from within the storage account container

1. Select the pricing tier for the new database and click Select. Importing a database directly into an elastic pool is not supported, but you can first import into a single database and then move the database into a pool.

Figure 17: Choose Azure SQL DB pricing Tier

1. Enter a DATABASE NAME for the database you are creating from the BACPAC file.
2. Choose the authentication type and then provide the authentication information for the server.
3. Click Create to create the database from the BACPAC.

Figure 18: Creating the database

Clicking Create submits an import database request to the service. Depending on the size of your database, the import operation may take some time to complete.

7. Monitoring the Progress of the Import Operation

1. Access Azure Portal
2. On the left hand pane, click SQL Servers
3. Select the server you are importing the database into
4. In the SQL server blade, in the Operations area, click Import/Export history

Figure 19: Checking import/Export jobs