September 20, 2016 at 4:11 am
Hi guys!
We have an old server with sql server 2005, now we have a new vmware esx with ssd hd and we want to migrate the old database 2005 to a newer virtual machine with sql server 2016.
What will be the best way for do that?
My intention is will not accept new connections on old server, make a full backup of 2005 database, restore it in the new server with sql server 2016.
This will be ok? Do you know if we will have a problems with compatibility?
Thanks a lot!
September 20, 2016 at 4:20 am
tbague (9/20/2016)
My intention is will not accept new connections on old server, make a full backup of 2005 database, restore it in the new server with sql server 2016.
That'll work.
You MUST test. One of the changes to SQL 2014 was a new cardinality estimator. Most queries either experience a minor performance improvement under the new CE, or stay the same. A small number show sever performance regressions. Test before you upgrade production, identify those queries and fix them (or your users get to do that testing after upgrade, which won't make them happy)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2016 at 7:29 am
There is a laundry list of things to do on a SQL migration:
Transfer Logins
Transfer SQL Agent Jobs
Re-create any Linked Servers
Re-create Replication
Restore DB's
Run DBCC CHECKDB
Update Stats
etc...
We like to re-ip/re-name the old server then re-ip/re-name the new server to match the old server and finally rename the SQL Instance so that any and all user connections do not need to be modified (no connection string changes needed).
If you have everything ready ahead of time we like to run FULL DB backups a day or two before the migration and restore the DB's on the new server WITH NORECOVERY. Then at migration time you can place the DB's in single user mode, take a DIFF backup then place the DB off-line and finally restore the DIFFs on the new server WITH RECOVERY to speed up the down time required for the migration process. With proper planning we can usually migrate a server within an hour using this pre-restore re-ip/re-name method even if the DB's are large.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply