This is an interesting case that came up recently, and that I’ve seen a few times before, but which I haven’t seen explained anywhere. Using database mirroring, you may see 1204 and 1454 errors on the mirror server with no other databases or activity on the mirror server apart from database mirroring: With no activity [...]
Paul S. Randal
In Recovery...
Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems
Posted on: January 15, 2013 1:35 pm
TechNet Magazine: July 2011 SQL Q&A column
Posted on: July 21, 2011 3:11 pm
The July edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Deferred log truncation from concurrent data and log backups Database mirroring monitoring Multiple transaction log files Best use of SSDs in a SQL environment (high-level) Check it out [...]
Importance of network latency when using database mirroring
Posted on: February 8, 2011 10:02 am
Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey: I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn't a statistically valid sampe [...]
Importance of monitoring a database mirroring session
Posted on: January 31, 2011 12:29 pm
Last week I kicked off a survey about how you monitor your database mirroring sessions. See here for the original post. Here are the results of the survey: The "Other" values were: 6 people monitor mirroring session state changes One uses a 3rd-party product to do monitoring One monitors response time for the application [...]
Survey: database mirroring latency between servers
Posted on: January 30, 2011 11:10 am
In my survey for this week, I'd like to know what you what the network latency is between your mirroring principal and mirror servers, and what kind of mirroring you're using. I'll report on the results during the second week of February. Thanks!
Survey: monitoring database mirroring
Posted on: January 24, 2011 11:12 am
In my survey for this week, I'd like to know what you monitor if you're using database mirroring. [Edit: The survey has closed now. See here for the results.] I'll report on the results during the first week of February. Thanks!
Whitepaper on high-availability architectures published
Posted on: June 4, 2010 11:00 am
The 20-page whitepaper I wrote this Spring for the SQL team has just been published. This whitepaper is titled Proven SQL Server Architectures for High Availability and Disaster Recovery and ties in with the longer whitepaper I wrote last year on High Availability with SQL Server 2008 (see here). This new whitepaper describes the five [...]
TechNet Magazine: May 2010 SQL Q&A column
Posted on: May 9, 2010 12:46 pm
The May edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: How to change the file locations for a user database The difference between page latches and page I/O latches Why database snapshots are not a good substitute for [...]
A SQL Server DBA myth a day: (30/30) backup myths
Posted on: April 30, 2010 9:19 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand [...]
A SQL Server DBA myth a day: (11/30) database mirroring failover is instantaneous
Posted on: April 11, 2010 7:16 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Following on from yesterday's myth about database mirroring failure detection [...]
A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately
Posted on: April 10, 2010 5:52 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Off to SQL Connections today in Las Vegas – hope [...]
A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays
Posted on: April 7, 2010 6:54 am
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) A short one today as I'm teaching a class on [...]
TechNet Magazine: March 2010 SQL Q&A column
Posted on: March 5, 2010 1:14 pm
The March edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Distributed transactions and database mirroring – why they don't work together Background processes that can cause I/Os even with no connections to the server How to restore backups [...]
What can cause log reads and other transaction log questions
Posted on: December 9, 2009 4:14 pm
Earlier today there was a question on SQL Server Central where someone wanted to know what could be causing so many reads on their transaction log. I was asked to chime in by fellow MVP Jonathan Kehayias (who also sent me some questions that I've answered in this post – thanks Jon!), so I did, with [...]
Things to consider when setting up database mirroring
Posted on: October 14, 2009 8:27 pm
Over the last week or so I've been helping the Product Support folks at Microsoft write a new KB article which discusses some of the things you should take into consideration when setting up database mirroring. It discusses memory, CPU, I/O bandwidth, network bandwidth, and transaction volume. One of the most interesting things it discusses [...]
SQL Server 2008 High Availability whitepaper published on MSDN
Posted on: September 28, 2009 12:42 pm
The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It's a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It's chock-full of links to other whitepapers, technical articles and Books [...]
TechNet Magazine: October 2009 SQL Q&A column
Posted on: September 26, 2009 9:50 pm
(First blog post from Poland! We're here to present at the Microsoft Poland Technology Summit.) The October edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Unexpected consistency checks? How much space is each database using in the buffer [...]
Adding geo-redundancy to failover clustering
Posted on: August 26, 2009 12:07 pm
A few weeks ago I kicked off a survey on how you add geo-redundancy to a failover cluster (see here for the survey). The results as of 8/26/09 are as follows: So why is this interesting? Well, many people will suggest failover clustering as the best way to provide high-availability for a database (or [...]
SQL Quiz #5: SANs and mirroring
Posted on: August 21, 2009 7:18 pm
There's another SQL Quiz (from Chris Shaw) doing the rounds where people blog the answer and then tag someone. This I got tagged by two people (Jason Massie and Gail Shaw) in the same day for the same quiz (albeit over a week ago). They either think I'm going to say something profound or funny, [...]
TechNet Magazine: June 2009 SQL Q&A column
Posted on: May 1, 2009 8:08 am
The June edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Combining index and statistics maintenance How some Enterprise-only features can prevent a database restoring on lower editions Why database mirroring can switch between SYNCHRONIZED and SYNCHRONIZING Use a [...]
Rolling upgrade using database mirroring (and a bug in 2008 RTM)
Posted on: February 24, 2009 11:00 am
While trawling through the latest 2008 Books Online this morning to answer a question, I noticed a new section that I hadn't seen before, which explains in detail how to perform a rolling upgrade with database mirroring. The link to the MSDN page is http://msdn.microsoft.com/en-us/library/bb677181.aspx and below I've linked to the flowchart from that page. Books [...]
TechNet Magazine: February 2009 SQL Q&A column
Posted on: January 16, 2009 10:15 am
OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to [...]
TechNet Magazine: December 2008 SQL Q&A column
Posted on: November 12, 2008 11:51 am
It's really scary how quickly time flies – seems like it was just last week when I last blogged about TechNet Magazine (actually it was a month ago when I blogged about my 2008 Change Tracking article - see here). Anyway, a new issue of TechNet Magazine has just come out and this one has the [...]
Conference Questions Pot-Pourri #9: Q&A around compression features
Posted on: November 11, 2008 9:29 am
Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have [...]
TechNet Magazine: October SQL Q&A column
Posted on: September 23, 2008 1:07 am
It’s been almost two weeks since my last post as we’ve been offline in Scotland (another photo post to follow – I owe you two now…) but now we’re back for the crazy Fall conference and teaching season. The latest installment of my regular Q&A column in TechNet Magazine is available at http://technet.microsoft.com/en-us/magazine/cc895648.aspx. This month [...]
SQL Server2008: New whitepaper on combining transactional replication and database mirroring
Posted on: September 2, 2008 8:05 pm
I just heard today that the first whitepaper I’ve written for Microsoft has been published! The abstract is: SQL Server Replication: Providing High-Availability using Database Mirroring This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored [...]
In Print: TechNet Magazine June 2008 SQL Q&A Column
Posted on: May 19, 2008 7:06 pm
My first magazine article is in print! I’ve taken over the bi-monthly SQL Q&A column for TechNet Magazine and I just received the June magazine in the mail today with my first column in it. Topics covered are: Creating corruption and using page checksums The shrink-grow-shrink-grow trap How many databases can be mirrored per instance [...]
Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials
Posted on: March 12, 2008 5:23 pm
A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* [...]
TechNet Radio interview with Paul and Kimberly on SQL Server 2008 – Part 1
Posted on: March 5, 2008 2:31 am
Wow – almost 10 days without a blog post – that must be a record for me! :-) Never fear – I'll be posting more over the next couple of weeks. Kimberly flew off to India yesterday to teach some Microsoft classes and unfortunately I couldn't join her this time as I'm teaching 3 classes [...]
SQL Server 2008 JumpStart
Posted on: February 15, 2008 12:38 am
Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft [...]
More new whitepapers: Database Mirroring and Log Shipping
Posted on: January 11, 2008 2:02 am
Just got an email notification of a new whitepaper from the SQL Customer Advisory Team on Database Mirroring and Log Shipping Working Together. It covers: Converting a log shipping setup to a database mirroring partnership Setting up log shipping to a 3rd destination (i.e. warm standby to go with the mirroring hot standby) Swapping the [...]
Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?
Posted on: December 12, 2007 6:07 pm
While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – [...]
Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?
Posted on: November 16, 2007 3:31 am
Here’s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session [...]
Conference Questions Pot-Pourri #4: How many databases can you really mirror per instance?
Posted on: November 9, 2007 11:58 pm
This is a question I was asked multiple times over the last week: the Microsoft guidelines for database mirroring say not to mirror more than 10 databases per instance – why is that and is it true? The answer is my favorite 'it depends!'. The number 10 is a rough guess at the sweet-spot for [...]
Conference Questions Pot-Pourri #2: Database mirroring
Posted on: November 5, 2007 7:45 pm
Sitting here in our Disaster Recovery class at SQL Connections and Kimberly’s on till lunch so I’m banging out a quick blog post covering the database mirroring (DBM) specific questions. Q1) Can I use IP addresses instead of server names when using the DBM Monitor? A1) Unfortunately not. Q2) Is there [...]
Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?
Posted on: October 25, 2007 5:04 pm
(Been a few days since I posted – had some real work to do :-) Today I'll post a few things from the queue that's been building up) This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored. A customer has a maintenance plan that involves [...]
More on Database Mirroring performance and index maintenance
Posted on: October 19, 2007 12:37 am
There’s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [...]
SQL Server 2008: Performance boost for Database Mirroring
Posted on: October 11, 2007 12:06 am
As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the [...]
Search Engine Q&A #3: Database mirroring failover types and partner timeouts
Posted on: October 1, 2007 11:31 pm
Ok – I’m on a roll today so to finish off I’d like to repost some info about database mirroring failover types (including how you may not actually get a failover when you expect it) and a tip for how to avoid unwanted failovers when combining clustering and mirroring. Mirroring failovers have cropped up several [...]
SQL Server 2008: New Performance Counters for Database Mirroring
Posted on: October 1, 2007 8:31 pm
Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received/Sec Number of bytes received per second. Bytes Sent/sec Number [...]
Search Engine Q&A #2: Moving a database while Database Mirroring is running
Posted on: September 28, 2007 3:27 pm
This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the [...]
SQL Server 2008: Automatic Page Repair with Database Mirroring
Posted on: September 27, 2007 10:53 pm
One of the hottest features in SQL Server 2005 is database mirroring, and it's helped many companies implement successful and relatively inexpensive high-availability strategies. In SQL Server 2008, Database Mirroring has been enhanced in several ways – one of which is the ability to automatically repair corrupt pages! This feature is based on the fact that the [...]
