Upgrading SQL Server– Intel Xeon-W Family

On August 30, 2017, Intel released their new Intel Xeon Processor W Family of one-socket workstation processors (hence the W) based on the Skylake-SP Xeon server processors. These processors are meant as a replacement for the older one-socket Intel Xeon E5-1600 v4 series that had up to eight physical cores. The Xeon-W processors will have between four and eighteen physical cores, support up to 512GB of RAM and have 48 PCIe 3.0 lanes.

These processors are essentially Xeon versions of the Skylake-X Core i9 HEDT processors with all of the professional features enabled such as extended memory support, vPro, Intel’s AMT, and the standard enterprise Reliability, Serviceability and Availability (RAS) features. They will require a new C422 chipset, and they will not work in existing X299 chipset motherboards.

These processors give you another possible choice for buying or building a powerful, professional level workstation. Value-wise, a system based on one of the AMD Ryzen Threadripper processors is going to be a much better option for most people. Anandtech is a decent write-up here.

 

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.

Upgrading SQL Server– Database Experimentation Assistant

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.

Upgrading SQL Server– Microsoft Data Migration Assistant 3.2

One useful tool that you should definitely take advantage of during an upgrade and migration effort is the Microsoft Data Migration Assistant (DMA), which replaces the old SQL Server Upgrade Advisor. Microsoft released the new DMA 3.2 on August 25, 2017.

This tool can be used for three main purposes. First, you can use it to find breaking changes, behavior changes, and deprecated features between a legacy version of SQL Server as the source (SQL Server 2005 and later) and a newer version of SQL Server as the target (SQL Server 2012, SQL Server 2014, SQL Server 2016, and Azure SQL Database).

Second, it can be used to identify new features in the target version of SQL Server (or Azure SQL Database) that might be useful given your legacy database schema and data size. Be forewarned that DMA has a heavy bias towards Azure SQL Database and other cloud-dependent features. For example, it is very eager to recommend Stretch database for large tables.

Finally, DMA can be used to actually migrate databases between legacy versions of SQL Server and a modern version of SQL Server or Azure SQL Database. An example of an assessment project is shown in Figure 1.

image

Figure 1: Microsoft Data Migration Assistant 3.2

If you are considering using DMA 3.2 to migrate existing on-premises databases to an Azure SQL Database, Microsoft’s Harini Gupta did some interesting benchmark testing showing how the Azure SQL Database’s service objective (S0, P1, P15, etc.) and the number of parallel tasks for the migration can affect the elapsed time for the migration.

 

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.