none
Moving the System Databases in a SQL Cluster

    Question

  • Hi,

    Iam tasked to move our System Databases (master,model & msdb) to a new storage location onto a different LUN / drive. Its a SQL Server 2008 R2 Active/Passive failover SQL Cluster.

    I'm assuming that the process for moving all the 3 databases are same. Is the procedure any different for moving the master database? Please advise.

    Aside, I'm planning to proceed it in this order: Model and then MSDB and then move the master. Is that okay?

    Other than taking a backup of these 3 System Database files, is there any thing else that I should safe guard so i can revert back the database in the event of a calamity?

    As for the Cluster, am I required to do anything, take any resource offline, etc?


    Thanks,
    Jai

    Jai

    Thursday, April 25, 2013 6:59 AM

Answers

  • Move the master database first.

    Then move the msdb and model database.

    Moving master database is different when comparing with msdb and model database movement.

    Refer Moving the master and resource database section in the below website.

    http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx

    Refer this for msdb movement.

    http://www.ryanjadams.com/2011/07/how-to-move-msdb/#axzz2RU2L4SSN

    Refer this for model movement.

    http://www.ryanjadams.com/2011/07/how-to-move-model/#axzz2RU2L4SSN

    Hope this helps you! CLuster experts may answers for this.

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by m-jai Friday, April 26, 2013 9:09 AM
    Thursday, April 25, 2013 2:03 PM

All replies

  • Move the master database first.

    Then move the msdb and model database.

    Moving master database is different when comparing with msdb and model database movement.

    Refer Moving the master and resource database section in the below website.

    http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx

    Refer this for msdb movement.

    http://www.ryanjadams.com/2011/07/how-to-move-msdb/#axzz2RU2L4SSN

    Refer this for model movement.

    http://www.ryanjadams.com/2011/07/how-to-move-model/#axzz2RU2L4SSN

    Hope this helps you! CLuster experts may answers for this.

    Regards http:\\sqldbatask.blogspot.com MCTS Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by m-jai Friday, April 26, 2013 9:09 AM
    Thursday, April 25, 2013 2:03 PM
  • Moving the system databases to a different drive for a clustered instance is no different from a standalone instance. However, if you will be changing the startup parameters for the master database and the SQL Server errorlog using SQL Server Configuration Manager, you have to do it on all of the nodes in your cluster.

    Another approach is to use mountpoints. Provision a LUN on your cluster and mount it as a folder on the root of the clustered drive that contains your system databases with the same name (make sure you rename the original folder to something else). Move all of your system databases in this mountpoint via the new folder name. When you bring the SQL Server cluster resource back online, everything is transparent since the folder names and drive letters are all the same. Check this article out for more details

    http://www.mssqltips.com/sqlservertip/2623/configuring-volume-mountpoints-on-a-sql-server-2008-failover-cluster-running-on-windows-server-2008/

     

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Thursday, April 25, 2013 4:18 PM
    Moderator
  • Thats a lot of work using the second approach using mountpoints. I think i will go the first route, but....what I dont understand is this - why is it not  transparent to the SQL when we do an " alter database master modify ". Is it mandatory to manually go in and change the properties in SQL configuration manager to change the drive/path in startup parameters?

    Thanks..


    Jai

    Friday, April 26, 2013 1:45 AM
  • Iam adding on to my earlier post.  After going through bass_players msdn link on moving master i realise.

    Sorry I was wrong. I had thought that there ought to be a " alter database master modify " task to move the master database as well like other databases. I had not realised that this is performed manually just be changing the properties and nothing else.

    Please correct/confirm me.

    Thanks..


    Jai

    Friday, April 26, 2013 2:07 AM
  • Thanks All, I managed to complete it and the above answers helped me through it.


    Jai

    Friday, April 26, 2013 6:31 AM