Question: What mechanism allows SQL Server 2008 to commit transactions on the mirror faster than SQL Server 2005?
This question was asked on a technical discussion group. My reply follows.
Hello. In SQL 2008 we have touted the “fast commit” that enables the mirror to more rapidly provide acknowledgement to the principal that it has received the transaction. Is this feature implemented through caching mechanisms that are persisted to disk? Or does the mirror allocate RAM for this and then provide the acknowledgement? The former is what the customer would like to hear for fear of data loss.
My answer:
I haven’t heard the term “fast commit” used before, but I can make an educated guess at what you are asking. There are a few different things going on that allows the transactions to be committed on the mirror faster in SQL 2008. Log stream compression: The stream of tran log records are compressed before being sent to the mirror. This results in faster transfer of transactions. Write-ahead of the log stream: As transactions are streamed in, the transactions are written to disk without processing them while at the same time transactions written to disk are being processed by the mirror. Better use of the log send buffers: rather than create a new log send buffer in the log cache every time a log flush occurs, SQL reuses existing log send buffers if there is enough free space to hold the log records. The log cache is a section of memory reserved for storing data that is going to written to the log.
My earliest forays into writing powershell for SQL Server found me scrambling for help. I found several resources online that covered some specific topics, but I still had trouble finding what I needed.
Ultimately, I found a lot of great articles on one of my old haunts Database Journal. I used to be regular on Database Journal. I posted some of my earliest articles there and am still a moderator of some of the forums there. I got a lot of help from some articles written by a fellow dba and Database Journal regular Muthusamy Anantha Kumar aka "The MAK". I got some additional help from articles written by another Database Journal regular that I didn't know as well, Yan Pan.
I decided to check out Amazon.com to see if they had any books on powershell for dba's and learned that MAK and Yan Pan have collaborated on a book on this subject. Based on the articles I have already read by MAK and Yan Pan on Database Journal and from already knowing the depth of experience and knowledge that MAK brings to the table, I pre-ordered a copy for myself. I think it will become an essential book for anyone that uses powershell for database administration.
Pro SQL Server 2008 Mirroring will be the first book dedicated to the subject of database mirroring. Most books only gloss over the topic of database mirroring. Even books that claim to be dedicated to failover clustering and database mirroring only have a few chapters on mirroring. My book will be more comprehensive and delve deeper than any other discussion on the topic so far.
Pro SQL Server 2008 Mirroring has just recently been added to Amazon.com's bookshelves. You should start seeing it for presale on other internet booksellers in the near future and should hit the stores in September.
Since this is my first published book, it's been really interesting for me to see it start showing up on internet booksellers in other countries. So far, Sweden leads the pack with three different internet booksellers showing my book. I even learned something new by searching for my book. I learned about the island country of Niue, a self-governing annex of New Zealand. I've wanted to visit New Zealand for a long time, and if I ever make it, I definitely want to include Niue on the itinerary.
For more information, please see the publisher's website or Amazon.com. If you see any other booksellers selling my book, please drop me a message or post it on here!!
Title: Pro SQL Server 2008 MirroringAuthor: Robert L DavisISBN10: 1-4302-2423-1ISBN13: 978-1-4302-2423-5Estimated page count: 400 pagesPublish Date: September 1, 2009Publisher: Apress Inc.
Are you still supporting SQL Server 2000? How about 7.0 or 6.5? Officially, Microsoft support has ended for these products. I know plenty of companies that are still using SQL Server 2000. Even within Microsoft SQL 2000 is still in use. Mostly it's old internal applications that no longer have development budgets and have entered the "sustained engineering" phase. In other words, no one is willing to pay for the labor to upgrade it, and no one is willing to pull the plug. Or maybe there's just one person still using it, but that one person has enough stroke to keep the application on life support.
I read a good article called Ordering Tables To Preserve Referential Integrity posted on SQL Server Central recently that included a script for calculating a logical order for tables based on defined referential keys. Someone requested a version of the script that would work on SQL Server 2000, and the author, R Glen Cooper, gladly posted an update for SQL 2000.
That got me thinking that I think it is time to stop officially supporting SQL Server 2000. I have frequently in the past posted versions of scripts that are compliant with SQL Server 2000 T-SQL. More recently, most of my writings have been on features that do not exist in SQL Server 2000 (such as database mirroring), and thusly SQL 2000 never entered the picture. Going forward, I will no longer be posting scripts specifically written to work in SQL Server 2000. I will gladly help out if someone needs a script for SQL 2000, but it will be by request only.
I'd love to hear if other people are still supporting SQL Server 2000 and to what degree. Please post any feelings you may have on the subject.
The following scripts can be downloaded as text files. You will need to change the file extension to .ps1 in order to execute them. Backup a database Restore a database Scan a server to find a free port Query DNS to get the FQDN of a server
To see some examples of my other forms of writing, please visit my page on WritersCafe.org. It is almost exclusively horror fiction, but I sometimes throw other things in there too from time to time. There's one science fiction story, a couple of poems, and quite a few humor pieces as well.
Look for me in the SQL Q&A section of the August, 2007 issue of TechNet Magazine. August issue of TechNet Magazine's SQL Q&A column
