Recovering From a Deleted Cluster Resource

I recently had the joy of hearing about a security issue on one of our SQL Server Failover Cluster instances, in an attempt to resolve the problem, someone inadvertently deleted the clustered SQL service from Failover Cluster Manager. Why? I have no idea—but I set about trying to figure out what to do to recover from it. Note—all of these screen shots are from Windows Server 2012 and SQL Server 2012, but the same technique applies in 2008R2—I don’t have a 2003 Cluster to test on, and I think the procedure may be a little more manual there (you may have to make a registry entry).

So how do we delete the service?—easy, right click on it and select remove.

Windows will try to warn us, but for the purposes of science we will continue:

After we click yes, the cluster deletes the service, and SQL Server goes to sad panda mode. This happens because the SQL Server was dependent on our Virtual Server Name, which disappeared when we deleted the service. I’m not sure why the Agent looks like it’s still running—it to is down.

A quick look over at our Domain Controller, shows that our cluster object (SQL2012CL1) is still in AD, so no recovery is needed there—so we don’t need to recover it. However, our DNS entry is gone.

So where do we start the repair process? Select Roles in Failover Cluster Manager, and then select Configure Role

Select the role “Other Server”

Set your Client Access Point—use the same name and IPAddress as your previous service.

Select your applicable disks—this is my demo instance, so I only have one cluster LUN.

Here’s where it starts to get good. We select the SQL Server and SQL Server Agent resource types.

It looks like we may be done—we aren’t.

This is where we need to set our dependencies and properties. So first right click on “New SQL Server” and select Properties

SQL is going to be dependent on the network name, the IP address, and the Disk that our cluster uses all being online.

So we add those—next go to properties in that same window. We need to set VirtualServerName and InstanceName to their correct values.

Lastly, I set the service name to the right value, but I don’t think it matters.

We need to do a similar process for the SQL Agent. It is only dependent on the SQL Server service.

We also need to define the parameters for it.

Then we can try to start everything up and yes, it’s all green!!

Hopefully this never happens to you, and it shouldn’t but in the event that it does, you now know what to do.

Resources from NoVA SQL—Virtualization for DBAs

Thanks for the Northern Virginia SQL Users group for letting me speak last night—it was a great time and the crowd was fantastic. Here are some resources from my presentation:

Slide deck is here

Denny Cherry’s blog post on tuning the VMWare vCenter database is here.

Microsoft’s licensing information on Hyper-V is here.

Lastly—to answer a question—Hyper-V can be installed on Windows Server Core—the details are here.


I’m Speaking at Northern Virginia SQL Server Users Group

Tonight I will presenting on Virtualization for SQL DBAs at the Northern Virginia SQL Users group at 7 PM in the ATT Building in Vienna, VA. I’ll be talking about topics like memory management for VMs, how monitoring your virtual servers is different than your physical, and why storage performance is even more important in a virtual environment. I will also touch on some of the cool new virtualization features that are built into Windows 2012.


Party Planning and Managing Databases—Similar or Different?

One of my many hobbies is food—cooking, eating and preparing it are some of my favorite ways to relax and wind down. I’ve taken this way too far at times, and while I would never call myself a chef (that term is reserved for leaders of teams of cooks), I’m not a bad cook. One of the more adventurous meals I’ve ever cooked was this New Year’s—we had a total of 6 people, who were all really into food, and I prepared a really great four course meal featuring foie gras, squab, lobster, and about 356 plates that needed to be washed.

Recently, I was the event organizer for SQL Saturday #121, in Philadelphia. In a moment of hubris, or perhaps drunkenness, I decided that we should have the after party at our house. So this wouldn’t be cooking for 6—it would be cooking for 20-40 people, who had varying tastes and interests, and the best part is I would be arriving at the same time as the guests. So I needed to do almost all of the preparations in advance, so that my wife (my co-worker) wasn’t pulling her hair out trying to get ready. At the same time, I take a lot of pride in my cooking, and didn’t want to just serve crap that I got from the grocery store.

So, how does this relate to us as database professionals? Think of that New Year’s Eve dinner as a mission critical database, which you are 100% in control of. I managed all the ingredients (the queries), the plates (the hardware), and the cooking (the tuning). I had a limited audience, so I could give them 100% of my focus—I cooked for 3-4 days for that meal. Much like you might spend 100% of you time on that one mission critical application. When it came time for me to serve the meal (go live) I had a super detailed checklist of what went on each plate, and what needed to be reheated and when. I even thought out the courses—in effort to minimize the amount of work I would need to put into each one. (optimization, scripting). After all, I wanted to be able to enjoy the meal, too. (Leave early)

Planning for a big crowd is a lot different—especially when they aren’t your sole responsibility. Imagine going from managing one application, to managing all of the databases for a business unit. You can’t spend all of your time optimizing one thing (making the fantastic squab reduction that takes 6 hrs). Yet, as a professional you still have a lot of pride in your work, so you want to deliver. (good food). Where do you make the decisions to compromise?

The first thing I did was outsourced dessert (You rock Erin!) and bread—I’m not a great pastry cook or baker, and both of these items are labor intensive and messy. (Think of them as tier 1 support). This was an easy decision from me—dessert is probably my weakest point as a cook, and as a professional you need to know when to bring in outside expertise.

I knew people would be hungry and thirsty when they arrived, so instead of doing something labor intensive like cocktails, we had cava and a keg (Yards Saison—yummy) ready to go. Also, instead of fancy hors d’oeuvres or caviar (sorry guys—I had a budget), we had hummus, olives, baba ghanouj and great cheese. (there was an Époisses) All of these were easily prepared well in advance (scripted) and just needed to be put in a bowl or on a plate. (clicked and run).

For the mains I made pork shoulder—this could be done a day or two in advance, and reheated (again—like for any major DB project if you can do the hard stuff in advance do it)—it also has a large margin for error (easy to rollback from), and is really great picnic food. I made a wide variety of sauces—a couple from Momofuku, and a more traditional BBQ sauce. K made potato salad which has bacon, and I knew we had a couple of vegetarian guests (Developers) so we made another chickpea salad, and cool mushrooms (no, not those mushrooms) when they arrived. Amazingly, after a long day, everything went wonderful, and we had a really great party.

So where am I going with all of this? When you are dealing with a large number of applications (or guests), you can’t afford the time and budget that you can when you are working with a much more limited set, with less diverse requirements. Also, PLAN, PLAN, PLAN, and then execute. Chefs use lots of checklists—I know I did for both of the parties mentioned above, DBAs should use checklists and scripts. You won’t forget it if it’s done in advance.




SQL Saturday #121–It’s a Wrap.

On Saturday I completed the first SQL Saturday, I’ve ever personally organized, I’ve spoken at many, volunteered at several, but this was my first time running the whole show. Our usergroup had done this twice before, so we weren’t totally new to the process. After sleeping about 15 hours yesterday I wanted to blog about and the lessons we learned.

I cook a lot–and I tend to get in over my head at times planning dinner parties, and end up doing these elaborate multi-course feasts. You prepare, prepare and prepare, make lots of lists, organize things in nice little boxes, and then when it’s time to execute, the event itself goes flawlessly (including the elaborate after party  K and I cooked for). SQL Saturday 121 went flawlessly.

Before I get my lessons learned, I would like to thank my speakers and most importantly my volunteers, and also my darling wife who ran the soon to be legendary after party. In particular Bill Emmert and Mark Kromer from Microsoft who arranged our wonderful venue, and made facility logistics a breeze. Also, Paul Rizza from Microsoft who stepped up to take care of whatever needs that cropped up the day of the event. Vince Napoli, who is the president of our user group helped me whenever I needed help, and Chuck Kruelle was everywhere and did everything on the day of our event. Also Christina Leo, Erin Stellato and my wife, Kelly blazed through the kitchen during the after party, made it much prettier than it was before. Also, thanks to Adam Belebczuk for making an ice run to make sure the after party had cold drinks.

So overall, the event went great, and frankly I wouldn’t have changed much. Here are somethings to always think about when running an event.

  • Always talk to your speakers to make sure you have a couple of back up sessions–people will cancel on you, sometimes it will be early, sometimes it will be two hours before their session. If you have sessions ready to go, it is pretty painless.
  • Birds of a Feather lunches are really cool idea–for both networking and logistical reasons. Especially if you don’t have a centralized area for lunch-it works really well for routing people into rooms.
  • Start planning your event as early as possible–we started planning in the January time frame, which gave us plenty of time to organize and not stress too much about little things.
  • Also, communicate with your speakers early and often. I say this more as a speaker than an event organizer, as it’s really nice to be able to plan your travel and know your schedule well in advance. We set our event schedule up about 6 weeks in advance of the event.
  • If you are planning on having a speaker’s dinner, also announce that early (once again so speakers can plan travel around it–they don’t need to the locale, just that you are having it). I know some events have shied away from doing them because of lack of budget, but even if you don’t have funding–it’s really just a good excuse for a networking event and a few adult beverages.
  • On that note, start thinking of a speaker’s dinner location a couple of months out as well, it’s not easy to arrange for a big party on short notice, especially on a Friday night–I couldn’t get my first two choices two months out.
  • Work closely with PASS–Karla Landrum is a wonderful resource and friend, and answered all of my questions (Usually by sending me a link I should have found) quickly.
  • Always, always have a backup. I have a blog post two posts down about not having a backup, and it wasn’t the only time we almost got burned by not having a backup.

So thank you again to all of my attendees, speakers, and sponsors. Everyone did a wonderful job and I got nothing, but positive feedback. Who knows, I may even think about running this again! Notes–photos from the event are available here.

Database Stats in AlwaysOn Secondary Replicas

I’ve been doing a lot of presenting lately on the new feature in SQL Server 2012—AlwaysOn Availability Groups, though most of by presentations are more configuration and infrastructure related, I am always interested in system performance. So one day, while getting ready for a presentation I read this in books online. Little did I know the rabbit hole it would lead me down.


Statistics for Read-Only Access Databases

Statistics on columns of tables and indexed views are used to optimize query plans. For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database.

Only SQL Server can create and update temporary statistics. However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

Delete temporary statistics using the DROP STATISTICS Transact-SQL statement.

Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

So Microsoft says that the stats are created in TempDB. So let’s test that theory. I have test stats table in AdventureWorks 2012, and the column “Gender” is not indexed, so it doesn’t currently have a statistic associated with it. So when we query it a statistic will get created

The is_temporary column on sys.stats shows that the statistic indicates that the stat is temporary—also, the _readonly_database_statistics suffix on the name of the statistic also indicates that the stat is of a temporary nature. But why is it showing up in AdventureWorks instead or TempDB—let’s see if it shows up in TempDB.

So it’s not in TempDB—even though books online says it is. This is where the chase down the rabbit hole begins. First, I looked up the definition of sys.stats.

I found that sys.stats joined sys.sysidxstats and tempstatvals. Both of which I presumed where in ResourceDB (which is a hidden system database that we can’t easily query). A google search revealed:

To query ResourceDB you can put your instance into single user mode (-m) and issue a “use mssqlsystemresource” (Thanks Adam Machanic for that). But I decided to be really bold and break out a hex editor. (Using a backup copy of ResourceDB). That got me this:

The crux of that is tempvalstats is derived from tempdb.sys.sysobjvalues (specifically valclass = 54). I suspect that the SystemValueClass is related to hex value below that I haven’t been able to decode.

So what all this shows me is that tempdb.sys.sysobjvalues OBJID is equal to the database id, subobjid is the object_id in the child database, and I’m not sure what the valnum column represents. Value and imageval are hex that I was unsuccessful in trying to convert to anything. However, what this finally shows me is that indeed the column statistic has been created in TempDB, which makes perfect sense, since the replica database is read only.

I hope you enjoyed this trip down the rabbit hole. Special thanks to Jonathan Kehayias, Adam Machanic, and Erin Stellato for their assistance in figuring all of this stuff out.

The Dog ate My Checklist—Or Why Backups Are Important

So as most of you may know, I’m the event organizer for SQL Saturday #121 Philadelphia. So I’ve been making lots of lists, given that I’m not as organized as I should be, some of these are in OneNote, some of them are in a text file in Notepad++, but in the last few days I’ve been keeping a list on paper list with a bunch of tasks on it. Since I had the grocery list for the after party on it, I tore it off of my lovely A4 pad (because I’m Euro like that), and took it to the store. I’m pretty confident I left it on the kitchen table after returning home with the groceries.

Molly is a lovely dog—we’ve had her for nearly 6 years now. She is wonderful with children and people, and doesn’t destroy much (she still has all of the toy’s she had when she was a puppy), however she has always had a penchant for chewing up plastic bags and paper-not just any paper, it’s always some receipt I need for an expense report, or some bill we hadn’t paid. Kelly and I’s anniversary was yesterday, so we took a break from folding boxes to go out to dinner. When we returned home we found this:


Yeah—that pile of paper on the towel, was my checklist. So why is this appearing on my database blog? Well think of my checklist as a database file that got corrupted, and had no backup. You are left trying to assemble bits and bytes of data using a hex editor. Fortunately for me it’s only one page that’s corrupted, but in a real database with no backup, you might have 10,000 corrupt pages that you need to go through.

Make sure you have backups—and test your restores.


%d bloggers like this: