Mitch Wheat - Treat the cause, not the symptoms!

I recently needed to find the location of SQL Server’s default data file path in order to create multiple database data files as part of an automated production install. After looking at and discarding a few options that included reading the registry directly, SQL Server Management Objects (SMO) seemed a logical choice. Talking to one of my colleagues, Piers, whose Powershell wizardary has to be experienced to fully appreciate, we (well he!) fired up a Powershell GUI and we took a look at the methods available.

As an aside, if you are not aware of this ‘trick’ it’s worth explicitly mentioning:

In Powershell, first load the relevant assembly into memory (which in this instance is Microsoft.SqlServer.Smo):

  > [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null

Then, create an instance of the type you are interested in (the Server type):

  > $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server “servername”

and pipe the object instance default method output through Get-Member to list all the Events, Methods and Properties exposed:

  > $smoServer  gm 

So having done that we found a property named DefaultFile which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell:

function Get-SQLServerDefaultDataFilePath
(
  [string]$sqlServer = $(throw 'sqlServer is required')
)
{
   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null
   $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $sqlServer

   $str = $smoServer.DefaultFile

   # if DefaultFile property is empty, it means default path has not been changed
   if ($str)
      {$str}
   else
      {$smoServer.MasterDBPath}
}

Update: Piers pointed out that Books Online contains a useful section on programming tasks using SQL Server Management Objects (SMO)

CodePlex is hosting an excellent whitepaper created by the Visual Studio ALM Rangers, containing guidance on working with Visual Studio 2010 Database projects:

Practical guidance for Visual Studio 2010 Database projects, which is focused on 5 areas:

• Solution and Project Management
• Source Code Control and Configuration Management
• Integrating External Changes with the Project System
• Build and Deployment Automation with Visual Studio Database Projects
• Database Testing and Deployment Verification

This release includes common guidance, usage scenarios, hands on labs, and lessons learned from real world engagements and the community discussions.

You can download the Visual Studio Database Guide here.

It may not be the greatest named application out there but SQL Sentry Plan Explorer is a free, lightweight, standalone tool that improves dealing with SQL Server query plans so much, you’ll wonder why it hasn’t been incorporated into SSMS (and let’s face it, viewing plans in SSMS really sucks!).

There are several ways to open a plan: In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into Plan Explorer. Or, save an execution plan from SSMS to a .sqlplan file, then open the file from Plan Explorer.

Download SQL Sentry Plan Explorer here.

Service Pack 1 for Windows Server 2008 R2 and Windows 7 has been released. Get it here.

A colleague asked me if I had any TSQL to hand that would round down to a 15 minute interval and also round to nearest 15 minute interval. A quick search found several formulas but several had rounding errors. Here are both without any rounding errors.

As noted, they have the limitation of working only until 5983 AD, but I figure I won’t be around!

Just so I remember where this article is (also applies to SQL Server 2008 Express):

How to configure SQL Server 2005 to allow remote connections

Microsoft Technical Fellow and all-round Windows Internals expert, Mark Russinovich, has an upcoming book release. And this time it’s not a new edition of ‘Windows Internals’!

His fiction debut, Zero Day, is set in a world completely reliant on technology (sounds familiar), and surrounds the events of cyber infrastructure attacks released on a largely unprepared world.

Assuming your user group is actually holding meetings :), User Group Support Services (UGSS) have released a series of videos on “How to give great presentations” aimed at first time speakers and anyone wanting to improve their skills (I know I need to):

This video series guides you through what you need to know to give your first presentation or to improve your existing presentation skills. You’ll learn how to choose the right subject for you, how to break subjects up so that your explanations and demonstrations are clear and understandable, how to construct your slide deck so that it covers essential subjects without sending your audience to sleep, why being nervous is completely normal and what you can do to make it manageable, tips and tricks for giving great demonstrations, how to prepare your laptop so that it does not fight you while you are presenting and finally how to deliver the presentation that you have worked so hard creating.