Jan 18 2009

Can I Get A Witness! - High Availability (HA) Database Mirroring

Posted by Mike Brunt at 8:53 PM ColdFusion | JRun-J2EE

We have blogged a good deal about clustering and HA concepts as they relate to ColdFusion and JRun.  In 2009 I will be blogging a lot more about database issues because almost all web applications and RIA's have dependencies and in my experience the most oft required dependency is a database.  This blog piece relates to SQL Server and database mirroring which was introduced in SQL Server 2005 and which is also available in SQL Server 2008.  There were some significant enhancements mainly related to mirroring performance in SQL Server 2008.

Transaction logs are a critical part of a SQL Servers overall operations.  The transaction log records almost all transactions that take place in the operations  if adding, changing and reading data in SQL Server.  As I noted in a previous article, the location of these logs along with managing their size are important considerations in building out enterprise level SQL Server installations.  Before database transactions cause any changes to actual data pages, the transaction log is created in log-buffer memory and eventually "hardened" to permanent disk storage on the hard drive.  In SQL Server 2005 and 2008 the transaction log is copied from one database server to another in the mirrored set.  In this sort of HA schema we are employing an active-passive paradigm; one database will serve all requests and
this is known as the "principal" in the mirrored set. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records from that transaction log to the mirror instance.  At the mirror instance those log details reside first in log-buffer memory and are subsequently "hardened" to permanent disk storage on the hard drive.

In this mirrored scenario there are two servers, a "principal" and a "mirror" member.  The principal member serves all requests and copies all changes to the mirror member from the transaction log.  If the principal database fails the fail-over has to be actioned manually.  There is another server which can be introduced to the mix to make fail-over automatic, this is known as the "witness" server.  Basically the principal, mirror and witness servers are in constant communication, to determine if the principal (active) server is available and in not a promotion of the mirror to principal can be achieved in seconds.

Here is a very long article on Microsoft's web site which goes into great detail about all this.  It is a good read if you want to know the granular details and use this feature of SQL Server 2005 and 2008.

Comments

dell parts

dell parts wrote on 04/04/10 6:56 AM

Excellent podcast, and thanks for including us. As usual, you did an excellent job at summarizing what blogging is all about. We really do love the information exchange with our colleagues.
Custom flag

Custom flag wrote on 07/12/11 3:09 AM

ohh man this is awesome post.i think they should usebanner to get more flow in look.
you know this is great working by the writer.The writer has done a great job in speniding his time in
research about this article. can i subscribe all his posts ?
I am really happy to read this. i was searching this from last two months and atlast i got it. hurrah..!!
mobile phone accessorie

mobile phone accessorie wrote on 06/18/13 2:03 AM

bout this article. can i subscribe all his posts ?
I am really happy to read this. i was searching this from last tw
buy instagram likes

buy instagram likes wrote on 06/20/13 9:53 AM

This will simultaneously sends that block of log records from that transaction log to the mirror instance.
facebook likes

facebook likes wrote on 07/19/13 12:09 AM

You know your SQL Servers overall operations projects stand out of the herd. There is something special about them. It seems to me all of them are really nice
Buy instagram Likes

Buy instagram Likes wrote on 08/02/13 10:29 AM

You create a terrific point. Got terrific information right here. I do believe that if more and more people considered them because of this, they'd have a very much better time realizing the difficulty. Ones watch is without a doubt something Identification want to see the rest of. Great information and facts,
link

link wrote on 08/03/13 6:03 PM

This is one of the most incredible blogs I've read in a very long time. The amount of information in here
is stunning, like you practically wrote the book on the subject. Your blog is great for anyone who wants to
understand this subject more.
Louis Vuitton Outlet

Louis Vuitton Outlet wrote on 08/20/13 6:57 PM

Very good post and thanks for sharing !This blog piece relates to SQL Server and database mirroring which was introduced in SQL Server 2005 and which is also available in SQL Server 2008.
click here

click here wrote on 08/26/13 3:03 AM

I really realize for your brilliant Efforts on spending time to post this information in a simple and regular manner, so That viewer and readers can easily understand the concept. I Efforts must appreciate you posting these on information.
<a href="http://www.followerspromotion.com">click here</a>
bankruptcy filings now

bankruptcy filings now wrote on 08/28/13 10:46 AM

Very nice and useful information. Why not write a book about this particular subject. For today’s economic variation and be short of of opportunities, it actually will be a very hot topic. Thanks a lot
buy youtube views

buy youtube views wrote on 10/02/13 9:48 PM

Excellent piece of information. I really appreciate the time you have taken to write this. SQL is a complicated piece of software so it's good to know someone is providing good and helpful information to us all.
Alex

Alex wrote on 11/25/13 3:09 AM

Dear Mike,

Just wanted to tell you that I appreciate everything that you are doing. Thanks buddy

Cheers
Alex
mikefoc

mikefoc wrote on 11/27/13 9:01 PM

In 1890, thanks to the efforts of a prominent group of Syracusans led by James Geddes, the Fair moved back to Syracuse, permanently. The Geddes group convinced the Agricultural Society and State political leaders that Syracuse was the best permanent site for many reasons, including its previous success, its geographic location and railroad facilities. Land was purchased by Geddes and his group and given to the State Agricultural Society with the understanding that the land could be used by the State only as long as the Fair remained on the land.


Pennyroyal (Mentha pulegium) and Neem (Azadirachta indica) oil have histories in traditional use as insect repellants. Pennyroyal (pictured left), never to be used internally, can be used as a wash for skin by combining one teaspoon of the fresh herb in one cup of water. The fresh leaves may also be rubbed directly on the skin.


<a href=http://outdoorgeargals.com/north-face-equipment-north-face-backpacks-c-18_23.html>cheap north face denali</a>
Among snow boarders, the most common and preventable injury is to the wrist, which accounts for 60 percent, say Camelback's crew. Fortner encourages boarders to wear wrist guard gloves, which almost completely eliminate wrist injuries, he said. "I haven't seen a fracture from anyone wearing a wrist guard here," he said..

Related articles:
http://www.outdoorgeargals.com
credit renouvelable sans justificatif

credit renouvelable sans justificatif wrote on 12/29/14 4:27 AM

thank you for the sharing this very awesome stuff.
clothing manufacturer

clothing manufacturer wrote on 07/04/15 4:25 AM

Excellent podcast, and thanks for including us. As usual, you did an excellent job at summarizing what blogging is all about. We really do love the information exchange with our colleagues.