If you have many databases, SQL Agent jobs, and logins with special permissions, moving an instance of SQL Server from one server to another can take a long time. However, keeping the database engine updated is important and it is not always possible to do it on the same server, ad example due to the need for an operating system upgrade.
I recently migrated an old installation from Windows Server 2003 and SQL Server 2005, here are some suggestions.
After having configured the new server in the optimal way, you need at first to recreate the database logins. Windows credentials are authenticated via Active Directory but for SQL ones it is necessary to export the passwords.
SQL Server Management Studio allows us to export all logins at once with a simple trick.
Select the “Logins” container and then press F7 (View > Object Explorer Details):
In the new window it is possible to select all the logins at the same time and export a creation script:
The script can be saved and run on the new server to restore all previous logins:
With the same technique we export SQL Agent jobs and everything that needs to be done massively:
Databases can be moved in two different ways:
- Backup & Restore
It is the slowest but safest way to move databases that does not alter the source server - Detach & Attach
Ideal when databases are large because it only involves moving data and log files. However, during the move the databases are completely inaccessible.
Once the databases have been mounted on the new server, it is important to increase the compatibility level to benefit all the features of the new version:
Finally, remember to match the SQL Server logins to the databases again. Unlike Windows logins (which are automatically recognized), SQL Server logins remain disconnected despite having the same username. For example: you created the login “simone” on the old server and also on the new one, the restored database correctly shows the permissions for “simone” but the access is denied.
A special script corrects and matches the permissions again:
EXEC sp_change_users_login ''Update_One'', ''simone'', ''simone''Moving an SQL instance requires a little work and some scripting, but by following all the steps above will be no surprises. If you then access the database via a CNAME you become independent from the machine name, avoiding reconfiguring all the clients every time.