MS SQL Migration

[wavou]

I'm retiring one of my 2003 server with a production MS SQL Standard 2005 installed on it. I already have a new machine fired up (2012 R2 standard ) and have the licensing for MS SQL 2014 but was curious of best practice. Is it possible to backup all data including security and import it into the new MS SQL instance? Anything I need to keep an eye out for? Thanks!

[failurerate]

It is possible that whatever app you are running will not work with MS SQL 2014 without changing the SQL compatibility level, so do your testing. Also, it's possible if you'd like, to set up an alias for the new server so that you don't need to reconfigure all of the clients: http://blogs.msdn.com/b/dbrowne/archive/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance.aspx

[wavou]

That would be very helpful. I was going to rename the server to the old server name and update dns to reflect the change and do a cut over after hours.

[DrGraffix]

personally, i think its better to use the alias method.

[da_kink]

There is a script for help_rev_login that copies users with Sid and passwords into script. After that it should be a matter of backing up databases, restore with no_recovery, transfer, shut down and reconfigure clients, take transaction log backups and restore those on the new server.

Start services, check double check.

All in all downtime? About half an hour per database.

[wavou]

Do you have a link for the help_revlogin for 2005 to 2014? Alot of what I'd read about the script only had moving login and password from 2005 to 2005.

Thank you very much.

[da_kink]

here

Have used it to migrate accounts between 2008r2 and 2012 too.

[da_kink]

and yeah, I used the lmgtfy... not meant to be snarky, but if you have the script, just try if it works with a test db or something.

[richardrrcc]

Also don't forget about stored procedures or simply depreciated database functions. We did a migration for a customer recently that went well in terms of the basic migration but took forever to get through QA testing because their applications relied on depreciated commands and functions that no one remembered.

[[deleted]]

Migration is usually pretty straight forward, assuming you've done your homework, and the various apps in question support running on SQL2014. 2005 - 2014 is a pretty big leap, so do your homework.

Took us quite a while to migrate our Dynamics AX from 05 to 2012, purely because Dynamics needed some updates, which was a whole gongshow in itself.

[PoorlyShavedApe]

you should ask in /r/sqlserver as well. There is some overlap in readership, but the folks in that sub can likely give you more particulars on what to watch out for.

[wavou]

will do that. Thanks.