January 21, 2014 at 3:13 pm
Hi guys
we have Active/passive SQL 2008R2 cluster environment.
looking for steps to move Data and log files from user Database and System Database for SQL Server Clustered Instance.
Currently Data and log files resides on same drive for user and system Databases..
Thanks
January 21, 2014 at 3:42 pm
For user databases - I would use ALTER DATABASE ... MODIFY FILE () to change the file locations, then take the database offline - copy the files to new location - bring database online.
For system databases - I would uninstall/reinstall SQL Server altogether. Moving system databases can be done, but in a cluster it will get tricky on how to start up in single-user mode for master - and you never get everything moved.
If your goal is to separate system from user, here is what I would recommend:
1) Move user databases as outlined above.
2) Create new LUN for system databases at new size
3) Shutdown SQL Server
4) Copy system databases/files from old LUN to new LUN (everything)
5) Relabel old LUN to different drive letter
6) Relabel new LUN to old drive letter
7) Verify new LUN is added to cluster as a resource
8) Restart SQL Server
As long as the system recognizes the new LUN as the old drive letter - and the new LUN has been added to the cluster then everything should come up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 22, 2014 at 5:24 am
logicinside22 (1/21/2014)
Hi guyswe have Active/passive SQL 2008R2 cluster environment.
looking for steps to move Data and log files from user Database and System Database for SQL Server Clustered Instance.
Currently Data and log files resides on same drive for user and system Databases..
Thanks
It's easy to achieve as long as you take your time and don't rush. I tend to stand up a new set of LUNs\disks and migrate across, the only downtime on the instance being when i move the master model and tempdb databases. There are a number of factors to consider though, for example, any default paths set in SQL Server will need to be changed to reflect your new LUNs as they directly affect where certain logs, etc are written to. If you use agent job step output files for instance these will need to be changed to reflect the new location. It's doable but requires thought and patience. I am seriously considering a detailed article around this and your post has only convinced me further 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 3:05 pm
Excellent Jeff . thank you so much
Planning to move all User and system Database Data file to one location and log on other drive.
January 22, 2014 at 3:20 pm
As I understand it you are only doing this to separate data from log? I would seriously consider, in fact recommend, only moving the user databases and perhaps tempdb, and leaving the system database files where they are. Much less risk and effort.
system databases should be separate from user databases anyway.
---------------------------------------------------------------------
January 23, 2014 at 6:33 am
As far as moving master, model and msdb there is no need to move them unless you want to. They are hardly used so moving them is more of a risk than bang for the buck. Moving the application databases you can use the detach/attach method or if they are small databases I find it easier to backup/restore them personally. TEMPDB can be the heavy IO one.... all you have to do is run the alter database statement and tell it the new path but make sure the folder(s) you want the mdf and ldf files for TEMPDB to exist and be accessible otherwise SQL Server won't start. Once you run the alter commands just stop/start SQLServer and it will recreate the files in the new place themselves.
January 23, 2014 at 4:40 pm
I think you can move the master db the same way for a cluster as you would for a stand-alone instance. Use the "SQL Server Configuration Manager" to put in the new file locations, under Services and Advanced Properties, as described in Books Online. Then you stop the SQL service (w/o a failover), physically move the files, and start the SQL service again.
I believe the other system files -- model, msdb and tempdb -- can all be moved using the ALTER DATABASE method.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply