SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Move resource database cluster


Move resource database cluster

Author
Message
GiantMetFan
GiantMetFan
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 40
I have been searching for a way to move all database files from old drives to new drives. I found this:

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

which does work. I have followed the Microsoft page above BUT it does not talk about if the environment is a cluster! Since I can't seem to find how to move master and the resource database within the same instance, I tried the web page above.
I make the necessary changes in the configuration manager for master, but when I get to the point that I need to move the resource database I cannot log in. So I run this:
NET START MSSQLSERVER /f /T3608

Looks like Sql Server starts from the service, BUT I cannot log into sqlcmd after this:
D:\>D:\MSSQL\90\Tools\binn\sqlcmd.exe -sMSSQLSERVER
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Now, I know this web site instructions work on Sql Server 2005 NONCLUSTERED. I have done this testing 5 times now without issue, but we do not have a clustered test environment to test.
I need to be able to move the resource database on a clustered environment.
Anyone try this?

Our environment:
SQL SERVER 2005 Enterprise 64-Bit SP3
Windows 2003 R2 Enterprise 64-Bit SP2

Any help would be appreciated!
GiantMetFan
GiantMetFan
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 40
After searching near and far for a solution that would work to move master and resource databases in a Sql Server 2005 Cluster environment, I have pieced together from Sql Server forums and Microsoft web sites a solution that works, verified and tested.
Here is my example:

-- Moving Master database with it's Resource database within Sql Server 2005 Clustered environment

Example Parameters:
###############################################################################
2 nodes running Windows 2003 Enterprise 64-Bit Servers
2 nodes running Sql Server 2005 Enterprise 64-Bit SP3
Version# Patch Level Edition
-------------------- ----------- --------------------------------------------------
9.00.4035.00 SP3 Enterprise Edition (64-bit)

From data folder: E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
From log folder: E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

To data folder: M:\MSSQL\MSSQL.1\MSSQL\Data
To log folder: M:\MSSQL\MSSQL.1\MSSQL\LOG
###############################################################################

1)From Cluster Administrator: stop the cluster service for the failover node

2)From SQL Server Configuration Manager: Change path to master.
Original configuration manager startup parameters:
-dE:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eE:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

To:
-dM:\MSSQL\MSSQL.1\MSSQL\Data\master.mdf;-eM:\MSSQL\MSSQL.1\MSSQL\LOG\ERRORLOG;-lM:\MSSQL\MSSQL.1\MSSQL\Data\mastlog.ldf
2)From Cluster Administrator: take the SQL Server resource offline
3)OS Move master data and log files to new location.

4)From command line on active node run:
NET START MSSQLSERVER /f /T3608

5) Open Sql Server Management Studio to a query window
6) To move the resource database, which is a requirement for Sql Server 2005, run:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'M:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'M:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf');
GO
7) OS move the files in step 6 to new location
8) From existing Sql Server Management Studio query window:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
go
9) Exit out of Management Studio
10) From command line on active node run:
NET STOP MSSQLSERVER
11) From Cluster Administrator: start the cluster service for the failover node
12) From Cluster Administrator: bring the SQL Server resource online again.

DONE!w00t
PK_DBA
PK_DBA
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 250
Mike,

Very nicely documented!

Did you also happen to move the *.cer files to the new location when you moved the master and resource databases?

Cheers,
PK
GiantMetFan
GiantMetFan
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 40
No .cer files involved.
Sue White
Sue White
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 92
question i followed a slightly different set of instructions to move the system databases including the resource one. my results were fine with everything but the resource database. the problem was when i tried to execute the alter statement i kept receiving an error that said database didn't exist, i was in management studio and pointed to master, so I'm a little unclear on how to run the alter statement once the server was brought up in minimal config. i have sql server 2005 clustered environment 64 bit. Any help with this issue would greatly be appreciated.
GiantMetFan
GiantMetFan
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 40
You didn't move the resource database data files until AFTER you ran the alter statements for the resource database correct?
Sue White
Sue White
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 92
correct
GiantMetFan
GiantMetFan
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 40
Do these files exist:
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Sue White
Sue White
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 92
yes.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search