How to migrate from on-premises to Azure SQL database

• Using the SQL Server Management Studio - Recommended when there are no code compatibility issues blocking the cloud migration.
• Using the SQL Server Data Tools - This approach is highly recommended when there are migration barriers, as the process of detecting and fixing the issues is simpler and more direct.

Migrate Using SQL Server Management Studio

Migration Steps Using the Deployment Wizard

1. Right-click the database and select the Deploy Database to Microsoft Azure SQL Database
2. Fill in the required fields. The server information is for the target (Azure SQL Database server). The settings to define the price tier are also configured at this stage. The bacpac file will be created locally and then applied on the Azure SQL Server, and because of this, we will need to store the bacpac file in a temporary place in the server.
3. Click Next.

4. Review the settings and click Finish.
5. Wait for the process to complete. At this stage the wizard will validate the database, create the DACPAC file, and apply the Azure SQL Server to create the database.

6. The database is now ready to use the server admin account to access the Azure SQL Server.

Migration Steps using the Export Data-Tier Application Process

1. Right-click the database and select the Export Data-tier Application.
2. Save the file in an Azure Blob Storage Account. You will need the account name and access key.
3. Select the container and click Next.
4. Click Finish, and wait for the processing to complete.
5. Once the process completes a "Success" message is seen as shown in the screen below. Otherwise, there are items needing to be resolved to make the database capable of being converted into an Azure SQL Database.
6. Connect to the Azure portal and choose the SQL Servers.
7. Select the SQL Server location where the database should be created, and then click the Import Database icon as shown below.
8. Complete the required settings, including the BACPAC file location, price tier, and server administrator’s password, and then click Create.
9. Once the process completes, the database will be seen in the list.

Migrate Using SQL Server Data Tools

Creating the Main Project

1. Using the SQL Server Data Tools BI, click the SQL Server Object Explorer tab and connect to the on-premises instance:

2. Right-click the database to be migrated to Azure, and then click Create New Project.
3. Add a name to the project and select a path to save the project files.
4. Click next and wait for the processing to complete.

5. After the project is created, right-click the project root, go to properties and change the Target Platform to Azure SQL Database. Save and close.

6. Right-click the project and click Rebuild. If problems are detected, all the errors will be shown in the Error List.

7. Go to File->New->Project, give a project name (I will name it AWAzure) and in the Solution option, click Add to solution:

Creating the New Schema

1. Now, select the options. Click the icon shown.

2. In the Schema Compare Options window, click to clear the following known non-supported items:

• Aggregates
• Application Roles
• Assemblies
• Asymmetric Keys
• Broker Providers
• Certificates
• Contracts
• Defaults
• Extended Properties
• Filegroups
• FIleTables
• Full-Text Stoplists
• Full-Text Catalogs
• Full-Text Indexes
• Message Types
• Partition Functions
• Partition Schemes
• Queues
• Remote Service Bindings
• Rules
• Sequences
• Services
• Symmetric Keys
• Used-Defined Types (CLR)
• XML Indexes
• XML Schemas Collections

3. Click Ok and save the Schema Comparison, as it can be useful later.
4. Select the source: The On-premises database.

5. Select the Target: The empty SQL Server create project.

6. Now, click Compare. Wait for the process to complete and then click Update (click YES in the confirmation pop-up), to update the selected target.

7. Next, go to the AWAzure (the target) project, right-click on the root, go to properties, and change the Target Platform to Azure SQL Database.

8. Click Save and Close the screen.

Resolving Problems

Deploying the Schema

1. To publish the database, right click the AWAzure project, and click Publish.

2. Edit the target instance and connect to the Azure SQL Server:

3. Fill in the database name and click Publish.

Moving the Data

1. Connect to the on-premises instance, right click the database used as the data source and follow the steps shown:

2. In the wizard, confirm the Server name and the source database, and then click Next.

3. In the Destination field, select SQL Server Native Client 11.0, fill in the server name, and select the target database.
4. Click Next.
5. For this step, keep the first option selected, and then click Next.

6. Make sure that all the tables are highlighted and click Edit Mappings.
7. Select Enable Identity Insert and then click Ok.
8. Then, in the main Wizard window click Next.

9. Make sure the Run immediately check box is selected and click Next.

10. In the following screen, review the options, and then click Finish.

11. Monitor and the data transfer and close the wizard.