Migrate database from 2005 to 2016

_{Wednesday, November 8, 2017 3:49 AM}

Hi,

  I am a newbie.  Try to upgrade the database from 2005 to 2016.  My steps are shown as follows.  Any best practices should be followed?  For example, re-indexing or execute some required sp and so on.  Thanks!

(1) Backup a database from old server

(2) Restore the database to SQL server 2016

(3) ALTER DATABASE [NAME] SET COMPATIBILITY_LEVEL =130

Ming

_{Wednesday, November 8, 2017 5:00 AM}

You might have to start by looking at breaking changes in SQL 2016

/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016

Once thats checked and ensured that nothing affects your current code you can go ahead with the db migration

Make sure you read these articles

/en-us/sql/database-engine/install-windows/are-you-upgrading-from-sql-server-2005

http://sql05upgrade.azurewebsites.net/

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Wednesday, November 8, 2017 6:30 AM}

Yep, did a lots of migration recently. No need to re-index, just apply Ola's script for index maintenance 

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Test your database with 130 level. as you may find that performance of specific queries is decreased.,

There are trace flags (querytraceon 9481) to downgrade the new cardinalty estimation feature or even you can set the database to 110 level

https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Wednesday, November 8, 2017 4:36 PM}

I've written a handful of posts on upgrades, you might find this page useful: https://thomaslarock.com/sql-server-upgrades/

HTH