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:

Compatibility Level Azure SQL Version & SQL Server
100 SQL Server 2008 and Azure SQL Database V11
110 SQL Server 2012 and Azure SQL Database V11
120 SQL Server 2014 and Azure SQL Database V12
130 SQL Server 2016 and Azure SQL Database V12

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

Product Database Engine Version Compatibility Level Designation Supported Compatibility Level Values
SQL Server 2016 13 130 130, 120, 110, 100
SQL Database 12 120 130, 120, 110, 100
SQL Server 2014 12 120 120, 110, 100
SQL Server 2012 11 110 110, 100, 90
SQL Server 2008 R2 10.5 105 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 9 90 90, 80
SQL Server 2000 8 80 80

Table 4: SQL Server Compatibility Levels

Note:

Azure SQL PaaS Database V12 was released in December 2014. One aspect of that release was that newly created databases had their compatibility level set to 120. In 2015 SQL Database began support for level 130, although the default remained 120.

Starting in mid-June 2016, in Azure SQL Database, the default compatibility level will be 130 instead of 120 for newly created databases. Existing databases created before mid-June 2016 will not be affected, and will maintain their current compatibility level (100, 110, or 120).

If you want level 130 for your database generally, but you have reason to prefer the level 110 cardinality estimation algorithm, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL), and in particular its keyword LEGACY_CARDINALITY_ESTIMATION =ON.

More details: https://msdn.microsoft.com/en-gb/library/bb510680.aspx

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:

SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Server 2012 SQL Server 2014 SQL Server 2016 Azure SQL V11 Azure SQL V12
SQL Server 2005 YES YES YES YES YES NO NO NO
SQL Server 2008 NO YES YES YES YES YES YES NO
SQL Server 2008 R2 NO YES YES YES YES YES YES NO
SQL Server 2012 NO NO NO YES YES YES YES NO
SQL Server 2014 NO NO NO NO YES YES NO YES
SQL Server 2016 NO NO NO NO NO YES NO YES
Azure SQL V11 NO          YES YES YES NO NO YES YES
Azure SQL V12 NO NO NO NO YES YES YES YES

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/

Note:

When migrating a database from SQL Server to Azure SQL, only Standard tier storage accounts can be used

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.

Export a data-tier application from the Tasks menu

Figure 10: Connecting to Database

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

Export a data-tier application from the Tasks menu

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

Import data-tier application menu item

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.

Import settings

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).

Database settings

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:

import database

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.

configure storage options

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.

select pricing tier

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.

create database

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

import export history

import export history

Figure 19: Checking import/Export jobs

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami