How to Relocate System Databases
- Microsoft SQL Server
When the current disk is at capacity or you need to move an instance between diskgroups, it may be necessary to relocate the system databases (i.e. Master, MSDB, Model and TempDB) to another disk. The Relocate System Databases feature takes the somewhat tedious process of moving system databases and automates it for you.
First you will want to identify where you want your system databases to be moved, both data and log files. A DxEnterprise managed volume that is added to the VHost diskgroup is highly recommended. Once your target disk(s) have been identified and configured, then you will follow these steps:
- Right-click on the instance and select “Relocate System Databases”. A dialogue box will appear to warn you that the instance will be stopped during this process.
- Click “Yes”
- Input the new location for your data and log files and select the system databases that you wish to move (Master database cannot be de-selected)
- Click “OK”
- Once the relocation has completed you can remove the files from the original location to reclaim disk space if desired (the relocation process does not delete the source files)
Additional information about the Relocate System Databases process:
- It copies the system databases from their current location to a new location, and modifies the Master database and the cluster registry to point to the new locations.
- It does not move user databases. For each user database, you will need to Detach, copy the files to the new location, then Attach.
- With DxEnterpise versions previous to 15.0.409.1409 – Relocate System Databases sets SINGLE_USER mode on the databases, so you will want to ensure that all user are disconnected beforehand.
- With DxEnterprise version 15.0.409.1409 and later – Relocate System Databases no longer sets SINGLE_USER mode on the databases. You must follow best practices defined by Microsoft (https://msdn.microsoft.com/en-us/library/ms345408(v=sql.110).aspx) and ensure the process is performed during a maintenance window with no user connections that may interfere with the ALTER DATABASE commands
NOTE: There are several methods to ensure that user connections do not interfere with the system database relocation; such as stopping any middle-ware services to prevent automated processes from repeatedly attempting to log in, setting a temporary firewall rule to prevent remote connections to the port used by the instance, and/or disabling logins for users of the instance prior to relocating.