I have implemented step by step creation of mirroring. Hope it will work for other programmers, DBA also for understanding.
What is SQL Server Mirroring?
SQL Server Mirroring is latest feature supported in SQL Server 2005 for disaster management. It assures data availability if principal server fails. In the mirroring database transaction log transfer from one server to another server automatically. And within few minutes other stand by server is ready if main server get down or some fault occurred.
What is initial requirement for SQL Server Mirroring?
For SQL server mirroring we should have SQL SERVER 2005 with Sp1 onward.
What are the advantages of SQL Server Mirroring?
• It provides high availability of database.
• Comparatively easy to implement.
• No Extra hardware required to implement.
What are the drawbacks of SQL Server Mirroring?
Its individual choice whether they take it as a drawback or extra feature or requirement. According to me below is main point which I don’t like.
• It slowdown your main database server.
• In some case we cannot use mirror database directly because it treated as read only database or we can say Snapshot.
What are the main terminologies used with SQL Server Mirroring?
Below are main terminologies which I think used in mirroring:-
Principal Server: – The principal server is the server which is mainly responsible to give response to end user. The end user is connected with this server first. The Primary database exists on this server.
Mirror Server: - The mirror server contains the copy of main database. The copy of main database is called mirror database and the server is called mirror server. In other words we can its stand by server which is updated by principal server by providing transaction log. Ideally it is not connected with end user.
Witness server: - The Witness server is an optional server. Witness server different from Principal server & Mirror server. It provides mechanism for automatic failover. In other words we can say that when primary server fail then end user connected to witness server instead of mirror which help out end user for database failover situation.
End points: – By the name it clear that it contain address and port number for connection between principal
Quorum – Quorum is the minimum relationship among the entire connected server.
What are the different modes of mirroring?
Basically mirroring work with 2 modes which are synchronous & Asynchronous.
1) Synchronous Mode: – This is high safety mode. In this every transaction which is committed on principal server will also need to be committed on mirror server on same time. It acknowledges end user only when transaction is committed on both servers. It can be achieve with 2 options
a) High safety with automatic failure-
By then name it is clear that if something going wrong means failure happened then we have to manually take care of end user connectivity.
b) High safety without automatic failure:-
With help of this feature we don’t have to do much effort at the time of failure automatically end user pointed to another server.
2) Asynchronous Mode: – This is high availability mode. In this principle server send transaction log but he don’t wait for acknowledge response. Means principle server committed transaction without waiting for response of mirror database. It does not provide automatic failover to the end user.
In Next part we will take a look for step by step implementation.
Thanks & Enjoy Mirroring
Rajat
Kindle Wireless Reading Device, Wi-Fi, 6" Display, Graphite - Latest Generation
