There's a new whitepaper on TechNet that I've just come across (even though it was published 6 months ago!) called Predeployment I/O Best Practices. It's really good - discussing the following:

• Guidelines for determining I/O capacity
• Disk configuration best practices and common pitfalls
• Using SQLIO to determine capacity and interpreting its results
• Using System Monitor to monitor an IO subsystem

You can read it here and I've added a link to our whitepapers page.

While I'm on the subject of I/O, Bob Dorr (A Senior Escalation Engineer in PSS) published a blog post last year that debunks a couple of urban legends around SQL Server's IO, specifically:

• The myth that SQL Server used one thread per data file
• The myth that a disk queue length greater than 2 indicates an I/O bottleneck

Check out his blog post and the subsequent discussion here.

Hit another issue last night while playing with the latest CTP - VS2005 can't cope with some of the changes in SS2008. The problem arose while defining an ASP.NET connection string that was pointing to a 2008 instance (while playing with query notifications and cache invalidation, but that's unrelated) - it failed with a slightly misleading error message from VS that my SQL Server instance had to be SQL Server 2005 Beta 2 or above!

Turns out there's a fix already (which works perfectly) - see http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en. Thanks to Bob Beauchemin for helping me out.

I came across an interesting bug in Management Studio in the latest Katmai CTP today - when a connection disconnects from a database, SSMS doesn't release the shared database lock that it holds. This prevents any operations that need exclusive database access (like a RESTORE) and can be somewhat disconcerting if you don't realize what's going on. Try executing the following in SSMS in the November CTP:

USE master;
GO

CREATE DATABASE MySSMSTest;
GO

USE MySSMSTest;
GO

CREATE TABLE test (c1 INT);
GO

USE master;
GO

DROP DATABASE MySSMSTest;
GO

And you'll see:

Msg 3702, Level 16, State 4, Line 1
Cannot drop database "MySSMSTest" because it is currently in use.

It works perfectly in SQLCMD in the same CTP, and also in SQL Server 2005. You can work around this by doing something like:

ALTER DATABASE MySSMSTest SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

GO

Not exactly ideal - but, hey, it's only a CTP. This has already been reported in Connect as issue 320135 - I added a workaround.

I'll post any further bugs that I find here as well as making sure they're on Connect.

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!

One of the cool features added in SQL Server 2005 for scaling-out a workload was peer-to-peer replication. The major drawback was that to change an existing peer-to-peer topology, the entire topology had to be quiesced. In SQL Servr 2008, the Configure Peer-To-Peer Topology Wizard in Management Studio has undergone a major face-lift and a peer-to-peer topology can be altered ONLINE - very cool!

To get to the wizard, you still need to go through the clunkiness of enabling peer-to-peer subscriptions in the Subscription Options pane of the Publication Properties of a new publication (by right-clicking the publication under the Replication->Local Publications folder in Object Explorer):

Once that's set to True, you can right-click the publication and you'll see a Configure Peer-To-Peer Topology option which will bring up the new wizard. Here's what you'll see with only a single node configured - I've hovered the mouse over the node to get the tool-tip to show up:

By right-clicking anywhere on the design surface you get a menu, from which you can select to Add a New Peer Node. Of course the node you select has to already have been setup for replication otherwise you'll get an error. As long as replication is already setup, you'll get the Add a New Peer Node wizard:

You need to set the Peer Originator ID to a number that isn't already being used by another node in the topology - it would be nice if the wizard would default to an ID it knows isn't being used instead of 1.  You then decide whether to have peer-to-peer connections with all the other nodes in the topology automatically setup. If you don't check that option, the node will appear on the topology viewer, but with no connections, like below (again I've brought up the tool-tip so you can see it's a different node than the first one - in this case a different instance inside a VPC):

If you don't check the option, you can create connections manually by simply right-clicking either node and selecting Add a New Peer Connection. You'll see a rubber-banding arrow that you pull to the node you want to connect to. I tested the automatic method of creating the connections and it works nicely too. Here's a three node topology in the viewer:

The rest of the wizard is as before - setting the Log Reader Agent and Distributor Agent security settings and how to initialize the new peer. At any point you can go back into the wizard and add or remove connections or peers. I'm pretty impressed!

During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and it turns out he's written a neat tool that codifies all the formulas.

In case you're interested, the part of the formula I had incorrect was for figuring out how many pages would be needed for the upper levels of an index b-tree. My formula was:

Num_Index_Pages = ∑_Level (Index_Rows_Per_Page)^{Level – 1}
where 1 <= Level <= Levels

and Øystein came up with a better formula:

Num_Index_Pages = ∑_Level (Total_Num_Rows/(Index_Rows_Per_Page)^Level )
where 1 <= Level <= Levels

where in both formulas:

Levels = 1 + log _{Index_Rows_Per_Page} (Total_Num_Rows / Index_Rows_Per_Page)

Check out Øystein's cool tool at http://dbgoby.blogspot.com/2007/10/db-goby-v10.html!