Upgrading SQL Server–Migrating with Database Mirroring

Another relatively quick and easy way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use database mirroring. You have to have each user database in Full recovery model before you can set up a database mirroring partnership, and it needs to stay in full recovery model at all times. When you set up a database mirroring partnership, both the principal instance and the mirror instance need to be on the same edition of SQL Server (meaning Standard to Standard or Enterprise to Enterprise). It is possible to do a rolling edition upgrade with database mirroring, as I describe here.

Database mirroring was deprecated in SQL Server 2012, but it still works just fine in SQL Server 2016 and in SQL Server 2017. Early in the SQL Server 2017 development cycle, Microsoft had hinted that they were actually going to remove database mirroring from the product for SQL Server 2017, but they later changed their mind, and I think they may never actually remove it from the product.

Database mirroring is supported as an upgrade/migration method if the principal instance is running SQL Server 2008 SP3 or later, SQL Server 2008 R2 SP2 or later, SQL Server 2012 SP1 or later, or SQL Server 2014, and the secondary instance is running SQL Server 2016 or SQL Server 2017. The database failover is a one-time, one-way trip when you go from an older version of SQL Server to a newer version of SQL Server. Microsoft has some guidance about this upgrade process here.

Compared to log shipping, database mirroring is a little more complicated to get set up, and it has more restrictions. Database mirroring is easier to set up if both servers in the partnership are in the same Windows Domain, but if this is not the case, you can use certificates for your endpoint authentication.

Synchronous database mirroring lets you run a simple command to fail over each mirrored user database from the principal instance to the mirror instance (as long as each database is in a synchronized state). This fail over typically takes about 5-10 seconds.

Here are the basic steps for doing the final Production migration to a newer version of SQL Server:

  1. Stop the application(s) that are using the principal database(s) on the primary server
  2. Make sure each database is in a synchronized state and is using synchronous mirroring
  3. Fail over each mirrored user database, using a T-SQL script
  4. Remove the mirroring partnership, using a T-SQL script
  5. Redirect the applications to the new database server

Steps three and four can usually be done in less than a minute, depending on how many databases you have, how much transaction log activity has occurred, and your network and storage infrastructure performance.

 

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.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Migrating with Log Shipping

One easy and quick way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use log shipping. Log shipping may not be new and shiny like availability groups, but it has very few blockers for implementation, and it does not add any additional workload to your legacy databases after you get it running. You do have to have each database in Full or bulk-logged recovery model before you can use log shipping. If you have a database in simple recovery model, and you try to enable log shipping, you will get a message like what you see in Figure 1.

image

Figure 1: Log Shipping Error Message

 

Log shipping does not have automatic fail over out of the the box, but you can pretty easily write some T-SQL to run the proper SQL Server Agent jobs on the primary and secondary server, check the status of the secondary database, and then recover the secondary database and bring it online on the new server. Doing all of this can semi-automate your fail over process.

Here are the basic steps for doing the final Production migration:

  1. Stop the application(s) that are using the primary database(s) on the primary server
  2. Run the LSBackup Agent job on the primary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSBackup_AdventureWorks2014′;
  3. Run the LSCopy Agent job on the secondary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSCopy_LABDB03_AdventureWorks2014′;
  4. Run the LSRestore Agent job on the secondary server for each log shipped database
    1. EXEC msdb.dbo.sp_start_job N’LSRestore_LABDB03_AdventureWorks2014′;
  5. Check the recent history of the log shipped databases on the secondary server
    1. Query the log_shipping_monitor_history_detail table in msdb
  6. Restore each log shipped database on the secondary WITH RECOVERY
    1. RESTORE DATABASE AdventureWorks2014 WITH RECOVERY;
  7. Redirect the applications to the new database server

Steps two through six can usually be done in just a few minutes, depending on how many databases you have, how much transaction log activity has occurred, and your network and storage infrastructure performance.

 

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.

 

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Preferred AMD EPYC 7000 Processors

Yesterday I talked about the critical importance of thoughtful processor selection for SQL Server as part of the upgrade and migration process, and I supplied a list of “preferred” Intel Scalable Processor family (Skylake-SP) processors at various physical core counts.

Today, I am going to cover a similar list for the new AMD EYPC 7000 Series of processors for one and two-socket servers that were launched on June 20, 2017. These are based on the same Zen architecture used on the AMD Ryzen desktop processors. The series begins with two-socket processor models designed to offer more physical cores, memory bandwidth, and PCIe 3.0 lanes compared to the current Intel Scalable Processor family or the previous generation Intel Xeon E5-2600 v4 family processors.

There are nine different models for two-socket servers, ranging from the eight-core EPYC 7251 to the 32-core EPYC 7601. All of these models have SMT (AMD’s version of hyper-threading), and Max Boost (AMD’s version of Turbo Boost). They also offer eight-channels of DDR4-2666 support (that have a total capacity of 2TB of RAM per socket) and 128 PCIe 3.0 lanes per socket.

There are three models specifically for one-socket servers (that have a P model number suffix), ranging from the 16-core EPYC 7351P to the 32-core EPYC 7551P. You can also use a non-P SKU in a one-socket server. All of these models have SMT, and Max Boost. They also offer eight-channels of DDR4-2666 support (that have a total capacity of 2TB of RAM) and 128 PCIe 3.0 lanes per socket. Unlike Intel, AMD does not artificially cripple some SKUs for product differentiation purposes.

Each physical processor has four Core Complexes (CCX) tied together with something AMD calls Infinity Fabric. Infinity Fabric consists of a Scalable Data Fabric (SDF) and a Scalable Control Fabric (SCF), and it is used for both intra-processor and socket-to-socket communication. Each physical processor shows up as four NUMA nodes in Windows.

Unlike Intel, AMD is not increasing the base clock speed in the lower core count models. They do have multiple SKUs available with 16, 24, and 32 physical cores, with slightly different base and max boost clock speeds.

For SQL Server usage, you would still want the “top of the line” SKU for a given physical core count, to get the most performance for each physical core license that you buy. These EPYC systems will have a lot of PCIe 3.0 lanes and very high memory density, so they might work very well for large SQL Server DW/Reporting workloads.

Compared to Intel, the number of processor choices (and confusion is much lower), as shown in Figure 1.

Picture4

Figure 1: Preferred AMD EPYC 7000 Series Processors for SQL Server Usage

Patrick Kennedy at ServeTheHome has been doing a lot of benchmarking and good analysis of early AMD EPYC systems, showing the importance of using DDR4 2666 memory, and having new BIOS/firmware updates.

 

Additional Resources

I have a 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.

 

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Preferred Intel Xeon Scalable Processors

One crucial part of doing a complete data platform upgrade is proper hardware and processor selection. Because of how SQL Server 2012 and newer core-based licensing works, processor selection is extremely important from a performance/scalability perspective and from a license cost perspective. Often, your SQL Server core license costs can far outweigh the hardware and storage costs, and a poor processor selection can make this situation much worse than it has to be.

On a non-virtualized server, every single physical core in the machine must have a SQL Server core license, with a minimum of four physical cores per socket. For licensing purposes, it does not matter if you have disabled any of cores in the BIOS, you still have to pay for them. On a virtualized server, you have two main choices. First, you can just license the virtual cores for each VM (which might map to underlying logical or physical cores in the host), with a minimum of four virtual cores per VM. Your other choice (if you have Microsoft Software Assurance) is to license all of the physical cores in the entire host, after which, you can create as many VMs with as many virtual cores as you like, assuming you are running Windows Server 2016 Datacenter Edition.

It also does not matter (to Microsoft) whether each core has good or bad performance, the license cost is exactly the same. Microsoft explains the Compute Capacity Limits by Edition of SQL Server to help make this more clear. What this all means is that you want to pick a particular processor at a given physical core count that has the best single-threaded performance possible, in order to maximize the value you get from each SQL Server core license. Quite often, you can and should pick a lower core count processor SKU that runs at a higher base clock speed and also may have more L3 cache per core rather than a high core count processor SKU (from the same processor family).

Intel recently introduced their new 14nm Intel Xeon Scalable Processor family for server usage. These processors use a completely new naming convention from previous Xeon families, and they are divided into Platinum, Gold, Silver, and Bronze lines. For SQL Server usage, I would avoid the Silver and Bronze lines completely, since they have greatly reduced clock speeds and often do not have Turbo Boost or hyper-threading. Their lower hardware cost is a false economy, since you give up so much performance per core, while your license costs remain the same per core.

Instead, you should focus primarily on the Gold line (unless you really need extremely high core counts or eight-socket support, which requires a Platinum model). Within the Gold line there are a number of particular processor SKUs that I think are the “best” choice for SQL Server at a particular physical core count. These choices are shown in Figure 1.

Picture2

Figure 1: Preferred Intel Scalable Processor SKUs for SQL Server Usage

Intel needlessly complicates this by their greedy product differentiation efforts. For example, they have some SKUs with an M suffix that support 1.5TB of RAM per socket instead of the 768GB of RAM per socket you get with a SKU that does not have the M suffix (so Xeon Platinum 8180M vs. Xeon Platinum 8180, for example). An M SKU means a $3,000.00 price increase, which you may want to pay for some SQL Server workloads. Keep in mind that Windows Server 2016 has a 24TB RAM limit, which you still would not hit with an eight-socket server with eight Xeon Platinum 8180M processors.

For most SQL Server workloads, you should be looking at the 4, 6, 8, or 12 core models in Figure 1. For example the 12-core Xeon Gold 6146 has a higher base clock speed and more L3 cache/core compared to the 14-core Xeon Gold 6132 or 16-core Xeon Gold 6142 models, so that you will actually have more capacity and better single-threaded performance with the Xeon Gold 6146, at a much lower SQL Server license cost.

The bottom line here is that you really need to some careful analysis to avoid making an expensive mistake. Make sure you don’t let Shon, the server admin make your hardware selection choices with no input from you!

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–August 8, 2017 Updates

Part of the upgrade and migration process is keeping your existing SQL Server instances properly patched. Being on a minimum Service Pack level is often required in order for some types of upgrade and migration operations. It is also required to be in a fully supported state from Microsoft. It is also just a good idea!

On August 8, 2017, Microsoft had a multitude of updates and new releases that are relevant for Windows and SQL Server. First, it was “Patch Tuesday” for August 2017, so there were a number operating system updates, Internet Explorer updates, Edge updates, etc. Then, if you have Microsoft Update installed (which is a superset of Windows Update), you would also be offered security updates to SQL Server 2012, SQL Server 2014, and SQL Server 2016 (for currently supported Service Pack levels).

Microsoft also released Cumulative Updates (CU) for SQL Server 2012, SQL Server 2014, and SQL Server 2016 (for currently supported Service Pack levels). The CUs also include the security hotfix offered by Microsoft Update (which also included the complete CU). So one way or another, if you took either update method, you should be fully up to date, whether you like it or not.

Actually, I think this is a good thing. Microsoft has been recommending proactive installation of CUs since March 24, 2016, as detailed in their Announcing updates to the SQL Server Incremental Servicing Model (ISM) post. As a consultant, I always advise people to regularly, explicitly install CUs on their own schedule, after they have done their own testing.

The Microsoft SQL Server Release Services blog has posts listing all of these new CUs.

 

SQL Server 2012

SQL Server 2012 fell out of mainstream support on July 11, 2017. Despite this, Microsoft decided to release SQL Server 2012 SP3 CU10 (with only five public hotfixes) on August 8, 2017, probably because of the security hotfix. They are also due to release SQL Server 2012 SP4 in September, which will have a lot of manageability and performance improvements back-ported from SQL Server 2014 SP2.

Cumulative update 10 for SQL Server 2012 SP3

Announcing SQL Server 2012 Service Pack 4

 

SQL Server 2014

If you are running SQL Server 2014, you have hopefully moved to the SP2 branch by now, since it has a lot of manageability and performance improvements. It will also be a “supported Service Pack” for a longer period.

Cumulative Update 13 for SQL Server 2014 SP1

Cumulative Update 6 for SQL Server 2014 SP2

 

SQL Server 2016

If you are running SQL Server 2016, you have hopefully moved to the SP1 branch by now, since it has the new programmability features in Standard Edition, and many other manageability and performance improvements. It will also be a “supported Service Pack” for a longer period.

Cumulative Update 7 for SQL Server 2016

Cumulative Update 4 for SQL Server 2016 SP1

 

SQL Server Management Studio

Microsoft also released SSMS 17.2 on August 8, 2017. They recommend that you use this version of SSMS, even if you are running a down-level version of SQL Server. It is officially supported all the way back to SQL Server 2008, but it should actually work pretty well on SQL Server 2005 and even SQL Server 2000.

Download SQL Server Management Studio (SSMS)

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Upgrade Methods

Back in December of 2012, I wrote a fairly long article called Making the Case for a SQL Server Platform Refresh that made the argument that late 2012 was a good time to make a compelling case for a complete platform refresh, where you would get new server hardware and storage, with a new operating system, and a new version of SQL Server, all at the same time.

In January of 2015, I made a similar argument in this article, SQL Server Data Platform Upgrade in 2015, talking about the upcoming SQL Server 2016 and Windows Server 2016 (which were still called vNext at the time).

Now, in August of 2017, I think it is also a good time to be considering a complete platform refresh in late 2017 or early 2018, since we have new server processor families from both Intel and AMD (which require new model servers), we have the release of SQL Server 2017 coming pretty soon, and we also have a fairly major refresh of Windows Server 2016 (called Windows Server 2016 RS3) coming later this year. Once again the stars are aligned, and it just makes sense to start planning how to upgrade and consolidate your legacy database servers.

Upgrade in Place

There are several ways to actually conduct an upgrade and migration effort. One fully supported method from Microsoft is to do an “upgrade in place”, where you simply run the setup program for your new version of SQL Server, and it installs the new binaries for on top of your old version of SQL Server, and upgrades all of your databases to the new version.

Personally, this would not be my first choice for an upgrade and migration method, for a number of reasons. First, there is some risk that the upgrade in place operation will not succeed, or that your applications will have functionality or performance issues after an upgrade.

Second, you will have to take a fairly long outage during the installation process, that could have been avoided with other upgrade and migration methods. This is also a stressful upgrade method for you and your organization, since you are under time pressure to complete it, and you are not completely sure everything will work when you are done.

Finally, an upgrade in place means using your existing hardware and storage, with your existing operating system. This means you may be losing a lot of performance and scalability, and you may be paying much more in SQL Server license costs than necessary.

Complete Platform Upgrade

A much better alternative is to do a complete platform upgrade, using at least one new server (but hopefully as many as you need to upgrade and consolidate every legacy database server). With this method, you can take as long as necessary to prepare, patch, configure, and test the new server and storage, along with the new version of the operating system and new version of SQL Server, until you and your organization are completely confident about the new platform. Once you reach this point, you can then decide when to do the final production migration of your data, using a method (such as log shipping or database mirroring) that will let you take just a brief outage (probably less than a minute) to fail over to the new server.

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.

SQL Server Diagnostic Information Queries for August 2017

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea.

Rather than having a separate blog post for each version, I have just put the links for all seven major versions in this single post. There are two separate links for each version. The first one on the top left is the actual diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs that correspond to each query in the set.

Here are links to the latest versions of these queries for SQL Server 2017, 2016, 2014 and 2012:

SQL Server 2017 Diagnostic Information Queries

SQL Server 2017 Blank Results Spreadsheet

SQL Server 2016 Diagnostic Information Queries

SQL Server 2016 Blank Results Spreadsheet

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results Spreadsheet

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results Spreadsheet

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2017 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2017)

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries (CY 2017)

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries (CY 2017)

SQL Server 2005 Blank Results Spreadsheet

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I am not planning on moving these to Github any time soon.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries, or that someone is running an incorrect version of the script for their version of SQL Server.

It is very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three related Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Upgrading SQL Server–Supported Migration Methods

When it comes down to actually migrating from a legacy version of SQL Server to SQL Server 2016/2017, there are several different methods for performing the migration, depending on what legacy version of SQL Server your are using. Here is a high-level overview of the various migration methods, by version:

Migrating from SQL Server 2005

Backup and Restore: You can restore a SQL Server 2005 database backup to SQL Server 2016. The database compatibility level will be automatically changed to 100 when you do this

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2005 to SQL Server 2016

 

Migrating from SQL Server 2008 or SQL Server 2008 R2

Backup and Restore: You can restore a SQL Server 2008 or SQL Server 2008 R2 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2016

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

 

Migrating from SQL Server 2012

Backup and Restore: You can restore a SQL Server 2012 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2012 to SQL Server 2016

Availability group: Availability Groups are supported if the primary replica is running SQL Server 2012 SP2 or later and the secondary replicas are running SQL Server 2016. The AG failover is a one-time, one-way trip

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2012 SP1 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2012 SP1 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported

 

Migrating from SQL Server 2014

Backup and Restore: You can restore a SQL Server 2014 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2014 to SQL Server 2016

Availability group: Availability Groups are supported if the primary replica is running SQL Server 2014 and the secondary replicas are running SQL Server 2016. The AG failover is a one-time, one-way trip

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2014, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2014, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported

 

Of these various methods, I generally prefer log shipping or database mirroring, since they are both easy to configure and use, and they offer very short down times for the actual Production migration. It is important that your legacy version of SQL Server has been patched to a new enough Service Pack (preferably the latest one available) so that you can use one of these methods.

 

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Standard Edition License Limits

SQL Server Standard Edition License Limits

One thing you need to consider as you are contemplating an upgrade and migration effort from a legacy version of SQL Server Standard Edition to a new version of SQL Server Standard Edition are the per instance license limits for memory, sockets, and physical cores.

This is far from purely an academic exercise. If you inadvertently use hardware or a VM that exceeds these per-instance license limits for SQL Server Standard Edition, you could have an unbalanced configuration that does not perform as well as it could. You could also be forced to pay for SQL Server Standard Edition core licenses that you are not even allowed to use in a single instance.

SQL Server 2008 Standard Edition

SQL Server 2008 Standard Edition has a license limit of four processor sockets. This applies whether it is virtualized or not. Since SQL Server 2008 used processor-based licensing, there is no separate, lower license limit to the number of processor cores, beyond the 64 logical processor limit shared with SQL Server 2008 Enterprise Edition.

SQL Server 2008 Standard Edition can use up to the operating system limit for RAM, just like SQL Server 2008 Enterprise Edition. Since SQL Server 2008 will run on Windows Server 2012 R2, you could conceivably have up to 4TB of RAM with SQL Server 2008 Standard Edition, although most people will be running SQL Server 2008 Standard Edition on Windows Server 2008 R2 or older, which have lower RAM limits. Typical two-socket servers from 2008-2009 would only support anywhere from 32GB to 288GB of RAM.

SQL Server 2008 R2 Standard Edition

SQL Server 2008 R2 Standard Edition also has a license limit of four processor sockets. This applies whether it is virtualized or not. Since SQL Server 2008 R2 still used processor-based licensing, there is no separate, lower license limit to the number of processor cores, beyond the higher 256 logical processor limit shared with SQL Server 2008 R2 Enterprise Edition. Of course 2010-2011 vintage two-socket servers only had up to 32 logical processors, so this wasn’t really an issue.

Unfortunately, SQL Server 2008 R2 Standard Edition had a new memory license limit (for the Database Engine) of 64GB per instance, while 2010-2011 vintage two-socket servers supported up to 288GB of RAM. This new license limit for Standard Edition was the start of a trend that continues up to the present day.

SQL Server 2012 Standard Edition

SQL Server 2012 made the move from processor-based licensing to core-based licensing. SQL Server 2012 Standard Edition is limited to four processor sockets, or 16 physical cores, whichever is lower. Again, this applies whether the instance is virtualized or not. Intel Xeon E5-2697 v2 processors had 12 physical cores, so you could exceed this license limit on a two-socket commodity server during the period before SQL Server 2014 was released.

Unfortunately, SQL Server 2012 Standard Edition had the same memory license limit (for the Database Engine) of 64GB per instance, while 2012-2013 vintage two-socket servers supported up to 768GB of RAM. The gap between the artificial license limit and what a commodity two-socket server could support had grown larger.

SQL Server 2014 Standard Edition

SQL Server 2014 Standard Edition is also limited to four processor sockets, or 16 physical cores, whichever is lower. Again, this applies whether the instance is virtualized or not. Intel Xeon E5-2699 v3 processors had 18 physical cores, so you could also exceed this license limit on a two-socket commodity server during the period before SQL Server 2016 was released.

SQL Server 2014 Standard Edition raised the memory license limit (for the Database Engine) to 128GB per instance, while 2014-2016 vintage two-socket servers supported up to 768GB of RAM. The gap between the artificial license limit and what a commodity two-socket server could support had grown smaller, which was a good thing.

SQL Server 2016 Standard Edition

SQL Server 2016 Standard Edition is still limited to four processor sockets, or 24 physical cores, whichever is lower. Again, this applies whether the instance is virtualized or not. The core limit increase was a welcome improvement. Intel Xeon E5-2699A v4 processors had 22 physical cores, so you could also exceed this license limit on a two-socket commodity server during the period before SQL Server 2017 was released.

SQL Server 2016 Standard Edition kept the memory license limit (for the Database Engine) at 128GB, per instance, while 2017 vintage two-socket servers now support up to 3TB of RAM (for Intel) or 4TB (for AMD) of RAM. The gap between the artificial memory license limit and what a commodity two-socket server can support has grown much larger, which is a bad thing. One improvement with SQL Server 2016 SP1 was separate memory limits of 32GB per instance for Columnstore segment cache usage, and 32GB per database for in-memory OLTP usage.

SQL Server 2017 Standard Edition

So far, Microsoft has not publicly announced any plans to raise the per-instance socket count, core count or memory limits for SQL Server 2017 Standard Edition over what they are in SQL Server 2016 Standard Edition with Service Pack 1.

Personally, I think this is a short-sighted policy that ignores the realities of modern two-socket commodity server hardware. Currently available Intel-based two-socket servers, such as the Dell PowerEdge R740 have Intel Xeon Scalable Processor Family processors, such as the Intel Xeon Platinum 8180M, which has 28 physical cores, and supports 1.5TB of RAM per socket. Upcoming AMD EYPC 7600 series processors have up to 32 physical processor cores and support up to 2TB of RAM per socket.

If someone in your organization purchased a two-socket Dell PowerEdge R740 with two Intel Xeon Platinum 8180M processors, and then installed SQL Server 2016 Standard Edition on your new server, here is what would happen:

First, by default, SQL Server would run all 24 of its license-limit physical cores on the first processor in the machine, only using 24 of the 28 actual cores in the first processor, with nothing running on the second processor. You can correct this issue using an ALTER SERVER CONFIGURATION command, so that you are using 12 physical cores on each physical processor, as I discuss here.

Second, Microsoft would still expect you to license all of the physical cores in the entire machine, even though you cannot use them in a single instance. For 56 physical cores times $1,858/core for SQL Server 2016 Standard Edition, that would be $104,076.00 for the entire machine. The extra license cost, for cores you cannot use in a single instance is $59,456.00. Not to mention that this single instance would also be limited to 128GB of RAM for the Database Engine, plus the extra memory limits for Columnstore and In-memory OLTP usage.

This is a worst case scenario example. Microsoft would probably argue that you could install multiple instances of SQL Server 2016 Standard Edition (at no extra cost) to allow you to actually use all of those core licenses that you had to buy. They would also argue that you could install SQL Server 2016 Standard Edition in a VM, and then only have to pay for the VM license costs.

Even so, I think that is a less than optimum situation. Microsoft should consider raising the Standard Edition core and memory limits to a more realistic level, and they should also clarify the licensing so that they explicitly don’t expect you to pay for core licenses that you cannot use in a single instance.

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.

Upgrading SQL Server–Generational Performance Increases for Two-Socket Servers

As I noted yesterday, Lenovo recently submitted a TPC-E benchmark result for a Lenovo ThinkSystem SR650 two-socket system, with two 14nm Intel Xeon Platinum 8180 28-core Skylake-SP processors and 1.5TB of DDR4 RAM. This system had a raw score of 6,598.36, and it has 56 physical processor cores. This flagship processor has a relatively low base clock speed of 2.5 GHz, with a Max Turbo frequency of 3.8 GHz. It also costs $10,009.00 for each processor.

So far, this is the only TPC-E benchmark submission for the new Intel Xeon Scalable Processor family (Skylake-SP). This submission is using SQL Server 2017. What is interesting is that it was done on June 27, 2017, which was several weeks before even SQL Server 2017 RC1 was released. Normally, Microsoft does not allow people to publish benchmark results on pre-release software, and they definitely have veto power over any TPC-E submissions. I am confident that the RTM version of SQL Server 2017 will have even better performance, especially since the release notes for SQL Server 2017 RC2 (which was available on August 2, 2017), states “This release contains bug fixes and performance improvements.”

A more affordable and faster processor choice for most SQL Server workloads is an eight-core processor for a two-socket server. Back in in Q2 of 2012, Intel released the 32nm Intel Xeon E5-2690 eight-core Sandy Bridge-EP processor, with a base clock speed of 2.9 GHz. Since then, they have released four new generations of two-socket Xeon processors, culminating in the current 14nm Intel Xeon Gold 6144 eight-core Skylake-SP processor that was released in Q3 of 2017. The comparative capacity and single-threaded performance of these five generations of Intel Xeon eight-core processors is shown in Figure 1.

Picture5

Figure 1: Generational Performance Increases for Eight-Core Intel Processors for Two-Socket Systems

What we have seen over the past five years is a definite slowing of single-threaded performance increases from these high clock speed eight core processors. Even so, in my experience, many organizations have historically done a pretty poor job of processor selection, so many legacy servers are not equipped with what was considered a “frequency-optimized” processor from a given processor family for a database server.

If you are building the case for an upgrade and migration effort, you need to consider the performance characteristics of your legacy hardware vs. what performance is possible with well-chosen new server hardware. Making good choices can allow you to have much better performance and more capacity, along with lower SQL Server licensing costs. Unfortunately, this won’t happen automatically. You have to stay involved and help make sure that the proper analysis and decisions are being made.

 

Additional Resources

I have a 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.

Here is a link to the complete series about upgrading SQL Server.