We are running the latest bits
Microsoft SQL Server continues its transformational journey by pushing out some key features which would help customers realize the true potential of their data. Incidentally, the SQL Server engineering group uses a large number of SQL Server deployments to manage our lab systems, test run scheduling, engineering dashboards and build systems. These are critical systems for us and without these running as a well-oiled machine, we would not be able to ship our releases, service packs and cumulative updates on time. Well, you get the point, these systems help us in measuring our quality scorecards and service KPIs, managing our engineering rhythms,meeting our deadlines and ensuring that we ship in a timely manner.
The What?
We had these systems running on SQL Server 2014 till recently and we decided to upgrade these systems to SQL Server 2016. And as we ship the next release of the SQL Server 2016 CTP, you now know that all the engineering systems that powered that work was running on the previous version of SQL Server 2016 CTP. What's more, these systems today run on Azure Virtual Machines running SQL Server 2016!
The Why?
What Azure offered was something that the team had been looking to leverage for a while now:
Even these systems see a peak in their workloads which meant that we needed to over-provision these machines for the peak workloads as missing shipping SLAs was not an option for us. The Azure Virtual Machines allowed us to leverage the elastic scale that Azure provides and allowed us to scale up and scaled down when required.
As an added bonus, Azure Virtual Machines provided us with the Automated Patching and Automated Backup options which reduced the efforts required to perform backups or and eliminated the need for scheduling maintenance for patching.
Automated Patching establishes a maintenance window for an Azure Virtual Machine running SQL Server. Automated Updates can only be installed during this maintenance window. For SQL Server, this ensures that system updates and any associated restarts occur at the best possible time for the database. It depends on the SQL Server IaaS Agent.
Automated Backup automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure VM running SQL Server. This enables you to configure regular database backups that utilize durable Azure blob storage.
SQL Server 2016 offers some key enhancements which we have received in many feedback sessions that we have had with customers around the world:
We are constantly adding different tests, dashboards and enhancements to track new or modified workflows which also means that we need to quickly catch performance regressions or performance bottlenecks. This is something that was made very easy through the use of Query Store and Live Query Statistics.
Availability Group enhancements like having load-balanced read-only replicas helped provide reporting capabilities across multiple concurrent users through multiple secondaries without compromising the read/write workload or even oversizing the read-only replica.
Query Store simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. Think of this as a timeline for your query performance where we give you the capability of getting the older performance back if there was a performance regression introduced due to a plan change.
SQL Server Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues. So you don't need to stare a DMV output and wait for the query to finish to know which is the most expensive part of your query plan. This is possible while the query is in-flight!
The Now
We are now able to patch our machines automatically during a scheduled maintenance period, scale as required and take corrective steps more efficiently to prevent performance problems. This allows us to keep up our promise of shipping quality software quicker, more efficiently and in a timely manner!
We have upgraded… Are you thinking of upgrading?
As we upgrade additional systems, look forward to additional insights. If you have a story to share, then you can do so by commenting on this blog or sending a tweet our way at @mssqltiger.
- SQL Server Product Group