Mirroring is introduced in 2005.Mirroring in one of the disaster recovery solution. Mirroring is an option to improve the availability of a databases which supports automatic failover with no loss of data.
Advantages :
* Protection against database failures.
* Automatic failure detection and failover.
* Automatic client redirection.
* Multiple operating modes.
* Improves the availability of the production database during upgrades.
Prerequisites :
* SQ Server 2005 with SP1 or above versions.
* Database recovery model should be FULL.
* Service Broker should be enabled on the databases.
* Both principle and Mirror servers should have same case sensitivity settings i.e either Enterprise or Standard editions.
* Witness server can have any edition i.e Enterprise / Express / Standard / Work Group .
* For Principal , Mirror and Witness servers TCP / IP should be enabled.
* All logins for connecting principal database should be reside on Mirror database.
* Like Log Shipping here also 3 servers are required.
Limitations :
* Maximum 10 databases per instance can support on a 32-bit system.
* Database Mirroring is not supported with either cross-database transactions or distributed transactions.
Principal Server :
One server serves the database to client is called Principal Server and is having original data. We can have only one Principal Server and it has to be on a separate server . Principal database recovery model should be FULL.
Mirror Server :
Mirror Server maintains copy of the principal database and database called as Mirror database and recovery model must be FULL and database state is WITH NO RECOVERY (Stand By).
Witness Server :
Mirror Server is an optional server used for automatic failover to the Mirror Server if the Principal becomes unavailable .To support automatic failover witness server is mandatory.
Operations running in Operating Modes :
Synchronous :
In Synchronous operation , The principal server sends the log buffer to the mirror server and then waits for a response from the mirror server.
It supports High Availability & High Protection Modes.
Asynchronous :
In Asynchronous operation , The Principal server does not wait for a response from the mirror server after sending the log buffer.
Operating Modes :
High Availability :
High availability mode runs synchronously i.e Prinicpal server sends the log buffer to the mirror server and then waits for a response from the mirror server. High availability requires a witness server instance so that it supports automatic failover.
High Protection :
High protection mode runs synchronously . In high protection witness server is not required i.e no automatic fail over support. Always commit changes at both the principal and mirror.
High Performance :
High performance runs asynchronously i.e the prinicpal server doesn't wait for a response from the mirror server after sending the log buffer. In high performance also witness server is not required so no automatic fail over support.
Note:
Witness server is not required in high protection and high performance mode so automatic failover is not applicable for high protection and high performance modes.
Operating Modes in 2012 :
High Performance (Asynchronous ) : Commit the changes at the principle and transfer them to the mirror.
High Safety with out automatic fail over (Synchronous ) : Always commit the changes at both the principal and mirror.
High safety with automatic failover (Synchronous ) : Requires a witness server instance. Commit changes at both principal and mirror if both are available. The witness server controls automatic failover to the mirror if the principal becomes unavailable.
Terminologies in Mirroring :
Log Buffer : A log buffer is a special location in memory (RAM) . SQL Server stores the changes in the database log buffer.
Hardening : As quick as possible the log buffer is written to the transaction log on disk . This process is called as Hardening.
End Point : An endpoint is a network protocol which is used to communicate Prinicpal , Mirror and Witness servers over the network.
Creation of Endpoint :
Create endpoint <endpoint name> State= Started / Stopped / Disabled. as tcp (listner_port=5022/5023) for database_mirroring (role=partner/witness)
The default port numbers of principal , mirror and witness servers are 5022,5023 and 5024.
select name,port from sys.tcp_endpoints
Trace flags used in Mirroring :
Trace flags are used to temporary set specific server characteristics or to switch off or switch on a particular behavior .1400 trace flag is used in mirroring.
Role-Switching : Inter changing of roles like principal and mirror are called role switching.
How to failover the mirror database manually ?
Alter database <database name> set partner failover (do on principal server)
(or)
SSMS --> Click on principal database --> Task --> Mirror --> Fialover
How to stop the database mirroring ?
Alter database <database name> set partner off
SSMS --> Click on principal database --> Task --> Mirror --> Remove Mirroring.
How to set a witness server to database mirroring >
Right click on Principal database --> Tasks --> Mirror --> Click on Configure Security --> Provide the Endpoint for Witness Server --> Click OK
(or)
Alter database <Database Name > Set WITNESS= 'TCP://sai.local:5024' (Do this from the Principal Server)
How to remove a witness server from Database Mirroring ?
Right click on Prinicpal Database --> Tasks --> Mirror --> Remove TCP address from the witness --> Click OK
(or)
Alter database <database name> set WITNESS OFF
How to set up fully qualified names for Database Mirroring ?
1. FQDN Error :
One or more of the server network addresses lacks a fully qualified domain name specify the FQDN for each server and click start mirroring again.
Syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
2. Rectifying fully qualified names :
* To view end points : select * from sys.database_mirroring_endpoints;
* Remove existing all endpoints from Principal , Mirror and Witness Serves :
Drop endpoint <end point name>
* Adding 'Local' as the primary DNS suffix as follows :
* Right-Click on My Computer and then click properties. The system properties dialogue box will appear.
* Click on computer name tab
* Click change. The computer name changes dialog box will appear.
* Click more. The DNS suffix and NetBIOS computer name dialog box will appear.
* Enter the appropriate DNS suffix for the domain.
* Enable "Change primary DNS suffix when domain membership changes" check box.
* Click OK to save the changes and then click OK to exit the Computer Name Changes dialog box.
* Click OK to close the System Properties dialog box and then restart the computer for the change to take effect.
Reconfigure the Database mirroring either GUI or T-SQL.
Advantages :
* Protection against database failures.
* Automatic failure detection and failover.
* Automatic client redirection.
* Multiple operating modes.
* Improves the availability of the production database during upgrades.
Prerequisites :
* SQ Server 2005 with SP1 or above versions.
* Database recovery model should be FULL.
* Service Broker should be enabled on the databases.
* Both principle and Mirror servers should have same case sensitivity settings i.e either Enterprise or Standard editions.
* Witness server can have any edition i.e Enterprise / Express / Standard / Work Group .
* For Principal , Mirror and Witness servers TCP / IP should be enabled.
* All logins for connecting principal database should be reside on Mirror database.
* Like Log Shipping here also 3 servers are required.
Limitations :
* Maximum 10 databases per instance can support on a 32-bit system.
* Database Mirroring is not supported with either cross-database transactions or distributed transactions.
Principal Server :
One server serves the database to client is called Principal Server and is having original data. We can have only one Principal Server and it has to be on a separate server . Principal database recovery model should be FULL.
Mirror Server :
Mirror Server maintains copy of the principal database and database called as Mirror database and recovery model must be FULL and database state is WITH NO RECOVERY (Stand By).
Witness Server :
Mirror Server is an optional server used for automatic failover to the Mirror Server if the Principal becomes unavailable .To support automatic failover witness server is mandatory.
Operations running in Operating Modes :
Synchronous :
In Synchronous operation , The principal server sends the log buffer to the mirror server and then waits for a response from the mirror server.
It supports High Availability & High Protection Modes.
Asynchronous :
In Asynchronous operation , The Principal server does not wait for a response from the mirror server after sending the log buffer.
Operating Modes :
High Availability :
High availability mode runs synchronously i.e Prinicpal server sends the log buffer to the mirror server and then waits for a response from the mirror server. High availability requires a witness server instance so that it supports automatic failover.
High Protection :
High protection mode runs synchronously . In high protection witness server is not required i.e no automatic fail over support. Always commit changes at both the principal and mirror.
High Performance :
High performance runs asynchronously i.e the prinicpal server doesn't wait for a response from the mirror server after sending the log buffer. In high performance also witness server is not required so no automatic fail over support.
Note:
Witness server is not required in high protection and high performance mode so automatic failover is not applicable for high protection and high performance modes.
Operating Modes in 2012 :
High Performance (Asynchronous ) : Commit the changes at the principle and transfer them to the mirror.
High Safety with out automatic fail over (Synchronous ) : Always commit the changes at both the principal and mirror.
High safety with automatic failover (Synchronous ) : Requires a witness server instance. Commit changes at both principal and mirror if both are available. The witness server controls automatic failover to the mirror if the principal becomes unavailable.
Terminologies in Mirroring :
Log Buffer : A log buffer is a special location in memory (RAM) . SQL Server stores the changes in the database log buffer.
Hardening : As quick as possible the log buffer is written to the transaction log on disk . This process is called as Hardening.
End Point : An endpoint is a network protocol which is used to communicate Prinicpal , Mirror and Witness servers over the network.
Creation of Endpoint :
Create endpoint <endpoint name> State= Started / Stopped / Disabled. as tcp (listner_port=5022/5023) for database_mirroring (role=partner/witness)
The default port numbers of principal , mirror and witness servers are 5022,5023 and 5024.
select name,port from sys.tcp_endpoints
Trace flags used in Mirroring :
Trace flags are used to temporary set specific server characteristics or to switch off or switch on a particular behavior .1400 trace flag is used in mirroring.
Role-Switching : Inter changing of roles like principal and mirror are called role switching.
How to failover the mirror database manually ?
Alter database <database name> set partner failover (do on principal server)
(or)
SSMS --> Click on principal database --> Task --> Mirror --> Fialover
How to stop the database mirroring ?
Alter database <database name> set partner off
SSMS --> Click on principal database --> Task --> Mirror --> Remove Mirroring.
How to set a witness server to database mirroring >
Right click on Principal database --> Tasks --> Mirror --> Click on Configure Security --> Provide the Endpoint for Witness Server --> Click OK
(or)
Alter database <Database Name > Set WITNESS= 'TCP://sai.local:5024' (Do this from the Principal Server)
How to remove a witness server from Database Mirroring ?
Right click on Prinicpal Database --> Tasks --> Mirror --> Remove TCP address from the witness --> Click OK
(or)
Alter database <database name> set WITNESS OFF
How to set up fully qualified names for Database Mirroring ?
1. FQDN Error :
One or more of the server network addresses lacks a fully qualified domain name specify the FQDN for each server and click start mirroring again.
Syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
2. Rectifying fully qualified names :
* To view end points : select * from sys.database_mirroring_endpoints;
* Remove existing all endpoints from Principal , Mirror and Witness Serves :
Drop endpoint <end point name>
* Adding 'Local' as the primary DNS suffix as follows :
* Right-Click on My Computer and then click properties. The system properties dialogue box will appear.
* Click on computer name tab
* Click change. The computer name changes dialog box will appear.
* Click more. The DNS suffix and NetBIOS computer name dialog box will appear.
* Enter the appropriate DNS suffix for the domain.
* Enable "Change primary DNS suffix when domain membership changes" check box.
* Click OK to save the changes and then click OK to exit the Computer Name Changes dialog box.
* Click OK to close the System Properties dialog box and then restart the computer for the change to take effect.
Reconfigure the Database mirroring either GUI or T-SQL.
No comments:
Post a Comment