In this post
-Introduction
-Database mirroring Terminology
-Database Mirroring Topology
-Facts about database mirroring
-Modes of Database Mirroring
-Mirroring States
-Example Database Mirroring
- Conclusion
Introduction
Database mirroring is a solution for increasing availability of data. Database Mirroring apply availability by at least two sides of server (SQL Server) with power of synchronous or asynchronous methodology techniques. It's let user to select one of two mode for availability (high performance or high safety).modes of availability will effects for how can failover server depending on database mirroring topology.
by the simple words, the idea of database mirroring is the way of coping data from the basic server(Principle
Server) to put it into another server (Mirror Server). if any disaster will occur for the basic server, then the second one can lead the dataflow of clients.
Database mirroring increase data protection by resolving specific kinds of errors that make corruption of reading data page (
read that).
The advantage of the database mirroring is failures are automatically detected and failovers can be made automatic, these are not applicable for log shipping or transactional replication solutions .
Database mirroring can be used with the following in SQL Server.
-Log shipping
-Full-text catalogs
-Database snapshots
-Replication
Database mirroring Terminology
· Principal Server : is the server that host a main database which need to increase high availability.
· Principal Database : database selected that has access by clients.
· Mirror Server : this server maintain a copy of data for principal database into second database.
· Mirror Database : database that is created in the mirror server to save all transaction that occurred in the principal database.
· Witness Server : this server monitor principal and mirror server, it's optional.
· Endpoint : connectivity object that communicate server together across network protocol .
Database Mirroring Topology
The simplest topology can be drawn is two server one of them is principal server and the second named mirror server. each of these servers install SQL Server. the principal server has a database that clients will connect to, and mirror server has a copy of principal server database into another database called mirror database.
Each of these server communicate together by endpoint object that created in both servers. the principal server is only online database that can accessed by clients and mirror server has many states according with conditions that occurs between two servers but no way to read the data in the mirror server except the snapshot database that can be taken from mirror database.
The another topology, is add witness server between principal and mirror server . witness server can monitor activity between other servers and can make automatic failover if any disasters occurs for principal server.
The previous topology can't control disaster by automatic failover, the administrator will do his activity to solve that (manual failover)
Facts about database mirroring
1- Database mirroring supported with full Recovery mode only . Simple and bulk-logged recovery model not supported.
2- All database compatibility level can be working with Database mirroring .
3- Database mirroring need to be same name in both of two sides servers.
4- Can't use Detach with database mirroring .
5- Administrator must check for allowance of Ports that is used in Endpoint objects of database mirroring .
6- Insure that account specified in database mirroring configuration is right.
7- No mirroring with streaming configuration database, it must be disabled from database level.
Modes of Database Mirroring
· Synchronous Mirroring (High Safety)
o Transaction can't commit on principal server until all transaction log records have been successfully copied to the mirror.
o High Safety can be
§ With automatic failover
Require a Witness Server
§ Without automatic failover
No Witness Server Found
· Asynchronous Mirroring (High Performance)
o Commit changes at the principal server and transfer them to mirror server .
Mirroring States
After the configuration of mirroring between two servers and starting the mirror action between them , each server has a specific state which describe the communication state, data flow and also differences of data between servers.
in the simplest topology of database mirroring we can draw data flow between two server which named (principal server and mirror server). each of them have a SQL instance that has a monitor action against each other using mirroring state of each database in them.
if we enlarge our topology, then we can set third server between principal and mirror server, it's name is witness server. in this case each of other servers monitor witness using connection state (connected/disconnected)
the following is the list of mirroring states in SQL Server.
SYNCHRONIZING state
· In this state, there are differences between contents of principal server and mirror server.
· principal server send log records to mirror server and then mirror server send that to mirror DB to apply it.
· starting of DB mirror session the principal DB is in the SYNCHRONIZING state and the mirror DB try to catch up that .
SYNCHRONIZED state
· In the case of SYNCHRONIZING state if mirror DB reach to completely caught up all that is sent from principal DB then the state will change to SYNCHRONIZED state.
· SYNCHRONIZED state mean principal server continues to send changes to mirror server and the mirror server continues applying changes to mirror DB.
· if transaction safety set to full, then automatic and manual failover are supported and no data loss after failover. · if transaction safety set to off, then some data will be loss.
SUSPENDED state
· this state mean mirror DB is not available .
· principal server is running without sending and log records to mirror server.
· this state occur after failover or administrator pauses the session.
· this state will work until mirror server will be start up again.
PENDING_FAILOVER state
· this state found only on the principal server after failover has begun but the server has not transitioned into the mirror role.
DISCONNECTED
· principal server has lost communication with mirrored server.
Example Database Mirroring
Assume that database 'northwind' in server 'Server08' need to be mirrored on server 'Server08\INST2' .
then consider the folwoing facts :
- Principal Server : 'Server08'
- Principal Database : 'northwind'
- Mirror Server : 'Server08\INST2'
- Mirror Database : Must be named 'northwind'
To create Database Mirroring, It's need to create Full backup from Principal Database but Before doing backup action ensure that your database in Recovery Model Full .
Note : Don't conflict between the need of make a Full Backup and Full Recovery model option on database.
The screen below show how to create backup action.
After that, goto the Mirrror Server 'Server08\INST2'. Right click on Databases folder and select Restore Database .
Now, the Mirror Database on Mirror Server will look like this.
Return back to Principal Server, in the Database Properties window of the Principal Database. select Mirroring tab option in the left pane .
Click on Configure Security Button, the following wizand will appear .
This step ask about Witness Server, which is not found in current example therefore select No option.
the Princopal Server instance is selected by default 'server08' because of the example begin the Database Mirroring configuration from this instance.
In this step, the configuration will create Endpoint object for Principal Server with the following information :
- Endpoint Name : Mirroring
- Listener Port : 5022
This endpoint let the Mirror Server to allow access to Princopal Server.
In the Mirror Server Instance step, Click on Connect button to select the second server that wanted to be Mirror Server. This step will create Endpoint object for Mirror Server with name Mirroring and Listener port 5023.
If two server (Principal and Mirror) in the same doamin, specify the service account for each server.
Press Finish button to start the configuration process.
Press Start Mirroring
Because of the Principal and Mirror servers in the same machine (there are two instance in the same windows server), it's not important to write servers nework addresses as a fully qualified name. but if the two server in separate servers, it's high recommended to specify every TCP/IP for servers as Fully qualified TCP address.
in the corrent situations press Yes
The Configuration of Database Mirroring finished now, in the Screen below note that both the Principal and Mirror endpoint are specified now by TCP://ServerName:Port_Number template .
Also note that in the section of Operating mode, there are two mode only available forg mirroring. the High safety with automatic failover is the only option not supported in the current situations, because of it's required a Witness Server which is not configured.
The results of Database Mirroring configuration is shown below.
the principal server
the mirror server
End point for Principal and Mirror server
Finally, if need now to Failover the Principal Server then, any of the following scenario should be met.
scenario 1
The Principal and Mirror server havn't any disaster (there are in good state), but the administrator in specific time need to failover the Principal because of any issue related with troubleshooting and performance for current Principal Server .
In this case, goto the Database Properties for Principal Database and from Mirroring Tab in the left pane Click on the Failover button .
The failover by this way will switch the principal to mirror server and mirror to Principal one .
scenario 2
If the administrator need to swich the Principal Database to new Principal Server, then from Mirroring tab in the Database Properties windows click on Remove Mirroring button and try to prepare full backup from Principal Database to restore it into the new Principal Server and then make a new Mirroring with Mirror Server.
scenario 3
The Principal Server has a risk or disaster, try to make a full backup and transactional log backup for Principal database and restore it to Mirror Database (Restore with Recovery) .
This way will swith Mirror Database to online database. after that think about any new strategy mirorring for this online database.
Conclusion
For more details please checkout the following links,
Database Mirroring
Database Mirroring Best Practices and Performance Considerations
http://technet.microsoft.com/en-us/library/cc917681.aspx
