|
|
Database Mirroring Articles |
|
|
|
|
|
 |
|
|
Database Mirroring Clinic Articles |
|
|
 Database Mirroring Clinic (in session) *More articles are being uploaded...
 | | Configuring DBM on a Low-Bandwidth Networks
Author :: Saleem Hakani
Date :: Sat 05/02/2009 @ 04:32 |
| For low bandwidth networks meaning lesser then the 10 mega-bits per second or by using modem (56 kbps or 33 kbps), throughput and response time degrades drastically...
READ MORE ABOUT THIS POST
Rule of thumb: You must configure database mirroring in high-bandwidth network only. For low bandwidth networks meaning lesser then the 10 mega-bits per second or by using modem (56 kbps or 33 kbps), throughput and response time degrades drastically. But if you have no other option other than to set it up on low bandwidth networks than you must not use synchronizing / high availability mode as it may just be too slow or may just freeze up, and this completely depends upon your business requirements and resources & budgets allocated for the project. The network bandwidth recommendation is completely based on the log generation rate of your application as setting up database mirroring in lower bandwidth networks can adversely impact the performance of database mirroring. Therefore, we highly recommend that you test your environment and the log generation rate and based on that make the decision. Our recommendation is to use high network bandwidth for database mirroring. You must always make sure both in high and low bandwidth network that your network bandwidth is significantly more than the log generation rate of your application. Otherwise, Database Mirroring performance will be a major bottleneck for your application environment.
|
| |
|
| | Achieving Geo-redundancy with Database Mirroring
Author :: Saleem Hakani
Date :: Thu 05/15/2008 @ 04:24 |
| You can setup database mirroring in Metropolitan Area Network or Wide Area Network primarily as a disaster-recovery solution. Several companies have lost their precious data during Katrina and Tsunamis, therefore it‘s always a good idea....
READ MORE ABOUT THIS POST
Achieving Geo-redundancy with Database Mirroring You can setup database mirroring in Metropolitan Area Network or Wide Area Network primarily as a disaster-recovery solution. Several companies have lost their precious data during Katrina and Tsunamis, therefore it‘s always a good idea to think ahead and setup your database mirroring environment with geo-redundancy. You must also decide what‘s important for you, is it the data or the availability if it‘s the availability than you will need to have powerful network backbone to be able to communicate with all the mirroring participants. Generally, in MAN and WAN environments roundtrip times ranges between 2 to 300 millisecond (based on your internet bandwidth) If the network latency between the principal and mirror server is >=2 milliseconds you must evaluate the performance with respect to the performance goals of your application and business requirements and only then you can determine if synchronous mirroring is the right choice for your environment. Also, with high network latency, the log send queue on the principal server can build up significantly under heavy workload, which may result in longer failover time and increased transaction loss in the event of a failover. Keep the following points in mind: If roundtrip <=2 then Best Practices: Setup SYNCHRONOUS Database Mirroring If roundtrip >=3 then Best Practices: Setup ASYNCHRONOUS Database Mirroring
|
| |
|
| | Best Practices when performing maintenance in a DBM environment
Author :: Saleem Hakani
Date :: Tue 03/11/2008 @ 12:38 |
| There may be times when you may have to perform maintenance on your Principal SQL Server instance due to Service pack installation, reboots, etc and yet would like to continue providing availability to your servers, In this article I'll expplain how you can achieve high availability and yet perform all the required maintenance tasks for a short period of time.
READ MORE ABOUT THIS POST
If you have configured database mirroring in synchronous with or without automatic failover mode, you can switch to the mirror server instance and make it available to the clients while you perform hardware or software maintenance on the principal server instance. So let’s see how we can do this: Step1: Always perform the maintenance on your mirror server instance first. You may perform reboots, install service packs, etc even without touching the principal server instance and once you are done performing maintenance on the mirror server instance you should make sure that the mirroring state on the principal server instance becomes “SYNCHRONIZED” Step2: Once you confirm that the mirroring state has become “SYNCHRONIZED” on the principal server instance, perform an manual failover from the principal server instance to mirror server instance by running the Alter Database Failover statement from the Principal server instance: Use Master; Alter Database SET PARTNER FAILOVER; Above statement will perform a failover from your principal server instance to the mirror server instance, during this failover all the users connected to the principal server instance will be disconnected and any new users connecting to the database will be auto-redirected to the mirror server. It is recommended that you stop the application for a brief moment of manual failover and restart after the failover succeeds. In this case, you will need to point your application to the new principal database server. You can now perform the hardware and software changes on the old principal server. As soon as you are done with the change and the database becomes available, it automatically re-establishes the mirroring session and assumes the role of the mirror. Q) How do you check if the database is a principal database or not? A) You can query Sys.Databases object from the master database to view the status of the database. Use Master; Select Name, Mirroring_Role_Desc from Sys.Databases where Name-‘DBName’; If the server instance is the principal, the value of Mirroring_Role_Desc column will be “Principal” else it will be “Mirror” Step3: If you have a witness configured you can now perform the same maintenance on the witness server without affecting the database mirroring session.
|
| |
|
| | How to read data from a Mirror database using Database Snapshots
Author :: Saleem Hakani
Date :: Thu 03/06/2008 @ 10:57 |
| In the database mirroring environment Principal database server interacts with all the users but the mirror database on a mirror instance only receives transaction log data from the principal database server and other than that mirror database doesn’t do anything as it’s in “RECOVERING” state all the time. In this article, I'll share the secrets of how you can make use of your mirror database copy for read only purpose using Database Snapshots. (New feature of SQL Server 2005)
READ MORE ABOUT THIS POST
How to make use of a mirror database using Database Snapshots Author: Saleem Hakani (Saleem@sqlcommunity.com) In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesn’t do anything as it’s in “RECOVERING” state all the time. Database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot creation. With Database Snapshots you can take advantage of the mirror database that you are maintaining for high availability purposes to offload reporting. You can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot. You can create a database snapshot on the mirror database only when the database is fully SYNCHRONIZED. As long as both mirror and principal server are communicating with each other database snapshots can be accessible to clients. Keep in mind that as database snapshot is static, new data is not available. You must create new database snapshots periodically and have applications direct incoming client connections to the newest snapshot. The new database is almost empty, but it grows over time as more and more database pages are updated for the first time. Because every snapshot on a database grows incrementally in this way, each database snapshot consumes as much resources as a normal database. Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database. Therefore, it is recommended that if you can live without touching the mirror databases/servers, great and if not you can keep only a few relatively recent snapshots on your mirror databases. If role switching occurs, the database and its snapshots are restarted, temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which has become the new principal database. Users can continue to use the snapshots after the failover. However, this places an additional load on the new principal server. If performance is a concern in your environment than it is recommended that you create a snapshot on the new mirror database when it becomes available and redirect clients to the new snapshot, and drop all of the database snapshots from the former mirror database. Note: For a dedicated reporting solution that scales out well, consider replication. Let’s create a snapshot on the mirror database, make sure you are connected to the mirror database: Use Master; CREATE DATABASE SQLCOMMUNITY_SnapSHOT_0400 on (Name=’SQLCOMMUNITY_0400_Data’, FILENAME= ‘H:\MSSQL\DATA\SQLCOMMUNITY_0400.snp’) As SNAPSHOT OF SQLCOMMUNITY; You are now ready to use database snapshots for querying mirror databases. How and where to view database snapshots? In the object explorer, connect to the instance of Microsoft SQL Server and expand “Databases” and than expand “Database Snapshots” and select the snapshot you want to view. Once you are done working with database snapshots, you may drop the database snapshots the same way as you would any user databases: Use Master; Drop Database SQLCOMMUNITY_SnapSHOT_0400;
|
| |
|
| | Comparing Failover Clustering with Database Mirroring
Author :: Saleem Hakani
Date :: Mon 02/25/2008 @ 07:55 |
| This article compares Failover Clustering with Database Mirroring.
READ MORE ABOUT THIS POST
Comparing Failover Clustering with Database Mirroring Failover clustering is another technology for maintaining your database servers highly available. It provides instance-wide high availability which can help you with hardware failure and can also help you with scheduled maintenance. In case of failure, the operating system and SQL Server would work together to provide automatic system failover. You can configure one node to failover during scheduled maintenance and let other nodes serve production traffic. Tip: SQL Server 2005 Failover clustering now supports up to eight nodes when running on Microsoft Windows Server 2003 Datacenter edition. SQL Server 2005 also extends clustering functionality to enable you to use clustering in conjunction with Analysis Services and Full-Text Search. Database Mirroring offers several advantages over failover clustering, such as faster failover, protection against storage failures, standard hardware, low maintenance, etc. The following table lists some of the differences between failover clustering and database. (Please note: If your business requirements are to provide server/instance wide high-availability then your best choice would be to go with SQL Server 2005 Failover Clustering) Following tables compares the differences between failover clustering and database mirroring:
|
Consideration
|
Failover clustering
|
Database Mirroring
|
|
Standby Type
|
Hot Standby
|
Instant Standby
|
|
Protection from Disks
|
If the shared disk crashes your entire cluster is down
|
Provides redundancy in the event of a server and or storage failure
|
|
|
|
|
|
H/A Level
|
Instance Level
|
Database Level
|
|
Failover Duration
|
> = 30 seconds + Database Recovery
|
Failover is fast, sometime takes seconds.
|
|
Distance Limitation
|
< = 100 miles; more with geographically-dispersed clusters
|
Virtually no distance limit
|
|
Hardware
|
Specific hardware required
|
Standard SQL Server 2005 hardware
|
|
Setup
|
Setup is more complex at the system level
|
Setup is easy and completely within SQL Server
|
|
Scope
|
Whole instance; all system & user databases
|
Only user databases can be mirrored
|
|
Failure Detection
|
Yes
|
Yes
|
|
Transparent to client
|
Yes, Reconnect to same IP
|
Yes, Auto-Redirect
|
|
Impact on throughput
|
No Impact
|
Minimal impact (Since the log records must be copied; response time may impact due to latency on commit)
|
|
Zero work loss
|
Yes
|
Yes
|
|
| |
|
| | How to change the default failover time for database mirroring
Author :: Saleem Hakani
Date :: Thu 02/21/2008 @ 02:35 |
| This article talks about how to configure the default time-out of your database mirroring environment.
READ MORE ABOUT THIS POST
Changing the default auto-failover timeout value The default timeout for communication between principal, mirror & a witness is 10 seconds. Meaning if any of the partners are not ping-able for 10 seconds it can throw a timeout message and the database can failover. Note: There are several other errors @ the O/S level that can cause failures and some of them are: 1) Network errors
2) I/O errors
3) Process errors, etc. You can make changes to the default timeout setting by configuring the partner timeout value as shown in the following T-SQL statement: Use Master; ALTER DATABASE [Database_Name] Set Partner TIMEOUT [Number_of_Seconds] ; Note: To keep a connection open, a server instance must receive a ping on that connection within the time-out period defined by the mirroring time-out value, plus the time required to send one more ping. Receiving a ping during the time-out period indicates that the connection is still open and that the server instances are communicating over it. On receiving a ping, a server instance resets its time-out counter on that connection. In asynchronous sessions, the default time-out value of 10 seconds cannot be changed. In synchronous sessions, however, you can control the time-out period. I recommend that you keep the time-out value at 10 seconds or greater, to avoid false failures.
|
| |
|
| | Pre-requisites and checklist for DBM setup
Author :: Saleem Hakani
Date :: Tue 10/16/2007 @ 08:23 |
|
READ MORE ABOUT THIS POST
Pre-requisites/Checklist form for setting up Database Mirroring Pre-requisites for setting up Database Mirroring: Setting up database mirroring is easy and simple if you plan ahead and collect all the required information before you start setting up database mirroring in your environment, just for your ease we are providing you with the following checklist: 1) Name of the Principal Server _______________________ 2) Name of the Mirror Server _________________________ 3) Name of the Witness Server ________________________ 4) Mirroring endpoint name for Principal server __________________________________________ 5) Mirroring endpoint name for Mirror server ___________________________________________ 6) Mirroring endpoint name for Witness server ___________________________________________ 7) Endpoint Port number for Principal server _____________________________________________ 8) Endpoint Port number for Mirror server _______________________________________________ 9) Endpoint Port number for Witness server ______________________________________________ 10) Operating mode ______________________________ 11) Safety Mode _________________________________ 12) Principal server domain name ___________________ 13) Mirror server domain name _____________________ 14) Witness server domain name ____________________ 15) Name of the database to be mirrored ____________________________________ 16) Have you created exact partitions on the mirror server? _____________________ 17) Location where backups are stored? ___________________________________________________ 18) Was latest SQL Server Service pack applied? _____________________________________________ 19) Location of the scripts for database mirroring? ___________________________________________ 20) Did you copy all jobs, logins, SSIS packages, server configuration from Principal server to the mirror server? _________________________ 21) Fully qualified server name for Principal ________________________________ 22) Fully qualified server name for Mirror ____________________________________ 23) Fully qualified server name for Witness ___________________________________ 24) Have you performed full and log database backups of the principal server? ___________________ Once you have the above checklist fully covered you are almost ready for setting up database mirroring: Keep the following points in mind before you start setting up database mirroring: 1. Make sure that both principal and mirrors are running on the same edition of Microsoft SQL Server 2005, they can either be standard edition or enterprise edition. 2. Make sure Microsoft SQL Server 2005 (any edition) is installed on the witness server. The witness server can run on any reliable computer system that can support SQL Server 2005. 3. Make sure that the mirror server has the exact same jobs, logins, SSIS packages, disk partitions, disk space, server configuration, etc (This is very important) 4. Make sure that all the databases participating in Database Mirroring are set to FULL recovery model. 5. If possible, the path (including the drive letter) of the mirror database should be identical to the path of the principal database. If the file layouts must differ, for example if the principal database is on drive F: but the mirror system lacks an F: drive, you must include the move option in the RESTORE statement. 6. All of the server instances in a mirroring session should use the same master code page and collation. Having different collation/code-page can cause problems during database mirroring setup. 7. For best performance, use a dedicated network interface card (NIC) for database mirroring. 8. Configure the mirror server with the server settings exactly as the principal server was configured. You may do this by running and comparing the output of ―Exec SP_Configure system stored procedure on both principal and mirror servers.
|
| |
|
| | Internals of DBM Failover / Role Switching
Author :: Saleem Hakani
Date :: Tue 10/16/2007 @ 07:35 |
|
READ MORE ABOUT THIS POST
Understanding different types of failovers/Role Switching and How do they internally work. Failover (aka. Role switching) involves transferring the role of a principal database to a mirror database role and vice-versa. In role switching, the mirror server acts as the failover partner for the principal server. When a failover occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly. As can be seen from the below picture Server_1 acts as a Principal server before the role switch is done and after the role switch it becomes the mirror server and the former mirror server becomes the principal server and the logs are transferred the other way round. There are three forms of role switching that exist with Database Mirroring: 1) Automatic Failover 2) Manual Failover 3) Forced Failover (Rarely done) Automatic failover Automatic failover can occur whenever either of the partner becomes unavailable. This requires mirroring to be in high-availability mode (safety=full) meaning you must have a Principal, mirror and a witness server configured. Also, for a successful automatic failover the database must already be synchronized, and the witness must be connected to the mirror server. The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server, but the witness is still connected to the principal server, the mirror server does not initiate a failover as the connection from witness to principal server is fine. Key points to remember for automatic failover: · Automatic failover requires witness server and transaction safety must be set to full · Automatic failover will be initiated only when the ―failure‖ happens and when the partners are fully synchronized and both the partners and the witness are connected. · Automatic failover could occur if the acting principal SQL Server shuts down or is rebooted or if the network connectivity is lost · Automatic failover could occur for any unplanned events caused by Hardware/software errors. Behind the scenes of Automatic failover: Several things happen behind the scenes whenever a failover occurs as listed below:
|
What happens during automatic failover:
|
|
Step1: All the client connections are terminated and the principal database state changes to ―DISCONNECTED‖
|
|
Step2: As there‘s a quorum between both the participants and the witness, both mirror and the witness will detect the failure of principal database.
|
|
Step3: As the mirror database is in NORECOVERY mode, all the logs from the
|
|
queue of the principal server is applied on the mirror database to completely recover it.
|
|
Step4: Once the log is fully recovered, it checks with the witness and decides that the database should now failover to the mirror. This usually takes seconds (based on your network and system performance) and if the principal database comes back before the mirror is fully recovered then failover is automatically cancelled.
|
|
Step5: After all the above steps are performed, the mirror database is brought online and it assumes the role of the principal. The database is now available and clients can connect to the new principal and continue operation. Once the former Principal comes back up, it finds out that its partner is now performing the principal role, it will take on the role of the mirror server and will synchronize the database quickly as possible and once it is ready to serve as mirror, failover is again possible, but in the reverse direction.
|
Manual failover You can manually perform failover from principal to mirror and vice-versa during planned maintenance like applying service packs or Hot-fix, rebooting the box, etc. Manual failover requires SAFETY=FULL and can only be done if the operating mode is set to either High Availability or High Protection mode and the partners must be connected to each other, and the database must already be synchronized. Forced failover (with possible data loss) Forced failover is strictly a disaster recovery method. Forcing failover may involve some data loss. Therefore, you should use this type of failover only if you are willing to risk losing some data in order to bring up the mirror database immediately. Forced Failover (with possible data loss) allows you to use mirror server as a warm standby server. This is only possible if the principal server is disconnected from the mirror server in a mirroring session. Support for forced service depends on the operating mode and the state of the session, as follows: Typically, high-performance mode supports forcing service whenever the principal server is disconnected. However, though unnecessary, a witness can exist for a high-performance mode session. In this case, forcing service requires that the mirror server and witness are connected to each other. High-safety mode without automatic failover supports forcing service whenever the principal server is disconnected. High-safety mode with automatic failover supports forcing service whenever the mirror server and witness are connected to each other and neither is connected to the principal server (as long as the mirror server was not in the process of rolling back the mirror database when it was last connected to the principal). You must perform this type of failover with extra care and must use this type of failover only if you must restore service to the database immediately and are willing to risk losing data. The effect of forcing failover is similar to removing mirroring, except that forcing failover facilitates re-synchronizing the databases when mirroring is resumed, at the risk of possible data loss. Forcing service initiates a smooth transition of the principal role to the mirror database. The mirror server assumes the role of principal server and immediately serves its copy of the database to clients. The new principal database runs without a mirror (that is, it runs exposed).
|
| |
|
| | What are DBM Enpoints and how do they work?
Author :: Saleem Hakani
Date :: Mon 10/15/2007 @ 08:58 |
|
READ MORE ABOUT THIS POST
ENDPOINTS: Communication Mechanism for Database Mirroring  Just to make it simple consider mirroring endpoints as a telephone which is used by 2 or more parties for communicating with each other. You use a specific telephone number to connect to the remote party and same can be considered in the mirroring session where in order to communicate with each other all the servers involved in database mirroring use TCP endpoints to communicate with each other on a specific TCP port. Before establishing a database mirroring session, you must configure to establish the communication mechanism between all the servers participating in database mirroring. This communication can be accomplished by creating endpoints on all the servers which controls the transmission control protocol (TCP) port to listen on. Database Mirroring endpoints use TCP protocol to send and receive messages between the server instances in database mirroring sessions. Each database mirroring endpoint listens on a unique TCP port number. The database mirroring endpoint of a server instance controls the port on which that instance listens for database mirroring messages from other server instances. Note: All mirroring connections on a server instance use a single database mirroring endpoint. Below picture shows how database mirroring communicates with servers using TCP Port. Note: 1) If your server has multiple instances of SQL Server participating in mirroring sessions, each instance requires its own endpoint, configured with unique TCP port.
2) Client connections to the principal server do not use database mirroring endpoint. Instead they use any of the available protocols using SQL network library which could either be TCPIP, Named Pipes, Shared Memory, VIA.
|
| |
|
| | Is Witness server a single point of failure?
Author :: Saleem Hakani
Date :: Sun 10/14/2007 @ 09:00 |
|
READ MORE ABOUT THIS POST
Is Witness a single point of failure? No, the witness server is not considered as a single point of failure in database mirroring session, because if the witness server fails, the principal and mirror can continue to form a quorum. (Automatic failover will not be possible when the witness is lost but you can manually perform a failover) The witness should be setup only if you intend to use High-Safety (High-availability) mode with automatic failover and if you are using high-performance mode than it is recommended that you don‘t setup the witness server or set the witness property to OFF.
|
| |
|
| | How does Client Redirection work with DBM?
Author :: Saleem Hakani
Date :: Thu 10/11/2007 @ 04:18 |
|
READ MORE ABOUT THIS POST
How does Client Redirection work with Database Mirroring? Client redirection is one of the most difficult process with other H/A technologies but with database mirroring in SQL Server 2005, applications connecting to the Principal database would be able to handle the connections during the failover of the Principal server to the Mirror server very easily. This has always been a problem with Log shipping and Replication but Database Mirroring can handle this very nicely. The new data access provider (MDAC) contains a connection object called “Transparent Client Redirect”. This object allows the caching of both the Principal and the Mirror server name. This process of caching is transparent and developers do not need to implement any code to implement this functionality. If an active connection to the principal database fails for any reason, for example, due to a database mirroring failover, and the application attempts to reconnect to the same principal server, the data access provider (MDAC) can attempt to reconnect using the failover partner name stored in the client's cache. Reconnecting is not automatic; however, the application must become aware of the error. Then, the application needs to close the failed connection and open a new connection using the same connection string attributes. At this point, the data access provider redirects the connection to the failover partner. Note: SQL Native Client verifies that it connects to a principal server instance but not whether this instance is the partner of server instance specified in the initial partner name of the connection string. Important: If the client gets disconnected from the database, the data access provider does not attempt to reconnect. The client must issue a new connection request. Also, if an application shuts down on losing the connection, it will lose the cached partner names. If the connection was lost because the principal server became unavailable, the only way that the application can reconnect to the mirror server is by supplying the failover partner name in its connection string. *You may use a new parameter in .NET 2.0 called “Failover Partner” in your connection string to specify the partner server name in database mirroring session. This will help your application to auto-redirect to the mirror.
|
| |
|
| | Database Mirroring Roles and Responsibilities
Author :: Saleem Hakani
Date :: Thu 10/11/2007 @ 05:56 |
|
READ MORE ABOUT THIS POST
Database Mirroring Roles and Responsibilities There are three roles a database can take on when participating in database mirroring session: 1. The principal Server role 2. The mirror Server role 3. The witness server role (optional) 1. Principal database role: The role of the principal server is to serve the production database to clients. Only the principal database is accessible to client connections. When the principal database receives changes requested by clients, the principal server sends those active changes to the mirror server. 2. Mirror database role: Mirror server holds an exact point-in-time copy of the principal database and is always ready to take over the principal server role in the event of a failure on the principal server. It acts as a hot standby server. When the mirror receives the changes sent by the principal, it places those changes in mirror databases log buffer and then commits them to disk as quickly as possible. Internally, the mirror server redoes the log on the mirror database with the oldest log record, record by record and as quickly as possible. Redoing the log involves applying the pending or queued changes to the mirror database in sequence, starting with the oldest record. Each change is done only once and it is then rolled forward. When the principal server truncates or shrinks the log for the principal database, the mirror server also shrinks the log at the same point. This is because the mirror server replays the activities of principal databases changes. Note: Your mirror server hardware, disk configuration and database file placement should be exactly the same way principal server is configured so that in the event of the failover the mirror functions exactly the same way as your principal server. 3. Witness server role: In the database mirroring topology you may have an optional third server called the witness. Witness server is required for enabling automatic failover from principal to mirror server or vice-versa. Unlike principal and mirror servers, the witness server does not serve the database. The role of the witness is to verify whether a given partner server is up and functioning. Supporting automatic failover is the only function for witness server. It uses quorum to identify which server holds the principal copy and which server holds the mirror copy of the database. (more information about quorum is explained in the next topic) Note: Witness server can be any computer that can support SQL Server 2005, it doesn‘t have to be a high-end computer as long as it meets the recommended requirements for SQL Server 2005.
|
| |
|
| | What are DBM Quorums and How do they work?
Author :: Saleem Hakani
Date :: Thu 10/11/2007 @ 05:56 |
|
READ MORE ABOUT THIS POST
What are different Quorum types and how do they work? Quorum basically is a relationship among two or more SQL Servers participating in a database mirroring session. Quorum is required only when a witness is setup for high availability in a database mirroring session. If a witness is present, a loss of either principal or mirror database leaves two servers to form a quorum. If the principal server cannot see the mirror server, but if it can see the witness and form a quorum with the witness, it can keep its database in service. Similarly, if the mirror and witness server cannot see the principal, but if it can see the witness and can form a quorum with the witness, the mirror can take on the role of the new principal server. There can be three types of quorum in database mirroring: 1. Full quorum 2. Partner to witness 3. Partner to partner What is a Full Quorum?  Full Quorum includes all the 3 servers participating in database mirroring (Principal, Mirror & Witness).The good thing about Full Quorum is that if the Principal is not available, Database Mirroring will continue to function as both Witness and Mirror can talk to each other and if the Mirror is not available, database mirroring will still continue to function as both Principal and witness can talk to each other.
What is a Partner to Witness Quorum?   Partner to Witness quorum includes a witness and either partner (Principal / Mirror). If the network connection between the partners is lost because one of the partners has been lost, two cases are possible: 1) The mirror server is lost, and the principal server and witness retain quorum. In this case, the principal sets its database to DISCONNECTED state and runs with mirroring in a SUSPENDED state (this is called running exposed because the database is currently not being mirrored). When the mirror server rejoins the session, it regains quorum as mirror and begins re-synchronizing its copy of the database. 2) The principal server is lost, and the witness and the mirror server retain quorum. What is a Partner to Partner Quorum?  This happens when the witness server is not available. If the witness is lost, automatic failover is no longer possible. When the witness comes back up, the session resumes normal operation.
As long as both Principal and Mirror retains quorum, the database continues in a SYNCHRONIZED state, and manual failover remains possible. When a session has only a partner-to-partner quorum and if either partner loses quorum, the database goes offline and remains unavailable until the principal server regains quorum with either the mirror server or witness. Note: If you expect the witness to remain disconnected for a significant amount of time, it is recommend that you temporarily remove the witness from the session.
|
| |
|
| | The anatomy of Transaction Safety & Operating Mode
Author :: Saleem Hakani
Date :: Thu 10/11/2007 @ 05:55 |
|
READ MORE ABOUT THIS POST
What are Transaction Safety Modes & Operating Modes? There are three types of operating modes and two types of transaction safety which helps determine the type of topology in database mirroring and the exact mode is based on the setting of transaction safety and whether a witness is a part of the mirroring session or not: 1) Synchronous with automatic failover (Safety=FULL) 2) Synchronous without automatic failover (Safety=FULL) 3) Asynchronous mode (Safety=OFF) Synchronous with automatic failover (Safety=FULL) (Also known as High Availability operating mode) supports maximum database availability with automatic failover to the mirror database. This operating mode is best used where you have fast and very reliable communication between the servers and you require automatic failover for a single database. Please note: With High availability mode the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. In this mode Database Mirroring is self monitoring. If the principal database suddenly becomes unavailable, or the principal‘s server is down, then the witness and the mirror will form a quorum of two and the mirror server will perform an automatic failover. Behind the scenes of High availability mode:
|
Step 1
|
Principal receives a transaction from client and writes the transaction to the T-log of the principal database
|
|
Step 2
|
While the principal server writes the transaction to the t-log on the principal server and once it has hardened the log it sends the log record to the mirror server. At this moment the principal server waits for an acknowledgement from the mirror server.
|
|
Step 3
|
During this time the mirror server hardens/commits the log to the disk and returns an acknowledgement to the principal server
|
|
Step 4
|
|
Once the principal server receives the acknowledgement from the mirror server it confirms this acknowledgement for this transaction and is ready to receive new transactions
|
|
High availability (High-safety) mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server. Note: For automatic redirection of your client application please look into "Client Redirection" topic in "Database Mirroring Clinic" Synchronous without automatic failover (Safety=FULL) (also known as High Protection operating mode) does not have witness server as part of the mirroring session. The principal database does not need to form a quorum, but because there is no witness, it must be with the mirror server only. Only manual failover is possible in this role as there‘s no witness. Asynchronous mode (SAFETY=OFF) (Also known as High Performance operating mode) has the transaction safety set to OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror. The mirror does its best to keep up with the principal, but it is not guaranteed at any point in time that all the most recent transactions from the principal will be hardened in the mirrors transaction log. In this operating mode a witness server plays no role, and a quorum is not required. Therefore automatic and manual failover is not enabled. It only allows FORCED SERVICE failover, which is also a manual operation. However, FORCED SERVICE will be rarely used. Normally you would switch to full safety. The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered if some of the transaction log blocks from the principal have not yet been received by the mirror. Following table lists some of the quick differences between operating modes.
|
Option
|
High Availability
|
High Protection
|
High Performance
|
|
Transaction Safety
|
FULL
|
FULL
|
OFF
|
|
Transfer Mechanism
|
SYNCHRONOUS
|
SYNCHRONOUS
|
ASYNCHRONOUS
|
|
Quorum Required
|
Yes
|
Yes
|
No
|
|
Witness Server
|
Yes
|
No
|
N/A
|
|
Failover Type
|
Automatic or Manual
|
Manual only
|
Forced only
|
The above table will give you an idea of the role of a witness server with the type of availability: If transaction safety is set to FULL, the principal and mirror servers operate in synchronous mode. As the principal server first hardens its principal database log records to disk, and then sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror‘s log disk. If the transaction safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgement from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service. Key points of High Availability operating mode: Ø The transaction safety is always set to FULL Ø The records are transferred synchronously. The client does not get a transaction committed response until the mirror server has acknowledged the principal server. Ø Both the partners and the witness server are required to be present. Ø The failover is automatic and manual failover is also possible. Ø No committed transactions are lost during failover. Key points of High Protection operating mode: Ø The transaction safety is always set to FULL Ø The records are transferred synchronously. The client does not get a transaction committed response until the mirror server has acknowledged the principal server. Ø The quorum between witness and partners is not required Ø Automatic failover is not possible as there‘s no witness server Ø No committed transactions are lost during failover Key points of High Performance operating mode: Ø The transaction safety is always set to OFF Ø The records are transferred asynchronously. The client gets transaction committed response as soon as the principal server has written the transaction to the log. The principal server does not wait for acknowledgement from the mirror server. Ø Quorum is not set and the witness server is not present. Ø During manual failover with safety OFF or FORCE_SERVICE, some transactions are lost during failover. (Generally this is never done as normally you would want to switch to full safety)
|
| |
|
| | Comparing LogShipping with Database Mirroring
Author :: Saleem Hakani
Date :: Wed 10/10/2007 @ 07:38 |
|
READ MORE ABOUT THIS POST
Comparing Log Shipping with Database Mirroring Log shipping provides you a warm standby of your data that can be brought online in the event your primary system fails for any reason. Log shipping works by restoring a full database backup to a secondary server, and then transaction logs from the primary database are continuously applied to the secondary database. Note: Log shipping is available only for user databases and not for system databases (same as Database Mirroring). The following table lists some of the differences between Log Shipping and Database Mirroring:
|
Consideration
|
Log Shipping
|
Database Mirroring
|
|
Data Transfer
|
T-Logs are backed up and transferred to secondary server
|
Individual T-Log records are transferred using TCP endpoints
|
|
Server Limitation
|
Can be applied to multiple stand-by servers
|
Can be applied to only one mirror server
|
|
Failover
|
Manual
|
Automatic
|
|
Failover duration
|
Can take more than 30 mins
|
Failover is fast, sometimes takes seconds
|
|
Role Change
|
Role change is manual
|
Role change is fully automatic
|
|
Client Redirection
|
Manual changes required
|
Fully automatic with the use of .NET 2.0
|
|
| |
|
|
|
|
|
|