In Recovery... | Database Mirroring

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 from a file containing multiple appended backups
The perennial problem of production databases being too large to restore in development

Check it out at http://technet.microsoft.com/en-us/magazine/ff458345.aspx.

Categories:
Backup/Restore | Database Mirroring | IO Subsystems | Shrink | TechNet Magazine

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 a list of everything I could think of. I thought it would make for a good post, so here it is, with a few more things I remembered while writing the post.

Before I start, if you're not comfortable talking log records and transaction log architecture, see my TechNet Magazine article on Understanding Logging and Recovery, which explains everything clearly, including how having too many VLFs can affect operations on the log that have to scan VLFs.

Each of these things can cause reads of the log:

Transaction rollback: when a transaction has to roll back (either because you say ROLLBACK TRAN or something goes wrong and SQL Server aborts the transaction), the log records describing what happened in the transaction have to be read so that their effects can be removed from the database. This is explained in the TechNet Magazine article. Note that it doesn't matter if you're using explicit transactions or not (i.e. BEGIN TRAN), SQL Server always starts a transaction for you (called an implicit transaction) so that it can put a boundary on what needs to be rolled back in case of a failure.
Crash recovery: crash recovery must read the transaction log to figure out what to do with all the log records in the active portion of the log (all the way back to the earlier of the most recent checkpoint or the start of the oldest active transaction). The log is read twice - once going forward from that oldest point (called the REDO phase) and then going backwards (called the UNDO phase). Again, this is explained in great depth in the article.
Creating a database snapshot: a database snapshot is a point-in-time view of a database. What's more, it's a transactionally consistent point-in-time view of a database - which means that, essentially, crash recovery must be run on the real database to create the transactionally consistent view. The crash recovery is run into the database snapshot, the real database isn't affected - apart from having all the active transaction log read so that crash recovery can run.
Running DBCC CHECKDB: creates a database snapshot by default on 2005 onwards, and runs the consistency checks on the snapshot. See above. There's a much more detailed description, including how this worked in 2000, in the first part of the 10-page blog post CHECKDB From Every Angle: Complete description of all CHECKDB stages.
Transaction log backups: this one's kind of obvious. A transaction log backup contains all the transaction log records generated since the last log backup finished (or since the log backup chain was established). To back up the log it has to read it. What's not so obvious is that a log backup will also scan through all the VLFs in the log to see if any active ones can be made inactive (called clearing or truncating the log - both misnomers as nothing is cleared and nothing is truncated). See my TechNet Magazine article on Understanding SQL Server Backups and in the blog post Importance of proper transaction log size management.
Any kind of data backup: (full/differential backup of a file/filegroup/database). Yup - data backups always include transaction log - so the backup can be restored and give you a transactionally consistent view of the database. See Debunking a couple of myths around full database backups and More on how much transaction log a full backup includes for details if you don't believe me.
Transactional replication: transactional replication works by harvesting committed transactions from the transaction log of the publication database (and then sending them to the subscriber(s) via the distribution database - beyond the scope of this post). This is done by the Log Reader Agent job, running from the Distributor. It needs to read all the log records generated in the publication database, even if they're nothing to do with the publications. More log equals more reads. My whitepaper on combining database mirroring and transactional replication in 2008 has more details on this stuff, as does Books Online.
Change data capture (in 2008): CDC uses the transactional replication log reader agent to harvest changes from the transaction log. See above. This means the CDC can cause the log to not be able to clear properly, just like transactional replication or database mirroring - see my blog post Search Engine Q&A #1: Running out of transaction log space for more details. Note the I didn't say Change Tracking - it uses a totally different mechanism - see my TechNet Magazine article on Tracking Changes in Your Enterprise Database for more details.
Database mirroring: DBM works by sending physical log records from the principal to the mirror database. If the mirroring sessions drops out of the SYNCHRONIZED state, then the log records won't be able to be read from memory and the mirroring subsystem will have to get them from disk - causing log reads. This can happen if you're running asynchronous mirroring (where you're specifically allowing for this), or if something went wrong while running synchronous mirroring (e.g. the network link between the principal and mirror dropped out, and a witness wasn't configured or the principal could still see the witness - again, beyond the scope of this post). Regardless, this is called having a SEND queue on the principal.
Restoring a backup: whenever backups are restored, even is you've said WITH NORECOVERY, the REDO portion of recovery is run for each restore, which reads the log.
Restoring a log backup using WITH STANDBY: in this case, you've essentially said you'd like recovery to run, but not to affect the transaction log itself. Running recovery has to read the log. For more info on using WITH RECOVERY, NORECOVERY, or STANDBY, see my latest TechNet Magazine article on Recovering from Disasters Using Backups, which explains how restores work.
A checkpoint, in the SIMPLE recovery mode only: see my blog post How do checkpoints work and what gets logged for a description of what checkpoints are and what they do. In the SIMPLE recovery mode, checkpoints are responsible for clearing the log (described with links above) so must read through all the VLFs to see which can be marked inactive.
When processing a DML trigger (on 2000): (thanks to Clay Lenhart for the comment that reminded me of this). In SQL Server 2000, the before and after tables that you can process in a DML trigger body are actually found from looking at the log records generated by the operation that caused the trigger to fire. My dev team changed this in 2005 to store the before and after tables using the version store, giving a big perf boost to DML trigger processing.
Manually looking in the log (with DBCC LOG or the table-valued function fn_dblog): this one's pretty obvious.

Phew - a lot of things can cause log reads, the trick is knowing which one it is!

As you can see, there could be a lot of activity reading from your log as well as writing to it, which could cause an IO bottleneck. Make sure that the IO subsystem on which you place the log file (note: you don't get ANY performance benefit from having multiple log files) can handle the read and write workload the log demands. RAID 1 or RAID 10 with a bunch of spindles to spread the IOs out (note/warning/achtung: that's a big generalization - don't reply with a comment saying it's wrong because you've seen something different - different scenarios have different demands), and a proper RAID configuration (64k multiple for a stripe size, NTFS allocation unit size, volume partition alignment).

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 is exactly how many worker threads are required on the principal and mirror for each mirrored database.

Check it out at: http://support.microsoft.com/kb/2001270 (the formatting looks a little weird in the final online version, assuming they'll fix that up).

Enjoy!

Categories:
Database Mirroring | High Availability

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 Online sections and also presents my methodology for planning a high-availability strategy.

You can get it at http://msdn.microsoft.com/en-us/library/ee523927.aspx.

Enjoy!

Here's the table of contents:

Introduction
Causes of Downtime and Data Loss
- Planned Downtime
- Unplanned Downtime and Data Loss
Planning a High-Availability Strategy
- Requirements
- Limitations
- Technology Evaluation
SQL Server 2008 High-Availability Technologies
- Logging and Recovery
- Backup, Restore, and Related Technologies
  - Partial Database Availability and Online Piecemeal Restore
  - Instant File Initialization
  - Mirrored Backups
  - Backup Checksums
  - Backup Compression
- Other Single-Instance Technologies
  - Online Operations
  - Database Snapshots
  - Hot-Add Memory and CPU
  - Resource Governor
- Multi-Instance Technologies
  - Log Shipping
  - Transactional Replication
  - Database Mirroring
  - Failover Clustering
  - Combining Multi-Instance Technologies
  - Virtualization
Mitigating the Causes of Downtime and Data Loss
High-Availability Features Supported by SQL Server 2008 Editions
Conclusion

(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 pool
Difference between SUSPECT and RECOVERY_PENDING
Why database mirroring failure detection isn't instant

Check it out at http://technet.microsoft.com/en-us/magazine/2009.10.sqlqa.aspx .

Categories:
CHECKDB From Every Angle | Database Mirroring | Memory | TechNet Magazine

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 group of databases). And it is a great technology to protect against server failure, but there's only a single copy of the database, which is the Achilles' heel of failover clustering. If that copy of the database is damaged, the application is down unless there's another copy of the database available. This is where providing geo-redundancy comes in. With that in mind, I'm surprised at the percentage of respondents that don't provide any geo-redundancy at all.

There are a bunch of options for providing a redundant copy of a database that is hosted on a failover cluster, with pros and cons to each, and that's what I'm going to spend the rest of this post on.

SAN replication: This is where the SAN hardware itself mirrors all write I/Os to a remote SAN, thus maintaining a remote copy of the database. The hardware has to provide disk-block size and write-order preservation; otherwise the database on the remote SAN could become corrupt. Imagine if write-ordering was not preserved and some data pages write I/Os were completed on the remote SAN before log records write I/Os (thereby breaking the write-ahead logging protocol) - recovery wouldn't be able to work properly! This mechanism requires a remote SAN, a second failover cluster connected to the remote SAN, a network including both clusters, and a big, fat network pipe between the two SANs. The bigness and fatness of the pipe depends, of course, on how many write I/Os are performed on the local SAN, and whether the SAN replication is synchronous or asynchronous.

Synchronous replication requires that the I/O is completed on the remote SAN and acknowledged back to the local SAN before the local I/O can be acknowledged to the local server. If the network bandwidth and latency can't support the volume of write I/Os trying to be replicated to the remote SAN, the I/Os will start to queue up and delays will be incurred on the local server. This will lead to the workload slowing down as SQL Server has to wait longer and longer for I/Os to complete. Now, with synchronous replication you have the guarantee that the remote copy of the database is completely in-sync with the local copy, so if a failure occurs, no committed data will be lost. If the network can't keep up though, you may have to switch to asynchronous replication. This means the local I/Os don't have to wait for the remote I/Os to complete, and so no performance penalty is incurred. BUT as the replication is now asynchronous, committed data may be lost if the local copy of the SAN is damaged.

Apart from the potential for performance problems with SAN replication, it's also very expensive - as another SAN, another cluster, and some beefy network hardware/bandwidth is required. This isn't a technology I'd expect a small company to be using or considering. Finally, the portion of the remote SAN that's being replicated to cannot be accessed at all. On the MAJOR plus side, all databases on the SAN are replicated at once, without having to setup a technology to provide a redundant copy of each. For application ecosystems that include multiple databases, this is what I like to recommend.

Log shipping: This is the simplest way to maintain a redundant copy of the database - it's just backup log, copy, restore log; repeat. It works seamlessly with failover clustering and is really easy to setup and maintain. The only problem with this is that you open yourself up to data loss, as a log shipping secondary is usually not right up-to-date with the primary. You can use the secondary for reporting/querying by restoring the log backup WITH STANDBY (which requires a little more configuration, but not much), and you can protect against accidental data damage by having a secondary with a load-delay configured, so the database is, say, 8 hours behind the primary. In my experience, this is the most common technology that's used in conjunction with failover clustering as it's the cheapest and easiest. On the downside, it's a single database solution so its not suitable for complicated application ecosystems.

Transactional replication: This isn't very commonly used at all, although again, it works seamlessly with failover clustering after a failover. The reason this isn't used very often for geo-redundancy is that transactional replication doesn't provide database-level redundancy, only table-level. It's also much more complicated to setup and troubleshoot when things go wrong, plus there's varying latency between a transaction committing in the publication database and it being applied to the subscription database(s).

Database mirroring: Database mirroring is the only technology apart from SAN replication that can provide a zero data-loss solution when configured for synchronous operation. It works by shipping the log records from a database rather than the raw I/Os, so doesn't require anywhere near as much capital expenditure, but the network has to be able to cope with sending the log generated on the principal, otherwise performance on the principal can be affected. Mirroring is relatively easy to setup and maintain, and the mirror database can only be accessed, but only through a database snapshot. When combined with failover clustering, you need to be careful about setting the mirroring partner timeout, so that the local failover cluster gets a chance to fail over before mirroring does. Checkout my blog post on this: Search Engine Q&A #3: Database mirroring failover types and partner timeouts. You can configure database mirroring for synchronous or asychronous operation, with the same performance and data-loss exposure caveats as SAN replication. SQL Server 2008 provides log stream compression and automatic page repair, which make this more attractive (see SQL Server 2008: Performance boost for Database Mirroring and SQL Server 2008: Automatic Page Repair with Database Mirroring, respectively), but only supports a single database. I'm seeing this combination start to be used more, but again, it's a single database solution so isn't suitable for complicated application ecosystems.

Backups/homebrew: Good old backups can easily be used to provide a very low cost way of maintaining a redundant copy of a database, and if you think about it, this is really do-it-yourself log shipping. At the very least, databases should *always* be included in a backup strategy, no matter what other high-availability technology(s) you may have implemented.

3rd-party solution: There are a few non-Microsoft solutions for providing redundancy with failover clustering which don't involve traditional SAN replication. I'm not an expert in any of them, but I've heard of anecdotal issues with the two I mentioned in the survey and worked with customers who've had real issues with PolyServe (one of which I blogged about).

Summary

When you're planning a high-availability strategy, you always need to consider the limitations of technologies while evaluating them. The big limitation of failover clustering is that there's no redundant copy of the database so you need to add another technology to provide that. I've just finished writing a 35-page whitepaper for Microsoft on the high-availability technologies in SQL Server 2008, as well as how to go about planning a strategy. It will be published before PASS in November, but in the meantime, this should have given you lots of food for thought.

Next post - the next survey!

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, or maybe profoundly funny. Can you say something funnily profound? Ah, got it: profound or strange, or strangely profound, or profoundly strange. Whatever. On with quiz. I'll try not to disappoint.

Question 1: Do you feel that you have a reliable SAN Solution? If so what is the secret?

No. Well, that was easy, eh?

Ok, seriously - we don't have a SAN or any production databases per se, as we're a training and consulting company. We do have a lot of storage hardware (3 x DELL MD3000i's packed with 26TB (unformatted)), but it's not managed by a SAN. However, we do have a lot of clients that DO have SANs. So how do we know they have a reliable SAN solution? I guess there are a number of different factors off the top of my head, and I'm not a SAN expert:

Was it designed for the job it's doing?
Are there redundant components to protect against hardware failure?
Was it configured by someone who knows what they're doing, with that brand of SAN?
Was it load tested to ensure it's can handle the job it was designed for? Was SQLIOSim run to simulate overloading the SAN to flush out any issues?
At the time it was configured, was the firmware all up-to-date, with no known bugs? I saw 'at the time it was configured' because you have to be careful about willy-nilly upgrades to firmware in the various components. Someone that doesn't know what they're doing can destabilize a SAN by upgrading a piece of firmware that subtly changes the behavior.
Are page checksums configured on the SQL databases to help detect I/O problems? Are regular consistency checks being run?

I would say that a 'no' answer to any of these is cause for concern.

Question 2: Describe database mirroring in laymen’s terms.

I'll try a few different answers. You be the judge.

1) I could make this very, very simple and just say "It's really technical and you don't want to know". That's not really in the spirit of things though.

2) Imagine 2 seven year-old girls, in separate rooms (like my youngest daughter and one of her friends). Maybe even separate countries. Girl #1 is painting a picture, using the standard easel setup. There's a webcam pointing at the painting that girl #1 is doing. Girl #2 has a monitor and can see what girl #1 is doing. They're also on the phone with each other. Whenever girl #1 paints a brush-stroke, she can't paint any more until girl #2 has made the exact same brush-stroke and said "Done it." That's the synchronous part of mirroring. Girl #1 can't get ahead of girl #2. Asynchronous mirroring is where girl #1 doesn't have to wait for girl #2 to keep up. With a witness, there's a third girl, with two webcams and another phone...

Hmm - ok this analogy isn't working. It seemed so promising! Let's try again...

3) This came to mind after taking a shower this morning in the hotel north of Houston. Database mirroring is like having redundant hot-water heaters. If the hot water fails from one heater, the heat-operated valve flips and the hot water is drawn from the other hot-water heater. You need two hot water heaters, and a fast-operating valve. If the first hot water heater is fixed/warmed up again, you can manually switch the water-flow valve back. If both hot-water heaters are unavailable, no hot water. See, failover clustering won't work, because then you've only got one hot water heater, with redundant pipes coming out of it. And replication won't work because there's a lot of latency between the water leaving the hot water heater and reaching the shower head. Ok - got a bit carried-away there.

Luckily we have a redundant hotel across the street, so if there's no hot water by the time we return from a day of bird-watching on the Gulf Coast, we can move to the redundant hotel. Although that's a lot more hassle, and a lot slower than if this hotel had a redundant hot water heater...

PS Some other folks (that I know of) have replied to the quiz - here are links to their answers:

And you can follow me on Twitter at @PaulRandal

PPS I'm not tagging anyone - been too long since the quiz started I think - the usual suspects have all been tagged already.

Categories:
Database Mirroring | IO Subsystems | Jokes

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 database mirror for reporting

Check it out at http://technet.microsoft.com/en-us/magazine/dd797578.aspx.

Categories:
Database Maintenance | Database Mirroring | Involuntary DBA | TechNet Magazine

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 Online also has a lot of other "how-to" topics around database mirroring - here are some links:

2005 mirroring how-to topics: http://msdn.microsoft.com/en-us/library/ms175059(SQL.90).aspx
2008 mirroring deployment how-to topics: http://msdn.microsoft.com/en-us/library/bb510479.aspx
2008 mirroring administration how-to topics: http://msdn.microsoft.com/en-us/library/ms175059.aspx
2008 mirroring monitoring how-to topics: http://msdn.microsoft.com/en-us/library/cc280423.aspx

Kevin Cox of the SQLCAT team also just blogged about a customer upgrade from 2005 to 2008 and some of the issues they faced. Btw - if you're not subscribed to their blog, you definitely should be - lots of cool stuff.

And now the bug. In 2008 RTM, if your database contains full-text then mirroring will not work when you perform a rolling upgrade. This is explained in KB 956017 (with a trace-flag workaround) and KB 957816, which points at 2008 RTM CU1 that has the fix in.

Categories:
Database Mirroring | High Availability | Install / Setup | SQL Server 2008 | Bugfixes

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 rebuild a transaction log?

Check out the column at http://technet.microsoft.com/en-us/magazine/2009.02.sqlqa.aspx

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 latest installment of my bi-monthly SQL Q&A column.

This month's topics are:

How row-overflow columns can lead to poor range scan performance, even on completely defragmented indexes.
Combining database mirroring and failover clustering without undesired failover behavior.
Adding differential backups to a full+log backup strategy to lower recovery time.
Memory settings for multi-instance failover clustering

You can get to the column online at http://technet.microsoft.com/en-us/magazine/dd228989.aspx.

Enjoy!

PS If you have any ideas for what would make a good SQL Q&A topic, please drop me a line - paul@sqlskills.com

Categories:
Backup/Restore | Database Mirroring | High Availability | Performance | TechNet Magazine

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 compression enabled will be logged in their row compressed forms, even if page compression is enabled.

Does data compression compress LOB columns?

No. There is no native compression support for any LOB columns (n/text, image, n/varchar(max), varbinary(max), XML), whether stored in-row or out-of-row. There's also no native compression support for FILESTREAM data.

Does log shipping use compression to compress the logs being shipped?

Log shipping does not ship transaction logs - it ships log *backups*. If backup compression is enabled for the instance hosting the log shipping primary database, or the log shipping job is changed to enable backup compression, then the log backups will be compressed and less data will be sent over the wire to the log shipping secondary(s).

Is backup compression the same as log stream compression with database mirroring?

No. Backup compression compresses backups (see my previous blog post here). Log stream compression with database mirroring compresses transaction log records before sending them between the principal and the mirror (see my previous blog post here).

Should I just turn on backup compression at the instance level?

Not necessarily. It depends whether the majority of database on the instance will benefit from backup compression. Backup compression (and any compression algorithm) uses CPU whether a decent compression ratio is achieved or not. Check what compression ratio is achieved first and then enable backup compression if its worth it. Otherwise, just enable it for individual databases.

Does data compression use the same algorithm as backup compression?

No. Backup compression uses a proprietary block-based compression algorithm that is part of Windows. Data compression uses up to 3 algorithms, depending on the level of compression configured. Row compression just makes all non-LOB columns into variable-length columns. Page compression does row compression, then common-prefix compression for each table columns, then common-value dictionary compression for each page. Details can be found at the following BOL sections: Row Compression Implementation and Page Compression Implementation.

Hope this helps!

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 I cover the following topics:

How backups and restores work and why the times for each may differ
The difference between log shipping and database mirroring around BULK_LOGGED operations
How other factors apart from log backups can contribute to excessively large transaction logs
Why database repair exists and why it shouldn't be used
Tracking index usage with sys.dm_db_index_usage_stats

Enjoy!

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 environment, the effect of mirroring partnership state changes, and the effect of mirroring failovers on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database.

Although brief overviews are given of both replication and database mirroring, it is easier to understand this white paper if the reader has some experience with one or both of these technologies, and has at least a rudimentary knowledge of database concepts such as transactions.

You can download it from http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/ReplicationAndDBM.docx and I'll put a link in our whitepapers page.

Enjoy!

Categories:
Database Mirroring | High Availability | Replication | SQL Server 2008 | Whitepapers

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
A tip on changing the default server port, from Jens Suessmeyer

I've also just completed a feature article for either the July or August issue dealing with database maintenance for the 'involuntary' DBA - more details when it gets published.

If you don't get the print version of TechNet Magazine, you can get to this month's SQL Q&A column at http://technet.microsoft.com/en-us/magazine/cc510328.aspx. There may not be anything new if you've been following my blog for a while, but if you've just started, it's worth a quick look.

Enjoy!

PS Let me know if you've got any good questions - I've already completed the August column but I'd like to hear of any questions you may have for later columns.

Categories:
Corruption | Database Maintenance | Database Mirroring | Shrink | TechNet Magazine

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* of behavioral changes since then (with more planned for CTP-6 Refresh and RTM), but if you want a high-level overview of a bunch of the features (albeit in bullet-point summaries on slides) then this is a good place to start.

For me, what's *REALLY* cool is that the site also has a downloadable VPC plus lab manuals for all of the AlwaysOn High-Availability hands-on labs that SQLskills.com wrote. We originally wrote these labs for SQL Server 2005 and I updated them all for CTP-5. The VPC has a long lab on each of the following:

Database Snapshots
Data Recovery and Preventative Techniques
Instant Initialization
Peer-to-Peer Replication (including the new Topology Wizard I blogged about here)
Table and Index Partitioning
Snapshot Isolation
Online Operations
Database Mirroring (including a demo I wrote of Automatic Page Repair, described here)
Service Oriented Database Architecture

There is some great depth in each of these - Kimberly blogged more info about the exercises in each lab here. There's also another VPC image with some higher-level labs on a variety of 2008 features and written by a number of different people- including some labs on Policy-Based Management and Performance Data Collection that Kimberly wrote.

So - where can you get these from? Go to http://sqlserver2008jumpstart.microsofttraining.com/ and hit the Download link on the right-hand side. Register and then you can get to the materials. The AlwaysOn VPC image is Collection 2 at the bottom of the page, and you'll see all the slide decks as you scroll down the page.

Enjoy!

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 myself:

an internal Microsoft class on Designing for High Availability
another internal Microsoft class on SQL Server 2008 for DBAs (similar to the JumpStart class I posted about here)
3 days of content for the new Microsoft Certified Architect: Database qualification - see the Microsoft Learning site here for details

Anyway - the subject of this post is to let you know that last week, Kimberly and I did two interviews for TechNet Radio on SQL Server 2008 technologies. Part 1 has just been released where we discuss security and availability features. You can get to it by going to the March 4th 2008 show here. Tune in and find out how I lull myself to sleep when Kimberly's out of town...

Enjoy!

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 that they'll use to train their SQL experts around the world on the new release. It's been very interesting watching the features develop through the CTPs (especially since I left the fold last August) - and making demos work on pre-release builds of the CTPs.

Teaching the course was a *blast* - the thing I love about teaching a really geeky crowd is the plethora of great questions and opportunities for going deep with explanations. Our team actually wrote and delivered the concurrently presented Developer and BI tracks as well. As you can see from the list below (and this is just the features a DBA needs to use/know about), SQL Server 2008 isn't a dot release of Yukon at all, as some people have suggested. Over the three days we covered:

Database Mirroring (D)
Backup Compression
Peer-to-Peer Replication (D)
Transparent Data Encryption (D)
Extensible (Off-Box) Key Management
All Actions Audited (D)
Policy-Based Management
Resource Governor (D)
Extended Events (D)
Spatial Indexes
Integrated Full-Text Search
Sparse Columns (D)
Filtered Indexes
Change Tracking
Change Data Capture (D)
FILESTREAM (D)
Performance Data Collection
Query Optimizer Enhancements
Data Compression (D)
Service Broker
Partition-Level Lock Escalation (D)

The features marked with a (D) are ones I demo'd during the course (Kimberly demo'd a bunch of the others - especially the tools features). Some of the demos were challenging to make work in time as we only got a pre-CTP6 build mid-January just before we headed off to China.

So why am I posting this? Well, a bunch of these features are in CTP-6, which should be just around the corner, and I have some easy-to-understand demos of them that I'll be posting here over the next month or so. Also, if this course sounds interesting, Kimberly and I will be teaching it in various configurations over the next year - starting with SQL Connections in April, a soon-to-be-announced class in Iceland in March, and the ITPro portion of TechEd in June.

Watch this space starting next week (today's the last day of six straight weeks of teaching for us so this weekend's a break :-))

SQLskills.com has released its first ever utility! :-)

After several years of being asked to make available the Dual Database Monitor application (that's present inside the popular AlwaysOn Hands-On Labs we give out), I've spent a bunch of time making it configurable, work outside the VPC environment, and available in an easy kit form for people to use. It comes with easy-to-follow instructions and example SQL scripts to get you going. It supports SQL Server 2005 and SQL Server 2008.

This is an invaluable tool to use when setting up a Database Mirroring partnership to check that mirroring is working ok, without having to hack up your own application to do it. It can also be used to monitor two nodes of a peer-to-peer replication topology (or more using multiple instances of the monitor). It comes in two versions - single-user or unlimited use within a single company.

Check out the DDM webpage here for more details and ordering info.

Categories:
Database Mirroring | Products | Replication

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 roles of the mirroring server and the log shipping secondary server

It's short at 8 pages but has some good info in it. It's available to download here.

While I was poking about for a better download location, I discovered another new whitepaper (from last year) on database mirroring, this time on Implementing Application Failover with Database Mirroring. Its concerned with how make applications failover gracefully when a mirroring failover happens. Again, its not very log but there's some useful code examples for ADO.NET and JDBC. You can download it here.

I've added both of these to our whitepapers page too. Enjoy!

Categories:
Database Mirroring | Log Shipping

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 - check it out here.

PS There's been a ton of interest in the slide deck idea I had so we'll be going ahead with that. Look for an announcement sometime in the first few months of next year about how to get them. Thanks to everyone that replied!

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 is running synchronously with the witness to allow automatic failover. A disaster happens to the site where the mirror and witness are, so the principal database is unavailable. I can't seem to access the principal at all to bring it back online by removing the witness and the mirror and witness won't be available for hours. What can I do?

A) The behavior you're seeing (the principal database becoming unavailable) is expected. In a mirroring configuration with a witness, the principal needs to have quorum with (i.e. be able to see) at least one of the other partners, either the mirror, the witness, or both. If it can't see either, it doesn't know whether the witness and mirror can still see each other and the mirror may have brought itself online as the new principal. (Kimberly likes to say that the principal thinks the witness and mirror are conspiring against it :-)) In this case though, the customer knows that the mirror and witness are actually down and so he wants to bring the principal database back online.

I repro'd this situation in a VPC with three SQL Server 2008 instances running mirroring between them (the behavior is exactly the same in 2008 and 2005). I did a net stop on the mirror and witness servers and the principal database went offline. Trying to get into the principal database results in the following error:

USE TicketSalesDB;

GO

Msg 955, Level 14, State 1, Line 1

Database TicketSalesDB is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened. Check the partner and witness connections if configured.

This is what I'd expect. The customer tried to remove the witness so let's try that:

ALTER DATABASE TicketSalesDB SET WITNESS OFF;
GO

Msg 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "TicketSalesDB" is available. Reissue the command when at least one of the instances becomes available.

That doesn't work either because removing the witness needs to happen on one of the partners as well as the principal. The only way to get out of this situation is to break the mirroring partnership completely.

ALTER DATABASE TicketSalesDB SET PARTNER OFF;
GO
USE TicketSalesDB;
GO

Command(s) completed successfully.

Categories:
Conference Questions Pot-Pourri | Database Mirroring | Disaster Recovery

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 the majority of customers based on hitting a thread limit on 32-bit machines. The factors that need to be considered are:

How much memory do the principal and mirror instances have? (hopefully the same)
How much processing-power do the principal and mirror instances have? (hopefully the same)
How much bandwidth does the IO subsystem have on the mirror instance? (hopefully the same as on the principal)
How much transaction log does the workload on each database generate?
How much network bandwidth is available between the principal and the mirror instances?

The last two factors are the most critical. If the network bandwidth available between the two instances is not enough to handle the combined transaction log generation rate per second from all databases being mirrored then performance will drop on the principal databases. SQL Server 2008 does alleviate some of this with log stream compression - see here for details. The next most critical thing to consider is the memory and thread requirements for mirroring - each mirrored database takes one thread plus some memory, so on low-powered servers, lots of mirrored databases may be too much load on the server when combined with the regular workload.

Here are some examples that I've seen:

A customer with 25 databases, all of which have very small amounts of activity, and not all at the same time, has them all mirrored with no problem.
A customer with only 3 heavily-loaded databases, but without a great network connection, that can barely mirror one of the databases without the lack of network bandwidth causing workload degradation.

The key to success here is to do the log generation calculation and then if it seems that the available network bandwidth will support the number of databases you want to mirror, test it first before relying on it in production.

I guess the bottom-line here is that any broad guidance is only that - your mileage may (and probably will) vary. Always do your own calculations and testing.

[Edit 10/15/2009: Checkout the new KB article I helped write that discusses this in detail: http://support.microsoft.com/kb/2001270]

Categories:
Conference Questions Pot-Pourri | Database Mirroring | Performance

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 any in-built throttling mechanism in DBM to allow the mirror to catch-up when synchronizing a synchronous mirroring session?

A2) Yes, if there's more than 1MB of transaction log on the principal that hasn't been sent to the mirror, the mirroring session state will be switched from SYNCHRONIZED to SYNCHRONIZING and the principal itself will start adding a few milliseconds delay to transaction commits until the amount of unsent log drops below 1MB

Q3) Are there any tips when setting up a mirroring session using backups?

A3) Yes, make sure that all the backups are restored WITH NORECOVERY on the mirror. The database has to be unrecovered otherwise the mirroring session cannot start and you're back to square one with restoring the mirror database.

Q4) What are the performance considerations with DBM - both in terms of the impact on the application workload and on DBM itself?

A4) Here are some links to resources that discuss this:

Whitepaper: Database Mirroring: Best Practices and Performance Considerations
Whitepaper: Database Mirroring: Alerting on Database Mirroring Events
MSDN webcast - look at Sessions 8 and 9: TechNet Webcast Series for the ITPro - Series Links
TechEd session based on the 1st whitepaper above

Q5) Any other resources?

A5) Blog posts...

Enjoy!

Categories:
Conference Questions Pot-Pourri | Database Mirroring

(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 running regular ALTER INDEX ... REORGANIZE on a 100GB clustered index to remove fragmentation. Three weeks ago they added database mirroring, with the database setup for synchronous mirroring. Every so often, they see the state of the mirror change from SYNCHRONIZED to SYNCHRONIZING and then a bit later back to SYNCHRONIZED. What's going on? Once a synchronously-mirrored database is synchronized, it should ever get out of sync, right?

Well not quite - if the communication link between the principal and the mirror is broken, then the mirror becomes unsynchronized. The exact behavior in this situation depends on how mirroring is setup and what's failed:

If there's no witness instance, then transactions will continue on the principal database but the transaction log starts to grow, because the transactions can't be cleared from the principal's log (even after a log backup) until they've been sent to the mirror. The database is running 'exposed'. Once the link is reestablished, the mirror while synchronize again.
If there's a witness, and the witness can still talk to the principal, then everything continues as in #1
If there's a witness, and the communication link between it and principal is also broken, the the principal will stop serving the database - transactions will stop. In this case, if the mirror and the witness can still see each other, then a failover will occur.

There are some great Books Online entries that describe all of this - see http://msdn2.microsoft.com/en-us/library/ms179344.aspx to start with.

The customer had situation #1. Every so often the mirror would change state and it seemed to coincide with the defrag job. Looking in the error log shows messages like:

2007-10-24 11:43:36.21 spid23s Error: 1474, Severity: 16, State: 1.

2007-10-24 11:43:36.21 spid23s Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://roadrunnerpr.sqlskills.com:5022'.

So the network link was dying sometimes when the defrag was running - that explains the switch between SYNCHRONIZED and SYNCHRONIZING. Why the network link was dying is still under investigation but it seems like the additional transaction log generated by the defrag job was causing the network to become overloaded and some component of it wasn't behaving correctly under load.

There are a few things to learn from this:

Not only do you need to make sure that your IO subsystem can handle the load on it correctly, you also need to make sure your network can handle the load on it. There are a bunch of tools available to stress-test network paths - one simple one is TrafficEmulator.
When you're running on your test system before going into production, make sure you test *everything* as if you were running in production - including maintenance jobs because they can add significant load to a production system.
When you implement an HA solution such as mirroring, consider all the ways that transaction log will be generated when figuring out the required network bandwidth to support your HA configuration - something like a defrag or rebuild can cause an enormous spike in log generation

Categories:
Database Maintenance | Database Mirroring | Search Engine Q and A

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 to the amount of transaction log generated and whether this causes a problem.

In synchronous mirroring, the additional log being generated could overload the network link between the principal and mirror. This will slow down the hardening of regular transactions in the mirror's transaction log, and thus led to a decrease in transaction throughput on the principal.
In asynchronous mirroring, the additional log being generated could again overload the network link - but this time there's no requirement for transactions to harden in the mirror before they can commit on the principal, so instead the SEND queue on the principal gets really large. Now, in asynchronous mirroring the SEND queue represents the amount of work that will be lost if a failover occurs, as its all the transaction log that hasn't yet been sent to the mirror. So, in asynchronous mode, a large index rebuild operation could lead to increase exposure to data loss in the event of a failover.

In SS2008, the log stream compression I blogged about here should go a long way to alleviating this problem. There are three ways I can think of to reduce the amount of transaction log generated by and index maintenance plan in SS2005:

Use a potentially less expensive (in terms of logging) solution for removing index fragmentation. Doing an index rebuild is guaranteed to generate an equivalent amount of transaction log to the size of the index being rebuilt, no matter how much fragmentation there is - because an index rebuild always rebuilds the entire index. The alternative is to do an index reorganize, either using my old DBCC INDEXDEFRAG or the new ALTER INDEX ... REORGANIZE. These will only generate transaction log when index pages are compacted and reorganized - so for less heavily fragmented indexes. There's no hard and fast rule here but I generally say where Logical Scan Fragmentation/Average Fragmentation in Percent from DBCC SHOWCONTIG/sys.dm_db_index_physical_stats, respectively, is less than 30%. You also need to consider page density too - but really this is a topic for a whole other post.
Be very selective on which indexes you choose to rebuild/reorganize. A lot of people have a maintenance plan that does this for every index every week, without checking whether the index is even fragmented or whether removing fragmentation for an index improves workload performance. Again, this a whole other topic but there is an old whitepaper for SS2000 I helped with that describes some of this - Microsoft SQL Server 2000 Index Defragmentation Best Practices.
Partition the tables/indexes so that the changing portion of the data is the only portion that's affected by index maintenance. If most of your data is read-only, there's no point in having it included in reindex/reorganize operations, right? Kimberly recently wrote a blog post about such an architecture here.

For more info on database mirroring performance considerations, checkout the whitepaper Database Mirroring: Best Practices and Performance Considerations. There's also a slide deck presentation based on this whitepaper that's been presented at various TechEds (I did it in China and Hong Kong last year and Kimberly did it in South Africa last year) - you can download it from the Hong Kong website here.

The bottom line is when mirroring is in the mix, you need to be more intentional with your database maintenance.

Categories:
Database Maintenance | Database Mirroring | Performance | Transaction Log

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 way mirroring works is by shipping all the transaction log from the principal to the mirror, and then replaying the log records on the mirror database. Network bandwidth can be a bottleneck for mirrored databases where the rate of transaction log generation is high enough to saturate the network. On synchronously mirrored databases (where a transaction cannot commit on the principal until all the transaction log for it has been hardened in the log of the mirror database), this can even lead to workload throughput degradation.

Log compression is a way to alleviate this problem. SS2008 Books Online states that compression rates of at least 12.5% are achieved - obviously the compression ratio could be much higher than that and is dependant on what's being compressed - i.e. the data that is being processed by the application. One of the tests they did for the post above shows a 5x compression ratio - that's pretty good.

The downside of this is that compression is not free - extra CPU must be used. The obvious CPU load increases comes from having to compress the transaction log on the principal and then decompress it again on the mirror before hardening it in the log. The non-obvious increase will come because the principal and mirror should end up being able to process more transactions/second, which means more CPU is needed.

This extra CPU could be a problem for some systems that are already pegged in terms of CPU load, and so they may actually see a drop in performance when log stream compression is enabled. Now, it's on by default when you upgrade to SS2008 but there is a way to turn it off. The blog post above divulges that there is a trace flag, 1462, that turns off log stream compression and effectively reverts the behavior back to SS2005.

Apart from the obvious benefits of this feature for systems that can afford the extra CPU, I'm excited because it may allow more mirrored databases to perform effective database maintenance - in the form of fragmentation control and index maintenance. When database mirroring is enabled, the database has to be running in the full recovery mode. This means that operations such as index rebuilds are fully logged - that's a lot of extra log being generated if you're used to switching to bulk-logged recovery mode to perform index operations. Of course, this isn't an issue for reorganizing an index (with ALTER INDEX ... REORGANIZE) as this has been fully logged (and has to be that way because of how it works) since I wrote the old DBCC INDEXDEFRAG for SS2000.

Anyway, for some customers the fact that these two operations are fully logged means that running them produces so much additional log that, in conjunction with the log from the workload itself, the network becomes a bottleneck for mirroring and it slows down both the maintenance and reduces the workload throughput. The addition of log stream compression means that for these systems, the log may be compressed enough so that the network is no longer a bottleneck and more regular fragmentation control can take place. This in turn will increase workload throughput as well - a double benefit!

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 times in my blog's search engine logs so this is an easy one to answer. This is based on a TechEd post from June on the Storage Engine blog.

Clustering, mirroring and altering the partner timeout

The first question is about combining failover clustering and database mirroring. A customer wanted to put the instance hosting the principal database on a failover cluster and have the mirror database on an unclustered server. The issue he saw with the setup was when something happened on the active cluster server and it failed over. The failover would take 60-90 seconds, which means the principal database would not be available during that time.

The problem stems from the way mirroring figures out whether to initiate a failover. The mechanism it uses is to have all SQL Server instances within a mirroring session send out 'pings' on each of its open mirroring connections every second. If an instance does not receive a ping from one of the other instances in the session within a defined time-out period (called the partner time-out), it considers the other instance as unavailable and takes the appropriate action. The default time-out period is ten seconds.

In the case above, when the cluster is failing over and the principal instance is unavailable for 60-90 seconds, the other instances in the mirroring session will time-out after ten seconds and initiate a mirroring failover. Although this is the correct behavior of mirroring, it is undesirable behavior for this customer. The solution to the problem is to change the partner time-out value - a facility that isn't well known.

To change the partner time-out value for a mirroring session, use the following code:

ALTER DATABASE mydatabase SET PARTNER TIMEOUT 90;

GO

The value at the end of the statement is the new partner timeout value in seconds. There are a couple of things to be aware of if you're going to do this:

You can only issue this statement on the principal server.
Be very careful not to set the time-out value too low otherwise you run the risk of triggering failovers becuase of false-failures - especially on heavily-loaded systems that may not be able to respond within the time-out period. In fact, if you specify a time-out value of 4 seconds or lower, SQL Server will automatically set the time-out to 5 seconds to help avoid such problems.

Database mirroring failover types

What are the different kinds of failures that can trigger mirroring failovers, and how quickly does the failover happen after the problem occurs? As with most questions I get, I can use my favorite answer of "It depends!" :-) Let's look at some examples of failures and see how quickly the failover occurs, in decreasing order of speed.

Fastest: The fastest possible failover occurs when the SQL Server instance crashes (so the mirroring connection endpoint no longer exists) but the operating system is still running. When the next ping comes from a partner instance, the OS knows that the network port (that was being used by the mirroring connection endpoint) is no longer being listened to and returns a failure. This immediately triggers a failover.
Fast: The next fastest failover occurs when the machine hosting the SQL Server instance crashes or shuts down (e.g. power supply cord pulled out of the back). In this case, nothing happens until the partner time-out period has expired (as there's no OS running to return the immediate failure) and then a failover will occur. By default this will be in ten seconds, but you can change this as I explained above.
Slow: A slow failover is when something happens to the server but it takes a while for the database to go offline. An example of this would be someone pulling out the transaction log drive on the principal server. Writes to the transaction log will start to queue up. After 20 seconds SQL Server will issue an IO warning but it isn't until 40 seconds has passed that SQL Server issues an IO failure and the database goes suspect. It's not until this point that the failover occurs - even though the partner timeout value is 10 seconds!
Maybe fast or not at all: This discussion all started by someone asking about how quickly a failover happens if a page checksum failure is detected. My answer was - it depends! If the page checksum failure happens during a query, then all that happens is that the checksum failure is reported, the query rolls back, and the connection is broken. It's only if the page checksum failure happens during a transaction rollback that a failover will be triggered. This is because a transaction rollback failure means the database is in a transactionally inconsistent state and has to be set to suspect mode - which triggers a mirroring failover.

So, don't assume that just because mirroring is setup that every failure will trigger a fast failover.

Categories:
Database Mirroring | Search Engine Q and A

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 of bytes sent per second.
Log Bytes Received/sec	Number of bytes of log received per second.
Log Bytes Sent/sec	Number of bytes of log sent per second.
Log Send Queue	Total number of bytes of log that have not yet been sent to the mirror server.
Pages Sent/sec	Number of pages sent per second.
Receives/sec	Number of mirroring messages received per second.
Redo Bytes/sec	Number of bytes of log rolled forward on the mirror database per second.
Redo Queue	Total number of bytes of hardened log that currently remain to be applied to the mirror database to roll it forward. This is sent to the Principal from the Mirror.
Sends/sec	Number of mirroring messages sent per second.
Transaction Delay	Delay in waiting for unterminated commit acknowledgement.

SQL Server 2008 now provides 21 counters, with the new ones highlighted in red. This info is taken from the 2008 July CTP 08Books Online that is downloadable here.

Name	Description
Bytes Received/sec	Number of bytes received per second.
Bytes Sent/sec	Number of bytes sent per second.
Log Bytes Received/sec	Number of bytes of log received per second.
Log Bytes Redone from Cache/sec	Number of redone log bytes that were obtained from the mirroring log cache, in the last second. This counter is used on only the mirror server. On the principal server the value is always 0.
Log Bytes Sent from Cache/sec	Number of sent log bytes that were obtained from the mirroring log cache, in the last second. This counter is used on only the principal server. On the mirror server the value is always 0.
Log Bytes Sent/sec	Number of bytes of log sent per second.
Log Compressed Bytes Rcvd/sec	Number of compressed bytes of log received, in the last second.
Log Compressed Bytes Sent/sec	Number of compressed bytes of log sent, in the last second.
Log Harden Time (ms)	Milliseconds that log blocks waited to be hardened to disk, in the last second.
Log Remaining for Undo KB	Total kilobytes of log that remain to be scanned by the new mirror server after failover. This counter is used on only the mirror server during the undo phase. After the undo phase completes, the counter is reset to 0. On the principal server the value is always 0.
Log Scanned for Undo KB	Total kilobytes of log that have been scanned by the new mirror server since failover. This counter is used on only the mirror server during the undo phase. After the undo phase completes, the counter is reset to 0. On the principal server the value is always 0.
Log Send Flow Control Time (ms)	Milliseconds that log stream messages waited for send flow control, in the last second. Sending log data and metadata to the mirroring partner is the most data-intensive operation in database mirroring and might monopolize the database mirroring and Service Broker send buffers. Use this counter to monitor the use of this buffer by the database mirroring session.
Log Send Queue KB	Total number of kilobytes of log that have not yet been sent to the mirror server.
Mirrored Write Transactions/sec	Number of transactions that wrote to the mirrored database and waited for the log to be sent to the mirror in order to commit, in the last second. This counter is incremented only when the principal server is actively sending log records to the mirror server.
Pages Sent/sec	Number of pages sent per second.
Receives/sec	Number of mirroring messages received per second.
Redo Bytes/sec	Number of bytes of log rolled forward on the mirror database per second.
Redo Queue KB	Total number of kilobytes of hardened log that currently remain to be applied to the mirror database to roll it forward. This is sent to the Principal from the Mirror.
Send/Receive Ack Time	Milliseconds that messages waited for acknowledgement from the partner, in the last second. This counter is helpful in troubleshooting a problem that might be caused by a network bottleneck, such as unexplained failovers, a large send queue, or high transaction latency. In such cases, you can analyze the value of this counter to determine whether the network is causing the problem.
Sends/sec	Number of mirroring messages sent per second.
Transaction Delay	Delay in waiting for unterminated commit acknowledgement.

Here's a little more explanation and what you can use these new performance counters to troubleshoot:

Log Bytes Redone from Cache/sec
- This measures how much of the transaction log in the redo queue is being read by the log redo task from the mirror's in-memory transaction log cache. Reading from the cache is a lot faster than having to read from the mirror's actual transaction log. Even though the log gets hardened on the mirror database's log disk, it does not need to be removed from the cache until the cache fills up with new transaction log from the principal.
- You could think of this as a cache hit ratio measure for the redo queue.
- If this number is lower than usual, it means that transaction log is arriving from the principal faster than the log redo task can roll forward the transaction log in the redo queue.
Log Bytes Sent from Cache/sec
- This is similar to the counter above. It measures how much of the transaction log being sent from the principal to the mirror is being read from the principal's in-memory transaction log cache. Sending from the cache is a lot faster than having to go to the transaction log itself and read from disk.
- You could think of this as a cache hit ratio for the send queue.
- If this number is lower than usual it means that the transaction log is being generated on the principal faster than it can be sent to the mirror.
Log Compressed Bytes Rcvd/sec
Log Compressed Bytes Sent/sec
- These are simple counters and can be used with the Log Bytes Sent/sec and Rcvd/sec counters to determine the compression ratio. Log stream compression is another enhancement in SQL Server 2008 that I'll cover in a future post.
Log Harden Time (ms)
- This measures the delay between the mirror server receiving a chunk of transaction log and it being hardened on the mirror database's log disk (i.e. the delay before the chunk of transaction log becomes part of the redo queue on the mirror server).
- If this number is higher than normal it means the mirror database's log disk is more heavily loaded and may be becoming saturated.
Log Remaining for Undo KB
Log Scanned for Undo KB
- The Books Online entries for these counters are self-explanatory.
- These counters give a way to monitor the undo phase after a failover occurs.
Log Send Flow Control Time (ms)
- This measures how long a mirroring connection had to wait before it could us the mirroring flow control buffer.
- If this number is higher than normal it means there is contention for the buffer, most likely because there are too many Database Mirroring partnerships running from a single instance.
Mirrored Write Transactions/sec
- As Books Online mentions, this counts the number of transactions in the principal database that had to wait for a commit record to harden in the mirror database's transaction log.
- If this value is lower than normal (for the same application workload) it means there is a bottleneck somewhere in the system.
Send/Receive Ack Time
- As Books Online mentions, this can be used to measure network latency between the principal and mirror servers.
- If this value is larger than normal it means that there is a network bottleneck between the principal and mirror servers.

Hopefully Microsoft will publish a whitepaper or some troubleshooting scenarios showing these counters being used.

Categories:
Database Mirroring | Performance | SQL Server 2008

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 principle and mirror to the SAN with no down time? or minimal down time?

As far as I know, there isn't any such document so I had a crack at coming up with a list of operations. Here's what I had:

Take a full backup of the principal on node A
Restore it on the SAN on node B using WITH NORECOVERY, remembering to use WITH MOVE to place the files correctly, and with a different database name than the current mirror
Take the required log backup on the principal and restore on the database copy on the SAN on node B
Break the mirroring partnership
Drop the current mirror database on node B
Rename the database on the SAN on node B to be the mirror database -- THIS DOESN"T WORK!
Setup the mirroring partnership to point to the newly restored database on the SAN on node B
Start mirroring and the new mirror will catch-up
Failover to the mirror on node B, which becomes the new principal
Follow the same procedure to move the new mirror on node A onto its SAN
Failback if you want to

And I promised to try it out to make sure I had it right so in this blog post I'm going to walk through the steps of doing this. It turns out that the steps above are slightly incorrect. Step 6 above doesn't work because the database is in recovery (so is inaccessible) and there's a short-cut when moving the database on the first node to avoid having to take and copy more backups. Let's see how it works and I'll post the corrected sequence at the end.

As I did in yesterday's mirroring post, I'm going to use the TicketSalesDB database from our Always-On DVDs. It's only a few hundred MB instead of 600GB but the principal is the same (no pun intended :-)). I've got mirroring running between two nodes, SQLDEV01 (the principal) and SQLDEV02 (the mirror), both of which are running 2005 SP2 and I've got a simulated workload inserting rows into the database. I don't actually have a SAN laying around so I'm cheating and I have directories called C:\SQLDEV01SAN and C:\SQLDEV02SAN instead. It's the location change that's the interesting part, not where the actual location is.

Step 1

On SQLDEV01, take a full backup and a log backup:

BACKUP DATABASE TicketSalesDB TO DISK = 'C:\SQLskills\TicketSalesDB.BAK' WITH INIT;

GO

BACKUP LOG TicketSalesDB TO DISK = 'C:\SQLskills\TicketSalesDB_Log.bak' WITH INIT;

GO

Step 2

On SQLDEV01, break the mirroring partnership:

ALTER DATABASE TicketSalesDB SET PARTNER OFF;

GO

And just check that it's gone:

SELECT mirroring_state_desc FROM sys.database_mirroring WHERE database_id = DB_ID ('TicketSalesDB');

GO

which returns:

NULL

Step 3

On SQLDEV02, drop the mirror database - this wouldn't work unless mirroring was no longer running:

DROP DATABASE TicketSalesDB;

GO

Step 4

Copy the backups to SQLDEV02 and restore them on the SAN and remembering to use WITH NORECOVERY:

RESTORE DATABASE TicketSalesDB FROM DISK='C:\SQLskills\TicketSalesDB.bak'

WITH

MOVE 'TicketSalesDBData' TO 'C:\SQLDEV02SAN\TicketSalesDBData.MDF',

MOVE 'TicketSalesFG2005Q1' TO 'C:\SQLDEV02SAN\TicketSalesFG2005Q1.NDF',

MOVE 'TicketSalesFG2005Q2' TO 'C:\SQLDEV02SAN\TicketSalesFG2005Q2.NDF',

MOVE 'TicketSalesFG2005Q3' TO 'C:\SQLDEV02SAN\TicketSalesFG2005Q3.NDF',

MOVE 'TicketSalesFG2005Q4' TO 'C:\SQLDEV02SAN\TicketSalesFG2005Q4.NDF',

MOVE 'TicketSalesDBLog' TO 'C:\SQLDEV02SAN\TicketSalesDBLog.LDF',

NORECOVERY;

GO

RESTORE LOG TicketSalesDB FROM DISK = 'C:\SQLskills\TicketSalesDB_Log.bak' WITH NORECOVERY;

GO

Step 5

On SQLDEV02, set the mirroring partner to be SQLDEV01:

ALTER DATABASE TicketSalesDB SET PARTNER = 'TCP://SQLDEV01:5091';

GO

Step 6

On SQLDEV01, start mirroring:

ALTER DATABASE TicketSalesDB SET PARTNER = 'TCP://SQLDEV02:5092';

GO

And check that it's running:

SELECT mirroring_state_desc FROM sys.database_mirroring WHERE database_id = DB_ID ('TicketSalesDB');

GO

This time it returns:

SYNCHRONIZED

Step 7

Now we need to failover so that we can move the database on SQLDEV01 onto its SAN. Before we do that, let's make sure that SQLDEV01 is the principal:

SELECT mirroring_role_desc FROM sys.database_mirroring WHERE database_id = DB_ID ('TicketSalesDB');

GO

which returns:

PRINCIPAL

Now force the failover:

ALTER DATABASE TicketSalesDB SET PARTNER FAILOVER;

GO

And query the DMV again to make sure. This time the mirroring_state_desc returned is:

MIRROR

Excellent!

Now, I did all of this while my workload was running and it automatically failed over to SQLDEV02, with the database now hosted on the SAN. To do the same move on SQLDEV01, we don't need to go through the backup and copy process again - we can just use the original backups we took in step 1.

Step 8

We need to break the mirroring partnership again, this time executing on SQLDEV02, the new principal:

ALTER DATABASE TicketSalesDB SET PARTNER OFF;

GO

On SQLDEV01, we can now drop the database and restore the original backups onto the SAN:

DROP DATABASE TicketSalesDB;

GO

RESTORE DATABASE TicketSalesDB FROM DISK='C:\SQLskills\TicketSalesDB.bak'

WITH

MOVE 'TicketSalesDBData' TO 'C:\SQLDEV01SAN\TicketSalesDBData.MDF',

MOVE 'TicketSalesFG2005Q1' TO 'C:\SQLDEV01SAN\TicketSalesFG2005Q1.NDF',

MOVE 'TicketSalesFG2005Q2' TO 'C:\SQLDEV01SAN\TicketSalesFG2005Q2.NDF',

MOVE 'TicketSalesFG2005Q3' TO 'C:\SQLDEV01SAN\TicketSalesFG2005Q3.NDF',

MOVE 'TicketSalesFG2005Q4' TO 'C:\SQLDEV01SAN\TicketSalesFG2005Q4.NDF',

MOVE 'TicketSalesDBLog' TO 'C:\SQLDEV01SAN\TicketSalesDBLog.LDF',

NORECOVERY;

GO

RESTORE LOG TicketSalesDB FROM DISK = 'C:\SQLskills\TicketSalesDB_Log.bak' WITH NORECOVERY;

GO

And setup mirroring again. On SQLDEV01:

ALTER DATABASE TicketSalesDB SET PARTNER = 'TCP://SQLDEV02:5092';

GO

And on SQLDEV02:

ALTER DATABASE TicketSalesDB SET PARTNER = 'TCP://SQLDEV01:5091';

GO

And we're running again.

Step 9

Now all we need to do is fail the workload back to SQLDEV01 by executing this on SQLDEV02:

ALTER DATABASE TicketSalesDB SET PARTNER FAILOVER;

GO

Summary

So - the corrected sequence for moving a database while mirroring is running is the following:

Take a full backup of the principal database on node A, and the required log backup

Break the mirroring partnership

Drop the current mirror database on node B

Copy the backups to node B and restore it on the SAN on node B using WITH NORECOVERY, remembering to use WITH MOVE to place the files correctly

Setup the mirroring partnership to point to the newly restored database on the SAN on node B

Start mirroring and the new mirror will catch-up

Failover to the mirror on node B, which becomes the new principal

Follow the same procedure to move the new mirror on node A onto its SAN, but using the original backups from step 1

Failback

Hope this helps.

Categories:
Database Maintenance | Database Mirroring | Example Scripts | Search Engine Q and A

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 principal and mirror databases are exactly the same. So, if a page becomes corrupt on the principal, SQL Server should be able to read the page from the mirror and use it to fix the principal. Similarly, if a page becomes corrupt on the mirror, the page can be read from the principal to fix up the mirror. Pretty cool, eh?

Details

The feature works for pages that have 824 errors, 823 errors where the OS returns a CRC error while reading the page (to prevent resource issues triggering a page repair), and pages that have 829 errors (where the page is marked as restore pending). See my previous post here for more details on page errors.
Pages are fixed asynchronously.
- If the page is corrupt in the principal, the query that hit the corrupt page will fail. Once discovered, a page is marked as being 829 until its fixed. This prevents an issue where a transient disk error could allow a subsequent update to change the page after it's been queued for being repaired, and then the page is overwritten with a copy from the mirror, losing the update. Nasty.
- If the page is corrupt in the mirror (which is discovered when the page is read as part of the continual recovery of the log), the mirroring session is suspended. The mirror keeps track of all corrupt pages that need to be repaired with copies from the principal. Once all corrupt pages have been repaired, the mirroring session will be resumed automatically. This means that if a page is corrupt in both the mirror and the principal, manual intervention will be required to resolve the issue.
The feature is only available in Enterprise Edition. This means that if the principal is on Enterprise Edition and the mirror is on Standard Edition, then corrupt pages on the principal can repaired from the mirror but not the other way around.
There is a new DMV - sys.dm_db_mirroring_auto_page_repair - that allows you to track corrupt pages in mirrored databases
- It covers all mirrored databases on a server.
- It provides info on the last 100 pages that were found in any mirrored database, as well as the status of the automatic page repair operation.
- This DMV isn't yet included in the SQL Server 2008 Books Online available on TechNet but will have the following info:
  - Database ID the page is in
  - The Page ID, split into file and page-in-file
  - The error type - distinguishing between 823 errors, torn-page errors, page checksum failures, and all-other-824 errors
  - The status of the page repair operation
  - The time that the status was last updated
If a page repair fails for any reason (e.g. the mirroring partner couldn't supply the page) then the repair will be marked as failed. If the page is then hit again (by a query on the principal or a recovery operation on the mirror) then it will be re-queued for repair.
Not all pages can be repaired - the file header page, database boot page, and allocation bitmap pages (GAM, SGAM, PFS) cannot be repaired this way.

In Action

I've got a system with a few SQL Server 2008 instances running so I decided to give it a try. Here's what I did, using the TicketSalesDB from the Always-On DVDs and labs that SQLskills produces:

Changed the database to use page checksums
Rebuilt the clustered index of one of the tables (so the pages have page checksums on)
Took the initial backup of the database that's needed for mirroring (so that it has no corruptions in)
Used DBCC IND to find a page in the leaf level of the clustered index and corrupted the page
Setup mirroring (but using the backup I took before introducing the corruption - so the mirror database will have a clean copy of the page I corrupted)
Performed a query on the table with the corrupt page to force the page checksum failure and kick-off automatic page repair

And it worked! Going back into the instance with the principal database and querying the DMV gives:

C:\>sqlcmd /E /S.\KATMAI01
1> SELECT * FROM sys.dm_db_mirroring_auto_page_repair;
2> GO
database_id file_id page_id error_type page_status modification_time
----------- ----------- -------------------- ---------- ----------- -----------------------
6 4 4256 -1 5 2007-09-27 17:23:20.067

(1 rows affected)
1>

The page I corrupted was (4:4256) and page_status of 5 means the repair succeeded. Running the query again confirms that the corruption has been fixed. The page was also logged in the suspect_pages table in msdb:

1> SELECT * FROM msdb..suspect_pages;
2> GO
database_id file_id page_id event_type error_count last_update_date
----------- ----------- -------------------- ----------- ----------- -----------------------
6 4 4256 5 1 2007-09-27 17:23:20.407

(1 rows affected)
1>

In Books Online (both 2005 and 2008), event_type of 5 means that the page was repaired.

I checked the SQL Server error log for the principal and this is what I found (the bolding is mine):

2007-09-27 17:17:10.41 spid25s     Database mirroring is active with database 'TicketSalesDB' as the principal copy. This is an informational message only. No user action is required.
2007-09-27 17:23:19.92 spid51      Error: 824, Severity: 24, State: 2.
2007-09-27 17:23:19.92 spid51      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x55684fbe; actual: 0x16e84fbe). It occurred during a read of page (4:4256) in database ID 6 at offset 0x00000002140000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Data\TicketSalesFG2005Q1.NDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2007-09-27 17:23:19.96 spid26s     Database mirroring is attempting to repair physical page (4:4256) in database "TicketSalesDB" by requesting a copy from the partner.
2007-09-27 17:23:20.42 spid26s     Database mirroring successfully repaired physical page (4:4256) in database "TicketSalesDB" by obtaining a copy from the partner.

I also checked the error log for the mirror and there's nothing relevant in there. I guess the same info would be output to the mirror database's error log if the mirror became corrupted. It's reasonably simple to check this using similar steps as above, but corrupting the database before the initial backup is taken, fixing the corruption again after the backup and before mirroring starts (so the corruption is on the mirror but not the principal), and then triggering an update on the corrupt page. When the update is replayed on the mirror, the corruption will be hit and the page repaired in the same way. I'll check later and blog if there's any difference.

Summary

SQL Server 2008 introduces an enhancement to database mirroring that can bi-directionally pull pages between the principal and mirror databases to fix page corruptions. One word of caution I'd give is that this feature doesn't mean you can ignore these errors when they occur - you still need to do root-cause analysis on the corruption and take steps to prevent them happening again before a corruption occurs that automatic page repair cannot fix (as I mentioned above).

Nevertheless, this is a tremendously useful feature that's going to save a lot of downtime. Cool!