Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Move resource database cluster Expand / Collapse
Author
Message
Posted Wednesday, February 24, 2010 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, 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!
Post #871937
Posted Monday, March 15, 2010 3:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, 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!
Post #883394
Posted Thursday, March 25, 2010 6:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:20 PM
Points: 10, Visits: 237
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
Post #890334
Posted Friday, March 26, 2010 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, Visits: 40
No .cer files involved.
Post #890652
Posted Monday, May 17, 2010 6:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 7:53 AM
Points: 374, 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.
Post #922855
Posted Monday, May 17, 2010 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, Visits: 40
You didn't move the resource database data files until AFTER you ran the alter statements for the resource database correct?
Post #922899
Posted Monday, May 17, 2010 7:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 7:53 AM
Points: 374, Visits: 92
correct
Post #922911
Posted Monday, May 17, 2010 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, Visits: 40
Do these files exist:
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Post #922914
Posted Monday, May 17, 2010 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 7:53 AM
Points: 374, Visits: 92
yes.
Post #922922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse