Migrating a SQL Server database to Azure SQL Database

Posted on by Panther

Introduction

Many businesses are moving their on-premises infrastructures to the cloud to take advantage of the scalability, flexibility, and cost-effectiveness of cloud services. As a result, migrating databases to the cloud has become a common task for many IT professionals. Microsoft Azure provides a cloud-based relational database service called Azure SQL Database, which is a fully managed service that meets industry standards for security, compliance, and availability. In this tutorial, we will walk through the steps to migrate an on-premises SQL Server database to Azure SQL Database.

Prerequisites

Before we begin, there are a few prerequisites that must be taken care of:

  1. A Microsoft Azure account.
  2. The latest version of SQL Server Management Studio (SSMS) installed on the machine that will be used for the migration.
  3. The latest version of SQL Server Data Tools (SSDT) installed on the machine that will be used for the migration.
  4. Firewall rules on the on-premises SQL Server must allow connectivity from the machine that will be used for the migration to the SQL Server instance.

Step 1: Prepare the Azure SQL Database

The first step is to prepare the Azure SQL Database that will host the migrated database.

  1. Log in to the Azure portal and navigate to the SQL databases page.
  2. Click the “+ Add” button to create a new database.
  3. Provide a name for the new database, select the subscription, resource group, and location for the database, and set the pricing tier.
  4. Click “Review +Create”, review the settings, and then click “Create” to create the new database.

Step 2: Generate the Azure SQL Database Schema

The next step is to generate the schema of the on-premises SQL Server database as a SQL script that can be executed on the Azure SQL Database.

  1. Open SQL Server Management Studio and connect to the on-premises SQL Server.
  2. Right-click on the database you want to migrate and select “Tasks” > “Generate Scripts”.
  3. In the “Introduction” screen, click “Next”.
  4. In the “Choose Objects” screen, select “Select specific database objects” and choose the tables, views, and stored procedures that you want to migrate. Click “Next”.
  5. In the “Set Scripting Options” screen, set the “Script for Server Version” option to “SQL Server 2016”, and set the “Script Data” option to “False”. Click “Next”.
  6. In the “Summary” screen, review the options and click “Next”.
  7. In the “Save or Publish Scripts” screen, set the “Save to File” option to “Single file”, and browse to a location on your local machine where you want to save the generated script. Click “Next”.
  8. In the “Summary” screen, review the options and click “Finish” to generate the script.

Step 3: Modify the Azure SQL Database Schema Script

Before executing the generated schema script on the Azure SQL Database, there are a few modifications that need to be made to the script.

  1. Open the generated schema script in a text editor.
  2. Remove the following line from the top of the script: 
    USE [DatabaseName]

    This line is not needed since the script will be executed against the Azure SQL Database specified in the connection string.

  3. Search for the following line in the script:

    SET ANSI_NULLS ON

    Add the following line immediately after it:

    SET ANSI_PADDING ON

    This will ensure that the data types are aligned between the two databases.

  4. Save the modified schema script.

Step 4: Deploy the Azure SQL Database Schema

The next step is to deploy the modified schema script to the Azure SQL Database.

  1. Open SQL Server Management Studio and connect to the Azure SQL Database.
  2. Open a new query window and copy and paste the modified schema script into it.
  3. Execute the script by clicking the “Execute” button in the toolbar or pressing F5.

Step 5: Migrate the Data

The final step is to migrate the data from the on-premises SQL Server database to the Azure SQL Database.

  1. Open SQL Server Management Studio and connect to the on-premises SQL Server.
  2. Right-click the database you want to migrate and select “Tasks” > “Export Data…”
  3. In the

    “Introduction” screen, click “Next”.

  4. In the “Choose a Data Source” screen, select “SQL Server Native Client 11.0” as the data source and provide the server name, database name, and authentication details. Click “Next”.
  5. In the “Choose a Destination” screen, select “Microsoft OLE DB Provider for SQL Server” as the destination and provide the Azure SQL Database connection string. Click “Next”.
  6. In the “Specify Table Copy or Query” screen, select “Copy data from one or more tables or views” and select the tables you want to migrate. Click “Next”.
  7. In the “Completing the Wizard” screen, review the options and click “Finish” to start the data migration.

Conclusion

Migrating a SQL Server database to Azure SQL Database is a relatively straightforward process that can be accomplished with just a few steps. By following the steps outlined in this tutorial, you should be able to successfully migrate your on-premises SQL Server database to Azure SQL Database. Remember to test the migrated database thoroughly before making it live in a production environment.

Related Post