SQL SERVER 2005 Database Mirroring (3)

Because server A cannot see the witness server W or the original mirror partner server B, you must enter the disconnected state and make the database unavailable.

Server B and Server w can compose quorum. Server B cannot see server A, so Server B tries to become the primary server and bring its database online. Because Server W also does not see server A, it agrees to server B. Server B now has a quorum, serves as the primary server role for the session, and then restores its database.

If you resume communication links, server A can see that Server B is now the primary server and that the witness server W also recognizes Server B as the primary server. Server A converts its role to a mirrored server, and then tries to synchronize with the new master server. The configuration results after synchronization are shown in Figure 15.

Figure 15: This scenario communicates with the restored version, and the database mirroring is in reverse direction

Summary: The witness server is located on the remote site of the mirror server, and automatic failover is generated if communication links between sites are interrupted.

Scene HACL5: Two sites, the witness server is at the primary server site

In this highly available scenario, assume that the witness is placed on the same site as the primary server, as shown in Figure 16, and then the communication between the two sites is interrupted.

Figure 16: Communication interruption between the primary server/witness server and the mirror server

In this case, Server B, which is responsible for mirroring the database, is orphaned by the primary server and the witness server.

Server A and server w continue to compose quorum, so server a maintains its database as the primary database.

However, server A also sets the database to disconnected state because it cannot see Server B and it is not possible to transfer data. Server B also does not see server A, so it also enters the disconnected state. The configuration results are shown in Figure 17.

Figure 17: Communication Failure In this scenario causes two partners to be disconnected states

Server a continues to accept transactions but cannot truncate transaction log records. If the link is restored quickly, the mirroring session can also be synchronized and returned to the initial operating state.

Summary: The witness and primary server are at the same site, the mirror server is at the remote site, and communication between sites does not cause automatic failover.

Summary: Communication outages in highly available scenarios

In a highly available configuration using three standalone servers, there are three separate communications links.

The primary server/mirror server failed to communicate and no automatic failover occurred.

The primary server/witness first has a communication failure, then the primary server/mirror server is disconnected, and automatic failover occurs. The recovery link will continue to mirror the reverse direction of the database.

mirror server/Witness communication failed, no automatic failover occurs.

In a highly available configuration mode with only one communication link, the witness resides on the site of the primary server or the mirror server.

The witness server resides on the remote site of the mirror server, and automatic failover occurs if the communication link between the sites is interrupted.

The witness and primary server are located on the same site, the mirror server is at the remote site, and communication between sites does not cause automatic failover to occur.

High Protection Program

High-protection mode works with safety full, but does not have a witness server. Because the mirrored configuration includes only the primary database server and the mirrored database server, only one communication link is available. This greatly reduces the number of scenarios.

Scenario 1: The High protection operation mode includes only two server instances, the primary server and the mirror server. Because there is no witness server, the automatic failover division is not possible. Only one communication link between the servers is interrupted, resulting in the configuration results shown in Figure 18.

Figure 19: The primary database is not affected if the mirror server is not available in a high protection scenario

Scenario 3: In a high protection scenario if the primary database is unavailable, the mirrored database must continue to be mirrored but enter the disconnected state, as shown in Figure 20.

Figure 20: In a high protection scenario, if the primary database is unavailable, the mirrored database enters the disconnected state

Because the high protection operation mode uses safety full, any damage causes the primary database to be available, while the mirrored database continues to maintain the recovering state: No database is online. The result is that the pattern is not a good solution for high availability requirements and is therefore more appropriate as a temporary scenario, such as having to remove the witness for a short period of time.

High Performance Solutions

High-performance operating mode works with safety off. There is no witness server role. Because the mirrored configuration includes only the primary database server and the mirrored database server, only one communication link is available. Although similar to high protection mode, the behavior is not the same as high protection mode because the safety is set to off.

Scenario 1: Use two instances of SQL Server in High-performance operating mode. One responsible for the primary database and another for the mirrored database. Therefore, there is only one communication link between the servers and may be interrupted, resulting in the configuration results shown in Figure 21.

Figure 21: Communication failures in high-performance scenarios, with two partners entering disconnected state, but the primary database is still available

Because safety is set to OFF, Server A does not need quorum to keep the database active. As a result, the primary server can continue to accept user activity, even though it has entered the disconnected state. If communication is restored, then the mirrored database will attempt to catch up with the primary database but not, or redo error if it cannot retrieve all the missing transactions.

Scenario 2: In a high-performance scenario, if the mirror database is not available, the primary database results are shown in Figure 22.

Figure 22: The primary database is unaffected if the mirror server is not available in high-performance mode

The primary database is still available because the safety is set to off.

Scenario 3: If the primary database is not available in high protection mode, the mirrored database is still mirrored but will be disconnected, as shown in Figure 23.

Figure 23: If the primary server is unavailable, Server B is not affected, but enters the disconnected state

High-performance operating mode and high protection mode, no automatic failover. Because the safety is set to OFF, the primary server will keep its database available when the mirror server is unavailable. Also because the safety is set to OFF, the transaction must not be guaranteed to reach the mirror database. If you force a failover to the mirror server, then some transactions may be lost.

Implementing Database Mirroring

You can find basic information for implementing database mirroring in the "How to" of database mirroring topics in SQL SERVER books Online. In this part of the white Paper, we examine a special example of implementing database mirroring and best practices.

Monitoring Database Mirroring

The state of each database mirroring partner can be identified by examining the primary and mirror databases in SQL SERVER Management Studio's Object Explorer. If the primary and mirror databases are synchronized, Object Explorer appends a (Principal, Synchronized) message to the name of the primary database appended to the name of the mirror server (Mirror, Synchronized). You can check the status of a database mirroring session by observing the status box at the bottom of the mirroring page in the Database Properties dialog box that pops up on the primary server. (The Database Properties dialog box does not open on the mirror server).

You can also query the database mirroring catalog views sys.database_mirroring and Sys.database_mirroring_witnesses (for more information about using catalog views to check the state of the database in a mirroring session. Refer to the "Database mirroring Catalog View Metadata" in the dynamic section of this white paper earlier. The full documentation for catalog views is also included in SQL SERVER Books Online. ）

Database Mirroring Performance Counters

You can use performance counters to monitor network traffic between mirrored partners in a database mirroring session. The SQL Server Database mirroring object contains a number of useful performance counters to monitor the primary server and the witness server. (see "Monitoring Database Mirroring" in SQL Server Books Online)

You can set up a database mirroring object on each of the databases. If you need to monitor more than one database on a single server, you can monitor either the activity of a database individually or all the activities of all databases.

For the primary server, the log Bytes sent/sec counter indicates the rate at which the primary server sends log data to the mirror server, and log send queue indicates how many bytes in the transaction log buffer at a given point in time are to be sent to the mirror server. As transaction log records are sent from the primary server to the mirror server, the primary server's send queue shrinks, but it also grows as new log records enter the log buffer. The transaction Delay counter on the primary server indicates the delay caused by the primary server due to a confirmation message waiting for the mirror server to receive the log. The SENT/SEC counter on the primary server is related to the data page sent by the primary server to the mirror server.

On the mirror server, Log Bytes received/sec indicates the difference between the mirror server and the primary server. (See above log Bytes sent/sec counter). The Redo queue counter indicates the size of the Redo queue. The mirror server uses the redo queue in the redo phase to rerun transactions from the autonomic server. Redo BYTES/SEC indicates the rate at which the mirror server performs transactions in the Redo queue.

For each partner server, the sends/sec and receives/sec counters indicate how many send and receive actions bytes sent/sec and bytes received/sec counters indicate the number of bytes that are included in the send and receive actions on each partner server.

Estimated redo and catch-up time

In the event of a failover, you can use redo queue and redo bytes/sec to estimate the time it takes for the mirror database to complete redo and become available. Use the following simple formula to calculate:

Estimated Redo Time (sec) =

(Redo Queue)/(Redo bytes/sec)

Similarly, if the activity on the primary server is ahead of the mirror server, you can use the log Send queue and log Bytes received/sec counters to estimate the time it takes for the mirror server to catch up with the primary server. The calculation formula is given below:

Estimated catch up time (sec) =

(log Send Queue)/(log Bytes received/sec)

Profiler events

SQL Server Profiler contains a database mirroring event class. The Database:database mirroring state Change event will record whether the monitored server has changed status. (See SQL Server Books Online topic "Database Mirroring State Change Event Class"). It can be helpful to include database name and state when you use this event class. You can use this event to notify you of any state changes in a database mirroring session.

Database Mirroring Row Error

The most error-prone area of database mirroring is the configuration process and the running process.

Exclusion Settings Error

If database mirroring has been set but cannot be started, restart all configuration steps.

1. Verify that the mirror server is as close to the primary database as possible. If you try to start database mirroring, you receive the following error message:

The remote "AdventureWorks" database does not roll forward to a point in time that is contained in the local database log copy. (Microsoft SQL SERVER, error: 1412)

Said mirror image database lags behind the main database. A transaction log backup of the primary database needs to be applied to the mirror database (using NORECOVERY), so that the mirrored database is restored to a point in time, and logging from the primary database can begin to receive from this point in time.

2. Verify that each server's SQL Server Windows service account trusts each other. If the domain in which the server is located does not have a trust relationship, make sure the certificate is correct.

3. By querying the Sys.database_mirroring_endpoints catalog view, confirm that the endpoint is not only defined but also started:

SELECT * FROM sys.database_mirroring_endpoints;

Verify that the correct fully qualified computer name and the correct port number are used. If you configure mirroring between multiple instances of a physical server, the port number must be unique. The SQL Server service login account requires connect to endpoint access rights.

Finally, confirm that the correct endpoint role is defined for the server.

4. Confirm that the correct mirror partner name is specified in the ALTER DATABASE command. You can check the mirror partner name in the Sys.database_mirroring catalog view of the primary server and mirror server (and sys.database_mirroring_witnesses witnesses in high availability mode).

Exclude Run-time errors

If the database mirroring settings are correct, and then an error occurs during the run, check the current state of the session. If the mirror is in suspended state due to an error, a redo error may be generated on the mirror server. Check to make sure that there is enough disk space on the mirror server for redo (the remaining space on the partition where the data file resides) and log hardening (the remaining space in the partition where the log file resides). When you are ready to restart the session, use ALTER DATABASE to start the session again.

If you cannot connect to the primary database, the most likely cause is that safety is set to full and the primary server cannot compose quorum. This can happen, for example, if the system is running in high protection mode (safety full but no witness), the mirror server is disconnected from the old master server. You can use the following command on the mirror server to force the mirroring server to recover:

ALTER DATABASE [AdventureWorks] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The problem is that once the mirrored database is restored, the mirror server becomes the primary server but cannot form a quorum and therefore cannot serve the database. In that case, simply set the safety to off to allow it to serve the database.

Security and performance

The performance of database mirroring is a function of the activity type and transaction security.

Transferring logs to the mirror server can affect primary server performance. The overhead that database mirroring brings to the primary server is a function of the activity type. Database mirroring operates best on multiple users and on systems with a large number of long transactions, because the normal transaction activity of the database server obscures the overhead incurred by the transmission log logging to the mirror server. If a single user performs a large number of short transactions sequentially, the overhead of database mirroring is significant for each transaction.

Primary server performance is also affected by safety settings. When safety is set to full, the primary server must wait for the mirror server to indicate that it has received confirmation of the log records before it can return the transaction commit message to the client. If there are a large number of users and a large number of long transactions, then this wait caused by the overhead is not obvious. Single-threaded systems and systems that contain many small transactions can run better when safety off.

Because the mirror server continuously restarts data update transactions received from the primary server, the data cache of the mirror server becomes ' hot '. In other words, the cache is processed using the data pages and index pages that are involved in the same data update operations as the primary server type. In order for the mirror server's cache to be closer to the primary server cache, database mirroring passes a select hint to the mirror server, allowing the cached content for the data query to be regenerated on the mirror server. This helps the mirror server to be closer to the primary server while reducing the remaining redo time when failover occurs. It is clear that any other activity on the mirror server, including queries against database snapshots, also affects the state of the cache and therefore increases the time it redo to complete the log when the failover occurs.

Testing Database Mirroring

When you set up your own system to test database mirroring, there are a number of options available. All database mirroring requires that the server in a database mirroring session be a different instance of SQL Server. Therefore, you can configure and test database mirroring on a single physical server if you have more than one SQL Server 2005 relational database engine installed. You can also test multiple instances on a single virtual server, but testing on a physical server is more believable.

Different physical servers are required for load and stress testing of database mirroring. Two or three instances on a single server may consume unrealistic server resources. In addition, the quality of network connectivity between servers is equally important. The better the network between the primary server and the mirror server, the faster the logging and messaging will be delivered.

The most realistic test is done on a real target server or test bed, and the physical properties of the final system are exactly the same. When you test multiple instances on a single server, you can only simulate the effect of server failure in database mirroring by stopping the instance or shutting down the computer. When you use multiple physical servers, you can test the effect of network connection failures by disconnecting the cable.

The following practices can help you create a test environment:

To test the server for failure, close the SQL Server instance, either through SQL Configuration Manager or by using SHUTDOWN with NoWait.

To test the communication failure, unplug the network cable from the server.

To test the database failure, stop the SQL Server service and rename it. MDF file, and then restart SQL SERVER.

To cause a redo error in the mirrored database, add a new file to the primary database and store the file in a partition that exists on the primary server but does not exist on the mirror server.

Another way to cause a mirror server redo error is to force the mirror server to have insufficient database file space.

To force the primary server to shut down the database, force the primary database to have insufficient data file space.

Forced log file space is insufficient to cause the primary or mirror database log buffer hardening to fail.

Preparing a mirror server for failover

Database mirroring is actually the connection of database to database. Only the data in the database is sent from the primary database to the mirrored database through logging. As with log shipping and replication, you must prepare a standby server and a mirrored database so that you can take complete control of the primary database when a failure occurs. When you are ready to mirror the server, you should consider it from the following layers.

At the physical server level, make sure that the standby server and primary server have the same or as close as possible physical CPU and memory configuration, otherwise the standby server will not be able to work after failover. There may also be a number of executable files that support applications, monitors, and support programs running, and so on, all of which need to be configured on the mirror server.

At the SQL Server level, make sure that the standby server and the primary server have the same SQL Server configuration (for example, AWE, maximum parallelization). But the most important thing is to login account and account permissions. All active SQL Server login accounts on the primary server must also exist on the mirror server, otherwise the application will not be able to connect to the new primary server using these login accounts if a failover occurs. You can use the SQL Server Integration Service's Transfer Logins task to copy login accounts and passwords from one server to another, but you must also set database permissions for these login accounts. If you transfer your login account to a different domain, you may receive a SID that does not match the color, and you need to match them.

There may also be a large number of support objects on the SQL Server master server that need to be transferred to the standby server: SQL Agent jobs and Alerts, SQL Server Integration service packs, support databases, definition of connection servers, backup devices, maintenance plans, SQL mail or database settings, There may also be Distributed Transaction Coordinator (MSDTC) settings, and so on.

Most of the SQL Agent jobs are forced to be disabled when they are transferred to the standby server. Once a failover occurs, you need to enable these jobs.

After a failover, if your application uses SQL Server authentication, you also need to resolve the login account on the new primary server of SQL Server to the database user on the new primary server. The best tool to complete this task is the stored procedure sp_change_users_login.

Problems with multiple databases

Many applications use multiple databases on a single server. Multiple databases may be referenced by one application or by multiple applications. However, database mirroring works only on one database at a time. You need to consider this when you design database mirroring.

If you want a high availability model, the best fit is an application that works with a database. When an automatic failover occurs, the application no longer requires any databases on the primary server. Consider what might happen if multiple databases are on a single server and operate in high availability mode. If a physical server loses power, or if one instance of SQL Server fails, or if network traffic fails, all databases will automatically fail over to the standby server, and their mirrors will become the new primary database. If the witness server is available, the application can connect to the new primary database. But what happens if a database has a paging due to a disk failure, so only the database is failed to transfer? In that case, the application might not be able to connect to all the correct databases.

Therefore, applications that rely on multiple databases are not suitable for use in highly available patterns of database mirroring. You can set safety to off, in effect not to use automatic failover, but you must use some efficient way to maintain synchronization with other database servers.

Database mirroring and high availability technologies

SQL SERVER 2005 now supports at least four high-availability technologies, although there are some overlapping features between different technologies, but each has its own pros and cons. These technologies are:

Database mirroring – For the sake of discussion, we will consider highly available operating modes as well as full safety and witness servers.

Failover clustering – The most typical configuration is a 2-node Windows Failover cluster configuration with one instance of SQL Server.

Log shipping – Use SQL Server's built-in log shipping and a separate monitoring server.

Transactional replication – A Distributor and a subscriber, if the publisher fails, the Subscriber acts as a standby server.

In this section we will compare the basic functions of these four technologies and then delve into how to supplement or provide a better solution for database mirroring.

The following table shows several high-availability features for four of technologies.

Table 14: Comparing SQL SERVER 2005 High-availability Technologies

Category	Usability features	Database Mirroring (HA mode)	Failover clusters	Log shipping	Transactional replication
Failover characteristics	Standby server Type	Hot	Hot	Warm	Hot
Automatic role Conversion	Is	Is	Need to write your own code	Need to write your own code
Failover retention of committed work	Is	Is	Whether	Whether
Failover type	Automatic and manual	Automatic and manual
Failover Process Database shutdown time	Less than 10 seconds	30 sec + Database restore	of variable	of variable
Physical configuration	Redundant storage Locations	Is	No (Shared disk)	Is	Is
Hardware requirements	Standard Server	Cluster-authenticated servers and storage	Standard Server	Standard Server
Physical distance Limit	No	100 meters	No	No
Other server Roles	Witness server	No	Monitoring servers	Distribution server
Management	Level of complexity	Low	High	Low	Medium
Accessibility of standby servers	Performance may be affected by a database snapshot	Not accessible	R/O but incompatible with database restore	Allow read-only work
Multiple standby servers	No	No	Is	Is
Standby server Load Delay	No	No	There are delays	No
Availability Range	Database	Server instance	Database	Database
Customer Access	Customer redirection	by ADO. NET and SQL Native client Support	No need, use virtual IP	Need to write your own code	Need to write your own code

The above table summarizes the characteristics of all four highly available technologies. A more detailed comparison is made in the next section.

Database Mirroring and Clustering

The main difference between database mirroring and failover clustering is that it provides different levels of redundancy. Database mirroring provides protection at the database level, while the protection provided by the cluster is at the server instance level. Another major difference is that in database mirroring, the primary server and mirror server are separate instances of SQL Server, two instances have different names, and SQL Server instances in the cluster use the same virtual server name and IP address, and no matter which node hosts the cluster instance, The virtual server name and IP address remain unchanged.

If you need database protection at the server level (for example, if your application needs to access multiple databases on the consolidated server at the same time), failover clustering will be a more appropriate choice. However, database mirroring has more advantages if you only need to provide availability for one database at a time.

Database mirroring does not require specialized hardware like clustering, and there is no potential risk of failed shared storage media. Database mirroring allows the standby database to start providing services in the shortest possible time, faster than any other highly available technology. In addition, database mirroring can be associated with ADO. NET and SQL Native Access client are well coordinated to enable failover of clients.

Database mirroring cannot be used in a cluster, but you can consider using database mirroring as a means to create a hot standby for a clustered database instance. This requires special care because the failover time of the cluster is longer than the timeout value for the database mirroring, and the mirroring session in the high available mode responds to the failover of the cluster, which is considered a primary server failure and then sets the cluster node to mirror state.

Database mirroring and transactional replication

Both database mirroring and transactional replication are based on reading the original server transaction log, but the technology used thereafter is quite different. (For more details on transactional replication, see Related Topics in SQL SERVER Books Online.) Transactional replication is used to configure high availability because it can deliver user transactions from the publishing database to the subscription database in seconds. The advantage of database mirroring is that speed is faster for replication, but all database transactions need to be delivered, not just transactions related to user tables.

Transactional replication technology is suitable for extending data to multiple subscribers. The subscription database for transactional replication is usually read-only, and transactional replication is an ideal candidate if near-real-time data access is required.

Database mirroring is compatible with transactional replication, and database mirroring is a useful way to maintain a hot backup of the publishing database. Other ways to protect replication publishers, such as log shipping cannot maintain the publisher's standby server before the publisher's own subscriber. In other words, transactional replication delivers transaction logs to subscribers faster than transaction log backups. Because database mirroring is fast, it is particularly useful for maintaining hot backups of the publishing database.

However, if the publisher fails, you must manually re-establish the Publisher by using the restored standby database, and then reconnect to the Distributor, which must do the same work as the standby server that maintains the publisher server with log transport.

Database mirroring and log transfer

Both database mirroring and log transport depend on the restore and restore capabilities provided by the SQL Server database. In database mirroring, the mirrored database remains recovering and continuously restores the transaction log of the autonomous database. Instead, the standby database in log transport applies transactions in transaction log backups periodically. Because bulk-logged data is attached to a transaction log backup, log shipping can work under the bulk-logged recovery model. Database mirroring is different, and it transfers log records directly from the primary database to the mirrored database and does not deliver bulk-logged data.

In many cases, database mirroring can provide the same data redundancy as log shipping, as well as higher availability and automatic failover. However, if your application relies on multiple databases on a single server, log shipping is a valid way (see "Multi-database considerations" described in the previous section).

In addition, some database mirroring scenarios can be supplemented by log shipping. For example, you can configure a highly available database image somewhere, and then transfer the primary database log to a remote site to provide disaster recovery. Figure 24 shows how this configuration can be done.

Figure 24: Shipping The primary database log to a remote server

The advantage of this approach is that once the entire site fails, the data on the second site continues to be available. However, if database mirroring fails, log shipping from server B to a remote standby server usually has to start again.

Other scenarios that use log shipping to supplement database mirroring are local standby for the primary server, and the primary server's database mirroring session is used for disaster recovery. At this point, the database mirroring session runs in High-performance operating mode, and the remote site's mirror server acts as a remote standby server.

Figure 25: Preserving all transactions through primary database log shipping

Data loss in High-performance mode if the primary server fails and the mirror server is restored using the forced recovery service. If you are logging the old primary server, and if the old primary server transaction log file is not corrupted, you can make a "end log" backup of the primary database to obtain the last set of records in the transaction log. If all other transaction log backups have been applied to the standby log transfer database, you can apply the end log backup to the standby server so that no data on the old primary server is lost. You can then compare the data in the log shipping standby server with the remote database and copy the lost data to the remote server if needed.

When you compare log transfer and database mirroring features, it is important to make database and log backups of the primary database. The log shipping server to which these log backups are applied can complement the database mirroring configuration.

Conclusion

Database mirroring is a new technology in SQL SERVER 2005 that enables high availability and High-performance database redundancy solutions. In database mirroring, when the transaction log buffers of the primary database are written to disk (hardened), transaction log records are sent directly from the primary database to the mirror database. This technique allows the mirrored database to be almost synchronized with the data in the primary database without losing the submitted data. In the availability operating mode, if the primary server fails, the mirror server automatically becomes the new primary server and restores the database. Use the new ADO. NET or SQL Native Access client driver, an application can also automate failover from its own server. Database mirroring becomes a new member of the highly available technology family provided by SQL SERVER 2005.