I have recently been working testing the migration of the on-premise SQL Server databases on Azure SQL. This blog is about the guidance on how this can be achieved.
Azure SQL is Microsoft’s PaaS offering where the IT teams in organizations don’t have to worry about the infrastructure and leverage the functionality of the SQL database almost as identical as what they are used to with on-premise SQL servers. This way new databases can be spun in a matter of minutes. More information about Azure SQL can be found here.
We will use a SQL 2012 for the purposes of this blog since most customers environments I have worked with are still on SQL Server 2012. We will be using SSDT(SQL Server Data Tools) 2015 and SSMS(SQL Server Management Studio) 2016, both of which are available to download from Microsoft for free(here and here). It is advisable to use the latest binaries for these because they have a complete feature set needed to test and migrate your databases to Azure SQL.
The migration of the SQL databases can be done in three steps.
- Test SQL database compatibility
- Fix compatibility issues
- Migrate to Azure SQL
Test SQL Compatibility and Fix Compatibility Issues
In order to test the compatibility of the SQL Server database with Azure SQL, Microsoft has provided the following options:
- SQL Server 2016 Upgrade advisor: This is the default SQL 2016 advisor which can be used to test the schema compatibility with Azure SQL. Although what i found was that this tool does not pick all the issues with the compatibility and you might be left with surprise after migration.
- SSDT 2015: This would be my go-to tool for looking into the issues with Compatibility and also helps with resolving them so that your database is compatible with Azure SQL. This provides testing for both Azure SQL V11 and V12. Although, always use V12 since there is a better parity for the features in Azure SQL V12 than V11 so you are likely to run into lesser errors during testing.
- SQL Package and SSMS: You can use these tools as well for finding the issues with the schema changes but they don’t provide much help with fixing the issues.
We will choose SSDT for this article. We have a created a Windows 2012 R2 VM on Azure with SQL Server 2012 SP3. I have downloaded and imported the AdventureWorks database from Microsoft’s website for SQL 2012. As you can see below, the database contains the data in the tables in SSMS
In the next blog, I will outline the best practices for the Azure SQL migration. Till next time…
Reference Link – http://cloudsunboxed.com/2017/01/07/migrating-sql-server-database-on-azure-sql-paas/
Posted by Sheikvara