{"id":1266,"date":"2017-08-28T13:41:48","date_gmt":"2017-08-28T20:41:48","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1266"},"modified":"2020-01-11T12:51:55","modified_gmt":"2020-01-11T20:51:55","slug":"upgrading-sql-server-sql-server-2016-upgrade-considerations","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/","title":{"rendered":"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations"},"content":{"rendered":"

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:<\/p>\n

\n

\u201cMicrosoft 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.\u201d<\/p>\n<\/blockquote>\n

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).<\/font><\/p>\n

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.<\/font><\/p>\n

Here are the steps for the validation and correction process:<\/font><\/p>\n

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

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

     <\/p>\n

    Additional Resources<\/font><\/u><\/strong> <\/p>\n

    My new Pluralsight<\/a> course, SQL Server: Upgrading and Migrating to SQL Server 2016<\/a> has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here<\/a>. <\/p>\n

    Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server<\/a>, taught by myself<\/a> and Tim Radney<\/a>. The first round of this course will be taught in Chicago from October 11-13, 2017. <\/p>\n

    Finally, I will be presenting a half-day session called Migrating to SQL Server 2017<\/a> at the PASS Summit 2017<\/a> 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. <\/p>\n

    Here is a link to the complete series<\/a> about upgrading SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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: \u201cMicrosoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[246,342,353],"tags":[350,352],"class_list":["post-1266","post","type-post","status-publish","format-standard","hentry","category-sql-server-2016","category-sql-server-2017","category-upgrading-sql-server","tag-upgrading-to-sql-server-2016","tag-upgrading-to-sql-server-2017"],"yoast_head":"\nUpgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"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: \u201cMicrosoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-28T20:41:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-11T20:51:55+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/\",\"name\":\"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2017-08-28T20:41:48+00:00\",\"dateModified\":\"2020-01-11T20:51:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/","og_locale":"en_US","og_type":"article","og_title":"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry","og_description":"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: \u201cMicrosoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/","og_site_name":"Glenn Berry","article_published_time":"2017-08-28T20:41:48+00:00","article_modified_time":"2020-01-11T20:51:55+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/","name":"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2017-08-28T20:41:48+00:00","dateModified":"2020-01-11T20:51:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-sql-server-2016-upgrade-considerations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Upgrading SQL Server– SQL Server 2016 Upgrade Considerations"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1266"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1266\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}