The Bit Bucket

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

Enjoy!

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

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:

https://connect.microsoft.com/SQLServer/feedback/details/611164/dbcc-checkdb-omit-tables-option

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/

 

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

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

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)

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

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

New Documentation

Updated documentation is now available at

http://ola.hallengren.com/sql-server-backup.html

http://ola.hallengren.com/sql-server-integrity-check.html

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

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

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.
• ALTER MUG  SET SINGLE_USER  WITH ROLLBACK IMMEDIATE;
• DENY CONTROL  ON OBJECT::MUG  TO public;
• knock knock who's there? sp_ sp_who? spid 1, spid 2, spid 3, spid 4…
• ALTER DATABASE CriticalDB
  SET ChuckNorrisMode = ON
  WITH NOWAIT;

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

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.