February 24, 2010 at 8:12 am
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!
March 15, 2010 at 3:37 pm
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:
March 25, 2010 at 6:29 pm
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
March 26, 2010 at 8:07 am
No .cer files involved.
May 17, 2010 at 6:25 am
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.
May 17, 2010 at 7:44 am
You didn't move the resource database data files until AFTER you ran the alter statements for the resource database correct?
May 17, 2010 at 7:57 am
correct
May 17, 2010 at 8:01 am
Do these files exist:
mssqlsystemresource.mdf
mssqlsystemresource.ldf
May 17, 2010 at 8:17 am
yes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply