SQLskills SQL101: Azure SQL Database Monitoring

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

If you are ready to start working with Azure SQL Database, which is Microsoft’s Platform as a Service (PaaS) offering for SQL Server, you will want some good diagnostic queries to use to help you better understand how your databases are performing and to give you more information than is exposed by the Azure Portal. After much delay and procrastination on my part, I have finally developed a version of my SQL Server Diagnostic Information Queries that are tailored for use with Azure SQL Database.

Azure SQL Database Diagnostic Information Queries

Azure SQL Database 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.

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

SQL Server Diagnostic Information Queries for September 2017

On September 1, 2017, Dropbox turned off the sharing feature that I was previously using to host these scripts, which broke hundreds of existing links on my blog. I will be switching to their new sharing method, at least temporarily, until I decide what to do.

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 have made some updates to these queries periodically since January.

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results Spreadsheet

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results Spreadsheet

SQL Server 2005 Diagnostic Information Queries

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!

Posted in: DMV Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

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.

Upgrading SQL Server– SQL Server 2016 Upgrade Considerations

One somewhat obscure issue you might encounter when upgrading from a legacy version of SQL Server to SQL Server 2016 or later is a change to how some data type conversions are handled. Microsoft describes it like this:

“Microsoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types.”

This changed behavior only comes into play when you are running on database compatibility level 130 or later, and it only affects persisted objects (such as persisted computed columns, filtered indexes, indexed views, and indexes that use computed columns).

Because of this potential issue, Microsoft recommends that you do some additional validation checking on your legacy databases before you change their compatibility level to 130 or higher to find out if you might be affected by these changes.

Here are the steps for the validation and correction process:

  1. Restore the legacy database to SQL Server 2016 or later instance in your test environment
  2. Enable global trace flag 139
  3. Run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
  4. Disable global trace flag 139
  5. Change the compatibility level of the database to 130 or later
  6. If Step 3 reveals any issues, then rebuild the objects that it identified

Microsoft has more detail about this change here. Personally, I think this just another step that you should add to your testing plan during an upgrade project.

 

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– Useful Improvements in SQL Server 2017

Even though much of the development effort and resources in SQL Server 2017 has been used to get SQL Server running as a first-class citizen on Linux, there are some interesting new features and improvements in SQL Server 2017 regardless of what operating system you are going to use.

One example are changes in the limits for tempdb initial file size for both tempdb data and log files. In SQL Server 2017, you can set the initial file size as large as 256GB (262,144MB) per file. If you set it to a size larger than 1GB and instant file initialization (IFI) is not enabled, you will get a warning during setup. Keep in mind that IFI only applies to SQL Server data files.

SQL Server 2017 also has a number of new DMV and DMFs that make it easier to manage and monitor your system. One example is sys.dm_db_log_info, which returns the virtual log file (VLF) count and other useful information about your VLFs. This is a more powerful replacement for the old DBCC LOGINFO command.

Another example (which I might be somewhat responsible for, due to much lobbying) are new columns in sys.dm_os_sys_info that reveal very useful processor information, including socket_count, core_count, and cores_per_socket. Getting this information form this DMV is much better that reading the SQL Server Error Log to to to find some of this information.

There are also nice improvements related to doing “smart” differential backups. The sys.dm_db_file_space_usage DMV has a new column, modified_extent_page_count which lets you track differential changes since the last full database backup in order to help decide whether it will be more efficient to take a full database backup instead of a differential backup.

For transaction log backups, there is a new DMF sys.dm_db_log_stats, which has a column called log _since_last_log_backup_mb. By checking the value of this column, you can programmatically decide when to take transaction log backups based on the amount of transaction log activity rather than just on time. This will let you take transaction log backups more frequently during periods of high activity (which can reduce how large the transaction log backup file is and also minimize transaction log file growth. It will also let you take transaction log backups les frequently during periods of low activity, which will reduce the number of transaction log backups that need to be restored, and possibly reduce your RTO times.

Small improvements like this, which are not always well publicized can really make your job easier as a DBA, which is yet another reason to push for an upgrade to SQL Server 2017.

 

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– Top Five Intel Xeon Scalable Processors for SQL Server Usage

On July 11, 2017, Intel formally released their new Xeon Scalable Processor Family of processors for data center usage. This new processor family has a somewhat ridiculous 58 different SKUs in the line. They are broken up into Platinum 8xxx, Gold 6xxx, Gold 5xxx, Silver 4xxx, and Bronze 3xxx sub-categories.

New two-socket server models(which are required for these processors), such as the Dell PowerEdge R740 let you choose from 41 of these available 58 processors (which is also somewhat overwhelming). Microsoft charges the same SQL Server core license prices regardless of the performance of each processor core. Given this, you need to try to pick a particular processor with an awareness of that fact to minimize your licensing costs.

Intel Xeon Bronze and Silver Processors

There are two choices in the Intel Xeon Bronze line, and six choices in the Intel Xeon Silver line. For SQL Server usage, you can and SHOULD ignore those eight processor SKUs from the Bronze and Silver lines. The Bronze processors have low base clock speeds, no Turbo Boost, no Hyper-Threading and low memory speeds. The Silver processors have low base clock speeds and low memory speeds.

Intel Xeon Gold and Platinum Processors

There are 22 different choices between the Intel Xeon Gold 5xxx and 6xxx lines, and 11 different choices in the Intel Xeon Platinum line. Out of these 33 different choices, I think five of them are the “best” choices for SQL Server usage, as measured by their single-threaded CPU performance. Even though these processors are more expensive than the Bronze and Silver lines, the extra hardware cost is a small fraction of the overall system cost, including SQL Server core licenses.

Top Five Intel Xeon Scalable Processors for SQL Server Usage

Here is my top five list, which is ranked by base clock speed and L3 cache size per physical core. Picking a processor from this list is going to give you the best performance for each one of your relatively expensive SQL Server 2016 Enterprise Edition core licenses, which cost $7,128.00 each.

  1. Intel Xeon Gold 5122 (4 cores)
  2. Intel Xeon Gold 6144 (8 cores)
  3. Intel Xeon Gold 6128 (6 cores)
  4. Intel Xeon Gold 6146 (12 cores)
  5. Intel Xeon Gold 6154 (18 cores)

Figure 1 shows my “preferred” processor models at each physical core count, along with some of their specifications and their price. Note that I have not included the “M” suffix processor models (which offer double the memory support for a $3,000.00 premium). Depending on your workload, you may want those for SQL Server usage, but I wanted to simplify the table somewhat.

You should also notice the huge premium that Intel charges for the highest core count SKUs, and think about whether that makes sense in your situation. If you can split your workload across multiple servers, you will be much better off from a performance and cost perspective with multiple servers with lower core count processors. Some choices are relatively bad (such as the Platinum 8170) due to their low base clock speeds.

Preferred Skylake-SP

Figure 1: Preferred Intel Skylake-SP Processors for SQL Server Usage

Figure 2 shows some comparative system metrics for a two-socket server with the given processor. These calculations are based on a single actual TPC-E benchmark score for a Lenovo ThinkSystem SR650 server using a pre-release version of SQL Server 2017. I suspect that we will see better results with the release version of SQL Server 2017.

Looking at these numbers, the Raw TPC-E Score is a measurement of the CPU capacity of the system, while Score/Core shows the single-threaded CPU performance for that particular processor. The License cost column shows how much it would cost to purchase SQL Server 2016 Enterprise Edition core licenses for that system.

The first thing jumps out to me is that the Platinum 8170 is a pretty bad choice compared to the Platinum 8168 or Gold 6154. Selecting the Gold 6154 instead of the Platinum 8170 would save you $114,048.00 in SQL Server license costs and $7,724.00 in hardware costs, with roughly the same capacity and much better single-threaded performance (which will be noticeable to your users).

Another good choice would be two database servers with the Gold 6146 instead of one database server with the Platinum 8180. This would save you $57,024.00 in SQL Server license costs and $6,874.00 in CPU costs (which would pay for a good chunk of the second server). Two servers would have more CPU capacity, twice the memory capacity, twice the number of PCIe 3.0 lanes, and much better single-threaded performance.

Comparative Skylake

Figure 2: Comparative Two-Socket System Metrics for Intel Skylake-SP Processors

 

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– Windows Server 2016 Licensing Issues

If you are planning a complete data platform upgrade, you should be planning on using Windows Server 2016 as your operating system (assuming you are planning on using Windows rather than Linux). Windows Server 2016 has a number of advantages over older versions of Windows Server, including higher license limits for memory, better networking and clustering support, and a longer remaining support lifetime (January 11, 2022 for mainstream support). Windows Server 2012 and Windows Server 2012 R2 will fall out of mainstream support on October 9, 2018, which is not that far away.

One potential issue with Windows Server 2016 is the fact that Microsoft is now using a core-based licensing system for that product. Microsoft requires a minimum of eight core licenses per processor and sixteen core licenses per server. This could potentially be a point of confusion and extra SQL Server licensing costs for some organizations.

Here is the scenario. Imagine that you have done your sizing analysis and calculations for SQL Server 2017 usage, and you have decided that a new two-socket server with two, quad-core Intel Xeon Gold 5122 processors will best suit your performance needs and budget requirements. That particular processor costs $1221.00 each.

Shon Smith, your well-meaning server administrator is aware of the new Windows Server 2016 licensing model, and he wants this new database server to actually have the sixteen physical cores that he was forced to buy Windows Server 2016 core licenses for. He also wants to minimize the hardware cost of this new server. Because of this, he decides to buy a two-socket server with two, eight-core Intel Xeon Bronze 3106 processors (which only cost $306.00 each). Shon has maximized his investment in those sixteen required Windows Server 2016 core licenses, and he has saved $1,730.00 on the hardware. But what has he done to you, the DBA?

First, this new database server now will require sixteen SQL Server 2017 core licenses instead of only eight. For SQL Server 2017 Standard Edition, this means a $14,872.00 increase in your SQL Server license costs, while SQL Server 2017 Enterprise Edition would cost $57,024.00 extra due to this hardware change. But you have twice as many physical cores, so that must be better, right?

Actually, not at all in this case. The Intel Xeon Bronze 3106 has a base clock speed of only 1.7GHz, with no Turbo Boost, no Hyper-Threading and an 11MB L3 cache. The Intel Xeon Gold 5122 that you specified has a base clock speed of 3.6GHz, a Turbo Boost speed of 3.7GHz, along with Hyper-Threading and an 16.5MB L3 cache.

The system you originally specified would have more than double the single-threaded CPU performance compared to Shon’s modified system. Your original system would also have more total CPU capacity than Shon’s modified system. The Intel Xeon Bronze 3106 is a terrible choice for SQL Server usage!

Keep in mind that most SQL Server instances only require Windows Server 2016 Standard Edition (rather than Datacenter Edition). Those sixteen required core licenses for Windows Server 2016 Standard Edition only cost $882.00 total, not each. This means that you should not let that required minimum Windows Server 2016 core license count sway your processor choice for SQL Server.

This exercise assumes that SQL Server 2017 licenses have the same pricing model as SQL Server 2016 licenses. It also uses a particularly bad processor choice by Shon, to illustrate the point. Using two eight-core Intel Xeon Gold 6144 processors would have given you much better performance and scalability (at a higher hardware cost) for that extra SQL Server license cost.

 

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– SQL Server Support Dates

One valid argument for considering a SQL Server upgrade sooner rather than later is the fact that the version(s) of SQL Server you are using in Production may be out of mainstream or even extended support. But what does that mean exactly?

It does NOT mean that out of support versions suddenly stop working. It does mean that they won’t receive any more regular servicing updates in the form of Service Packs or Cumulative Updates. You may also have issues where 3rd Party applications are no longer supported on out of support versions of SQL Server.

To be fair, you can also run into the opposite issue, where 3rd Party applications are not supported on newer versions of SQL Server. Figure 1 shows the relevant support dates for SQL Server 2000 through SQL Server 2016.

Picture6

Figure 1: SQL Server Support Dates

If your SQL Server version is out of support, it is also likely that the version of Windows that it is running on will be out of support. Figure 2 shows the support end dates for Windows Server 2003 up to Windows Server 2016. Both Windows Server 2012 and Windows Server 2012 R2 will fall out of mainstream support on October 9, 2018, which is not that far away as I write this. From a support perspective, Windows Server 2016 gives you a lot more time in a supported state.

Picture7

Figure 2: Windows Server Support Dates

Microsoft recently announced a new program called Premium Assurance, which lets you add six more years of product support to 2008 or newer versions of SQL Server or Windows Server, for an escalating annual cost (based on when you purchase it). The annual cost is a percentage of the original total license cost, that went up to 7% on August 1, 2017. The cots will go up to 9% on July1, 2018. This program is an add-on to Software Assurance. This is a pretty expensive option that I would not prefer to use, for both economic and technical reasons, unless I had no other choice.

 

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.