Move Instance To New Disks (On Same Nodes)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Move Instance To New Disks (On Same Nodes)

Author

Topic

agdavidson
Starting Member

17 Posts

Posted - 2011-02-16 : 12:40:44

We recently purchased a new SAN, and currently, we have both the old and the new SAN connected to our database cluster, which is running SQL Server 2005 SP2 in an active/passive setup w/ two nodes. We've already migrated the user databases to the new disks, but now I need some help planning exactly how we'll move the system databases to the new disks.

Here's what I have so far:

STEP 1 - Update system catalogs to tell distribution, model, and MSDB that their files are moving.
For each of these databases, issue the following command against all data and log files:

alter database <DBNAME>
modify file
(
name = '<LOGICAL_FILENAME>'
,filename = '<OS_FILENAME_USING_NEW_PATH>'
)

STEP 2 - Stop SQL Server, move the physical files, start SQL Server

STEP 3 - Update system catalogs to tell TEMPDB to create itself in a new location next time SQL Server starts up.

alter database TEMPDB
modify file
(
name = '<LOGICAL_FILENAME>'
,filename = '<OS_FILENAME_USING_NEW_PATH>'
)

STEP 4 - Stop and restart SQL Server

STEP 5 - Stop Cluster Service on the passive node.

STEP 6 - On the active node, go into SQL Server Configuration Manager, and change the startup parameters to use the new paths for master's data and log files (both will be housed in same folder, per SQL Server requirements), as well as the new path for the ERRORLOG.x files.

STEP 7 - Take SQL Server resource offline

STEP 8 - Move master's data and log files, SQL Server will recreate ERRORLOG file itself.

STEP 9 - From the command line on the active node:

NET START MSSQLSERVER /f /T3608

STEP 10 - Connect to SQL Server, and update system catalogs to tell the resource DB that its files are moving.

ALTER DATABASE mssqlsystemresource
MODIFY FILE 
(
NAME=data 
,FILENAME= 'J:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf'
)

ALTER DATABASE mssqlsystemresource
MODIFY FILE 
(
NAME=log
,FILENAME= 'J:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf'
)

STEP 10 - Move the physical files.

STEP 11 - From the command line on the active node:

NET STOP MSSQLSERVER

STEP 12 - Start Cluster Service on the passive node.

STEP 13 - In Cluster Administrator, right-click the cluster name, and move the Quarum to its new disk.

STEP 14 - Bring the SQL Server back online.

So, that's great and all, I've tested it and it works. But it leaves me wondering: what about the MSSQL.1\MSSQL\Backup, MSSQL.1\MSSQL\FTData, MSSQL.1\MSSQL\Jobs, and MSSQL.1\MSSQL\repldata folders. Will SQL Server still be trying to deposit stuff into these folders (which are on the old SAN disk)? Also, what about SSIS packages? Do I just move the files and update the references to their paths? Are there other things I need to address that I have not?

Thanks, in advance, for taking the time to review this long post and offer your experience and expertise. It will be much appreciated, as I really need this to go smoothly!!

-Austin

agdavidson
Starting Member

17 Posts

Posted - 2011-02-16 : 19:26:55

As luck would have it, Microsoft called me today to follow-up on another case, and I ran this by them... they said all those other folders I was concerned with are not an issue. So, looks like I'm good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources