Your company decides to update its SQL 2005 server to SQL 2012, finally!!! At first you jump for joy but then you realize, it's you that will have to do the work to get this done. And of course, they need it done yesterday. So what do you do? You basically have two choices, an in-place upgrade or a server migration.

In-Place Migration

An in-place upgrade is tempting. You keep the same server (hardware) and just upgrade the SQL version (and maybe the OS version as well). You get to keep all your drives and network shares, and any custom software (like AS400 or Teradata drivers). You don't have to worry about all those applications that have the SQL server name hard coded into their connection strings either. Sounds like a great idea. Or is it?

If you were moving from SQL 2008 to SQL 2008 R2 or maybe from SQL standard edition to enterprise, then I would say, yes, an in-place upgrade would be a reasonable choice. But if you are moving from SQL 2005 to SQL 2012 and most likely from Windows 2003 to Windows 2012, then no, I would say the danger is too great.

If your system has been around for a few years, then it probably has issues of one type or another (gremlins). Doing an upgrade tends to make these issues worse. There is also no guarantee that any custom software you have installed will play nice with the new OS and SQL Server versions. Even if everything tests out OK on your development server, there is always the chance that the production server will be slightly different and that will cause the upgrade to fail. At that point, your production server is down and might be down for a while. Not good.

In addition, the rollback process for an in-place upgrade is to reinstall the old versions of your software. This could mean substantial downtime when recovering your database server.

Server Migration

Your other choice is to do a server migration. This is where you request a brand new server and install the OS and SQL Server versions that you need. This has its benefits since you are getting a new server (and presumably newer hardware), and you can set the optimal drive and memory configuration for it. Since you have two servers, you can test the new server while still running on the old server and compare the results to make sure they are the same. You also have an easy rollback plan if the cutover goes badly (just keep using the old server).

Of course there is also a down side; this is more work. You have to install SQL Server and configure it to match the old server. You have to make sure you set up the new server with all the same file shares and OS permissions so it matches the original server. You have to find and install all the extra software that you will need (like Oracle or AS400 drivers, monitoring software, and so on). Hopefully you have a great relationship with the server team and they help you get it all set up just right.

Then you still have to copy over all SQL objects and that can be the really challenging part. You have to copy over all the users (both domain and SQL logins), all your linked servers, possibly all your SQL Server Agent Jobs, and all the databases and data. If your server has dozens of databases and maybe hundreds of users, this can be a very daunting task. But with the right tools and knowledge, it can be much easier than you might think.

Users

There are two types of users on SQL server. The domain users (AD) and the SQL logins.

Domain users are great because they are managed through AD (active directory) and the DBA does not have to worry about their passwords which are also managed by AD. Also, SQL will use the AD SID (security identifier) so orphaned users are not an issue either.

SQL Logins are logins created in SQL Server with username and password and are assigned a SQL Server SID. If you create user “JSmith” with the password “12345” on two SQL instances, they will have different security identifiers and so when you restore the AdventureWorks database from Server 1 to Server2, the user JSmith will be an orphaned user even though JSmith is a valid SQL login on both servers. It is orphaned because the SIDs are different and that is what SQL server uses to map back to the login.

Then there is the issue of SQL login passwords. In a perfect world all the SQL logins and passwords are stored in a secure password management system and updated on a regular basis. In reality, there are probably logins whose passwords have been lost and have never been reset because they are used by everything and if you change it, all kinds of bad things would happen. So how do you find the password for those logins and copy them to the new server without breaking anything?

Good news!  There is a stored procedure for that and it is called sp_help_revlogins. It is designed to script out all the create user commands so that you can then run the script on the new server. It even keeps the original SID and will include the encrypted password for the SQL Logins.

Here is an example of that the output looks like.

CREATE LOGIN [test_user] WITH PASSWORD = 0x0200CFD03BEF72CDE496D5DCE86CD64AC06335153CCCE570162095BD24D23067B5FC9DA01032B384BC4A063E9F9321598BB18650359094385E9714DB76F1F80D3663D5D55 HASHED, SID = 0x4DBAA580D6A4783358DFD25CB59E6, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Run that on the new server and test_user will be created with the same password and SID from the original server. Just remember to remove the users that are specific to the original server. Since the SID will be the same, once you restore the databases to the new server you won't have to worry about orphaned users and the problems they can cause

A simple search on the web can find this stored procedure or you can download it from here: https://blogs.msdn.microsoft.com/lcris/2006/04/03/sql-server-2005-a-proposed-update-of-sp_help_revlogin/

Restore Databases

The second large hurdle can be how to restore all the databases from your original server? Sure you can script restore commands for each database, but how long will that take? And let's say some of the databases have multiple file groups so the restores look like this.

  RESTORE DATABASE MyAdvWorks
     FROM AdventureWorks2008R2_Backup
     WITH RECOVERY,
     MOVE 'AdventureWorks2008R2_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf',
     MOVE 'AdventureWorks2008R2_Data2' TO 'D:\MyData\MyAdvWorks_Data2.mdf',
     MOVE 'AdventureWorks2008R2_Data3' TO 'D:\MyData\MyAdvWorks_Data3.mdf',
     MOVE 'AdventureWorks2008R2_Data4' TO 'D:\MyData\MyAdvWorks_Data4.mdf',
     MOVE 'AdventureWorks2008R2_Data5' TO 'D:\MyData\MyAdvWorks_Data5.mdf',
     MOVE 'AdventureWorks2008R2_Data6' TO 'D:\MyData\MyAdvWorks_Data6.mdf',
     MOVE 'AdventureWorks2008R2_Data7' TO 'D:\MyData\MyAdvWorks_Data7.mdf',
     MOVE 'AdventureWorks2008R2_Data8' TO 'D:\MyData\MyAdvWorks_Data8.mdf',
     MOVE 'AdventureWorks2008R2_Data9' TO 'D:\MyData\MyAdvWorks_Data9.mdf',
     MOVE 'AdventureWorks2008R2_Data10' TO 'D:\MyData\MyAdvWorks_Data10.mdf',   
     MOVE 'AdventureWorks2008R2_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf'

There is another way, with a script named Restore Script Generator. It will examine all the backup files in a given directory and then, using xp_cmdShell, will read the backup files and create the above script for you. It will create one script per backup file in the directory you use. Then just find and replace D:\MyData with F:\SQL\Data (or whatever directory the new server uses) if you need to, and you are ready to go. This handy little script can be found at: https://www.sqlservercentral.com/scripts/Restore/99629/

In my experience these are two of the larger issues with doing a SQL migration, but as I have shown, with the right tools, it can easily be done. This allows you to quickly move all the data and users to the new server and start testing as soon as possible.