July 30, 2013 at 5:26 am
Hi there,
I have the need to migrate a 2008 R2 SQL Cluster onto a new site (unfortunately physically moving the kit is not an option). We will prepare a new cluster to be identical to the existing one in terms of SQL version, file structure etc.
Once that is ready I would ideally like to backup and restore the system databases to the new cluster to make the process easier. Does anyone know if there are any special considerations when transferring system databases from one cluster to another, or is it exactly the same process as it would be if they were stand-alone servers?
I'm aware moving system databases can sometimes be a bit of a minefield, but it would save a lot of work!
Thanks,
Matt
July 30, 2013 at 6:02 am
Are you planning on just restoring msdb (simple) or msdb and master (so complicated I have not done it in 10 years)?
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
July 30, 2013 at 6:09 am
i would disagree that restoring system databases is going to save you any work.
I personally prefer to script out things like certificates, master keys, linked servers and logins, and jobs in msdb, run those them on a clean system, and then restore the databases.
you can even generate scripts based on the existing server to generate the restore commands;
it's less prone to errors, in my opinion, and easier to test/rollback in the case of an issue. you also avoid the whole issue where both servers have to be named exactly the same, and still have the option of leaving the existing server in place.
Lowell
July 30, 2013 at 6:11 am
I would look at the reason behind why you are wanting to restore all system databases before just thinking "to keep it the same". If its to copy all user access then script all the users out. As the above post says msdb is easily transferrable so all jobs and schedules are kept the same.. but master, although possible.. could cause you issues that you can avoid.
July 30, 2013 at 6:24 am
Thanks for all your responses - I think just copying the MSDB may be good advice, as in reality it's really only the jobs and schedules which would be the most time consuming to recreate manually, then I'll just script the logins and linked servers etc. I have moved the master db once or twice before without too many issues but probably best to avoid it.
I assume copying over the MSDB should be no different on a cluster compared to a stand alone server?
July 30, 2013 at 6:26 am
Why not just script out all the jobs? It's easier than restoring msdb...
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
July 30, 2013 at 6:30 am
GregoryF (7/30/2013)
Why not just script out all the jobs? It's easier than restoring msdb...
I could do, although I seem to remember having mixed results doing it this way before....can't quite recall why though?! Either way would be fine so long as it worked to be honest!
July 30, 2013 at 6:48 am
be careful about just copying system databases between instances, especially clustered instances. Things like different computernames or collations and SMKs can cause a lot of trouble. Migrate the objects between instances, do not copy the databases. Whilst it may seem to work initially you'll likely encounter issues down the line
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 30, 2013 at 6:53 am
Ok, thanks for all the wise advice - I'll stick with the scripting option and see how I get on.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply