Database Mirroring - SQL Server 2008

As noted in the previous section, many of the well-known HA technologies included in SQL Server, such as failover clustering and log shipping, are very valuable but somewhat cumbersome to configure and use. Also, in many cases, they solve only half of the problem for many applications—namely, failing over the database server. This leaves the application developer in a lurch, since all applications must be programmed with special code to failover the application to a new database server in the case of a failure in the original database server.

With database mirroring, SQL Server provides both database availability and application availability through the SQL Native Access Client (SNAC) stack, which understands and can work with database mirrors.

How Database Mirroring Works

Figure shows the basic architecture of the database mirroring technology. In the simplest deployment of database mirroring, there are two major server-side components: the principal server instance (principal) and the mirror server instance (mirror). The principal, as the name implies, contains the principal database. This is the database where you will perform your transactions.

Database mirroring architecture, with witness

Database mirroring architecture, with witness

The basic idea behind database mirroring is that synchronized versions of the database are maintained on the principal and mirror. If the principal database (or the principal itself!) becomes unavailable, then the client application will smoothly switch over to the mirror database, and operation (from the users’ point of view) will continue as normal.

So, a client interacts with the principal and submits a transaction. The principal writes the requested change to the principal transaction log and automatically transfers the information describing the transaction over to the mirror, where it is written to the mirror transaction log. The mirror then sends an acknowledgment to the principal. The mirror continuously uses the remote transaction log to “replicate” changes made to the principal database to the mirror database. With SQL Server 2008, a good amount of focus has been placed on the performance of handling these log records, and the following enhancements have been made:

  • The data stream between the two partners is compressed. Note that SQL Server will compress the data stream between partners only when a 12.5 percent compression ratio can be achieved.
  • SQL Server adds records to the mirror log asynchronously, while at the same time processing records from the log on disk.
  • SQL Server has improved its efficiency around the buffers used to queue up log records when sending them to the mirror.
  • When a failover occurs, SQL Server has improved the undo/redo process for transactions on the mirror (which was the principal before failure) that may not have been commited. SQL Server sends page hints to the principal early in the undo process, so that the principal can cache the pages into the send queue for the mirror.

Database mirroring runs in-process in SQL Server. Unlike replication, which is a log reader, database mirroring sends the log buffers stored in memory to the mirror before writing them to disk.

Synchronous and Asynchronous Modes

Database mirroring has a synchronous and an asynchronous mode of operation. The synchronous mode will force the principal to not consider a transaction committed until the mirror has entered the transaction into its transaction log and sent back an acknowledgment. In asynchronous mode, the principal does not wait for the mirror to acknowledge that it has received the transaction before committing. This mode is potentially faster, but you do run the risk of the two databases getting out of sync, since it is not guaranteed that a transaction actually made it to the mirror.

What happens, then, in the basic principal/mirror setup if the principal experiences a failure? Typically, the DBA will manually force service on the mirror, so that the mirror becomes the target for the application. The client is automatically and transparently (to the user) redirected to the mirror. This transparent client redirection is an interesting innovation. Without this technology, developers would need to write custom code into their application in order to failover gracefully to the mirror node.

If, instead, the mirror experiences a failure then, in synchronous mode or asynchronous mode, the principal database would continue to be available, but would be exposed for the period of time that the mirror is unavailable.

Let’s now introduce the third server component into our mirroring architecture: the witness server instance (witness). Unlike the principal and the mirror, the witness does not perform any database operations—it doesn’t maintain a transaction log or a mirror copy of the database. Its primary function is to allow automatic failover (no DBA intervention). Essentially, the witness monitors the operation of the principal and mirror, and if the principal exhibits no heartbeat response within a defined timeout period, then the witness triggers automatic failover to the mirror. Furthermore, if the mirror fails, but the principal is still in contact with the witness, the principal can continue to operate. When the witness detects that the mirror is back online, it will instruct the mirror to resynchronize with the principal.

The witness can also break any ties between the principal and the mirror to prevent split-brain scenarios, where both machines think they are the principal. For example, if both servers come up at the same time and both think they are the principal, then the witness can break the tie. When a failure happens, all participants in the mirror get a vote to decide who the principal is. Of course, if the principal itself is down, then only the mirror and the witness will vote. In this scenario, the witness and mirror would decide the mirror needs to become the principal, and the failover would occur. When the original principal comes back online, it would assume the role of mirror, and the log buffers would go from the new principal to the new mirror.

Database mirroring uses the idea of a quorum when running in synchronous mode. A quorum is the minimum number of partners needed to decide what to do in a mirrored set. In database mirroring, a quorum is at least two partners. The partners could be the principal and the witness, the witness and the mirror, or the principal and the mirror. A full quorum is when all three partners— principal, mirror, and witness—can communicate with one another. If at any point a partner is lost, such as the principal server, the other two partners establish a quorum and arbitrate what should happen. Each partner in the quorum gets a vote and, in the end, the witness breaks any ties between the principal and the mirror.

If, for some reason, all three partners lose communication with each other, automatic failover will not occur. You will want to make sure that your witness can always talk to your mirror over the network, since this link is crucial to making sure that automatic failover will occur.

When working with a witness, but in asynchronous mode, the principal will send the transaction details over to the mirror to commit, and commit the transaction locally. The principal does not wait for the acknowledgment from the mirror before committing. Since synchronization cannot be guaranteed in this mode, you lose the ability to perform automatic failover. Microsoft recommends that if you are going to run in asynchronous mode, you do not use a witness, since a quorum would be required, but you are not getting the benefits of the quorum, because asynchronous mode does not support automatic failover. Asynchronous mode may be suitable on occasions when you are willing to sacrifice HA for performance. Since synchronous mode requires committing on both sides, if you do not have acceptable network bandwidth, or if the mirror gets behind, your overall application performance could suffer due to delayed transaction commits.

Table summarizes the different database mirroring modes, and the pros and cons for each mode.

Database Mirroring Modes

Database Mirroring Modes

Database Mirroring States

In database mirroring, your system goes through a set of database states. These states indicate the status of your mirror, and you can query for them, as you will see when we look at how to monitor database mirroring using catalog views. Table lists the different states for databases that are part of database mirroring.

Database Mirroring States

Database Mirroring States

Automatic Page Repair

SQL Server 2008 Enterprise Edition adds some very nice functionality with regard to repairing certain SQL Server errors that are the result of failed/corrupted page reads. If a partner (principal or mirror) within a database mirroring configuration fails with one of the errors listed in Table, SQL Server will automatically try to resolve the error by replacing the page with a fresh copy that it requests from the other partner. Obviously, much care needs to be taken during this process to ensure that both partners are at a consistent state with regard to transactions, and this is automatically handled by the database mirroring session.

Errors Accounted for by Database Mirroring

Errors Accounted for by Database Mirroring

For errors reading data pages on the principal, the following actions are taken:

  • An entry is entered into the suspect_pages table for the particular page in error.
  • The principal server then sends the page ID and current log sequence number (LSN) to the mirror server. At the same time, the page is marked as restore pending, making it inaccessible to application queries.
  • The mirror ensures that it has applied all transactions up to the provided log sequence number within the request. Once the mirror and the principal are transactionally consistent, the mirror attempts to retrieve the page identified by the page ID provided within the request. Assuming that the mirror can access the page, it is returned to the principal.
  • The principal replaces the corrupted page with the new page from the mirror.
  • The page is marked as restored within the suspect_pages table, and the principal applies any deferred transactions.

For errors reading data pages on the mirror, the following actions are taken:

  • When a page error is detected on the mirror, the mirroring session automatically enters a SUSPENDED state.
  • An entry is entered into the suspect_pages table on the mirror for the particular page in error.
  • The mirror server sends the page ID principal server.
  • The principal attempts to retrieve the page identified by the page ID provided within the request. Assuming that the page can be accessed, it is returned to the mirror.
  • It is possible that the mirror provided the IDs of multiple corrupt pages during the request. Therefore, the mirror must receive copies of each requested page before it tries to resume the mirroring session. If all pages were provided from the principal and the mirror session is resumed, then the entries within the suspect_pages table are marked as restored. If the mirror does not receive all pages requested, then the mirroring session is left in a SUSPENDED state.

Page repairs are performed asynchronously, so requests for the corrupt page may fail while the page is being replaced. Depending on the state of the page repair, the request will return the appropriate error code (823 or 824) or error 829, which indicates the page is marked as restoring and actively being restored. It may be possible to account for this within the data access layer of your application and retry the query if you know that database mirroring is being used.

Managing Database Mirroring Using T-SQL

In this section, we’ll demonstrate how to set up a mirroring architecture using Transact-SQL (T-SQL). This example uses three SQL Server server instances (principal/mirror/witness). As discussed previously, you can use only two server instances (no witness), but then you lose the ability to do automatic failovers.

One of the main prerequisites for setting up database mirroring is to ensure that your databases run in full recovery mode, since database mirroring uses the transaction log and requires the more extensive logging and log information created when you use full recovery mode. To do this, you can use the following ALTER DATABASE statement:

USE master;
GO
ALTER DATABASE YOURDATABASEHERE
SET RECOVERY FULL;
GO

Also, remember that nonlogged operations will not work with database mirroring, so do not use the nonlogged bulk copy program (bcp) to load data into the database.

Setting Up Connectivity

Database mirroring uses the endpoint connection model and requires that you set up endpoints so that the principal and the mirror can talk to each other. You have the option of either using the Security Support Provider Interface (SSPI), which uses Windows credentials, or using certificate-based authentication.

Using Windows-Based Authentication

If you want to use Windows-based authentication, the accounts that you use for your SQL Server service accounts involved in the database mirroring session must be in the same domain, or at least in a trusted domain. If the accounts are not in trusted domains, the connection between the principal and the mirror will fail. If you do not have trusted domains or you do not use Windows-based authentication, you must use certificates for authentication, as described in the next section.

You will also need to assign permissions to the accounts to be able to connect to the other server(s), via endpoints, and also to the database, as you will see shortly. The example in this chapter assumes that you use the same service account for all your instances.

To use Windows-based authentication, perform the following steps:

  1. Create the endpoints for mirroring. To create an endpoint, you need to use the CREATE END POINT statement. This statement takes the name of the endpoint, the state, the protocol, and the payload. The following example creates a new endpoint that uses a TCP payload on a particular port. This code should be run on the principal server. Make sure to give your endpoint a unique name.
  2. Create a login, if one does not already exist, and assign CONNECT permissions to the endpoint for that login. The following code creates a login that maps to the Windows account that the witness will use to log on, and it assigns permissions on the endpoint:
  3. Perform the same operations on the mirror, since the witness needs to be able to connect to and authenticate against both servers. The following code creates an endpoint and a user login, and assigns permissions on the mirror for the witness:
  4. CREATE ENDPOINT MirroringEndPoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=10111)
    FOR DATABASE_MIRRORING (ROLE=ALL)
    GO
    USE master;
    GO
    CREATE LOGIN [YOURDOMAINwitnessaccount] FROM WINDOWS;
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [YOURDOMAINwitnessaccount];
    GO
  5. Create an endpoint on the witness to allow the principal and the mirror to connect to the witness. This example assumes that the same Windows account is used to log on to both the principal and the mirror. The following code performs this step:

Connecting Using Certificates

You may want to use certificates for authentication rather than Windows-based authentication for your connectivity. Certificates use the encryption technology introduced in SQL Server 2005.

The following steps demonstrate how to create your certificates and assign them for your database mirroring authentication. You should perform these steps on the principal database server.

  1. If you have not already set up your database master key for encryption, you must do that now; otherwise, you will get an error when you try to create the certificate in the database. The following T-SQL command creates the database master key, which is secured via a password that you specify:
  2. CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!@@@gh!2a*'
    GO
  3. Create the certificate you want to use inside your database. The following command creates a certificate using the encryption technology provided within SQL Server:
  4. USE master;
    CREATE CERTIFICATE HOST_ServerACert
    WITH SUBJECT = 'Server A cert for DBM';
    GO
  5. Create your endpoints to use your certificates. Notice that you pass the authentication and the encryption values to the CREATE ENDPOINT function.
  6. Back up the certificate and transfer it securely to your mirror database server. You can back up the certificate using the following command:
  7. BACKUP CERTIFICATE ServerACert TO FILE = 'C:ServerACert.cer';
    GO
  8. Perform steps 1 through 4 on your mirror database server, except change ServerA to ServerB for the naming. You’ll need the certificate from server B copied over to server A.
  9. Create a login for your mirror database and make that login have permissions to the certificate on your principal. This is for the incoming connection, as opposed to the steps you just performed for the outgoing connection. The following code creates the login and also grants connect permissions on the endpoint for database mirroring:
  10. USE master
    GO
    CREATE LOGIN mirrorlogin WITH PASSWORD = '!@#1579212'
    CREATE USER mirroruser FOR LOGIN mirrorlogin
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [mirrorlogin]
    GO
  11. Assign your user to your newly copied certificate from the mirror server. The following code creates a certificate and uses the mirror’s certificate:
  12. CREATE CERTIFICATE mirrorservercert
    AUTHORIZATION mirroruser
    FROM FILE = 'c:ServerBCert.cer'
    GO
  13. Reverse the procedure in steps 6 and 7 on the mirror so that the principal can log on to it. Also, make sure to allow the witness to be able to log on to both the principal and the mirror using the same steps just outlined.

Backing Up and Restoring the Principal Database

Now that you have security and connectivity set up between your principal, mirror, and witness, you can get your database ready to go. Again, before performing the steps presented in this section, make sure your database is in full recovery mode. The first step is to back up the database so that you can move it over and restore it on the mirror. The following code will back up your principal database:

USE YourDB
BACKUP DATABASE YourDB
TO DISK = 'C:YourDB.bak'
WITH FORMAT
GO

Once you copy over the database to your mirror server, you need to restore the database, which will ensure that you have the database in the same state of principal and mirror. You must make sure that you use the same name for the database on both the principal and the mirror. According to Microsoft’s recommendations, the path (including the drive letter) of the mirror database should be identical to the path of the principal database. If these pathnames differ, you cannot add any files to the database. It’s not required, but it will make it easier for you, since you will not need to change configuration. Also, you must restore your database using the NO RECOVERY option with database mirroring.

The following code restores the database on the mirror:

RESTORE DATABASE YourDB
FROM DISK = 'C:YourDB.bak'
WITH NORECOVERY
GO

If you are testing database mirroring on the same server using multiple instances, or your path names differ between your principal and mirror for the database, you will need to restore your database with the MOVE keyword. The following code changes the restore path using the MOVE keyword:

RESTORE DATABASE YourDB
FROM DISK='C:YourDB.bak'
WITH NORECOVERY,
MOVE 'YourDB' TO
'C:Program FilesMicrosoft SQL ServerMSSQL.2 MSSQLDataYourDB_data.mdf',
MOVE 'YourDB_log'
TO 'C:Program FilesMicrosoft SQL ServerMSSQL.2 MSSQLDataYourDB_Log.ldf';
GO

Establishing the Principal/Mirror Partnership

You will always want to make sure you configure your mirror server first to be a partner with your principal server before you configure the principal. To do this, you use the ALTER DATABASE statement with the SET PARTNER statement for the database mirroring option. The ports you set are server-wide, so if you run database mirroring on the same server using multiple instances for testing, you will want to use different ports. The following code sets the partner for the mirror to the principal using
the TCP endpoint you created earlier:

ALTER DATABASE YourDB
SET PARTNER =
'TCP://YourPrincipalServer.YourDomain:10111'
GO

On the principal, you need to set its partner to the mirror server using the following code:

ALTER DATABASE YourDB
SET PARTNER =
'TCP://YourMirrorServer.YourDomain:10111'
GO

Finally, you need to set the witness. You can do this from either server. The following code performs this operation:

ALTER DATABASE YourDB
SET WITNESS =
'TCP://Your Witness Server.YourDomain:10111'
GO

Changing Transaction Safety Levels

By default, database mirroring sets the transaction safety level to FULL. The FULL transaction safety level provides the highest levels of protection and availability, and it is required if you want to run in a synchronous state. If you want automatic failover, you need to have a witness server. If you do not want to run in FULL transaction safety mode, you can modify your transaction safety level using the database mirroring options offered by the ALTER DATABASE statement. The following code shows the different options you have with the ALTER DATABASE statement for database mirroring:

ALTER DATABASE dbname SET PARTNER { = 'Partner Server'
| FAILOVER
| FORCE_SERVICE_ALLOW_DATA_LOSS
| OFF
| RESUME
| SAFETY { FULL | OFF }
| SUSPEND
| REDO_QUEUE ( integer { KB | MB | GB } | OFF
| TIMEOUT integer
} ALTER DATABASE dbname SET WITNESS { = 'Witness Server'
| OFF
}

So, to change your transaction safety level from FULL to OFF, use the following command on the principal:

ALTER DATABASE dbname SET PARTNER SAFETY OFF.

You may want to run with your transaction safety level OFF if you want to maximize performance. Doing so shifts the session into asynchronous operating mode, which maximizes performance at the cost of safety. If the principal becomes unavailable, the mirror stops but is available as a hot standby. You need to make the failover happen to the mirror.

Adjusting the Timeout Setting

Beyond the database states listed in Table, database mirroring also implements a heartbeat between the partners in a mirror. By default, this heartbeat is sent every 2.5 seconds between the servers. If the partner does not respond after four pings, a failover is initiated. What will happen depends on whether you’re running in synchronous or asynchronous mode, and whether a witness is present. See Table for more details.

You can customize the timeout setting to either shorten or lengthen it depending on your situation. For example, you may want to lengthen the timeout setting if you have slow connectivity between your servers and do not want false failovers to occur. To change the timeout setting, use the ALTER DATABASE statement on the principal and set in seconds the timeout period you want, as shown in the following code:

ALTER DATABASE dbname SET PARTNER TIMEOUT 15

In high-performance mode, the timeout value cannot be changed and is always 10 seconds. In high-safety mode, the timeout can be configured. It is recommended that the timeout value be set to 10 seconds or more; otherwise, you may overload your system by having false failovers all the time due to missed ping messages.

Initiating a Fail over

There may be times, such as when you are testing or after you have upgraded one of the mirror partners, that you want to manually initiate a failover, to make sure failovers will work when required. Rolling upgrades can be supported only if the physical log file format used by SQL Server does not change because of the upgrade. For example, if you want to install a Windows patch on the principal and then on the mirror, and this patch does not affect SQL Server, you can install the patch on the mirror, failover the principal to the mirror, and apply the patch on the old principal. When you’re finished, you can failover the mirror back to the principal. Using this technique allows you to drastically reduce planned downtime within your database environment.

To initiate a failover manually, your mirror must be in the SYNCHRONIZED state. Also, you must connect to the master database before failing over the server, since you cannot be connected to the database that you are about to failover. When you issue the manual failover, the principal will disconnect
clients, uncommitted transactions will be rolled back, and the last remnants of the log will be shipped over to the mirror. The mirror then becomes the principal, and the principal becomes the mirror. All your clients will need to reconnect to the new principal. To issue the failover, use the ALTER DATABASE statement with the FAILOVER option as follows on the principal server:

ALTER DATABASE dbname SET PARTNER FAILOVER

If you are running without a witness in FULL transaction safety mode, manual failover is the only type of failover you can perform.

If you are not running in FULL transaction safety mode (also called asynchronous mode), you can force a failover as well, but there may be some data loss since this mode does not guarantee that the mirror has received all thelogs from the principal. To force a manual failover in this mode, you must use a different option with the ALTER DATABASE statement on the principal:

ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The reason that database mirroring can failover very fast is that it takes advantage of the fast recovery technology in SQL Server. The mirror is constantly running REDO when it receives the log files from the principal. When the mirror is failed over to, it moves from REDO to UNDO and opens up the database for clients if you’re using Enterprise Edition. For Standard Edition, the database is available after both REDO and UNDO are complete.

Suspending and Resuming Mirroring

There may be times when you want to suspend mirroring and then resume it later. For example, you may want to do this if you find a bottleneck in your system and want to allow the principal to quickly complete its pending transactions, and at some later point you want to apply the same changes to the mirror. In such cases, you can suspend the session, apply your changes to the principal, and then reenable the mirror once the bottleneck is removed. When you suspend a mirroring session, client connections are retained. Also, the log is not truncated, since the log will need to be sent to the mirror at some point, unless you break the mirror partnership. You will not want to suspend a mirror for a long period of time, since the principal’s log file could fill up the available storage on your server.

To suspend the session, use the ALTER DATABASE statement on either partner as follows:

ALTER DATABASE dbname SET PARTNER SUSPEND

To resume the session, just change SUSPEND to RESUME:

ALTER DATABASE dbname SET PARTNER RESUME

Terminating Database Mirroring

To terminate a database mirroring partnership, you can use the following ALTER DATABASE command on either partner:

ALTER DATABASE dbname SET PARTNER OFF

When you terminate a partnership, all information about the session is removed, and each server has an independent copy of the database. The mirror database will be in a restoring state until it is manually recovered or deleted. If you want to recover the mirror database, use the RESTORE command with the WITH RECOVERY clause.

Managing Database Mirroring Using Management Studio

Rather than going through the manual steps to set up mirroring, you can perform all the same actions using SQL Server Management Studio. You can also pause and remove mirroring, as well as initiate a failover from Management Studio. Using the wizard is much easier than writing your own T-SQL code to configure mirroring; however, you get more control over your configuration using T-SQL.

To get started, launch Management Studio and select the database you want to set up as the mirror. Right-click the database and select Properties. You should see mirroring as a page type in the left navigation pane, as shown in Figure.

Mirroring settings in the Database Properties dialog box

Mirroring settings in the Database Properties dialog box

The Configure Database Mirroring Security Wizard will step you through setting up the correct security settings for your principal, mirror, and witness, if used. The wizard will create all the endpointsettings for you automatically. Figure 4-3 shows the wizard screen where you can select which servers to configure.

Configure Database Mirroring Security Wizard server selection page

Configure Database Mirroring Security Wizard server selection page

Once you select the servers you want to configure, the wizard steps you through a set of questions to configure your endpoints. Note that if you are testing database mirroring using multiple instances on a single machine, you will need to specify different ports for your endpoints. Figure shows setting the ports, encryption options, and endpoint options in the wizard.

Setting principal server instance options in the Configure Database Mirroring Security Wizard

Setting principal server instance options in the Configure Database Mirroring Security Wizard

Next, the wizard will step you through setting the exact configuration for the remaining partners in your mirroring set. You will need to fill out the same information as you filled out for the principal.

Finally, you need to specify the SQL Server service accounts for your servers if they are different. If they are not different, you should leave the text boxes blank.

Setting service account options in the Configure Database Mirroring Security Wizard

Setting service account options in the Configure Database Mirroring Security Wizard

Once you have done this, the wizard will complete and attempt to set up your mirroring configuration. the updated mirroring settings in the Database Properties dialog box once the wizard has completed.

Mirroring settings in the Database Properties dialog box after running the Configure Database Mirroring Security Wizard

Mirroring settings in the Database Properties dialog box after running the Configure Database Mirroring Security Wizard

Once the wizard has completed, you’ll need to perform some manual steps before you can click the Start Mirroring button in the Database Properties dialog box. You’ll need to manually back up and restore your database from the principal to the mirror. Once you’ve restored the database with the NO RECOVERY option, you can click the Start Mirroring button. Management Studio will attempt to start database mirroring using the same commands you saw earlier to manually set up your mirror pair. Figure shows a successfully started database mirroring session in Management Studio.

A successfully running mirror being monitored in Management Studio

A successfully running mirror being monitored in Management Studio

The Mirroring section of the Database Properties dialog box will show the current state of the mirroring. You can also get a quick view of the role and status via Object Explorer, as shown in Figure.

Quick check of mirroring status in Object Explorer

Quick check of mirroring status in Object Explorer

Full-Text Indexing and Mirroring

Since full-text indexing catalogs are now backed up when you back up your databases, when you restore the principal onto the mirror, the full-text catalog is restored as well. As Data Definition Language (DDL) changes are made to the principal’s catalog, these changes will be reflected on the mirror.

After a failover, a crawl is automatically initiated on the mirror to update the full-text index. If you create a new catalog on the principal and the mirror cannot perform the operation for some reason, the database state will become SUSPENDED for mirroring.

Service Broker and Database Mirroring

You can use Service Broker and database mirroring together. When combining the two, you must configure Service Broker with two addresses for your route: the principal database server’s address and the mirror database server’s address. Service Broker will connect to both machines to see who the current principal is, and it will route the message to that principal. If the principal goes down, Service Broker will connect to the mirror, and if the mirror is now the principal, Service Broker will begin delivering messages to the new principal.

You can monitor database mirroring when combined with Service Broker by using the database mirroring Transport object, which is under the Service Broker object, which is, in turn, under the SQL Server object in Performance Monitor. One caveat, though, is that to use Service Broker with database mirroring, you must always have your transaction safety level set to FULL.

Client Applications and Database Mirroring

Normally, when programming your client applications for HA, you need to write a lot of special code to handle the failover of your application. With database mirroring and the .NET or SNAC SQL Server providers, transparent client redirection is automatically handled for you.

Before diving into how to change your client application to use database mirroring, we must first emphasize that transparent client redirection is not a panacea. You still will need to write good code that fails when there is an error and retries the operation. If you do not, transparent client redirection will not magically restart your transaction and complete the operation for you.

When working with transparent client redirection, you must be running the version of the SQL Server providers that comes with SQL Server. There are two modes in which you can program database mirroring: implicit and explicit.

In implicit mode, you do nothing special in your connection string code. Instead, when you first connect to a SQL Server server instance that is part of a database mirror, the SQL Server provider will cache the name of the partner server in memory. If the principal goes away, the SQL Server provider will try to connect to the new partner server on subsequent connections. If a new partner is added after the failure as a new mirror, Microsoft Data Access Components (MDAC) will cache the new partner’s name as well.

With explicit mode, you need to specify the server name for the mirror in your connection string. You use the following syntax in your connection string to specify the failover partner:

";Failover Partner=YourServerName"

Even if you specify the name in the connection string, the SQL Server provider will override the name you specify with the name it retrieves from SQL Server for the failover partner. The reason to specify the partner name in the connection string is to harden your initial connection to the principal. If the SQL Server provider cannot connect to the principal to begin with and there is no partner specified in the connection string, the connection will fail. However, if there is a partner specified in the connection string, the SQL Server provider will try the failover partner for the connection.

Regardless of whether you specify the partner name in the connection string, which is a good practice, you must always specify an initial catalog or database to which to connect. If you do not, transparent client redirection will not work, and the SQL Server provider will throw an error when you attempt to create your connection.

Finally, remember that the failover partner’s name is cached in memory. This means that if the application crashes or is restarted, the cached name goes away. For this reason, you will want to make it a habit to specify the failover partner name in the connection string.

Monitoring Database Mirroring

As you saw earlier, you can see the current state of the mirroring in the Mirroring section of the Database Properties dialog box in SQL Server Management Studio. SQL Server provides a variety of other ways to monitor database mirroring, including Database Mirroring Monitor, system stored procedures, catalog and dynamic management views, Performance Monitor, Profiler, and event logs.

Database Mirroring Monitor

Database Mirroring Monitor is available in SQL Server Management Studio. It encapsulates information from the database mirroring catalog views (discussed shortly) by asynchronously capturing the results from the sp_dbm monitor update system stored procedure on the principal and the mirror. It presents a graphical user interface into those results, which are stored in an internal system table within msdb. Furthermore, Database Mirroring Monitor allows for an eventing subsystem through configuration of thresholds on key performance metrics.

Database Mirroring Monitor displays information regarding the partners (principal, mirror, and witness) within a mirroring session, as well as the state of the mirroring session (SYNCHRONIZED, SYNCHRONIZING, and so on) and whether there is a witness present. Database Mirroring Monitor also displays performance information about the principal and mirror server logs. To configure warning thresholds, click the Warnings tab in the Database Mirroring Monitor window. You can specify the thresholds in the Set Warning Thresholds dialog box.

Database Mirroring Monitor showing a synchronized session

Database Mirroring Monitor showing a synchronized session

System Stored Procedures and Views

If you would prefer to view mirroring status information in textual format, instead of graphically with the Database Mirroring Monitor, you can run the sp_dbmmonitorresults system stored procedure to retrieve history.

SQL Server has three catalog views related to database mirroring, as well as one dynamic management view:

  • sys.database_mirroring: This catalog view contains mirroring information about each database in the SQL Server instance that has mirroring enabled. You can retrieve mirroring and other information about the database using sys.databases as well.
  • sys.database_mirroring_endpoints: When you need to work with endpoints, you will want to use this catalog view. Using it, you can figure out which ports your servers are listening on and make sure that you have set up your endpoints correctly. Most of the columns in this view are inherited from the sys.endpoints view. You could use sys.endpoints to view all endpoints in your system, including your database mirroring endpoints.
  • sys.database_mirroring_witnesses: This catalog view contains a row for every witness role a server instance plays in a database mirroring partnership.
  • sys_dm_db_mirroring_connections: As the name suggests, this dynamic management view lists information about the connections being used by database mirroring sessions. Figure shows using the database mirroring views from Management Studio.

Using the database mirroring views inside Management Studio

Using the database mirroring views inside Management Studio

Performance Monitor Counters

You can also monitor database mirroring using Performance Monitor and the database mirroring counters. You can find the Performance Monitor counters under the SQL Server: Database Mirroring object. Table lists some of the more important Performance Monitor counters that you can use with database mirroring.

Some Database Mirroring Performance Monitor Counters

Some Database Mirroring Performance Monitor Counters

If you let Performance Monitor run and there is no transaction activity on your principal, you will see the pattern of the database mirroring pings appear in your capture. Monitor the Total Bytes Sent/sec or the Mirroring Bytes Sent/sec counter to see the pings.

Profiler

You can use Profiler to watch the changes that are happening to your databases that are part of a mirror. Profiler will not show the log being shipped from one server to another, but you will see any SQL commands sent to the current principal or any snapshots on the mirror. You can also see the state change events when roles change because of a failover. To find the database mirroring–specific event in Profiler, look under Database and select the Database Mirroring State Change option.

Event Notifications and Logs

You can use a couple of event notifications for database mirroring to support very powerful automated processing when a mirroring state changes:

  • Database Mirroring State Change: Triggered when a state change occurs, such as a mirror becoming a principal.
  • Audit Database Mirroring Login: Can be used to monitor security issues between the principal, mirror, and witness.

Database mirroring puts out events to the Windows Event Log and the SQL Server error log. Some events are information, such as messages telling you that database mirroring has started and what the role of the server is. You can scan the event log to quickly see what’s happening with your database mirroring setup, or you can use a third-party tool to monitor the log. Database mirroring event IDs are in the 1400 range, so if you want to filter for them, you will want to filter in that range for SQL Server.

Performance Considerations for Database Mirroring

Depending on how you configure mirroring, you can expect different levels of performance. For example, if you use the FULL transaction safety level, your performance on your principal will be affected by two primary factors: the mirror’s I/O ability on the transaction log and your network connection, since the network packets must make a round-trip between the principal and the mirror.

On the mirror, you will want to make sure that you have the same computing and I/O power as the principal. The reason for this is in the case of a failover. You do not want 100 percent load on a larger machine rolling over to a less capable machine. I/O throughout is also very important on the mirror, since the mirror is performing sequential log writes and singleton lookups. Make sure to put your data, log, and tempdb files on different drives that use different disk controllers, if possible.

The edition of SQL Server that you use also affects database mirroring. Even though the Standard Edition of SQL Server supports database mirroring, it has limited capabilities. The Transaction safety level must always be FULL in Standard Edition, and the mirror database server always uses a single thread for the REDO queue processing. In Enterprise Edition, one thread is created per four CPUs for the REDO queue processing.

Finally, make sure to keep your backup and restore procedures in place, even if you use database mirroring. You should back up your databases from the principal server.

Limitations of Database Mirroring

There are a few limitations with database mirroring. First and foremost is that database mirroring supports only two nodes in terms of failover support. Both log shipping and failover clustering support more than two nodes. For scale-out, read-only scenarios, you should consider peer-to-peer replication, rather than database mirroring.

You can use both log shipping and replication with database mirroring. You should make the principal database server the primary in your log shipping topology. You can then make one or more other database servers your secondary database servers that get the log shipped to them. Do not make the mirror database server your secondary database server. As for replication, you can use your principal database server as a publisher and secondary servers as subscribers.

You cannot mirror the master, msdb, temp, or model system databases. Furthermore, you must keep all your logins and jobs in sync manually, since database mirroring does not mirror your logins and jobs between servers. Also, make sure to remove all database snapshots on the mirror before breaking your mirror.

While many customers have requested this feature, you cannot back up your database from the mirror. Instead, you need to back up your databases from the principal server.

Finally, mirroring supports the relational engine only at a database level. If you want instance failover, you will need to configure mirroring for every database in an instance. Also, if you want HA for other components, such as Analysis Services and Reporting Services, you will need to use Windows clustering.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics