Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/glenn/wp-config.php on line 94

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":1228,"date":"2017-08-07T10:46:46","date_gmt":"2017-08-07T17:46:46","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1228"},"modified":"2020-01-11T12:51:53","modified_gmt":"2020-01-11T20:51:53","slug":"upgrading-sql-server-day-7","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/","title":{"rendered":"Upgrading SQL Server–Supported Migration Methods"},"content":{"rendered":"

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

Migrating from SQL Server 2005<\/font><\/u><\/strong><\/p>\n

\n

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

Migration tools: You can use the Microsoft Data Migration Assistant<\/a> (DMA), which is especially useful for moving server level objects<\/font><\/p>\n

Bulk load: You can bulk copy tables from SQL Server 2005 to SQL Server 2016<\/font><\/p>\n

<\/font> <\/p>\n<\/blockquote>\n

Migrating from SQL Server 2008 or SQL Server 2008 R2<\/font><\/u><\/strong><\/p>\n

\n

Backup and Restore: You can restore a SQL Server 2008 or SQL Server 2008 R2 database backup to SQL Server 2016<\/font><\/p>\n

Migration tools: You can use the Microsoft Data Migration Assistant<\/a> (DMA), which is especially useful for moving server level objects<\/font><\/p>\n

Bulk load: You can bulk copy tables from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2016<\/font><\/p>\n

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

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

 <\/p>\n<\/blockquote>\n

Migrating from SQL Server 2012<\/font><\/u><\/strong><\/p>\n

\n

Backup and Restore: You can restore a SQL Server 2012 database backup to SQL Server 2016<\/font><\/p>\n

Migration tools: You can use the Microsoft Data Migration Assistant<\/a> (DMA), which is especially useful for moving server level objects<\/font><\/p>\n

Bulk load: You can bulk copy tables from SQL Server 2012 to SQL Server 2016<\/font><\/p>\n

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

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

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

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported<\/p>\n

 <\/p>\n<\/blockquote>\n

Migrating from SQL Server 2014<\/font><\/u><\/strong><\/p>\n

\n

Backup and Restore: You can restore a SQL Server 2014 database backup to SQL Server 2016<\/font><\/p>\n

Migration tools: You can use the Microsoft Data Migration Assistant<\/a> (DMA), which is especially useful for moving server level objects<\/font><\/p>\n

Bulk load: You can bulk copy tables from SQL Server 2014 to SQL Server 2016<\/font><\/p>\n

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

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

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

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported<\/p>\n

 <\/p>\n<\/blockquote>\n

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

<\/font>  <\/p>\n

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

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

I have a 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. <\/p>\n

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

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 […]<\/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-1228","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–Supported Migration Methods - 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-day-7\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Upgrading SQL Server–Supported Migration Methods - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-07T17:46:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-11T20:51:53+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=\"4 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-day-7\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/\",\"name\":\"Upgrading SQL Server–Supported Migration Methods - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2017-08-07T17:46:46+00:00\",\"dateModified\":\"2020-01-11T20:51:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Upgrading SQL Server–Supported Migration Methods\"}]},{\"@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–Supported Migration Methods - 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-day-7\/","og_locale":"en_US","og_type":"article","og_title":"Upgrading SQL Server–Supported Migration Methods - Glenn Berry","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/","og_site_name":"Glenn Berry","article_published_time":"2017-08-07T17:46:46+00:00","article_modified_time":"2020-01-11T20:51:53+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/","name":"Upgrading SQL Server–Supported Migration Methods - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2017-08-07T17:46:46+00:00","dateModified":"2020-01-11T20:51:53+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/upgrading-sql-server-day-7\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Upgrading SQL Server–Supported Migration Methods"}]},{"@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\/1228","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=1228"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1228\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}