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.

 

SQL Saturday #121 — Philadelphia

For the past several months, along with my outstanding volunteer staff, I have been in the planning phases for SQL Saturday Philadelphia. Thanks to the wonderful folks at Microsoft we have an outstanding location, our sponsors have been wonderful, and we have an awesome group of speakers assembled. You can find the schedule here.

Also, if you hve a smartphone and the Guidebook application, you will be able to download our event guidebook.

There is one bit of bad news, if you are reading this, and haven’t registered it’s too late–we’ve sold the event. You are welcome to join the wait list, and we will let you know if anyone cancels.

I will be presenting on SQL Server licensing at 2:45, and then doing a really cool panel discussion about managing your career in IT with Brian Moran and Karen Lopez. I’m also making a guest appearance in Mark Kromer’s Data Warehousing session.

I look forward to being able to breathe again next Saturday. I hope to see you there.

Upcoming Events

My speaking schedule is pretty packed for the next few weeks. Here goes:

  • 5/17 The Impact of FDA Regulations on Maintaining Your Database Environment — SQL PASS Healthcare Virtual Chapter. This talk will harken back to my days in the Pharmaceutical and Medical Device industries. I will discuss the rules and some tips and tricks I’ve learned along the way, to actually get work done while maintaining compliance.
  • 5/19 SQL Saturday #141 Waltham, MA I will be speaking at the Boston area SQL Saturday. I will deliver my session on HA and DR options in SQL 2012, as well as a new topic on the new Licensing Rules for SQL Server.
  • 5/26 SQL Saturday #106 San Juan, PR It’s been several years since I visited Puerto Rico–I supported them during my time at Wyeth, and always enjoyed my travels, so I’m looking forward to this trip. In addition to HA and DR, I will be talking about SANs for DBAs. I’ve updated that presentation with some new info that I learned about in my EMC training last month.
  • 6/8 SQL Saturday #121 Malvern, PA In addition to talking about SQL licensing at this SQL Saturday, I’m also the event organizer, a new role for me. I will also be in Mark Kromer’s Data Warehouse session to discuss a bit of storage architecture, and I’m doing a panel discussion with Brian Moran and Karen Lopez on why you shouldn’t let HR manage your career.

It will be a very busy few weeks, but I am looking forward to it. If you are at any of the events, please stop by and say hi.

SQL Saturday #118 — Madison WI

I’m here in balmy Madison, Wisconsin presenting today at SQL Saturday #118 presenting on High Availability and DR options in SQL Server 2012. Jes Borland (b|t) and the outstanding crew have done a fantastic job with this event.

Anyway, I wanted to slides and some resources.

Slides are here.

Books online for AlwaysOn Availability Groups are here:

MSDN.

The version of this presentation I gave at the 24 Hours of PASS is here (requires PASS login):

Videos

Windows 8 Early Impressions

Late last week, I fired up a Windows Server 8 VM—I wanted to play with it a home a bit, before setting up a large scale environment here at work. The first thing I noticed was how much the GUI has changed—there isn’t a start button, and most tasks are driven by the Server Manager utility and/or PowerShell. Since, I’m a big fan of the command line and Windows Core, I think this a good thing. Start learning PowerShell now—you will need it in the near future.

Upon further digging, I found a feature called cluster-aware updating. Further research there, took me to this Microsoft White Paper. This is a cool new feature that will allow for clusters to be updated automatically, reducing downtime and hopefully be a much more seamless process we have currently.

There are still some challenges around the GUI—this will probably be one of the biggest changes for Windows admins in a long time. DBAs—you have less to worry about, just don’t plan on physically logging onto your servers, but you’re doing that anyway, right?

 

What Version of SQL Server AM I Running?

I’m in the midst of a project using System Center Configuration Manager’s (SCCM) Desired Configuration Management (DCM) component. It has taken us some time to come up to speed on this, so our initial configuration checks were strictly against the Windows OS. Last week, we started working on the first SQL check—checking for service pack level. Initially, and for simplicity reasons, I wanted to avoid logging into to the database. So we put together the following PowerShell script.

 

 

 

 

It looked simple enough—we wrapped some version logic around it, and then tried to run it on a mass scale. It returned some of the following version numbers:

Version

—————-

10.3.5500.0

10.3.5500.0

10.50.1600.1

10.2.4000.0

10.50.1600.1

10.1.2531.0

10.50.1600.1

10.2.4000.0

10.51.2500.0

 

A few of those are actual legitimate build numbers, but most are not in the list at SQL Server Builds. This pattern is consistent in both the registry and the advanced properties of the SQL Server service in configuration manager. A connect item has been filed specifically about SP1 for 2008R2 doing this, but as the above data shows, it appears to be occur across multiple versions.

Additionally, I have a Premier support ticket open with Microsoft, but we haven’t had a solid response yet.

On Friday night after a glass of wine or two, I decided to see what would happen if I hacked the registry back to the “correct” version. I built a new VM using SQL 2008 R2 SP1—version is 10.50.2500, but shows as 10.51.2500. I did a global find and replace in the registry for 10.51 to 10.50, as well as replacing MinorVersion from 51 to 50. It worked in so far as PowerShell and SQL Config Mgr reported the proper version; however when I attempted to apply a CU to the instance, the installer broke. Don’t try this at home!

We can always log into the database and run select serverproperty(‘productversion’) and get the correct version—which SQL Server clearly isn’t getting from the operating system. This is a relatively minor issue, but I’m surprised it hasn’t risen to the surface more.

 

 

 

AlwaysOn versus Active Data Guard—A Value Proposition

With the launch of SQL 2012, much of my focus has been on the new Availability Groups functionality. I do lots of work in the HA and DR space, and the ability to utilize secondary copies of databases is an outstanding functionality enhancement to SQL Server. Additionally, now we can have multiple secondary replicas (up to three). One thing to consider is licensing—a lot of organizations used to leave their DR servers unlicensed—which is perfectly fine with Microsoft, as long are you are not using it. If you are using the read-only, or secondary backup functionality, those servers must be licensed. However, this will still be a lot cheaper than the alternatives.

As some of you may know, I also work with Oracle databases in my role—I’m on a cross functional Infrastructure Architecture team. Recently, I was involved in a project where I had the opportunity to evaluate Oracle’s Active Data Guard product, which is a cost option to Oracle Enterprise edition (meaning additional charge on top of Enterprise licenses). This product is a new feature as of Oracle 11G—mind you Data Guard has been around since version 8 of Oracle (around 2002) and is a fairly mature product, much like SQL Server mirroring (introduced in 2005 SP1). In terms of licensing standard Oracle Data Guard has been included in Enterprise Edition since it was introduced, however Oracle requires the DR server to be licensed at all times, even if it was not used on a regular basis. Oracle has supported multiple secondary databases for a while, and does support limited read use of a secondary (if in the logical standby model—which is fairly limited in its datatypes allowed).

Since, I’ve been playing with both databases recently—I decided to do a feature and cost comparison. All costs are from current price lists from MSRP from both Oracle and Microsoft. The server architecture is feature in the below picture; a primary, a DR and a reporting server . So three servers, which will for the purposes of licensing will have 2×4 core Intel CPUs.

 

Below is a table of Disaster Recover features in both platforms, along with the licensing costs (the detailed licensing costs are available upon request)

Microsoft SQL 2012 Always On Oracle Enterprise with Standard DataGuard Oracle Enterprise Edition with Active Data Guard
Licensing Costs $164,976 $570,000 $690,000
Take Backup on Secondary Copy Yes Yes Yes
Read Only Queries on Secondary DB Yes Limited Yes
Fixes Corruption in-line Yes Yes Yes
Multiple Secondary Replicas Yes (3) Yes Yes
Synchronous or Asynchronous Transfer Yes Yes Yes
Virtual Network Name Yes Yes Yes

As you can see, for the disaster recovery (and offline reads) SQL Server matches Oracle feature for feature, at less than 30% of the cost. No slight to Oracle—they have a great (albeit very expensive) RDBMS, I’m just impressed at what the SQL Server team did with AlwaysOn. And speaking as a DBA with many years’ experience with both platforms, I find Availability Groups easier to configure than DataGuard. Additionally, there has been some complaints about Microsoft’s new pricing strategy, however this shows how much cheaper SQL is, compared to Oracle.

24 Hours of PASS–My Session

Slides are here

 

So I had the honor of being selected to speak at the upcoming 24 Hours of PASS (3/21, or 21/3 for my international readers), and I was thrilled, almost as thrilled as if I had been selected to drive in the 24 Hours of Le Mans. Ha–since the racing thing won’ t be happening, I’m always excited to talk about databases.

My session will talk about all of the new HA and DR options in SQL Server 2012–I’ll cover what new in clustering and then discuss and demo the exciting new feature AlwaysOn Availability Groups. I’ve been test driving them for 6 months now, and am really happy with the feature.

As always if you have any questions or comments about my session share them here. And if you are in the New Jersey SQL users group, you will get a dress rehearsal, er sneak preview of my session the night before on March 20.

Matching EMC LUNs to Windows Disks

We are doing some SAN performance testing on the latest EMC VMAX array. As part of this, we are testing different storage configurations and policies and doing some baselining. I recently requested a set on LUNs for a new SQL install from my storage admin–I didn’t think about it, but I asked for disks that were the same exact size (200 GB) for both my data files and transaction logs. These disks were under different policies–so I had to identify them, which would prove difficult.

Windows storage manager shows me this picture of the disks assigned (note this pic is post fact–the disks have been configured):

EMC PowerPath shows us the following view:

As you can see we have two sets of disk numbers, and the numbers in PowerPath <> the numbers in Windows. One would think if someone looked under properties of the disk in Windows, you could find the underlying information. Unfortunately, they aren’t there. Enter the INQ utility, from EMC. It’s available via public FTP here.

You will run this utility from DOS, and use the following flag inq -showvol -sym_wwn. It will show the physical drive (which is the Windows number) and the EMC Device #–which is available in PowerPath. It’s still an ugly manual process, but it gets us the data.

Hopefully there are some PowerShell cmdlets for this info from EMC in a coming release of PowerPath.

 

 

 

 

Wheeling Presentation

Thanks to the Wheeling SQL Server usergroup for letting me present on SANs for DBAs.The slides can be found here.

Denny Cherry’s article discussing SQL DR options along with SAN DR options can he found here.

If you would like to vote for my session on New HA and DR Options in SQL Server 2012 at SQL Rally (and vote for John’s while you’re there!) vote here (until the end of the day today–vote here

%d bloggers like this: