To help make this simpler to achieve, here is a quick overview of the pros and cons that Azure brings to the table in comparison with its more traditional counterpart.
Advantages of using Azure SQL Database
With Azure SQL, Microsoft aims to take a cloud-first approach to providing database capabilities to its clients. There are a whole host of perks that come with migrating to this managed ecosystem, including a few of the following.
High availability
If you have a minimal tolerance for database downtime, Azure is definitely the way to go. It provides the guarantee of 99.99% availability as part of the SLA laid down by Microsoft itself, and there are also plenty of user-definable solutions to prevent outages from occurring. For example, you can choose to effectively mirror your database in secondary geographic locations so that if one instance goes down, another can be up and running almost instantly. In comparison, being able to do this with SQL Server running on hardware for which your business is solely responsible would involve significant costs as well as technical skills. Of course there are tons of great monitoring tools around which make improving SQL Server uptime less of a challenge today. In this comparison of SentryOne, Idera, Quest and Redgate you will see just how far these tools have evolved.
Backups and consistency checks
Backing up your data is important whether you are a home user or a DBA responsible for the requirements of an entire multinational corporation, and in the Azure SQL Database environment this is a responsibility that is taken care of automatically. Whatever the status or purpose of your databases, you can trust that every byte will be backed up and kept safe so that restoring it is straightforward if disaster strikes. All three main types of SQL database backup are used, covering both differential and transaction log backups in combination with full data backup for total peace of mind. Also included in this suite of disaster recovery capabilities is the option to restore a database to a sustainably managed Azure datacenter in an entirely different part of the planet, which can help to account for problems that afflict specific geographic regions. Once again, when using SQL server hosted in-house, you would need to wrangle the backup process yourself, which could prove taxing. Azure will also check for data corruption as part and parcel of its normal operations, so the integrity of your data should remain intact at all times. This is another load off the mind of the average DBA.
Automatic tuning
Tinkering with database performance and staying on the lookout for issues has gotten much easier over the years thanks to the aforementioned monitoring tools, but where Azure sets itself apart is in terms of the automated nature of its tuning potential. While it may not yet be able to tackle the more complex and nuanced aspects of tuning, it is more than capable of harnessing machine learning algorithms to take care of those everyday tuning tasks that would otherwise fall into the lap of the human DBA. From removing duplicate indexes to correcting queries that are not functioning as intended after a change has been made, this automation goes above and beyond what is available with vanilla SQL Server.
Monitor analytics
With the help of Azure SQL Analytics you can access a seemingly unending torrent of data relating to how the system is performing. This delivers advantages not only in terms of moment-to-moment monitoring, but also with regards to providing long term overviews of query activity and resource usage. This also ties into the automation that Azure strives to achieve, as it can highlight disruptive events and give you a better idea about how to fix query blocking and deadlocks. Similar capabilities can be harnessed in SQL Server, although separate software suites will be needed as a bolt-on, often at additional cost.
Disadvantages of using Azure SQL Database
Now that all of the perks of Azure have been laid out, let us take you through the pitfalls that are also present.
Missing SQL Agent
Microsoft is working perpetually to improve the features that are integrated with Azure SQL Database, but for the time being there are still a few absent functions that are noteworthy. Chief amongst these is the lack of SQL agent, which means that completing time-specific tasks is not as straightforward. That is not to say that it cannot be achieved, but rather that to do so you will need to first get a handle on how Azure’s automation works and go from there.
Database Transaction Units confusion
If you are used to managing SQL Server on hardware over which you have direct control, you should know precisely what resources are available to you. In Azure, this is less clear because of the way that DTUs are deployed as a kind of catch-all to cover everything from CPU horsepower to memory and I/O use. In essence you will need to carefully consider your needs and translate this into DTUs as closely as possible so that you know exactly how many you actually need, otherwise you could end up over-committing or under-provisioning.
Conclusion
There are positives and negatives to using any database solution, and Azure’s take on SQL is undeniably innovative no matter which way you look at it. That said, be sure to weigh your options carefully before migrating away from SQL Server for good. Kevin Kline is a Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals. You can follow Kevin on Twitter and LinkedIn.
