As part of the Engineering Services team of the Data Platform within Cloud + Enterprise, we face the same challenges as our customers when it comes to managing SQL server versions, upgrading and moving to Azure.
Earlier this year, we took a hard look at our services. We were running dozens of them, old ones, new ones. When it came to legacy services, some had not been touched for years and were running on SQL server versions dating as far back as 2000 and various versions of 2005 and more. So we were afraid to touch them and did not want to spend the time needed to upgrade as the services were running fine. However, the longer we waited, the harder it got. Sounds familiar?
Back in March we decided to upgrade roughly 150 databases we had in production. We wanted to use the latest version so we could benefit from their innovations and leverage the elasticity of the cloud. Depending on our workflows, we used different options. For about half of them, we migrated them to SQL Azure using Database as a Service model. For about 30% of them, we went with the SQL Azure on VM model using the Infrastructure as a Service model. The rest is a work in progress.
For our IAAS solutions, we consolidated our databases on D4 VMs. We lowered our operation costs and we are dogfooding our own SQL services, preventing bugs to ship into production. By doing monthly upgrades of our 40 or so databases with no downtime and in only three hours, we get access to the latest features.
Before and after the upgrade, we had performance issues in several workflows that we were now able to quickly improve it using the new Query Store which provides us with insight on query plan choice and performance. It simplifies troubleshooting by enabling us 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 our review. It separates data by time windows, allowing us to see database usage patterns and understand when query plan changes happened on the server. Like a flight recorder, it gives us deep insights and enables us to continuously improve our query performance.
Next on our list will be to use stretchDB for our Test Data. We keep years of test run results and we will be able to optimize and improve greatly our typical daily queries on more recent data while still having access to our older data without needing to change our queries and existing apps.
Without hesitation if we had to do it again, we would. We get access to new features monthly, without downtime. Our systems are more stable and optimized. So to answer the question: To upgrade or not to upgrade, we answered yes and don't regret it. What will your answer be?
Check out the latest SQL Server 2016 Preview and running SQL Server on VM.