SQL Connections is in less than two months now and our pre-con and post-con workshops are filling up fast - checkout my previous blog post here for the full list of what we're presenting.

Now we've finalized our TechEd US sessions with Microsoft and can let you all know that we're going to be there as usual! This time Kimberly and I are doing a joint pre-con workshop on SQL Server 2008 - so if you can only make it to one conference this year, you can choose TechEd or SQL Connections and still catch our 2008 workshop plus other sessions :-)

There's a catch though - this year TechEd US has split into two weeks (to mirror what TechEd Europe has been doing for a while). The first week is for Developers and the second week is for IT Pros. We'll be at the IT Pro week in Orlando, June 10-13. Here's what Kimberly and I are doing (all jointly presented this year):

Pre-Conference Workshop: PRC21 Microsoft SQL Server 2008 Overview for Database Professionals (Monday 6/9)

SQL Server 2008 offers an impressive array of capabilities for professional developers that build upon key innovations introduced in SQL Server 2005. The use of many of these will have manageability and infrastructure implications for a database—and hence the DBA! There are also enhancements to existing high-availability technologies, plus a variety of new tools to aid in managing performance, scalability, administration, and troubleshooting. This workshop helps you understand how to exploit the new toolset and how to manage a database that makes use of the new features in SQL Server 2008. Come to this workshop so your developers don't surprise you with new demands once your company upgrades!

Topics covered include:

• Availability Enhancements (Database Mirroring, Backup Compression, Peer-to-Peer Replication)
• Security Enhancements (Transparent Data Encryption, Extensible Key Management, All Actions Audited)
• Policy-Based Management
• Troubleshooting and Throttling (Resource Governor, Extended Events)
• New Development Technologies (Spatial Indexes, Sparse Columns, Filtered Indexes, Change Data Capture, FILESTREAM)
• Performance Data Collection
• Scalability Enhancements (Data Compression, Partition-Level Lock Escalation)

Session 1: Essential Database Maintenance

In this session, Paul and Kimberly will run you through their top-ten database maintenance recommendations, with a lot of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (to save you time!). Everything in this session will be practical and applicable to a wide variety of databases. Topics covered and myths debunked include: backups, shrinks, fragmentation, statistics, and much more! Focus will be on 2005 but we'll explain some of the key differences for 2000 and 2008 as well.

Session 2: Corruption Survival Techniques

Your database is corrupt - what do you do? Well, it depends! How critical is the data? Do you know what's really wrong with the database? What does all that DBCC CHECKDB output mean? Should you restore or repair? It’s all about limiting downtime and data-loss when a corruption occurs - from knowing the tools to understanding the implications of choices you make. In this demo-heavy session Paul and Kimberly will give you insight into how to recover from corruption without making things worse. Most importantly you'll get step-by-step instructions for dealing with the more common scenarios.

Session 3:  Are Your Indexing Strategies Working?

So you spent a bunch of time figuring out what indexes you should have while designing and testing your database. Now you're in production and six months have gone past. Are your strategies still valid now? Is SQL Server using the indexes you created? Are the users issuing the queries you thought they would - or are you missing crucial indexes? More importantly - how can you figure any of this out? In this demo-heavy session, Paul and Kimberly will show you how to analyze what's currently going on with your database and how to bring your initial strategy up-to-date. Come along to this session to help you find out what you might not know about your workload!

Here's a question that came in - what changed in SQL Server 2005 that allows concurrent log and full backups?

First a little background, in case you didn't know about the change in behavior. In SQL Server 2000, a concurrent log backup with either a full or diff backup (I'll just say 'full' from now on but take it to mean 'full or diff') was not permitted. The reason is that a log backup would clear the inactive portion of the log once it's been backed up, but a full backup may still need some of that log so it can't be cleared (see this post and this post for an explanation). The simple route was taken of disallowing concurrent log backups with fulls.

In SQL Server 2005, the restriction was lifted, but there's a subtle twist. You can do concurrent log backups with fulls BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you're relying on very frequent backups to manage the log size.

So - what changed that allowed the SS2000 restriction to be lifted? Nothing - just the code was changed to delay the log clearing and allow the concurrent backups.

Pretty cool change - but watch out for the twist.

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

Last year I posted on my old blog about the active SQL Server team blogs. I just happened to post on February 14^th and so in every class Kimberly teaches, she makes fun of how romantic I was to post that on Valentine's Day. So what better thing to post this year than an update to that old post!

Again, this is a list of 'active' blogs, not just one-post wonders, or blogs that are inactive but have a ton of fantastic info archived on them. I've grouped them by rough area and updated the list from last year, removing some that have been inactive for many months. I've also added a list of non-SQL team blogs that I follow too. Eventually I'll put this on our blogs page too - http://www.SQLskills.com/blogs.asp.

Enjoy (and Happy Valentine's Day again Kimberly! )

General SQL Server

• Samples team - code samples, for SQL Server 2005 and 2008
• Buck Woody - BOL, Tools
• Customer Advisory team (plus checkout their dedicated site http://sqlcat.com/)
• PSS Escalation Services team - another PSS blog, lots of how-it works info
• PSS Support team
• Euan Garden - SQL, VS, ...
• Dan Jones
• TechNet Magazine SQL Q&A

Compact/Express

• Compact Edition team
• SQL Express team
• Steve Lasker

Data Programmability

• Data Programmability team
• ADO.NET team - replaced the Data Access blog
• SQL Protocols team
• XML team
• Protocols team
• Angel Saenz-Badillos - JDBC / Linq

Storage Engine

• Storage Engine PM team  - my old blog, still going strong thanks to Sunil
• Slava Oks - SQLOS / memory management
• Laurentiu Cristofor - Security
• Raul Garcia - Security
• Security Team

Service Broker

• Roger Wolter - SOA, data management
• Rushi Desai - Service Broker
• Remus Rusanu - Service Broker (inactive now Remus has left - see below for his new blog)

Relational Engine

• Query Processor team (replaces the old Query Optimizer team blog)
• Programmability & API Development team
• CLR Integration team
• Denis Ruckebusch - XML datatype (inactive now he's left MS)
• Lyudmila Fokina - various including index builds (in Russian)
• Michael Rys - XML, XQuery
• Craig Freedman - query processing, execution and plans
• Isaac Kunen - Spatial data

Analysis Services / Data Mining

• Jamie MacLennan 

Reporting Services

• John Gallardo
• Bob Meyers
• Brian Welcker
• Lukasz Pawlowski - RS, Notification Services

Sync Services / Replication

• Synchronizer
• Sync Framework

SSIS / DTS

• SSIS Wiki
• SSIS team

Manageability / Tools

• Manageability team
• Michiel Wories - SMO / WMI samples (inactive)
• Gert Drapers - Data Dude

SQLskills.com Team

• Kimberly Tripp
• Stacia Misner
• Simon Sabin
• Bob Beauchemin
• Conor Cunningham

Select MVPs I Read

• Tony Rogerson
• Kalen Delaney
• Adam Machanic
• Colin Leversuch-Roberts - aka The Grumpy Old DBA
• Remus Rusanu
• Kevin Kline
• Andrew Kelly
• Tibor Karaszi
• Linchi Shea
• Sean McCown

Almost a year ago to the day I asked a question on my old blog - how long does *your* CHECKDB take to run? Well, I had a bunch of replies but I never got around to posting the results. I was reminded of this recently so threw together some graphs of the results. I've plotted database size against run-time of DBCC CHECKDB, for a number of different numbers of CPUs, and mostly on SQL Server 2000. I've also added a linear trend line too.

Now - bear in mind this doesn't take into account the multitude of other factors that can affect how long DBCC CHECKDB takes to run (see my previous post here for the list).

Hopefully these are interesting to some of you!

After many reminders (thanks Adam Machanic!) I've added Conor and Simon to the two aggregated feeds over all the SQLskills.com blogs.

There are two feeds:

1. SQL Server 2008 Category Posts
• Provides previous 30 day's posts
• http://pipes.yahoo.com/pipes/pipe.run?Age=30+days+ago&_id=mhCQs_B13BGv3jGgjtzu1g&_render=rss
2. All posts
• Provides previous 20 day's posts
• http://pipes.yahoo.com/pipes/pipe.run?Age=20+days+ago&_id=vv9PBOp13BGAc67kLO2fWQ&_render=rss

The amount of content is really growing as Simon and Conor also seem to not sleep like me :-)

Enjoy!

This post is based on one from my old MSDN blog but the topic has come up a few times in recent days so I want to revamp it and re-post.

There are two things that confuse people about mirrored backups - can you mix-n-match backup devices from the mirrors, and what exactly do the various sizes mean?

1) Single-device backup, no mirror

The code below creates a single-device backup with no mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
GO

The BackupSize in the HEADERONLY output is 168,899,072 bytes and the on-disk size of the file mediaset1device1.bck is 161MB.

2) Single-device backup, mirrored

The code below creates a single-device backup with a mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck'
MIRROR TO DISK = N'C:\SQLskills\mediaset2device1.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset2device1.bck';
GO

The BackupSize in the HEADERONLY output of both files is 337,798,144 bytes. This is double the size of the backup in case #1 above - and it because there are now two copies of the backup. The on-disk size of both files is 161MB, which is what we'd expect as mediaset2device1.bck is a copy of mediaset1device1.bck.

3) Two-device backup, no mirror

The code below creates a two-device backup with no mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck',
DISK = N'C:\SQLskills\mediaset1device2.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
GO

The BackupSize in the HEADERONLY output is 169,959,424 bytes. This is nearly exactly the same as for the single-device backup in case #1, but includes a bit more to account for the extra metadata in the second device. This time, the on-disk size of the file mediaset1device1.bck is 81MB. This is half of the on-disk size from the single-device case #1 as the backup is now split between the two files.

4) Two-device backup, mirrored

Now let's try to mix devices from the two backup media sets and see if it's possible:

RESTORE DATABASE AdventureWorks
FROM DISK = N'C:\SQLskills\mediaset1device1.bck',
DISK = N'C:\SQLskills\mediaset2device2.bck'
WITH REPLACE, STATS;
GO

And it works fine - excellent! That's the whole point of having mirrored backups.

One other question is - can backup device types can differ between media sets in the same backup. The answer to this is no - as documented in Books Online. All the backup devices involved in a single backup, regardless of whether they're part of a mirror media set or not, must be of the same type and have similar characteristics.

Hope this is useful.