SQL Server Database Migration(On-Premises) to Azure SQL Database

The primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database.

Migrate to a single database or a pooled database :

There are primary two methods for migrating a SQL Server 2005 or later database to Azure SQL Database. The first method is simpler but requires some, possibly substantial, downtime during the migration. The second method is more complex, but substantially eliminates downtime during the migration.

In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL Database is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

Method 1: Migration with downtime during the migration

Use this method to migrate to a single or a pooled database if you can afford some downtime or you’re performing a test migration of a production database for later migration.The following list contains the general workflow for a SQL Server database migration of a single or a pooled database using this method.

VSSSDT migration diagram
  1. Assess the database for compatibility by using the latest version of the Data Migration Assistant (DMA).
  2. Prepare any necessary fixes as Transact-SQL scripts.
  3. Make a transactionally consistent copy of the source database being migrated or halt new transactions from occurring in the source database while migration is occurring. Methods to accomplish this later option include disabling client connectivity or creating a database snapshot. After migration, you may be able to use transactional replication to update the migrated databases with changes that occur after the cutoff point for the migration.
  4. Deploy the Transact-SQL scripts to apply the fixes to the database copy.
  5. Migrate the database copy to a new database in Azure SQL Database by using the Data Migration Assistant.

Also we can Import a BACPAC file to a new database in Azure SQL Database.

Optimizing data transfer performance during migration :

The following list contains recommendations for best performance during the import process.

  1. Choose the highest service tier and compute size that your budget allows to maximize the transfer performance. You can scale down after the migration completes to save money.
  2. Minimize the distance between your BACPAC file and the destination data center.
  3. Disable autostatistics during migration
  4. Partition tables and indexes
  5. Drop indexed views, and recreate them once finished
  6. Remove rarely queried historical data to another database and migrate this historical data to a separate database in Azure SQL Database. You can then query this historical data using elastic queries.

Optimize Your Performance Once the migration has been completed by Updating statistics with full scan after the migration is completed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.