Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Solved
Move SQL System Databases to a new SAN location on a Cluster Server
Posted on 2015-01-13
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
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
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?
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
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2
2- +1
10 Comments
Active today
The path parameters in Sql Configuration must be changedBeing a cluster don't forget to change it in all nodes.
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.
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.
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
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
- 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?
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
-lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER
- 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?
Active today
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:
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).
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
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).
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\DAT
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Dat
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\DA
New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DA
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
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DAT
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?
I stopped De SQL Server Agent (TEST) and ran the following scripts:
ALTER DATABASE model MODIFY FILE (NAME = modeldev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE model MODIFY FILE (NAME = modellog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev , FILENAME = 'K:\MSSQL10.TEST\MSSQL\DAT
ALTER DATABASE tempdb MODIFY FILE (NAME = templog , FILENAME = 'N:\MSSQL10.TEST\MSSQL\Dat
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\DA
New Startup Parameters:
-dK:\MSSQL10.TEST\MSSQL\DA
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
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file K:\MSSQL10.TEST\MSSQL\DATA
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "K:\MSSQL10.TEST\MSSQL\DAT
Error: 5105, Severity: 16, State: 1.
A file activation error occurred. The physical file name 'K:\MSSQL10.TEST\MSSQL\DAT
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?
Hi,
You put wrong filenames:
Start instance in master-only mode, connect with sqlcmd and use correct commands, e.g.
Commands to start in master only from command prompt:
*Default instance
*Named instace
Cheers,
Daniel
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
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')
Commands to start in master only from command prompt:
*Default instance
NET START MSSQLSERVER /f /T3608
*Named instace
NET START MSSQL$instancename /f /T3608
Cheers,
Daniel
Thanks Daniel,
A big mistake was made, messed it up here. It should be:
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?
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?
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')
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
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"
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:
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"
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
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
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:
Each Login (Windows or SQL)
* Its Server Roles
* Every database to which the login is mapped
* The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month8 days, 19 hours left to enroll
604 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.