Database Mirroring Overview Rajib Kundu Definition A software

Database Mirroring Overview Rajib Kundu

Definition • A software solution for increasing the probability that a • • • database is available. An easy-to-manage alternative or supplement to failover clustering or log shipping. Involves two copies of a single database that typically reside on different computers. Only one copy of the database is currently available to clients known as the principal database Updates made by clients to the principal database are mirrored on the other copy of the database, known as the mirror database For automatic failover a 3 rd server is installed called the witness

Possible configurations • Without witness With witness

Database mirroring sessions • As soon as mirroring starts, each partner begins • • • to maintain state information in its database about that database and about the other partner and the witness, if any This state information permits the server instances to maintain an ongoing relationship known as a database mirroring session. Throughout a database mirroring session, the server instances monitor each other by regularly exchanging PING messages with each other The state information is maintained until the database owner stops the session

Concurrent sessions • A given server instance may participate in multiple, concurrent database mirroring sessions (one per mirrored database) as either a partner or a witness server instance. • Every session (and its database) is independent of the other sessions.

Operating Modes • Synchronous: If transaction safety is set to FULL, operation is synchronous. Ensures that a committed transaction is committed on both partners, but at the risk of increased transaction latency. – High availability: If a witness is present – High protection: If a witness is absent • Asynchronous: If transaction safety is set to OFF, operation is asynchronous. The transactions commit without waiting for the mirror server to harden the log, which minimizes transaction latency. This is also called high performance mode

Database states Database state Description SYNCHRONIZING The contents of the mirror database are lagging behind the contents of the principal database SYNCHRONIZED The mirror database contains the same data as the principal database if the principal database is shipping the active log. SUSPENDED The mirror copy of the database is not available PENDING_FAILOVER This state is found only on the principal server when a manual failover has been issued but not yet accepted by the mirror DISCONNECTED The partner has lost communication with the other partner and the witness, if one exists.

Role Switching • Within the context of a database mirroring session, the • • • principal and mirror roles are typically interchangeable in a process known as role switching. In role switching, the mirror server acts as the failover partner for the principal server, taking over the principal role and bringing its copy of the database online as the new principal database The former principal server, when available, assumes the mirror role, and its database becomes the new mirror database Potentially, the roles can switch back and forth repeatedly.

Synchronous Mode: Quorum in DB Mirroring • A relationship that exists among server instances that are currently in communication with each other (in the CONNECTED state).

Synchronous mode: The role of the witness

Synchronous mode: Failover types • Automatic: Witness detects this and issues a failover • Manual: The database administrator can use manual failover for upgrading hardware or software without sacrificing high availability – ALTER DATABASE <database_name> SET PARTNER FAILOVER

High performance mode (Asynchronous) • When the principal fails, the database owner has several choices, as follows: – Leave the database unavailable until the principal becomes available again. If the principal database and its transaction log are intact, this choice preserves all of the committed transactions at the expense of availability. – Stop the database mirroring session, manually update the database, and then begin a new database mirroring session. • Force service (with possible data loss) on the mirror server.

Forced Service (with Possible Data Loss) • Forcing service reassigns the principal role to the mirror • • server, which becomes the principal server and makes the database available. The database runs exposed (that is, unmirrored) until the other partner reconnects and assumes the mirror role Force service only if you must restore service to the database immediately and are willing to risk losing some data. ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS • Available in high protection and high performance modes

Client Connections to a Mirrored Database • To connect to a database mirroring session, a client can • use either SQL Native Client or. NET Data Provider for Microsoft SQL Server as the data-access provider. The data-access provider, whether SQL Native Client or ADO. NET Data Provider, stores both an initial partner name and a failover partner name (when it is available). – In general, to establish a connection for a mirrored database, the data-access provider first tries the initial partner name. – If that connection fails, then the data-access provider tries to connect using the failover partner name, if available. – If that fails, then the initial partner name is retried. The dataaccess provider continues to alternate between the servers until it times out.

Client Connections to a Mirrored Database

Possible Failures During Database Mirroring • Hard errors: A communications failure due to a • network error that a server instance can directly detect is known as a hard error. Soft errors: A failure that a server instance cannot directly detect, such as an unresponsive remote host or router or a remote database going down, is known as a soft error. – Soft Errors That Might Cause Mirroring Timeouts: A slow disk system; a hanging operating system, server, or database state; disk or disk controller errors; memory errors, or an offline or suspect database – Undetectable Soft Errors: Data disk failure

Using Database Snapshots with Database Mirroring • You can take advantage of a mirror database that you • • are maintaining for availability purposes to offload reporting If failover occurs, the database snapshots remain on the new principal database (on the server instance where they were created). Users can continue to use these snapshots during and after the failover When the former principal database is available as the new mirror database, you should create a new database snapshot on the new mirror database and redirect clients to the new snapshot.

Metadata for Database Mirroring • sys. database_mirroring This view displays the database mirroring metadata for each mirrored database in a server instance. • sys. database_mirroring_endpoints The sys. database_mirroring_endpoints catalog view displays information about the database mirroring endpoint of the server instance. • sys. database_mirroring_witnesses This catalog view displays the database mirroring metadata for each session in which a server instance is the witness. • sys. dm_db_mirroring_connections • This a dynamic management view returns a row for each database mirroring network connection. There also peformance counters that can be used • Also Notifications and events for database mirroring sessions are available in the SQL Server Profiler. .