Using the Master Database from a Mirror

If the primary device used for the master database becomes unavailable or has been corrupted while the server is running, the mirror device for master automatically becomes active. However, when the primary device for the master database has failed and you need to restart the server, use the sqlservr executable with the -r option. The -r option enables the server to find and use the mirror device for the master database when restarting SQL Server.

For example, if the MASTER mirror device is D:\SQL60\MIRROR
\MASTMIR.DAT, the following example restarts the server using the mirror device:

sqlservr -c -dc:\sql60\data\master.dat -ec:\sql60\log\error.log 
    -rd:\sql60\mirror\mastmir.dat

where

-c

Starts SQL Server independent of the Windows NT Service Control Manager.

-d

Is the path of the primary device for the master database.

-e

Is the path of the error log file for SQL Server.

-r

Is the path of the mirroring device to be used to mirror the master database.

Note The sqlservr utility requires that both the -d and -r options be present on the command line. The program always attempts to use the primary device specified by the -d option before it uses the -r option. When the primary device for master cannot be used, the server uses the device specified with the -r parameter. You cannot force the server to use the mirror device as the primary device if the original primary device works.

Configuring the server startup parameters to run SQL Server with the -r option allows SQL Server services to automatically use the mirror device for the master database when the primary device for master fails. The -r option requires the full path of the mirror device.

You can configure the server startup parameters by using either SQL Setup or SQL Enterprise Manager. The following procedure assumes you are using SQL Setup.

    To modify the server options to specify -r

1. From the Microsoft SQL Server 6.0 program group, double-click the SQL Setup icon.

   

2. Follow the onscreen instructions until the Microsoft SQL Server 6.0 Options dialog box appears.

   

3. Select Set Server Options and choose the Continue button.

   The Select Server Options dialog box appears.

   

4. Choose the Parameters button.

   The Server Parameters dialog box appears.

   

5. In the Parameter box, type the path of the mirror device. For example:

   -re:\mirdir\mastmir.dat

6. Choose the Add button, and then choose the OK button.

   The Select Server Options dialog box reappears.

7. Choose the Change Options button.
8. Choose the Exit button.

If the -r option is present, sqlservr is always executed with both the -r and the -d options. This minimizes the chance that you will get a message stating that the MASTER device is unusable. If the -r option is present, the mirror file is used when the primary device fails.

Note You should periodically check the error log to determine if you are running SQL Server using the mirror of the master database, because it is possible to have a no longer operational primary device but not be aware of it. Since it is possible to not be aware of the primary device failure, you might want to use -r only if it becomes necessary because the primary device fails.