Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
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.
Move SQL System Databases to a new SAN location on a Cluster Server
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?
Who is Participating?
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?
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?
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month6 days, 3 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
You put wrong filenames:
Open in new window
Start instance in master-only mode, connect with sqlcmd and use correct commands, e.g.
Open in new window
Commands to start in master only from command prompt:
*Default instance
Open in new window
*Named instace
Open in new window
Cheers,
Daniel