The second part of our radio interview with TechNet has been released (see here for part 1). In this installment we discuss troubleshooting and manageability in SQL Server 2008. You can get to it by going to the March 11^th 2008 show here.

Enjoy!

(Cross-posted on Paul and Kimberly's blogs)

With the Spring SQL Server Connections show coming up next month, its time to start planning for the Fall show. Once again Kimberly and I will be Co-Chairs of the Conference and we'd like to invite you to submit abstracts for the Fall 2008 SQL Connections conference, to be held in Las Vegas, November 10-14th, 2008.

The conference will take place shortly after the SQL Server 2008 launch (when it actually RTMs, not the 'launch' that happened February 27th), and will focus heavily on SQL Server 2008. Abstracts are still welcome on best practices for SQL Server 2005 and how to upgrade and migrate applications from SQL Server 200x to SQL Server 2008. However, we will consider all topics - from design to performance to troubleshooting to architectures to programming. Also, we're likely to closely follow our past conference format with three primary themes/tracks: Infrastructure and Scalability, Programming, and Business Intelligence. You should aim for the intermediate to advanced audience for any SQL Server 2005 materials and for SQL Server 2008 we'll consider any level - especially depending on the newness of the release.

Note: You will not be able to speak at a DevConnections show if you are also presenting at a competitor’s show, in the same state, within 30 days of the DevConnections show. To be more specific, if you wish to speak at SQL Connections or any other DevConnections show this Fall in Las Vegas, you are precluded from speaking at any competing conferences in the state of Nevada between October 10, 2008 and December 13, 2008.

For submitting session abstracts, please use this URL:  http://www.deeptraining.com/devconnections/abstracts

The tool will be open from March 10th to midnight EST April 9th, after which we won't accept any abstracts. If you have used this site before and have forgotten your password, you can have your secret word mailed to you. It's better to do this then to create a new account. Please keep the abstracts under 200 words each and in one paragraph. Please do not use bulleted items or line breaks, and please use a spell-checker. If you have an issue with the site, please follow up with an email to paul@sqlskills.com.

Please create a new speaker record (if you haven't submitted before) and submit at least 3 abstracts; however, it will help your chances of being selected if you submit 5 or 6 abstracts. There's one slight change for this conference only - we won't have a fourth track for speakers just delivering a single conference session, so if you only submit a single session abstract, you're unlikely to be picked.

What you will get if selected:

• $500 per conference talk. (Additional compensation for pre/post conference workshops.)
• Coach airfare and hotel stay paid by the conference
• Free admission to all of the co-located conferences
• Speaker party
• The adoration of attendees
• etc.

Thanks for your continued support for our SQL Connections conference and we look forward to seeing your bright and shiny new abstracts!

Thanks,
Kimberly L. Tripp & Paul S. Randal
SQL Connections Conference Chairs

During the various courses I've been teaching, people are interested in how FILESTREAM performance compares with storing BLOBs in the database itself. I have some performance graphs based on measurements the dev team made - these have been presented publicly by myself and the dev team so I can share them with you here.

There are three graphs below, showing the relative performance for read, insert, and update of:

• BLOB data stored in FILESTREAM format and accessed through the WIN32 streaming APIs. The times include getting a transaction context from SQL Server, getting the file path, doing the operation, closing the file, and committing the transaction in SQL Server.
• BLOB data stored in FILESTREAM format and manipulated through T-SQL
• BLOB data stored in varbinary(max) format (and obviously manipulated through T-SQL)

The data is the same in each test. The tests were performed on a 4-way box with a cold buffer pool. One interesting point to note is that for smaller data sizes, it's faster to manipulate them through T-SQL than through the file system - this is expected based on research Jim Gray did when putting together the original TerraServer.

Just over a month ago I posted on how to enable FILESTREAM in CTP-5 and the pre-CTP-6 build I was working with. Now that's all changed! CTP-6 is a hybrid of the CTP-5 method and what will eventually be the methodology in RTM. The changes were made to separate the OS-level configuration from the SQL-level configuration, so a SQL admin can't invoke OS-level changes. This makes sense to me.

In a nutshell, the new way of enabling FILESTREAM will be:

• OS admin enables FILESTREAM when installing SQL Server or through the SQL Server Configuration Manager
• SQL admin enables FILESTREAM in the instance using sp_configure. This will always succeed, but if this is done before the OS-level enabling, then FILESTREAM operations will fail.

I won't go into all the details as Joanna Omel (the Program Manager for FILESTREAM in the Storage Engine) has blogged about them on the Storage Engine PM team blog - see here for her post.

More on CTP-6 changes as I hear about them (or trip over them!)

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!

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.