How to migrate SQL Server System databases

After SQL Server is installed by default, the four system databases (master, model, MSDB, and tempdb) of SQL Server are automatically placed in the installation path, that is, under the Program Files folder of the system disk. The problem is that most database servers install the system on a raid1 array in order to take care of the performance, cost, and high availability at the same time, generally, this raid 1 array does not necessarily use 15 kb of SAS, but some only use 10 KB of SAS. What's more, for the cost, the installation of two kb SATA will be complete. In addition, the raid1 array itself is an array with high read performance but poor write performance. Therefore, it is very unfavorable for system databases, especially tempdb databases, it will certainly affect the performance of the entire SQL Server. Therefore, migrating the system database to an array with higher performance is a basic solution to solve the hardware performance bottleneck.

The following describes how to migrate a system database to another partition (Taking Microsoft SQL Server 2008 R2 as an example ):

First, migrate the master database. The master database is the core of the entire SQL server instance. All the settings are stored in the master database. If the master database has problems, the entire instance will be paralyzed. First, open SQL Server Configuration Manager, select the SQL server services node in the list box on the left, and then find the SQL Server service of the instance of the system database to be migrated in the list box on the right, for example, SQL Server (MSSQLServer), stop the service of this instance, right-click, select the bottom "properties", and switch to the "advanced" label, as shown in:

See "startup parameters". The parameters here need to be changed. As shown in:

Sort the characters as follows:
-DC: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ data \ master. MDF;
-EC: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ log \ errorlog;
-LC: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ data \ mastlog. LDF

Basically, we can see that "-d" is followed by the location of the master database data file, and "-e" is the location of the error log of the SQL server instance, -l is the location where the log file of the master database is located. Modify the path of the data file and log file to a proper location. You do not need to change the location of the error log. For example, you can store the data file in the sqldata folder of disk D, the parameters are as follows:

-DD: \ sqldata \ master. MDF;-EC: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ log \ errorlog;-Le: \ sqllog \ mastlog. LDF

Click "OK" to save and close the dialog box.

Next, you need to cut the data files and log files of the master database to the path defined in "startup parameters", and then start the SQL Server service of the instance.

Note: At this time, the SQL Server service may still fail to be started. Make sure that the configuration is correct and the NTFS permission is correct, if you do not use the local system to start the SQL Server service, after you change the storage path, you need to grant the corresponding permissions to the new drive letter or folder so that the service can be started, we recommend that you grant the full control permission to the corresponding account. As for the reason, it is experience. You have to ask Microsoft.

Now, the master database has been migrated.

For migration of tempdb, MSDB, model, and other system databases, refer to another blog:

Http://www.cnblogs.com/Frank-Zhao/archive/2012/06/05/2537001.html