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

The SQLCAT team has published a very detailed (and very long) whitepaper on implementing failover clustering with SQL Server 2008. There are some major differences from 2000 and 2005 related to setup and SP/CU installs so this is worth reading even if you're experienced with 2005 failover clusters.

You can download it from http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx and the link's on our whitepapers page.

(And yes, we're still on vacation until the end of July - no real blogging/Twittering)

The SQLCAT team have published another excellent whitepaper - this time the long-awaited one on the SQL Server 2008 data compression feature. Thirteen people inside and outside Microsoft (including me) provided technical reviews and the authors (Sanjay Mishra along with Marcel van der Holst, Peter Carlin, and Sunil Agarwal) did a great job. I remember leading an effort back in 2005 to see if we (the SQL team) could get some form of data compression into SQL Server 2005 RTM (no, obviously) so it's great to see data compression out there and now with top-class proscriptive guidance on when and how to use it.

Bottom line: don't just go an turn it on without analyzing whether you'll get a decent compression ratio and your workload is suited to data compression.

You can get to the whitepaper at: Data Compression: Strategy, Capacity Planning and Best Practices and I'll add it to our Whitepaper Links page.

Enjoy!

I've just been setting up some of our new hardware, and wanted to do some background reading to ensure I use the correct disk partition offset, RAID stripe size, and NTFS allocation unit size to enable the best possible performance for the volumes I'm creating.

You may not of heard about this (or your disk admins may not have heard about this) but on Windows Server 2003 and before, the default partition offset typically causes worse-than-optimal performance - and correcting it can get gains of maybe as high as 30% in terms of IO latency and duration. The SQLCAT team have just published a *fantastic* whitepaper (written by Jimmy May and Denny Lee) which explains the issue simply and clearly and shows you how to correct it. You should checkout the whitepaper at Disk Partition Alignment Best Practices for SQL Server.

The summary is that on Windows Server 2003 and before, the default partition offset is 31.5KB (63 x 512byte disk sectors), which does not align nicely with the common RAID stripe sizes of 64K or 128K, or the optimal NTFS allocation unit size of 64KB. This can lead to having to read/write multiple stripes every so often and a big perf drop. It can be fixed, as detailed in the whitepaper. For volumes *created* on Windows Server 2008, the problem does not exist as it creates a default partition offset of 1024KB.

In fact Jimmy just published a blog post to help you make the case to your disk admins/customers a few days ago: Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars.

Luckily I'm using Windows Server 2008, which correctly sets the disk partition for the vast majority of cases.

Next thing I considered was RAID stripe size and NTFS allocation unit size (previously known as 'cluster size'). Kendal Van Dyke just published an *excellent* blog post series that provides a lot of empirical evidence as to what the best numbers are for the RAID level you're using. This saved me a lot of time. Check out his series at Disk Performance Hands On Series.

The Dell MD3000i units I'm using don't go any lower than 128KB for a RAID stripe size, so the default is fine. Unfortunately, I forgot to set the NTFS allocation unit size to 64KB when creating the partitions in Windows, so I need to recreate the partitions.

A massive thank-you to these guys for saving me a lot of time and hassle. You should go read this stuff too.

Microsoft's popular performance troubleshooting whitepaper has been updated for SQL Server 2008. You can download Troubleshooting Performance Problems in SQL Server 2008 at http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx.

I've also added it to our whitepaper-links collection page at http://www.sqlskills.com/whitepapers.asp.

Microsoft's Steffen Krause has written an excellent whitepaper on Tuning the Performance of Change Data Capture in SQL Server 2008, that I technically reviewed, and it was published late last year. To get an overview of Change Data Capture (CDC) before reading the whitepaper, see the TechNet Magazine article I wrote for the November issue, titled SQL Server 2008: Tracking Changes in Your Enterprise Database. The new whitepaper covers:

• Configuration of sys.sp_cdc_enable_table parameters
• Configuration of the CDC capture job
• Using Extended Events to determine the performance and characteristics of CDC
• Influence of CDC scan job parameters on CDC performance
• Influence of workload characteristics on CDC performance
• Influence of sys.sp_cdc_enable_table parameters on CDC performance
• CDC cleanup job considerations
• Transaction log file considerations

Check it out at http://msdn.microsoft.com/en-us/library/dd266396.aspx.

Well, it was released a year ago but I only just found out about it last week. The SQL CAT team published a whitepaper last year called Database Snapshot Performance Considerations under I/O-Intensive Workloads. For a variety of workloads and with different numbers of concurrent database snapshots, it covers:

• How much time does it take to create a database snapshot?
• Transaction and I/O patterns during database snapshot creation
• Transaction throughput and response time with multiple database snapshots
• Impact of database snapshot on index creation
• Restoring the source database from a database snapshot
• Recommendations

Check it out at http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBSnapshotPerf.docx