Rarely a day passes where I do not interact with a client asking about how to leverage SQL services in Azure. There are many reasons why I have these conversations:
- Older SQL server instances such as 2008/2005/2000 are either out of support or ending soon (SQL Server 2008 SP4 is currently supported but it is in Extended Support mode).
- SQL server pricing has become a cost factor in supporting applications for the business.
- Cloud first standards are requiring the investigation of the platform for consideration.
While there are many complicated reasons that cause businesses to consider moving to Azure SQL, the activity itself doesn’t have to be challenging.
Where do you start? First of all, analyze your existing databases to see what challenges you will face. The easiest way to do that is to use the Azure Data Migration Assistant (DMA). This tool will detect any compatibility issues which may impact your database’s functionality if it is moved to Azure SQL and recommend performance and reliability improvements to improve the migration. Additionally, it will enable you to create a migration project to move your schema and data to Azure SQL.
Method 1: Migration with Downtime
This method is best used if you can afford downtime. Microsoft even provides a step-by-step tutorial to walk you through the migration process using the AdventureWorks database for a low risk experience. Check that out here.
The following list contains the general workflow for an SQL Server database migration using this method:
- Assess the database for compatibility using the latest version of Data Migration Assistant (DMA).
- Prepare any necessary fixes as Transact-SQL scripts.
- Make a transactionally consistent copy of the source database being migrated and ensure that no further changes are being made to the source database (or you can manually apply any such changes after the migration completes). There are many methods to quiesce a database, from disabling client connectivity to creating a database snapshot.
- Deploy the Transact-SQL scripts prepared earlier to apply any necessary fixes to the database copy.
- Export the database copy to a .BACPAC file on a local drive.
- Import the .BACPAC file as a new Azure SQL database using any BACPAC import tool. I recommend using the SQLPackage.exe tool for best results.
Optimizing Data Transfer Performance during Migration
The following list contains recommendations for best performance during the import process:
- Choose the highest service level and performance tier that your budget allows to maximize the transfer performance. You can scale down after the migration completes to save money.
- Minimize the distance between your .BACPAC file and the destination data center.
- Disable auto-statistics during migration.
- Partition tables and indexes.
- Drop indexed views and recreate them once the migration has been completed.
- Remove rarely queried historical data to another database and migrate this historical data to a separate Azure SQL database. You can then query this historical data using elastic queries.
Optimizing Performance after the Migration Completes
You should update statistics with a full scan after the migration has been completed.
SQL Server database migration to Azure SQL Database | Microsoft Docs
Method 2: Use Transactional Replication
If you cannot afford to remove your SQL Server database from production while performing a migration, you can use SQL Server transactional replication as your migration solution. To use this method, the source database must meet the requirements for transactional replication and be compatible with Azure SQL Database.
To use this solution, you must configure your Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue to occur.
With transactional replication, all changes to your data or schema show up in your Azure SQL Database. Once the synchronization is complete and you are ready to migrate, simply change the connection string of your applications to point them to your Azure SQL Database. Once transactional replication drains any changes left on your source database and all your applications point to Azure DB, you can uninstall transactional replication. Your Azure SQL Database will now be your production system.
You can also use transactional replication to migrate a subset of your source database. The publication that you replicate to Azure SQL Database can be limited to a subset of the tables in the database being replicated. For each table being replicated, you can limit the data to a subset of rows and/or a subset of columns.
Migration to SQL Database Using the Transactional Replication Workflow
The following describes the steps required to perform a migration to SQL Database using the transactional replication workflow:
- Set up Distribution
- Create Publication
- Create Subscription
You should use the latest version of SQL Server Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. Older versions of SQL Server Management Studio cannot set up SQL Database as a subscriber. Update SQL Server Management Studio.
Some Tips for Migrating to SQL Database
- Use a local distributor
- This causes a performance impact on the server.
- If the performance impact is unacceptable, you can use another server, but it adds complexity to management and administration.
- When selecting a snapshot folder, make sure the folder you select is large enough to hold a BCP of every table thay you want to replicate.
- Snapshot creation locks the associated tables until it is complete, so schedule your snapshots appropriately.
- Only push subscriptions that are supported in Azure SQL Database. You can only add subscribers from the source database.
Resolving Database Migration Compatibility Issues
There are a wide variety of compatibility issues that you might encounter depending both on the version of SQL Server in your source database and the complexity of the database you are migrating. Older versions of SQL Server have more compatibility issues. Use the following resources in addition to a targeted Internet search using your search engine of choice to ensure a smooth migration:
- SQL Server database features not supported in Azure SQL Database
- Discontinued Database Engine Functionality in SQL Server 2016
- Discontinued Database Engine Functionality in SQL Server 2014
- Discontinued Database Engine Functionality in SQL Server 2012
- Discontinued Database Engine Functionality in SQL Server 2008 R2
- Discontinued Database Engine Functionality in SQL Server 2005
References: Much of this document was sourced/replicated from this Microsoft post on SQL Migration.
For more from Michael Chapman, head over to his blog.