8. Database Mirroring

Topics In This Module.

• Working with Database Mirroring 
• Terminology 
• Pre-requisites to implement Mirroring 
• Pre check mirroring ports ENABLED or DISABLED 
• Architecture 
• Operating Modes 
• Differences between Modes 
• Port numbers involved in Mirroring 
• Requirements for Mirroring 
• Configuring Mirroring 
• Mirroring failover 
• Issues on Mirroring

 

 ***============================================================================***

Mirroring

What is Mirroring?

Mirroring is a technique where each and all individual transactions from buffer are 
transferred from one database (Principal) to another database (Mirror), Through end 
points.

In this technique whenever a DML is executed, according to WAL concepts the 
transaction need to written to buffer as well as log file, from the primary server buffer the 
transactions are transferred to other server buffer and log file only then the transaction 
shows successful execution.

This says that the communication is happing through end points, hence we call it as an 
End points Communication, default port for mirroring is 5022.

There are two types of Endpoints they are:

1. TCP
2. Https

Mirroring Configuration 

Check List: 

1. Three Servers 
1. Primary Server/Instance 
2. Mirror Server/Instance. 
3. Witness Server/Instance (Optional for automatic failover) 
2. Recovery Model: Should be Full 
3. Recovery State: Should be No-Recovery i.e., Restoring mode. 
4. Sysadmin privileges for user who is configuring mirroring. 
5. Database Name, Instance Versions, compatibility, collation of database/instance must be same.
6. SQL Server Instance Editions 
1. Asynchronous mirroring: Developer and Enterprise editions. 
2. Synchronous mirroring: Developer, Enterprise, standard. 
7. Needed to restore a full and tlog backup taken from primary in no-recovery mode i.e., in restoring mode and only then we can configure mirroring. 
8. SQL Server version greater than or equal to SQL 2005 RTM:

Note: It’s not recommended to configure mirroring on SQL Server 2005 RTM version, if needed to configure enable trace flag on all three instances, trace flag number: 1400, and then configure SQL Server 2005 RTM version, but still there is no SQL server monitor tool on SQL 2005 RTM version, so it’s recommended to use SQL server 2005 SP1 or greater to configure mirroring. 

Witness can be configured on any version or edition of SQL server irrespective of version or edition of primary and mirror server/instance.

Mirroring cannot be configured on system databases

Mirroring Monitoring

Mirroring can be monitored using two ways they are: 

1. Using Mirroring Monitor tool. 
1. Right click on database -> reroute to tasks -> click on “Launch Database Mirroring Monitor” 
2. Using Stored procedures in MSDB database.

Points about Mirroring: 

• Mirroring is introduced in SQL server 2005. 
• This is a database level HA/DR technique and requires at least 2 servers/database to configure. 
• In this configuration one can maintain only two copies of data i.e., an original data and a copy data, this means the configuration can be done only between two databases. 
• We can also use 2 or 3 servers based on requirement the server used in this configuration are called as below with its properties: 
• Principal Server – Maintains original data 
• Mirror Server – Maintains a copy of original data 
• Witness Server – Monitors the mirroring status between the mirroring pair. 
• If 2 servers are used in the configuration then it is a DR – Disaster Recovery. 
• If 3 servers are used in the configuration then it is a HA – High Availability. 
• This technique works only when database is in FULL recovery model, and in restoring mode in mirror server and also the database name should be same in both principal and mirror servers.

Mirror Pair: Principle and Mirror server operating together that hence both are called mirror pair. 
Operating together: Transactions happening on principle are reflected to mirror using endpoints this means operating together. 

Quorum: The Relationship between principal database, mirror database and witness database are called quorum. 

Endpoints: Mirroring is an endpoint-based communication; log records are copied from principal buffer to mirror buffer 

Logical service point/name associated with a port number is an end point, we can use two types of end points they are TCP end points, HTTP end points 

5022 is a default endpoint port for mirroring; this comes with Build-in Security encrypted data will be copied automatically through this end points. 

• How to create or alter end points?
• How to use end points?
• How to check end points are working or not? 
• How to check port number, end points, status of already configured mirroring? 

Full Qualified Domain name: 

<endpoint type>://<server name>.<domain name> :<port> 

Example: - TCP://desktopvm.igc.com:5022

Important Points: 

• Transactional safety types are Full or Half. 
• There are three types of failovers in mirroring they are: 

1. Automatic Failover 
2. Manual Failover 
3. Forcible Failover 

• Mirroring can be configured in two types/modes they are: 

1. Synchronous Mirroring 
2. Asynchronous Mirroring 

Synchronous Mirroring 

            In Synchronous mirroring, if any operations are applied on principal database records/transactions/pages are created on principal server buffer then they are copied to mirror server buffer and then the transactions are applied/commit on mirror database then informs principal database only then the principal database commits the transactions and informs the user about the completed successfully. 

Using this it can be said as performance will be a slow but the data security is high

Low Performance but high reliability 

We can configure Synchronous mirroring with 2 or 3 servers, if 2 servers are used it is manual failover, if 3 servers are used it is automatic failure.

Data will be in sync all time, High Safety Mode 

Asynchronous Mirroring:

            In Asynchronous mirroring, if any operations are applied or principal database record/transactions/pages are created and commit here, it does not care about the data is copied to mirror database or not. But mirroring will be InProgress. 

Using this it can be said as high performance but no the data reliability or security 

High Performance but No data reliability

• This is a DR technique, 2 servers are used to configure, only forcible failover. 
• Data will not be in sync, High Performance Mode.

There are three types of operating modes in mirroring they are: 

1. High Availability Mode 
2. High Protection Mode 
3. High Performance Mode 

High Availability Mode 

            This mode is also called as is High safety Automatic failover, which is synchronous mirroring with automatic failover; 

            This configuration requires 3 servers; this configuration allows you to write transactions on both the databases hence there will be a bit low performance if the distance between principal database and mirror database is high; there will be continuous ping process between the 3 servers to maintain the quorum and to perform automatic failover. 

High Protection Mode 

            This mode is also called as High Safety Manual failover, which is synchronous mirroring with manual failover. 

            This configuration requires only 2 servers; this configuration allows you to write transactions on both the databases hence there will be a bit low performance if the distance between principal databases and mirror database is high; There will be no ping process hence we need to perform manual failover. 

High Performance Mode 

            This mode is also called as High-performance forcible failover, which is Asynchronous mirroring with forcible failover. 

            Primary database does not wait for the commit or copy happening in mirror server which gives a bit high performance when compared to above configurations, we can use 2 or 3 servers in this configuration but witness server will not be able to perform the failover, hence no meaning on using the 3rd server; There might be any data loss when doing forcible failover. 

=> Below are the database states in mirroring configuration they are: 

1. Synchronized state: When both the databases are in sync then the database will be in this state 
2. Synchronizing state: When data is syncing between both the databases, the databases will be in this state 
3. Suspended state: When mirroring is paused or data/transactions are not coming from principal then we see databases in this state.
4. Pending failover state: When the databases are failover from principal to mirror but still the users are connected to principal then database goes into this state. 
5. Disconnected state: communication is not happening or lost between principal and Mirror servers.

Creating Snapshot of a database: 

Checklists and requirements: 

1. Snapshot database should have same number of data files that the source database has and also should have size on disk 
2. Database should not be corrupted and should be online. 
3. Source database (the database on which snap is created) cannot be deleted when snap is present on that database 
4. We cannot perform database backup when snap is present. 
5. Snap database is read only database. 
6. We can read the mirror database only when snapshot database is present. 
7. Database shot is present only in Enterprise and developer editions 

Use below commands to create a snapshot database: 

CREATE DATABASE <Snap Database Name> ON (NAME = ‘<snap db logical File name>’, FILENAME = 'F:\SQL2012ENT5\Data\admin.snp') AS SNAPSHOT OF <mirror database name on which snap need to be taken>  

Example: 

 CREATE DATABASE [adminsnap1] ON (NAME = 'admin11', FILENAME = 'F:\SQL2012ENT5\Data\admin1112.ss'), (NAME= 'admin22', filename = 'F:\SQL2012ENT5\Data\admin2212.ss') AS SNAPSHOT OF admin

Remember Points (Note): 

➔ Individual transactions are copied form principal database to mirror database using network end points. 

➔ Once the transaction copied it is written on mirror database and once it is done in mirror database it acknowledges the transaction write. 

➔ Only then the transaction is committed on principal. Which is a time taking process when compared to other techniques. 

➔ Only two copies of data can be maintained in mirroring, main copy and mirror copy. 

➔ Heartbeat is the timeout time between the databases principal and mirror 

We cannot use load balancing here, as mirror database is in restoring state, but we can take the snap shot of database and use it for read database and redirect queries to mirror database.

Redirection Logic: ????

Note: 

“The time taken by a transaction to get commit is more in mirroring when compared to log shipping, because in mirroring the principal database waits for the acknowledgement from mirror server, but in log shipping the transaction is directly committed in primary database.” 

Case Study Points: 

When principal database/servers fail? 

• High Availability mode: Failover happens automatically 
• High Protection mode: We need to perform manual failover, from mirror server 
• High Performance mode: We need to perform forcible failover with data loss, form mirror server

When Mirror database/servers fail? 

• High Availability mode: Operations/transactions cannot be performed 
• High Protection mode: Operations/transactions cannot be performed 
• High Performance mode: No effect, sync will be suspend/disconnect state As operations are not performed break the mirroring configuration after sync, for quick resolutions 

When witness database/servers fail? 

• High Availability mode: Automatic failover does not happen, if any issues 
• High Protection mode: No Witness server here 
• High Performance mode: No Witness server here 

When witness and mirror database/servers fail? 

• High Availability mode: Operations/transactions cannot be performed. 
• High Protection mode: Operations/transactions cannot be performed. 
• High Performance mode: No effect, sync will be suspend/disconnect state

As operations are not performed break the mirroring configuration after sync, for quick resolutions 

When witness and principal database/servers fail? 

• High Availability mode: Operations/transactions cannot be performed, to resolve this perform manual failover and break mirroring. 
• High Protection mode: Operations/transactions cannot be performed, to resolve this perform manual failover and break mirroring.
• High Performance mode: Operations/transactions cannot be performed, to resolve this forcible failover with data loss. 

When Principal and mirror database/servers fail? 

• High Availability mode: Operations/transactions cannot be performed
• High Protection mode: Operations/transactions cannot be performed 
• High Performance mode: Operations/transactions cannot be performed 

When OS is up and running but SQL server services are down does the witness server do failover? 

Mirroring commands: 

=>Run below on principal databases 

#To disconnect the mirroring use the below

alter database <Database Name>  set partner safety off

#To change the database mode from Async to sync

alter database <Database Name> set partner safety full

#To change the database mode from sync to Async

alter database <Database Name> set partner off

#To pause the mirroring between database use below

alter database <Database Name> set partner suspend

#To Resume the mirroring between databases, use below

alter database <Database Name> set partner resume

#To failover the databases from principal to mirror use below

alter database <Database Name> set partner failover

=>Run below on Mirror databases 

#To pause the mirroring between database use below 

alter database <Database Name> set partner suspend 

#To Resume the mirroring between databases, use below 

alter database <Database Name> set partner resume 

#To failover the databases from principal to mirror use below – manual failover 

alter database <Database Name> set partner failover #only when principal is down 

#To failover the databases from principal to mirror use below – forcible failover 

alter database <Database Name> set partner FORCE_SERVICE_ALLOW_DATA_LOSS 

#To switch the mirroring off use below 

alter database <Database Name> set witness off 

#When the database is in restoring to bring it online use below 

restore database <Database Name> with recovery 

Some other case studies same as log shipping?