In Recovery...

SQL PASS interview with Jen and Sean McCown

paul.nospam@nospam.sqlskills.com (paul) — Mon, 16 Nov 2009 17:08:00 -0800

(Reposting from Kimberly's blog)

At PASS we hung out with Sean McCown and Jen McCown (of MidnightDBA) and chatted... and, despite our better judgement, agreed to do an interview. These aren't overly technical but they are fun!

And, our favorite "travel tip" from the video is to know your plane and check your seat on http://www.seatguru.com/ - we use it religiously when planning our travel.

Enjoy!

SQL Connections Fall resources uploaded

paul.nospam@nospam.sqlskills.com (paul) — Sat, 14 Nov 2009 13:42:00 -0800

We're sitting here in the Las Vegas airport on the way home to Seattle for 3 whole weeks and I've just finished uploading the resources for the SQL Connections conference that ended yesterday.

On our Past Events page I've uploaded zip files for:

Our joint pre-con on database maintenance
Kimberly's post-con on indexing
My session on logging and recovery
My session on backup strategy
Kimberly's session on covering indexes
Kimberly's session on sparse columns and filtered indexes

We had a great week down here in Vegas - lots of very enthusiastic attendees and many (what we call) 'repeat offenders' :-) Saw a couple of great shows too - Cirque du Soleil's Mystere on Monday and Bette Midler last night. I'm not especially a fan of hers, but she was *excellent*.

Looking forward to the next Connections - cheers!

Lock logging and fast recovery

paul.nospam@nospam.sqlskills.com (paul) — Fri, 13 Nov 2009 13:24:00 -0800

One of the things I love teaching is how the transaction log and logging/recovery work. I presented a session on this at both PASS and SQL Connections in the last two weeks, and in both sessions I promised to write some blog posts about the deep internals of logging operations. This is the first one in the series. Previous blog posts that dive into logging operations are:

How do checkpoints work and what gets logged (this also explains how crash recovery starts, using the boot page and the most recent checkpoint log record)
Finding out who dropped a table using the transaction log
How expensive are page splits in terms of transaction log?
Inside the Storage Engine: More on the circular nature of the log
Ghost cleanup redux
Inside the Storage Engine: Ghost cleanup in depth

Ok, on with the show.

SQL Server 2005 introduced a feature called 'fast recovery' in Enterprise Edition. This allows a database to become available for use after the first part of recovery (REDO) completes and before the (usually longer running) second part of recovery (UNDO) completes. See my TechNet Magazine article Understanding Logging and Recovery in SQL Server if you don't know what I'm talking about. But how does SQL Server do this?

The answer is lock logging. A log record describes a single change made to a database. For log records describing changes that can be used as part of UNDO (yes, some changes to the database are one-way only - for instance PFS page changes), from 2005 onwards the log record also includes a description of which locks were being held at the time the change was made. These locks were necessary to protect the change being made when the original transaction was running (before the crash) and so the same locks will be necessary to protect the anti-operation which reverses the change. I'll explain more about these anti-operations in one of the next in-depth logging blog posts.

The Storage Engine does two passes through the log as part of crash recovery. The first pass does REDO and also reads the log records that will be processed as part of the second pass (UNDO), looking at the lock description and actually acquiring those locks. For fast recovery, at that point the database is brought online. This is possible because the recovery system knows that it already has the correct locks to guarantee that it can safely generate and perform the anti-operations necessary to perfom UNDO. One side-effect of this is that although the database is available for use, a query may bump into one of the locks being held to allow fast recovery - in which case it will have to wait for that lock to be dropped as UNDO progresses.

Cool eh?

That was the introduction to allow me to do some gratuitous spelunking around the internals :-) I'm going to create a few simple examples to show you lock logging in the log. Now, don't get confused - it's not logging actual locks (the memory used to hold the lock itself), it's just logging a description of which locks were held and in which modes.

Here's the script to create a database with a simple table. I'm using a LOB column and specifically setting it to be stored off row (see Importance of choosing the right LOB storage technique) so we can see some text page locks too. I'm using the SIMPLE recovery model for simplicity (ha ha) - so I can clear the log when a checkpoint occurs rather than having to muck around with log backups. I'll insert the first row and then clear the log.

CREATE DATABASE LockLogging;
GO
USE LockLogging;
GO

CREATE TABLE LockLogTest (c1 INT, c2 INT, c3 VARCHAR (MAX));
GO
EXEC sp_tableoption 'LockLogtest', 'large value types out of row', 'on';
GO

INSERT INTO LockLogTest VALUES (1, 1, 'a');
GO

ALTER DATABASE LockLogging SET RECOVERY SIMPLE;
GO
CHECKPOINT;
GO

Now let's try the first operation - a simple insert - and look at the log records using fn_dblog (and I'm skipping the checkpoint log records):

INSERT INTO LockLogTest VALUES (2, 2, 'b');
GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information]
FROM fn_dblog (NULL, NULL);
GO

Operation        Context       Page ID        Slot ID Locks Lock Information
---------------- ------------- -------------- -------- ----- -------------------------------------------------------------------
LOP_BEGIN_XACT   LCX_NULL      NULL           NULL     NULL NULL
LOP_INSERT_ROWS LCX_TEXT_MIX 0001:00000098 1        2     ACQUIRE_LOCK_IX PAGE: 18:1:152; ACQUIRE_LOCK_X RID: 18:1:152:1
LOP_INSERT_ROWS LCX_HEAP      0001:0000009a 1        3     ACQUIRE_LOCK_IX OBJECT: 18:2073058421:0;
                                                                 ACQUIRE_LOCK_IX PAGE: 18:1:154; ACQUIRE_LOCK_X RID: 18:1:154:1
LOP_COMMIT_XACT LCX_NULL      NULL           NULL     NULL NULL

We can see page IX and row X locks for the LOB value being inserted into the text page, plus table IX, page IX, and row X locks for the data record being inserted into the heap. The lock resources break out as follows:

18:1:152 is page 152 in file 1 of database ID 18
18:1:152:1 is slot 1 on page 152 in file 1 of database ID 18
18:2073058421:0 is object ID 2073058421 (the object ID of the table LockLogTest) in database ID 18

Notice also the LOP_BEGIN_XACT and LOP_COMMIT_XACT log records - even though I didn't do an explicit transaction, SQL Server has to start one internally for me (called an implicit transaction) so that there's a boundary for where to rollback if something goes wrong during the operation.

And now an update operation (with a checkpoint first to clear out the log):

CHECKPOINT;
GO
UPDATE LockLogTest SET c1 = 3;
GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information]
FROM fn_dblog (NULL, NULL);
GO

Operation        Context       Page ID        Slot ID Locks Lock Information
---------------- ------------- -------------- -------- ----- -------------------------------------------------------------------
LOP_BEGIN_XACT   LCX_NULL      NULL           NULL     NULL NULL
LOP_MODIFY_ROW   LCX_HEAP      0001:0000009a 0        3     ACQUIRE_LOCK_IX OBJECT: 18:2073058421:0;
                                                                 ACQUIRE_LOCK_IX PAGE: 18:1:154; ACQUIRE_LOCK_X RID: 18:1:154:0
LOP_MODIFY_ROW   LCX_HEAP      0001:0000009a 1        3     ACQUIRE_LOCK_IX OBJECT: 18:2073058421:0;
                                                                 ACQUIRE_LOCK_IX PAGE: 18:1:154; ACQUIRE_LOCK_X RID: 18:1:154:1
LOP_COMMIT_XACT LCX_NULL      NULL           NULL     NULL NULL

Just as we expected - a table IX lock, a page IX lock, and two row X locks on that page.

Now, what about something more complicated like a TRUNCATE TABLE? Have you heard the myth about it not being logged? Right - it's a myth:

CHECKPOINT;
GO
TRUNCATE TABLE LockLogTest;
GO
SELECT [Operation], [Context], [Page ID], [Slot ID], [Number of Locks] AS Locks, [Lock Information]
FROM fn_dblog (NULL, NULL);
GO

Operation        Context       Page ID        Slot ID Locks Lock Information
---------------- ------------- -------------- -------- ----- -------------------------------------------------------------------
LOP_BEGIN_XACT   LCX_NULL      NULL           NULL     NULL NULL
LOP_LOCK_XACT    LCX_NULL      NULL           NULL     1     ACQUIRE_LOCK_SCH_M OBJECT: 18:2073058421:0
LOP_MODIFY_ROW   LCX_IAM       0001:0000009b 0        1     ACQUIRE_LOCK_X RID: 18:1:155:0
LOP_MODIFY_ROW   LCX_PFS       0001:00000001 0        1     ACQUIRE_LOCK_X PAGE: 18:1:154
LOP_MODIFY_ROW   LCX_PFS       0001:00000001 0        1     ACQUIRE_LOCK_X PAGE: 18:1:155
LOP_MODIFY_ROW   LCX_IAM       0001:00000099 0        1     ACQUIRE_LOCK_X RID: 18:1:153:0
LOP_MODIFY_ROW   LCX_PFS       0001:00000001 0        1     ACQUIRE_LOCK_X PAGE: 18:1:152
LOP_MODIFY_ROW   LCX_PFS       0001:00000001 0        1     ACQUIRE_LOCK_X PAGE: 18:1:153
LOP_SET_BITS     LCX_SGAM      0001:00000003 1        NULL NULL
LOP_SET_BITS     LCX_GAM       0001:00000002 1        NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000014 89       NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000011 78       NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000014 90       NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000041 164      NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000041 165      NULL NULL
LOP_COUNT_DELTA LCX_CLUSTERED 0001:00000041 166      NULL NULL
LOP_HOBT_DDL     LCX_NULL      NULL           NULL     NULL NULL
LOP_MODIFY_ROW   LCX_CLUSTERED 0001:00000014 89       2     ACQUIRE_LOCK_IX OBJECT: 18:7:0;
                                                                 ACQUIRE_LOCK_X KEY: 18:458752 (0000c2681664)
LOP_HOBT_DDL     LCX_NULL      NULL           NULL     NULL NULL
LOP_MODIFY_ROW   LCX_CLUSTERED 0001:00000014 90       2     ACQUIRE_LOCK_IX OBJECT: 18:7:0;
                                                                 ACQUIRE_LOCK_X KEY: 18:458752 (00007a581379)
LOP_MODIFY_ROW   LCX_CLUSTERED 0001:00000011 78       2     ACQUIRE_LOCK_IX OBJECT: 18:5:0;
                                                                 ACQUIRE_LOCK_X KEY: 18:327680 (00001df3833b)
LOP_COMMIT_XACT LCX_NULL      NULL           NULL     NULL NULL

Lots of logging and lots of locks. If you look at the Context column, you'll see that the operation is modifying allocation bitmaps (LCX_IAM, LCX_PFS, LCX_SGAM, LCX_GAM) but taking locks on the table pages, not on the allocation bitmaps themselves - they're only ever latched (an internal, much lighter-weight, synchronization mechanism). This is done as the pages comprising the table are deallocated - this is all done because the table's small enough that the Storage Engine chooses to deallocate all the storage immediately, instead of pushing it all onto the task queue for the deferred drop background task. See my previous post Search Engine Q&A #10: When are pages from a truncated table reused? which discusses this too.

There are no actual row operations performed on the table itself. The only table row operations are down at the bottom on table with object IDs 7 and 5 (sysallocunits and sysrowsets, respectively) to update the page counts, first IAM, and first page entries for the table.

So - hopefully this has been useful to you. In the next post in the series, I'll discuss compensation log records and how rollback operations work.

PS Send me an email or put in a comment if there's something in particular about the log (or log records) you'd like to see explained.

Australia and PASS resources and wrapup

paul.nospam@nospam.sqlskills.com (paul) — Tue, 10 Nov 2009 16:33:00 -0800

Today's our rest day here at SQL Connections and one of the things on my list is to post all the resources from our recent events.

On our Past Events page I've uploaded zip files for:

Kimberly's 4-day performance tuning class in Melbourne
My 4-day maintenance/DR class in Melbourne
Kimberly's pre-con at PASS on indexing
My post-con at PASS on disaster recovery
Kimberly's spotlight session on covering indexes
My spotlight session on logging and recovery

Speaking of PASS, what a wonderful conference it was! This year's surprise guest was Twitter - which quite changed the social dynamics of the whole event. I was really looking forward to meeting everyone I've gotten to know (virtually) since getting sucked into Twitter back in April and it was fun seeing how people looked in real life compared to their Twitter avatars. The SQL community has a vibrant Twitter presence, with lots of people (myself and Kimberly included) online and happy to help out answering questions. One of the coolest things we did was to tweet what was going on in each other's workshops, so people could follow along who weren't there. Checkout the #ktprecon and #prpostcon Twitter hash-tags for the resource links and pithy epithets from the workshops. I even managed to heckle Buck Woody in his session while I was at home - remote-heckling!

There were a bunch of events that happened too that others have blogged photos of, including the opening reception Celebrity Quiz Bowl (which Kimberly and I won), the official launch of the MVP Deep Dives charity book (which Kimberly and I edited part of), the Birds of a Feather lunch (where we hosted a table each) and Kilt-Wearing-Wednesday. Sadly, I couldn't procure a kilt to wear but I did show a picture of me wearing a kilt last year at my sister's wedding in Scotland. Maybe next year.

Now we're in Las Vegas for the Fall SQL Connections conference. I'll be posting resources from our workshops and sessions on Friday, while I'm sitting in Kimberly's post-con.

TechNet Magazine: feature article on recovering from disasters using backups

paul.nospam@nospam.sqlskills.com (paul) — Thu, 05 Nov 2009 08:36:00 -0800

The November 2009 edition of TechNet Magazine is up on the web and contains my latest feature article, the second in a 3-part series on backups/restores/repairs.

In this article I explain all about using the RESTORE command. Not much point having backups if you don't know how to use them! Topics include:

The four phases of a restore operation - how they work and how you can speed them up
Figuring out what you need to restore when a disaster happens
Figuring out what you're actually able to restore
Using WITH RECOVERY / NORECOVERY / STANDBY
Point-in-time restore operations
Considerations when restoring to a different location

There seem to be a few formatting issues when the magazine's web folks put the article up - I've notified them and hopefully they'll fix them soon.

You can get to the article at: http://technet.microsoft.com/en-us/magazine/ee677581.aspx

Enjoy!

Busy week coming up at the 2009 PASS Summit

paul.nospam@nospam.sqlskills.com (paul) — Sun, 01 Nov 2009 12:08:00 -0800

It's PASS time again in Seattle and we've got a busy week lined up. It's going to be a cavalcade of meeting new people, and in the best tradition of conferences past, I'll do my best to remember most people's names who I met last year but please don't be offended if I don't, give me a prod in the right direction :-) Best bet for having me remember is to have something brightly colored on show: Jason has vivid red hair, Denny has partly bright blue hair, Brent has big green fists, and Thomas has off-color blog posts. My brightly-colored attire will include a tall blonde woman next to me most of the time.

Here's what we have definitely on the schedule so far:

Our pre-con workshop on Monday
Playing in the welcome reception Quiz Bowl Monday night
Birds of a Feather lunch on Tuesday. I'm doing "Chow Down on Corruption" and Kimberly's doing "Kimberly Tripp Covers Indexes (and Lunch)". Our tables are going to be next to each other so that we can heckle. My table will at least have to make it known that our table title is cleverer than hers.
My Wednesday morning Spotlight session on Understanding Logging and Recovery
Kimberly's Wednesday afternoon Spotlight session on Covering Indexes
Our post-con workshop on Friday

And some parties and other frivolity thrown in.

Hope to see you there!

PS Seems like #sqlpass is the Twitter tag for the conference, prepare to be overwhelmed!

PPS If you're speaking for the first time at PASS, checkout my long Valentine's Day blog post about how to present: Public Speaking: A Primer

Video of didgeridoo player at Ayers Rock

paul.nospam@nospam.sqlskills.com (paul) — Thu, 29 Oct 2009 14:34:00 -0800

For the last few weeks we were in Australia, teaching classes with our sister company SQLskills.com.au. After we were done with work we headed out to Uluru (formerly known as Ayers Rock) in the remote outback of the Northern Territories for a long weekend. We'll both be posting a bunch of photos from the trip, but to start with I just uploaded a 2 minute video I took of an excellent didgeridoo player. He was playing for a group of us doing the Sounds of Silence dinner, watching the sunset at Uluru. Fantastic!

Click the image to go to the movie on Facebook (no login required).

Enjoy!

Tracking expensive queries with extended events in SQL 2008

paul.nospam@nospam.sqlskills.com (paul) — Thu, 15 Oct 2009 16:13:00 -0800

As part of the 2008 DBA class we're teaching down here in Melbourne, I did a demo of using predicates and file targets with extended events, so I want to blog the script for people to play with.

For background info on extended events see:

My TechNet Magazine article from 2008: Advanced Troubleshooting with Extended Events
Jonathan Kehayias' excellent whitepaper: Using SQL Server 2008 Extended Events
Previous blog post on using the system health event session to get historical deadlock info

In this scenario, I'd like to track queries that make heavy usage of the CPU on my system.

First off I'll create a test database to play with.

USE MASTER;
GO
IF DATABASEPROPERTYEX ('production', 'Version') > 0 DROP DATABASE production;
GO

CREATE DATABASE production;
GO
USE production;
GO

CREATE TABLE t1 (c1 INT IDENTITY, c2 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID(), c3 CHAR (5000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1_CL ON t1 (c1);
CREATE NONCLUSTERED INDEX t1_NCL ON t1 (c2);
GO

SET NOCOUNT ON;
INSERT INTO t1 DEFAULT VALUES;
GO 1000

-- Get the database ID to plug into the event session
SELECT DB_ID ('production');
GO

Notice that I grabbed the database ID of the new database I created, as I'll need that when defining the extended event predicate.

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'EE_ExpensiveQueries')
   DROP EVENT SESSION EE_ExpensiveQueries ON SERVER;
GO

CREATE EVENT SESSION EE_ExpensiveQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
   (ACTION (sqlserver.sql_text, sqlserver.plan_handle)
      WHERE sqlserver.database_id = 18 /*DBID*/ AND cpu > 10 /*total ms of CPU time*/)
ADD TARGET package0.asynchronous_file_target
   (SET FILENAME = N'C:\SQLskills\EE_ExpensiveQueries.xel', METADATAFILE = N'C:\SQLskills\EE_ExpensiveQueries.xem')
WITH (max_dispatch_latency = 1 seconds);
GO

I'm monitoring the event that fires whenever a statement completes, and I'm filtering by database ID (make sure you plug in the correct database ID when you try this yourself) and by the number of milliseconds of CPU time the statement used. Note: when using predicates you should always make sure that you order the predicate tests such that the most restrictive predicates (those most likely to evaluate to false) are first in the list, so the predicate evaluation 'short-circuits' as quickly as possible (a standard programming practice with boolean logic). I'm also using a file target, just to show how it can be done, instead of the ring buffer which is commonly used for extended events demos.

Now I'm going to turn on the event session and do some queries in the production database.

ALTER EVENT SESSION EE_ExpensiveQueries ON SERVER STATE = START;
GO

USE production;
GO

SELECT COUNT (*) FROM t1 WHERE c1 > 500;
GO

SELECT SUM (c1) FROM t1 WHERE c3 LIKE 'a';
GO

ALTER INDEX t1_CL ON t1 REORGANIZE;
GO

ALTER INDEX t1_CL ON t1 REBUILD;
GO

That should have generated some long-running queries. Now I'll switch in to the context of master (so the querying of the event session itself doesn't get captured by the event session) and see what I've captured.

USE master;
GO

SELECT COUNT (*) FROM sys.fn_xe_file_target_read_file
('C:\SQLskills\EE_ExpensiveQueries*.xel', 'C:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL);
GO

In this case I've got 3 entries. I can pull these out using the following code:

SELECT data FROM
(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
('C:\SQLskills\EE_ExpensiveQueries*.xel', 'C:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL)
) entries;
GO

But I get three XML blobs back, like so:

What's more useful is to pull everything out of the XML blob programmatically using the code below:

SELECT
   data.value (
      '(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time],
   data.value (
      '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],
      CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000
      AS [Duration (s)],
   data.value (
      '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement],
      SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50)
      AS [Plan Handle]
FROM
   (SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
      ('C:\SQLskills\EE_ExpensiveQueries*.xel', 'C:\SQLskills\EE_ExpensiveQueries*.xem', null, null)
) entries
ORDER BY [Time] DESC;
GO

Time                    CPU (ms) Duration (s) SQL Statement                              Plan Handle
----------------------- -------- ------------ ------------------------------------------ --------------------------------------------------
2009-10-16 17:59:29.623 30       1.214875     ALTER INDEX t1_CL ON t1 REBUILD;           0x06001000EB672A07B8C0C807000000000000000000000000
2009-10-16 17:59:28.407 20       0.024076     ALTER INDEX t1_CL ON t1 REORGANIZE;        0x0600100003594903B8C0C807000000000000000000000000
2009-10-16 17:59:28.343 51       0.045144     SELECT SUM (c1) FROM t1 WHERE c3 LIKE 'a'; 0x06001000FAF5B11EB820C307000000000000000000000000

Now I can plug in one of the plan handles to a query of sys.dm_exec_query_plan to get the graphical query plan:

SELECT [query_plan] FROM sys.dm_exec_query_plan (0x06001000FAF5B11EB820C307000000000000000000000000);
GO

And clicking on the resulting XML 'link' gives the query plan:

And now I can tweak the production workload to potentially behave better.

Just for kicks I went back into the context of the production database and ran the XML parsing again to capture the query plan - try it yourself - pretty gnarly! :-)

Now, if I was going to make this more useful, I'd use a ring buffer target, with a polling mechanism every few seconds to make sure that I can capture the graphical query plan for expensive queries before the plan is pushed out of cache - maybe I'll get around to doing that and publish the complete solution.

In the meantime, here's a zip file containing the entire script that you can download and play with: EE_ExpensiveQueries.zip (1.72 kb)

Enjoy!

Photos from Warsaw trip in September

paul.nospam@nospam.sqlskills.com (paul) — Thu, 15 Oct 2009 13:27:00 -0800

I've finally gotten around to tidying up and posting a bunch of photos from the second half of our trip to Europe trip in September. Warsaw's an excellent place (although we were repeatedly told that Krakow is better - we'll see when we go next time) with a lot of cool history to soak up. We spent a day wandering around the old town (heavily reconstructed after WWII) and the Royal Castle (totally reconstructed). We also saw some of the old 3-metre high containing wall from the Jewish ghetto during WWII - very interesting and thought-provoking.

I've uploaded two albums to Facebook (no login required):

Tour of the Royal Castle - see http://www.facebook.com/album.php?aid=2040519&id=1293146061&l=dca686cead
Wandering around the Old Town - see http://www.facebook.com/album.php?aid=2040630&id=1293146061&l=a8107c0aa5

Here are a few examples: part of the enclosure wall from the Jewish ghetto, church bell tower, statue of Chronos in the Royal Castle.

And a few more: tank track from the remote-controlled German tank that destroyed the Cathedral, larger view of the room containing Chronos, Plac Zamkowy (Castle Square) with the Royal Castle on the right.

Enjoy!

Things to consider when setting up database mirroring

paul.nospam@nospam.sqlskills.com (paul) — Wed, 14 Oct 2009 20:27:00 -0800

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!