SQL Down Under Podcast 50 – Guest Louis Davidson now online

Hi Folks,

I've recorded an interview today with SQL Server MVP Louis Davidson. In it, Louis discusses some of his thoughts on database design and his latest book.

You'll find the podcast here: http://www.sqldownunder.com/Resources/Podcast.aspx

And you'll find his latest book (Pro SQL Server 2012 Relational Database Design and Implementation) here: http://www.amazon.com/Server-Relational-Database-Implementation-Professional/dp/1430236957/ref=sr_1_2?ie=UTF8&qid=1344997477&sr=8-2&keywords=louis+davidson


Query Performance Tuning class now has online option

The query performance tuning class has been a popular class but we've only been running it in local cities. We're now running it in a fully-interactive instructor-led online class, complete with hosted hands-on labs. Later this year it will also be available amongst our upcoming on-demand training offerings.

If you're keen to spend some time in a query performance tuning class, let us know.

For the online offering, we've spread it over 4 x 1/2 days to make it easier to attend. We've got the same hands-on-labs and course content as the in-person class. The labs are hosted so you don't need to provide equipment but we have a lower cost attendance option if you don't need the hosted labs.

The initial course is offered to suit Asia-Pacific timezone but we intend to alternate it with an offering to suit US timezone.

Details are online here: http://www.sqldownunder.com/Training/SQLServerQueryPerformanceTuning.aspx

Run database checks but omit large tables or filegroups – New option in Ola Hallengren's Scripts

One of the things I've always wanted in DBCC CHECKDB is the option to omit particular tables from the check. The situation that I often see is that companies with large databases often have only one or two very large tables. They want to run a DBCC CHECKDB on the database to check everything except those couple of tables due to time constraints.

I posted a request on the Connect site about time some time ago:


The workaround from the product team was that you could script out the checks that you did want to carry out, rather than omitting the ones that you didn't. I didn't overly like this as a workaround as clients often had a very large number of objects that they did want to check and only one or two that they didn't.

I've always been impressed with the work that our buddy Ola Hallengren has done on his maintenance scripts. He pinged me recently about my old Connect item and said he was going to implement something similar. The good news is that it's available now.

Here are some examples he provided of the newly-supported syntax:

EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands = 'CHECKDB'

EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands = 'CHECKALLOC,CHECKTABLE,CHECKCATALOG', @Objects = 'AdventureWorks.Person.Address'

EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands = 'CHECKALLOC,CHECKTABLE,CHECKCATALOG', @Objects = 'ALL_OBJECTS,-AdventureWorks.Person.Address'

EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands = 'CHECKFILEGROUP,CHECKCATALOG', @FileGroups = 'AdventureWorks.PRIMARY'

EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands = 'CHECKFILEGROUP,CHECKCATALOG', @FileGroups = 'ALL_FILEGROUPS,-AdventureWorks.PRIMARY'

Note the syntax to omit an object from the list of objects and the option to omit one filegroup.

Nice! Thanks Ola!

You'll find details here: http://ola.hallengren.com/


SQL Down Under Podcast – Gadi Peleg – Data Quality Services

Well it's been a few months but I'm back on a roll creating some SQL Down Under podcasts. The first out the door is an interview with Gadi Peleg from the SQL Server team, introducing Data Quality Services.

Gadi came to Microsoft when Zoomix was acquired.

Details of this podcast (and other available podcasts) are here: http://www.sqldownunder.com/Resources/Podcast.aspx

Hope you enjoy it even though there are some telling signs that I recorded it at 3AM 🙂

If you are using iTunes, you can also subscribe here: http://itunes.apple.com/au/podcast/sql-down-under/id503822116?mt=2

Free eBooks – SQL Server and other Microsoft Technologies

Great to see the advice from Gail Erickson about the release of a number of SQL Server related eBooks on the new Microsoft eBook Gallery site. It's good to see this sort of content moving over to eBook formats.

The e-books that are currently available include:

  • SQL Server 2012 Transact-SQL DML Reference
  • Master Data Services Capacity Guidelines
  • Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery
  • QuickStart: Learn DAX Basics in 30 Minutes
  • Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide

You'll find details of them here: http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

FIX: DQS Won't Work after upgrading to CU1 – SQL Exception 0x80131904

There are lots of reports of data quality services (DQS) not working after installing CU1 for SQL Server 2012. One symptom is that when trying to connect from the DQS client, you receive the following error: "System.Data.SqlClient.SqlException (0x80131904): An error occurred in the Microsoft .NET Framework while trying to load assembly id 65581"

After applying CU1, you need to execute:

dqsinstaller.exe -upgrade

This is typically from the folder C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn (but depends upon your installation path)

New updated maintenance plan scripts from Ola Hallengren

Great to see our buddy Ola Hallengren has created an updated version of his database maintenance scripts. Many people use these in place of the database maintenance plans that ship with SQL Server.

Here's the updated info from Ola and the best part is that they continue to be free:

New Version Available

A new version of the SQL Server Maintenance Solution is now available. The new version supports SQL Server 2012.

One new feature in SQL Server 2012 is AlwaysOn Availability Groups. This feature supports multiple replicas of a database. You can back up any replica and define a preferred backup replica.

SQL Server 2012 also supports online rebuilding of indexes with varchar(max), nvarchar(max), varbinary(max), or XML data types or large CLR types. The new version of the SQL Server Maintenance Solution makes use of this capability.

You can read more about the most recent version of the solution at http://ola.hallengren.com/versions.html

or download it at


New Documentation

Updated documentation is now available at




If you have been linking to the old documentation web page, please update your links to point to these new URLs.

#SQLMug – Like a collectors set of 5 x geeky SQL Mugs?

Hi Folks,

For a while, I've been wanting to get some great SQL mugs printed for SQL Down Under but I need further inspiration so here's your chance to get a collectors set of 5 SQL mugs:

Send me (greg @ sqldownunder . com) a great line to go onto the mugs, along with your country and a delivery address. I'll pick the best 5 and get mugs printed with those sayings. If you're one of the 5, I'll send you a collectors set with one of each of the 5.

Simple enough?

Here are some ideas I've already received to get you started:

  • Chuck Norris gets NULL. Nothing compares to him either.
  • knock knock who's there? sp_ sp_who? spid 1, spid 2, spid 3, spid 4…
    SET ChuckNorrisMode = ON

I'll probably cut off new entries around the end of April.

SQL Server 2012 Early Adoption Cook Book <- starting to be available

I've been working on a team with Roger Doherty building parts of what's now become the SQL Server 2012 Early Adoption Cook Book.

So, if you work on the bleeding edge of SQL Server and are keen to get your head around what's coming, this is a seriously good resource.

Time to go and get it.

The material is constructed as a large number of bite-sized pieces. Each presentation is about 15 minutes in length, and each demo is about 5 minutes. And there are lots of them.

Look for recordings of these by the original authors on the Channel9 website soon too, followed by local in person presentations from Joe Homnick in many locations.