I'm currently working on a migration plan to migrate our SQL Server to some new hardware.
The current installation is SQL Server 2005 standard: it used to be clustered but the node got removed at some point, but still is configured as a clustered.
The new server will be a SQL Server 2008 standard machine, non-clustered.
I'm wondering whether or not I should transfer the master database from the old machine to the new one?
I mean, while the databases it hosts are going to be the same, the configuration is going to be different (different versions and not clustered).
In addition of transferring the user databases, my approach was to script all the logins, users, jobs, etc and restore them on the new machine, but I'm afraid of forgetting something?
On the other hand, I'm anxious of compatibilities issues by transferring the master database due to the different version (SQL 2005 vs 2008 and clustered vs non-clustered).
Please could someone provide me with some clarification on the caveats of each option vs the other?
Best Answer
You should not restore the master database to a different target machine than the one it was backed up from.
Since you say the backup is part of a (failed) cluster, you definitely don't want to restore that to a non-clustered server (with a completely different hostname.)
Among the reasons why you shouldn't is that each SQL Server installation generates local user and group accounts that grant rights on the database server, and encryption keys specific to THAT instance of SQL Server.
Restoring the master database elsewhere means that information will no longer match the original server.
That said, the master database does contain all security credentials and login information, so you may want to temporarily restore it somewhere to extract this information.
Jobs are not stored in the master database.