In this era of Cloud computing, developers are often tasked with migrating databases to Azure Cloud. This blog highlights some of the steps required for a successful migration. In a recent project, we migrated 12 Sql Server databases with about 1TB data from on-premise to Azure Cloud.
| Import | Connect to the On-Premise SQL Server database and import database schema to a Database project in Visual Studio. |
| Migration Wizard | This is SSDT compatibility checker. Set the target database to Microsoft Azure SQL Database V12. Build the project to start compatibly check. |
| Fix Errors | Fix all errors and warning. Make sure to check for all warning and resolve them before publishing the database. |
| Publish Schema | Create a new local database and publish the schema to the local database. This will create an Azure compatible database. |
| Validate Database | Validate database, run some quick check to make sure all objects are migrated to the local database. |
| Compare Database | Using Visual Studio SSDT data compare, do a compare between the source database and local database. This will create a list all source database with data and target (local database) with no data. |
| Copy Data | Copy the data from source database to the local database. |
| Validate Database | Run the Visual Studio SSDT data compare again. This time the source and target (local database) should match. You can also smoke test your application against this local database to validate the database. |
| Backup Database | Create a storage account on Windows Azure and backup the local database to Windows Azure Storage. |
| Restore Database | Create a database on Windows Azure SQL and restore the backup from the Azure storage. |
| Validate Database | Run the Visual Studio SSDT schema/data compare between the local database and Azure SQL database. This time the source and target should match pretty close. It might not match 100% percent, because of the compatibility issues. Smoke test your application against this Azure SQL Database to validate the database. |
| Add to Source Control | Add Database project to Source Control (Visual Studio online or any other on-premise /cloud based source control) |
Tomy's Blog 