Migrating to Azure SQL Database
As well as hosting the databases used by new cloud applications, Microsoft’s Azure SQL Database service can also become a home for existing databases when organisations migrate their on-premises applications to the cloud. The operational, financial and technical benefits of using cloud services mean that organisations are increasingly considering how they can modernise their existing IT platforms by moving them to the cloud. Previously, organisations were happy to deploy new capabilities in the cloud while they retained their existing platforms on-premises in their data centres. That trend is now changing and an increasing number of organisations I work with are asking how they can migrate what they already have to the cloud – and then make best use of the cloud services available to them.
This article considers how and why organisations are moving their databases to Microsoft’s cloud relational database service, Azure SQL Database.
What is Azure SQL Database? (The summer 2017 answer)
Azure SQL Database is Microsoft’s cloud relational database service, a fully managed (PaaS) variant of the SQL Server database engine. It provides a Database-as-a-Service capability where Microsoft supplies a user database to hold an application’s tables and code while it manages the underlying database server platform.
Although some of that server platform is accessible by developers and system administrators, most of SQL Server’s server-level functionality and management features aren’t – such as the much missed SQL Server Agent scheduling service. While that might seem like a significant limitation, Microsoft’s objective is to provide an API endpoint that receives T-SQL commands and then replies with data and messages, and it does that very well.
If there’s one non-technical advantage that organisations like time after time about the service, it’s that the hourly cost of using the service includes the software licensing it uses. Businesses using the service no longer need to worry about buying enough SQL Server licensing to run the SQL Server workloads it hosts for them.
However, the service is about to evolve. To complement its Database-as-a-Service capability, Microsoft recently announced a Managed Instance variant of the service. While very little about it has been revealed so far, we know that it’ll provide access to a fully managed instance of the SQL Server database engine, rather than to just a database. Developers and administrators will be able to connect, develop, deploy, and manage in the same way as they would with a traditional installation of the SQL Server database engine. The advantage for them though is that as a PaaS service, Microsoft will manage amongst many things the instance’s patching, backups, and high availability.
While that new variant of the service will undoubtedly accelerate the migration of databases to Azure, it’s not yet available. In fact, all that’s publicly known about it so far, and its complementary Database Migration Service, is what’s in a nine minute MSDN Channel 9 video here. Consequently, this article focuses on migrating to the Database-as-a-Service variant.
Deciding what to migrate
Deciding when and how to move applications from an on-premises data centre to the cloud is a broad subject of its own and beyond the scope of this article. However, Microsoft provides very detailed technical guidance, "Moving Applications to the Cloud", in its Patterns and Practices library that’s available to download from here.
When considering which existing databases make good candidates to migrate to the Azure SQL Database service, rather than perhaps to a regular instance of SQL Server hosted in an IaaS virtual machine, then there are some guiding principles which can help you decide. While the service is functionally rich and high performing, the way the Microsoft delivers the Database-as-a-Service format service introduces some limitations compared to how a traditional instance of SQL Server works. The list below identifies some of the most significant considerations organisations should review when thinking of moving a database to the Azure SQL Database service:
- Single vs. multiple database applications – When a query to one database then accesses or modifies data in another, it uses what’s known as a cross-database reference. While these are natively and transparently supported by traditional instances of SQL Server, Azure SQL Database service doesn’t support them. It does have external table and elastic query features that can be used to create workarounds, but this service works best when an application has a single, albeit sometimes very large and very busy, database.
- Application vendor support – The biggest non-technical consideration is often whether the vendor who supplies an application will still provide support for it when it’s using a PaaS database service. This isn’t usually a problem for applications developed internally, especially those written specifically to use Azure SQL Database, but it can be a blocker to migrating others if the vendor won’t provide support. Generally speaking, the best application databases to move to Azure SQL Database are those developed internally or for applications which can no longer get or need vendor support.
- Server-level features – Some data-centric applications are written to use some of SQL Server’s powerful, but server-level, features such as change data capture or service broker, or some support teams need to use monitoring tools such as SQL Profiler or Performance Monitor. While the Azure SQL Database service provides a strong set of management tools and functionality, there are some features that cannot be provided in a PaaS service. The list of features the service does and doesn’t support is available here and where a required feature isn’t available then either application refactoring to use a supporting Azure service or the use of IaaS virtual machines is usually the alternative.
- Workload sizing – The way that server resources, such as CPU and memory, are allocated to a database hosted by the Azure SQL Database service is very different to a traditional SQL Server database engine. Databases are allocated Database Transaction Units (DTUs), which determine the amount of server resource available to them, or they can share a number of DTUs with other databases in an elastic pool. Knowing the smallest number of DTUs a workload requires is important as the cost of using the service is determined by how many DTUs are allocated. Using a true ‘only-pay-for-what-you-need’ charging model does though introduce the possibility of reducing operating costs by “dialing down” the number of DTUs allocated at quieter times of the day.
Migration assistance
Having decided to migrate a database to the Azure SQL Database service, the best way to start that process is by using Microsoft’s Data Migration Assistant tool. This reviews an existing SQL Server database for known incompatibilities with the Azure SQL Database service and reports about issues that will block a database’s migration to the service as well as code that uses only partially supported features – both valuable insights into whether any development work will be needed. The tool is available to download from here.
Figure 1 – The Azure SQL Database specific options
When a database is ready for migration to the Azure SQL Database service, then the most popular way to upload a database is to create and deploy a database BACPAC. While there are command line methods which can be scripted, Microsoft also provides a GUI based method that uses SQL Server Management Studio and the Azure Portal. Because of the constantly evolving nature of the Azure SQL Database service, and its isolation within the Azure platform, it’s not possible to upload a regular SQL Server database backup file and restore that to the service. Consequently, Microsoft provides comprehensive guidance about how to migrate a database here.
Figure 2 – The database import option in the Azure Portal
Migrating to Azure SQL Database
Today, moving an existing SQL Server database to the Azure SQL Database service isn’t just a case of hosting it using the same server software but on someone else’s compute infrastructure. Instead, it’s a migration to a Platform-as-a-Service variant of the SQL Server database engine. A service that’s designed to support databases for both cloud applications and its on-premises and IaaS virtual machine relations. Having access to cloud scalability, pay-for-what-you-use billing, and a fully managed database is a trade-off though. Databases used by simple single-database applications are usually the easiest to migrate, ageing 3rd party vendor supplied systems the hardest. When they do migrate though, databases hosted by the service can be easier and cheaper to run, and get the newest SQL Server features first.
Gavin Payne is the Head of Digital Transformation for Coeo, a Microsoft Gold partner that provides consulting and managed services for Microsoft data management and analytics technologies. He is a Microsoft Certified Architect and Microsoft Certified Master, and a regular speaker at community and industry events.
Comments
- Anonymous
July 25, 2017
Nice article, thanks.Are there any particular security considerations? e.g. credentials? access control? - Anonymous
August 02, 2017
Great and informative article. I was wondering if we could move our SharePoint 2013 workload over to Azure SQL? - Anonymous
March 13, 2018
I found it, very helpful.