Nov 272012
 

Three weeks ago I had quite the trip to the Pacific Northwest. I was very fortunate to present in Portland at SQL Saturday #172, and then headed up to Seattle for the 2012 PASS Summit. It was a fantastic week and I hope I can do something similar next year! Here’s some thoughts I had from each event in no particular order.

SQL Saturday #172

  • Meatloaf with bacon gravy

    Bacon in gravy form!

    Portland is an awesome city! I had been there once before and had a terrific time, so I was very happy to be able to return. If you’ve never been, I highly recommend you check it out sometime. As a bonus, it’s incredibly pedestrian-friendly and the public transit options are excellent.

  • Consult FlavorTown USA before traveling. If you’re a fan of Diners Drive-Ins and Dives, this website is for you! They keep track of all the places the show has visited so you can stop by yourself should you be in the neighborhood. It turns out my hotel was only a few blocks from Blueplate Lunch Counter so I headed there for lunch after I got into town. I got the meatloaf with mashed potatoes and creamy bacon gravy. Amazing!
  • Always have a backup plan. Anything can happen before or during your presentation, so be a good Boy (or Girl) Scout and BE PREPARED! I keep extra copies of all my presentation materials in multiple places: email, Dropbox, S3, and a thumb drive. Being prepared for disaster saved my bacon, as my Demo VM decided to corrupt itself the night before the event. Fortunately nothing was truly lost due to the backups, so I ran them on my local machine instead and all was good with the world.
  • Bacon on a doughnut is divine. Voodoo Doughnut is a can’t-miss destination if you’ve never been to Portland before. For DBAs, you’re in even more luck, because one of their specialties just happens to be the Bacon Maple Bar.
  • Buck Woody

    Buck says the cloud is more than vapor!

    There’s always something to learn. One of the things I love about SQL Saturdays and SQL Server in general is that no matter how much experience you have there will always be someone who knows more or is more familiar with with a specific subject than you. Not because they’re smarter than you, but because their work environment has led them to gain experience with different features of SQL Server. All these different people coming together to share these experiences for free is what makes SQL Saturday events and the community in general so amazing.

  • The cloud is coming for your career. I had the immense pleasure of finally meeting Buck Woody (@buckwoody), who gave the keynote on this topic. This should come as no surprise to any of us, and Buck gave plenty of tips on how to keep our skillsets relevant during this period of architectural change. It was an excellent talk!

PASS Summit

  • Quiz Bowl

    With Tim Ford and the Quiz Bowl trophy

    I love Seattle’s weather! Really. I know Robert L. Davis (@SQLSoldier) wrote a blog post entitled “Don’t Be That PASS Summit Guy” where he encourages first-timers to avoid doing things such as commenting on the rain or lack of sunshine, but I’m commenting in a positive way so I hope that’s alright. Seriously though I’m a big fan of overcast and rainy weather, and I think I only saw the sun for an hour or two over the 5 days I was in Seattle. I’m really not sure why I haven’t moved there yet!

  • Quiz Bowl. I had heard of it, but having never been to Summit before, didn’t exactly know what to expect. I’m so glad I went to check it out at the welcome reception, and was in disbelief when I was selected to play. I got paired with community expert and @SQLChicken extraordinaire Jorge Segarra, and together we dominated the game, emerging victorious. I took home a new iPad as a prize; Jorge was exempt from winning as only the participants picked from the audience were eligible. I was so excited to play, as it brought me back to the time I spent on my high school scholastic bowl team! A huge thank you to Tim Ford (@SQLAgentMan) and Louis Davidson (@DrSQL) for putting it all together, and to SQL Sentry for sponsoring!
  • There’s a lot of DBAs just like myself out there. I’ve been to a lot of SQL community events, but never anything as large as the PASS Summit, which had probably ten times the number of attendees as any SQL Saturday I’ve been to. It’s quite inspiring to know how many other people out there share my excitement and fascination with data and its intelligent management.
  • I’m really bad at names and faces. I have a terrible habit of forgetting who people are if I’ve only met them once or twice. If this happened to you at the Summit, please accept my most sincere apologies. While it’s no consolation, please know that it horrifies me to have someone walk up to me and start talking like they know who I am and I can’t for the life of me figure out who they are.
  • Feel the community love. I was incredibly happy to be able to meet lots of people in person that I’ve previously only chatted online with. So many that I won’t enumerate them here for fear of this post becoming a laundry list of names. You folks know who you are though, and I’m so glad to be able to put a face with your name and twitter photo!
  • Karaoke

    Serenading the unicorn at SQLKaraoke

    The sessions are awesome. So many blog posts about PASS Summit talk about the social and community aspects, and they’re absolutely right. Getting all these awesome people together in one place lends itself to some amazing networking. But just like back in college where I had to go to class every once in a while (or at least that’s what I’d tell my parents), the real meat and potatoes of Summit is the sessions, and they were incredible. For me, the hardest part was deciding which ones to attend because I’d often find myself having to pick between 2 or 3 that I really wanted to see.

  • Twitter and Foursquare are incredibly useful. Not that this was any secret before, but it became especially apparent to me while at an event with so many attendees that use them. There was a constant dialog on Twitter of where people were, what sessions they were attending, and how they were going. Similarly Foursquare made it very easy to let others know where you’re at so you all can meet up. I really can’t imagine the Summit without social networking tools!
  • SQLKaraoke is epic. I’ve been reading the tweets and blog posts about it for a few years now, and have partaken in karaoke at several SQL Saturdays, but nothing prepared me for the real deal at Bush Garden. I’m not sure I have the words to do it justice, but it just so happens that there’s a SQLKaraoke Blog which can probably do a much better job than I!

So that was my week in SQL Paradise! I really can’t wait to do it again next year! If this sounds like something you’d enjoy, registration is now open for the 2013 PASS Summit in Charlotte, NC. Register before January 4, 2013 and receive the lowest possible price!

Nov 202012
 

At work, a key function of my team is importing new systems into our data warehouse, most of which come from vendors. To do this we utilize many different methods depending on the capabilities of the source system and desired timeliness of the data. ETL through SSIS, snapshot replication, and transactional replication are among the different technologies we employ.  Occasionally for data sources backed by small SQL Server databases, we’ll restore full backups of the database in lieu of ETL. This method is very straightforward and requires zero modifications to the source system, since all we need are copies of backups that are already being created.

When restoring a backup for this purpose I will always run DBCC CHECKDB to make sure all the data is intact before proceeding. Last week I was working on importing a new system in this way and for no reason at all I decided to check and see when the vendor last ran a consistency check before I started my own. SQL Server keeps track of the last successful consistency check and stores it in what’s known as the “boot page” of each database, which is page 9 of file 1. The contents of the boot page can be viewed by using either the DBCC PAGE or DBCC DBINFO commands, both of which are officially undocumented by Microsoft but widely mentioned in blog posts. Paul Randal (@PaulRandal) has an excellent post on this topic that explains some of the data stored on the boot page.

While both commands will return what I want in this case, I usually run DBCC DBINFO as it’s quicker to type, so that’s what I’ll demonstrate. To view the boot page using DBCC DBINFO you first need to enable trace flag 3604 so SQL Server will direct its output to the client. After that, pass the name of the database into DBCC DBINFO as shown below:

DBCC TRACEON (3604);
DBCC DBINFO ([DatabaseName]);

You’ll see that the boot page contains a great deal of information, but what we’re interested in is the dbi_dbccLastKnownGood field, which contains the date and time of the last run of DBCC CHECKDB where no errors were found.

Getting back to my story, this database I restored from our vendor showed a dbi_dbccLastKnownGood value of 1900-01-01, which means that DBCC CHECKDB has never been run on that database (or at least has never completed successfully.) This led me to wonder if the vendor wasn’t running consistency checks, or if the restore process simply cleared out that value. I turned to the #sqlhelp hash tag on Twitter, where I got a quick reply from Paul Randal saying that the boot page is copied from the backup and restored just like any other page in the database.

To prove this on our own, here’s a simple test:

-- create a database
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'TestDB')
	DROP DATABASE TestDB;
GO

CREATE DATABASE TestDB;
GO

ALTER DATABASE TestDB SET RECOVERY SIMPLE;
GO

USE TestDB;
GO

-- run DBCC CHECKDB on it
DBCC CHECKDB (TestDB);

-- view the boot page
DBCC TRACEON (3604);
DBCC DBINFO (TestDB);
-- save the output of DBCC DBINFO to a text file.

-- back the database up
BACKUP DATABASE TestDB
TO DISK = 'F:\TestDB.bak';

-- restore a copy of it
RESTORE DATABASE TestDB_Restored
FROM DISK = 'F:\TestDB.bak'
WITH MOVE 'TestDB' TO 'F:\TestDB_R.mdf',
MOVE 'TestDB_log' TO 'F:\TestDB_R.ldf';

-- view the boot page from the restored copy
DBCC DBINFO (TestDB_Restored);
-- save the output of DBCC DBINFO to another text file.

From here I compared the two text files using a compare utility. I use WinMerge because it’s very simple, and also can generate HTML output which you can see here. You’ll notice the dbi_dbccLastKnownGood values are identical as expected. There are very few differences between the files, but all of them are a necessary part of the restore operation. The fields I saw differences in are:

dbi_dbid – Database IDs must be unique within an instance, so a restored database will receive a new ID.

dbi_dbname – We also restored our database with a different name, so the names are different as well.

dbi_crdate – The time the database was restored is listed as its creation date, so these values differ.

dbi_dbbackupLSN – I’m speculating here, but I’d imagine these fields differ because I viewed the boot page of TestDB before I backed it up.

dbi_LastLogBackupTime – This database is in the SIMPLE recovery model so log backups aren’t possible, but the restore operation apparently sets this value.

recovery fork information – These fields will differ because my restore of the database created a different recovery path. This is a very interesting topic, and I recommend reading about recovery paths if you’re curious.

service broker – Every database should have a unique Service Broker ID. Service Broker IDs should be unique globally, but there’s no easy way to enforce that, so instead it is enforced at the instance level. Backing up and restoring a database doesn’t change it’s Service Broker ID by default, so if you restore a database with the same Service Broker ID as an already existing database, Service Broker is disabled on the new database. You can see that the dbi_svcBrokerGUID values match in both files, proving this is the case. To avoid Service Broker being automatically disabled at restore time, a new Service Broker ID can be generated during the restore by using the NEW_BROKER option in the RESTORE command.

We now have proof that the dbi_dbccLastKnownGood field is not changed by backing up and restoring, meaning that the vendor in question was not running DBCC CHECKDB. Don’t be so quick to yell at your vendors though, because it turns out that not all integrity check operations will update this field. Erin Stellato (@erinstellato) wrote a great post last week on what checks update dbccLastKnownGood. Be sure to check it out to get that part of the story.