This may come as shocking news to some, but Microsoft SQL Server and Microsoft Azure SQL DB (Azure SQL Database, Azure SQL DB, SQL Azure Database, or whatever name you know it as) are very much not the same thing. They may look similar and they may act similar, but they are very much not the same thing. The same applies to other cloud providers who are offering SQL Server in a Platform as a Service (PaaS) offering such as Amazon’s AWS. They aren’t SQL Server and they aren’t Azure SQL, they are somewhere in between. While other cloud providers are just running a managed SQL Server implementation, they aren’t exposing the full SQL Server instance to you so various features won’t be available to you in their PaaS solutions.
As an administrator it’s very important to remember this as planning migrations and designing systems to run on one or the other have VERY different requirements . This is because Microsoft has committed to a Cloud First deployment method where the cloud is always going to be ahead of “the box” (what we install on-premises).
The code behind Microsoft SQL Server and Azure SQL DB is actually very similar. In fact SQL DB is a fork of the SQL Server code, and as new features are written into Azure SQL DB those code changes get merged back into the SQL Server codebase for release in the next service pack or the next major version of the SQL Server product.
The code that’s now included in SQL Server 2016 has been running in Azure SQL DB for months and many of the features which were “introduced” in SQL Server 2016 such as Row Level Security, Dynamic Data Masking, Replica Seeding, etc. were all in use in Azure SQL DB well before they were introduced to the on-premises product.
Because Microsoft is releasing features to the cloud first, this means that treating PaaS services like they are just an extension of the on-premises environment just doesn’t work.
Azure SQL DB for DR
For example, if you wanted to run a SQL Server on-premises and use Azure SQL DB for your DR strategy, my response would be that you’re trying to use the wrong technology. Azure SQL DB and SQL Server are different beasts built to solve different problems. The appropriate DR solution in Azure for a SQL Server running on-premises would be a virtual machine running in Azure IaaS as the Azure IaaS solution would be a like for like solution supporting all the same features. Attempting to use a PaaS solution as a Disaster Recovery solution for an on-premises SQL Server deployment would mean that your DR solution isn’t the same as your production solution with features not being available. While you may not use any of those features today, can you guarantee that 5 years from now you still won’t be using those features?
Moving large dev databases to SQL DB
If you have a large database that you want to move to Azure SQL DB for testing, then you probably don’t want to be running that in Azure SQL DB to begin with. You probably want to run that within a VM. That means just uploading your backup to Azure, or if it’s too large for that just ship it to Microsoft using the import/export service.
If you are planning on using Azure SQL DB for your development platform, and SQL Server on-premises, you’re planning incorrectly. Because SQL DB is different from SQL Server the only development databases which should be hosted in SQL DB are databases which are hosted in SQL DB for production, or which are being moved to SQL DB.
One cloud for production, and one for DR
The thought of using multiple cloud providers for DR and expecting that to “just work” when using PaaS is problematic and naïve. Suggesting this shows a lack of understanding about how PaaS services and the cloud work. Each cloud provider has implemented their cloud solutions a little differently, so using one as DR for another isn’t going to be something which you can do right out of the box. If you did want to put something like that together it would require that your application be written to handle that sort of workload, and error-handling using complex asynchronous messaging to ensure that databases in both cloud providers are up to date with current information.
Even when you are using PaaS a hybrid co-location provider to public cloud provider architecture is a better solution than trying to span public cloud providers. While there are very large firms who broker applications between clouds, this is difficult and requires advanced networking, automation, and coding skills.
Outgrowing SQL DB
There’s the worry that the database might outgrow the PaaS solution. This is definitely a concern for those who are building services for PaaS platforms. However, if you have designed sharding of your database correctly then this shouldn’t be an issue. If you haven’t designed your sharding correctly, then your implementation will be a nightmare and it will need re-architecting in the future. If you plan on simply moving monolithic databases from on-premises to a PaaS database platform, your migration will most likely fail. Back in the early days of Azure SQL Database this was a painful proposition (I believe the initial white paper was 150 pages). Now Microsoft offers a set of tools called elastic scale which greatly reduce any pain associated with sharding your database.
These are the among the reasons that you need to have the person who designs your cloud architecture to have a solid understanding of cloud and the limits of the various options available. Even then, you need someone who can think like an architect: documenting what your solution requirements are, understanding how those requirements are supported by a solution, and how to implement them in a way that works with your solution goals. If the person who’s designing your cloud migration doesn’t understand all the various solutions and options, then the migration is bound to have problems, many of which can be very complex to solve, taking a lot of time and potential a decent amount of outage time to resolve; not to mention wasted consulting dollars.
Not every database (or application) should be running in PaaS. Azure Database is best choice for apps that live in the cloud. If you are developing a new customer facing app, this is probably your best choice. In all likelihood you will never run your ERP system in a PaaS database. Knowing when to place systems in a PaaS solution, when to place systems in an IaaS solution, and when to place systems on-premises is the mark of a true solutions architect.
Denny
The post SQL Azure != SQL Server appeared first on SQL Server with Mr. Denny.
