Another useful, if somewhat complicated, free tool from Microsoft is the Database Experimentation Assistant (DEA). Microsoft released DEA version 2.1 on July 25, 2017, with many new improvements over the earlier preview versions as detailed here.
Here is how Microsoft describes the DEA tool:
“Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any new version of the SQL Server will be able to use these analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build higher confidence, making it a successful upgrade experience.”
Unlike the Database Migration Assistant (DMA) that I discussed previously, installing and using DEA is a little more involved. What this tool allows you to do is to record a production or at least production-like workload on one version of SQL Server than then replay that same workload on a different version of SQL Server (on a different server) to see how the new version performs with your data and workload. It could also be used to compare different hardware, storage, or different configuration settings.
The Microsoft Data Migration blog is a good source of addition information about both DMA and DEA.
Additional Resources
My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.
Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.
Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.
Here is a link to the complete series about upgrading SQL Server.