Solved

Move SQL System Databases to a new SAN location on a Cluster Server

Posted on 2015-01-13
10
274 Views
Last Modified: 2015-01-17
We are moving our current SQL Cluster Server storage to a new SAN.

After running T-SQL
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

The path parameters in Sql Configuration must be changed
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

On a Cluster Sever I see that the SQL Server and Agent are stopped and a Deamon Launcher is running.

What is the correct procedure to change the path parameters in Sql Configuration on a Cluster Server?
Change the path settings in the SQL Server properties / Startup Parameters and then restart the Deamon Launcher?
0
Comment
Question by:NicoNL
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40548877
The path parameters in Sql Configuration must be changed
Being a cluster don't forget to change it in all nodes.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 167 total points
ID: 40549528
You'll have to physically move the database MDF/NDF/LDF files at the OS level, too - just doing an "ALTER DATABASE" command won't be sufficient to migrate these databases to your new storage location. When we've had to do this before, we've always done a detach/copy/reattach process to get them moved.

You'll have to do the ALTER DATABASE for your TempDB, since it's re-created from scratch every time the server is started up and will be moved the next time the instance comes online. To move your master and other system databases, you'll need to modify the startup parameters of your MSSQL service on each cluster node to ensure it knows where the database files are located

As an alternate to this whole confusion, and depending on your tolerance for downtime, could you accommodate something like this (much simpler approach)?

1. Mount new storage to an accessible location on the active node
2. Shut down MSSQL database instance
3. Copy all contents of old storage mount to new storage mount (using something that persists ACL and journaling, like XCOPY)
4. Change mount point of old storage to move it out of the way (give it a new drive letter)
5. Remount new storage mount into location of old storage mount
6. Bring MSSQL database instance back online

SQL Server is none-the-wiser - aside from the outage you took to copy the files, the database server has no idea you swapped storage and no additional commands are required to redirect anything. We've also done this before and it works great.

If you're moving one database at a time for downtime reasons, then the other methods would be necessary, but if you're swapping out the disk under the whole database instance, this is the way easier way to go.
0
 

Author Comment

by:NicoNL
ID: 40550138
Thanks for your replies.
I like the swapping storage drives approach, but downtime must be kept as short as possible.

Can you please check the following steps are correct? It's a two node active passive cluster.

- Stop SQL Server Agent (instance) on active Cluster Server.

- Change the path parameters in Sql Configuration
These must be changed on both active and passive Cluster servers?

-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

- Run T-Sql Alter database script for tempdb, msdb, model and the master.
This must be run on both active and passive Cluster servers?

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

- Stop SQL Server (instance)

- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
No need to move the tempdb/templog, they will be recreated on startup.

- Start SQL Server (instance) and SQL Server Agent (instance).

- Next step is to detach and attach all user databases one after the other.

- Finally Quorum and MSDTC.
These can be recreated with SQL server and Agent running?

Any tips?
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 166 total points
ID: 40550762
Instead of "Stop SQL Server Agent (instance) on active Cluster Server" should be "Stop SQL Server (instance) on active Cluster Server" but before that you need to do the "Run T-Sql Alter database script for tempdb, msdb, model and the master".  
This must be run on both active and passive Cluster servers? This need to be executed while SQL Server is running so you just need to connect to the SQL Server using SSMS and the run the following commands:

-- Move TempDB
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'NewPath\tempdb.mdf')
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'NewPath\templog.ldf')
GO

-- Move Model
ALTER DATABASE model
MODIFY FILE ( NAME = modeldev , FILENAME = 'NewPath\model.mdf' )
GO

ALTER DATABASE model
MODIFY FILE ( NAME = modellog , FILENAME = 'NewPath\modellog.ldf' )
GO

-- Move MSDB
ALTER DATABASE msdb 
MODIFY FILE ( NAME = MSDBdata , FILENAME = 'NewPath\MSDBData.mdf' )
GO

ALTER DATABASE msdb 
MODIFY FILE ( NAME = MSDBlog , FILENAME = 'NewPath\MSDBlog.ldf' )
GO

Open in new window


For the master database the procedure is different, so now is the step to change the startup parameters in both nodes:
-dNewPath\master.mdf
 -lNewPath\mastlog.ldf

Now is time for physically move the files to the new location:
- Move the databases master/masterlog model/modellog MSDBData/MSDBLog to the new location.
- No need to move the tempdb/templog, they will be recreated on startup.

After that you can:
- Start SQL Server (instance) and SQL Server Agent (instance).
0
 

Author Comment

by:NicoNL
ID: 40551643
Thanks for all the info. I started today with the TEST instance, but there is some bad news here. The instance does not want to come back online after moving the System Databases.

I stopped De SQL Server Agent (TEST) and ran the following scripts:

ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data')

Result:
The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Stopped the SQL Server (TEST) and changed the Startup Parameters:

Original Startup Parameters:
-dY:\MSSQL10.TEST\MSSQL\DATA\master.mdf;-eY:\MSSQL10.TEST\MSSQL\Log\ERRORLOG;-lY:\MSSQL10.TEST\MSSQL\DATA\mastlog.ldf

New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DATA\master.mdf;-eK:\MSSQL10.TEST\MSSQL\Log\ERRORLOG;-lK:\MSSQL10.TEST\MSSQL\DATA\mastlog.ldf

After bringing the TEST instance back Online it didn't want to start anymore. It looks like a rights issue.

Errorlog:
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA for file number 1.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DATA". Operating system error 5: "5(Access is denied.)".
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA for file number 2.  OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DATA". Operating system error 5: "5(Access is denied.)".
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DATA' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Error: 945, Severity: 14, State: 2.
Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The folder rights of original and new location are the same, except for a Username MSSQL@TEST.
I can't find this user MSSQL@TEST anywhere in AD. I added the SQL service account, but still the TEST instance doesn't want come back online.

What can be the problem here?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 14

Accepted Solution

by:
Daniel_PL earned 167 total points
ID: 40551957
Hi,
You put wrong filenames:

ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA')--it's a folder
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data')--it's a folder

Open in new window


Start instance in master-only mode, connect with sqlcmd and use correct commands, e.g.  

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBData.mdf')

Open in new window


Commands to start in master only from command prompt:
*Default instance
NET START MSSQLSERVER /f /T3608

Open in new window


*Named instace
NET START MSSQL$instancename /f /T3608

Open in new window


Cheers,
Daniel
0
 

Author Comment

by:NicoNL
ID: 40552369
Thanks Daniel,

A big mistake was made, messed it up here. It should be:

ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\modellog.ldf')

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\MSDBLog.ldf')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DATA\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Data\templog.ldf')

Open in new window


There are 3 instances on the server:
SQL01
SQL04\Production
SQL05\TEST

Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
NET START mssql$SQL05\TEST /f /T3608
or
NET START mssql$TEST /f /T3608
or
NET START "SQL05\TEST" /f /T3608

Open in new window


Can SSMS T-SQL query be used for this instance in startup master-only mode?

If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
sqlcmd -S "SQL05\TEST" -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"

Open in new window

0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 40552461
Hi,
Is the following command correct for startup in master-only mode for the test instance SQL05\TEST?
>> NET START mssql$TEST /f /T3608

Can SSMS T-SQL query be used for this instance in startup master-only mode?
>> Unfortunately not

If sqlcmd must be used is the following command correct, when I put the ALTER DATABASE... lines into D:\Script\TEST.sql?
>> Try this one:
sqlcmd -S "SQL05\TEST" -d master -i "D:\Script\TEST.sql" -o "D:\Script\Output.txt"

Open in new window

0
 

Author Comment

by:NicoNL
ID: 40553189
Thanky you! That did the trick. Instance TEST is corrected and back online.
0
 

Author Comment

by:NicoNL
ID: 40555224
I've requested that this question be closed as follows:

Accepted answer: 0 points for NicoNL's comment #a40553189
Assisted answer: 125 points for Vitor Montalvão's comment #a40548877
Assisted answer: 125 points for ryanmccauley's comment #a40549528
Assisted answer: 250 points for Daniel_PL's comment #a40551957

for the following reason:

SQL is mirated to new SAN
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now