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.
Below are the steps to test the AdventureWorks database compatibility with Azure SQL using SSDT 2015.
- Open the SSDT and connect to the SQL Server so that databases are accessible from the console.
- Right Click on the database and click “Create New Project”. This will open a new a window where you can name the project and Click Start. This will import the database into the SSDT. Below is the screenshot for the steps to create the project.
- Once the project is created, right-click on the project under solution explorer and click Properties. Change the Target platform to Azure SQL Server V12.
- Once done right-click on the project again and click Build. This will build the solution to check if the database is compatible with Azure SQL and can be migrated.
- OOB the Adventureworks database will work and will not throw any errors after the Build process. We have purposefully added a NT AUTHORITY\SYSTEM account in the database users so that the build process will show an error. Any accounts with a \ is not supported with Azure. Currently with V12 local accounts and Azure AD accounts are compatible with the databases. SSDT will pick these issues and will ask you to correct before proceeding forward. Also its worth noting that SSDT can test compatibility for V11 Azure SQL as well as you can see int he picture below.
- As you can see i do have multiple errors after i ran the build, in order to resolve them double click on them and you can comment them out if you dont need them. OR if its a SQL store procedure with a TSQL syntax which is not compatible with Azure SQL. For the complete list of the difference in the compatibility, click here.To resolve the errors, I have commented the TSQL statements. The errors will disappear automatically once they are resolved.
- After the issues are resolved rebuild the project to test the compatibility with Azure SQL V12 again. This time build should go through without issues as seen below.
- After the build is complete, we would need to publish the database back to local SQL database so that we can create a copy of the database schema. If the SQL server you are importing the database is older than SQL 2014, you would need to reset the target platform(as shown below) before the publish otherwise you will run into issues since Azure SQL database is not backward compatible with SQL 2012.
- Once the database schema is published, you can run the data comparison between your production database and the newly built database with SSDT. Click on the database from SSDT and click data comparison and choose the V12 database and click finish. This will run a schema and data comparison before you can import the data to the database.
- As you can see below, the data comparison ran with no issues and we can update the target database with the data using the Update Target button so that we can have a compatible copy for the migration to Azure SQL.
Migration to Azure SQL
Once the issues with the databases are resolved from a compatibility. A copy of the database should be taken and tested for the migration of the database to Azure. There are multiple options available for the migration of the database from on premise architecture to Azure. SQL Server Management Studio 2016, BACPAC Export, sqlpackage with PowerShell among others. We are using the SSMS 2016 since it contains the latest binaries for the migration from on premise to Azure SQL. Migration approach is based on the amount of downtime which can be afforded with the database to be offline. In most cases its little to no downtime is asked by the organization since the databases transaction logs change frequently. In those cases transactional replication is recommended so that an initial copy of the database is moved to Azure and then, changes are replicated until the cut-over date and change the connection string of your applications to point to the Azure SQL. More information for the Transaction replication can be found here. I will be writing a separate blog on the Transaction replication would work with the Azure SQL migration.
The migration done below is using the SSMS 2016. Prior to the migration, we need to make sure that there is a SQL server created on the Azure. Once the server is created, database will automatically be created from SSMS during the migration. The PowerShell script to create the SQL server on Azure can be found here.
- After the Azure SQL server is created. Open the SSMS and connect to the local SQL server. You can use SSMS 2016 with the previous version of SQL servers.
- Choose the newly created database and right click on the database and choose options for task> Migrate to Azure. This will open the migration wizard. Click Next.
- You would need to point the SSMS to the Azure SQL server. you might need to change the SQL server firewall on Azure to allow your on-premise server to connect to Azure on port 1433.Once connected, you can choose the SQL tier and the temp location for the bacpac file which will be uploaded on Azure and Click next.
- Review the settings and click finish.
- Once completed, your on-premise database will be on Azure SQL and you can connect it from your box using SSMS. 🙂
In the next blog, I will outline the best practices for the Azure SQL migration. Till next time…