disaster recovery Archives - Page 4 of 5

June 23, 2012

Even HA Guys Do Dumb Things

Hi again, everyone! It’s been a little bit since my last blog post but I’ve been here, there, and everywhere doing customer work as well as doing a lot of presenting (including my first mutli-day public high availability class in a long time this past week). I can finally take a moment and breathe.

One thing I’m catching up on is a bit of maintenance. As some of you who have seen me know, I have my little powerhouse laptop (Panasonic CF-J10) from Japan. Right now it’s coming up on its birthday. Hard to imagine that I’ve kept a laptop for a year without getting a serious case of upgradeitis, but I have. I have had one nagging problem that I always do – drive space. Even with a 512GB SSD inside, I still carried an external 512GB SSD and another 256GB SSD for a total of 1.25TB of space. This is largely due to the sheer numbers of virtual machines (VMs) that I use depending on the situation and which hypervisor I decide to use (either Hyper-V or VMware Workstation). The only downside to my J10 is that it isn’t truly a 6Gb SATA III bus.

I was very excited when OCZ announced their 1TB SSD quite a few months back. I’m no stranger to being an early adopter of hardware – especially SSDs. When I got my Toshiba R600 back in 2009 or so, it had a 512GB SSD. This is at least 2 years before they were mainstream and “somewhat” reasonable in cost. Toshiba I believe was the only one at the time doing them, and it was easily well over half the cost of the laptop (if not more). Same with the SSD (32GB I believe) in my Sony VGN-G1 in 2006 or 7. The Octane line came out but the 1TB version didn’t really appear until springtime of this year – and the price was stupidly expensive. I mean, even I flinched. A good 512GB SSD will still usually cost in the $700 – $900 range, and I figured double that, and maybe a little more. My 512GB in the Toshiba was about the same, so that’s what I figured. Boy was I wrong! List price on this thing is about $3,000 US, and is generally $2,500 (give or take) street price. Ouch. I can get 1.5TB in three drives for that (albeit not as convenient …). I pretty much decided that I’d sit this one out until prices came down to where I thought they should be. I know I’m weak on this stuff. This past week, I saw that a seller on Amazon had the drive at a price I was willing to pay, and I snagged it. The drive showed up on Friday.

I also usually put on pre-release versions of Microsoft OSes once they are fairly stable. This goes back to at least Vista where I ran it probably for 6 months to a year on my old Sony Vaio SZ, and the same could be said with Windows 7. With the Japanese laptops, this is always an interesting proposition especially since I like to make sure things like the function buttons work. With Sony, most things always worked and once the OS was officially released, everything did. Until now, I haven’t taken the plunge on Windows 8. My disdain for Metro on a non-touchscreen laptop is palpable, but I also know it’s the future. I’ve been using it a bit on Windows Server 2012, and I figured at some point I’d put it on at least one of my Panasonics (I still have the J9 and S9 in addition to the S10 – I really need to sell some!).

Disclaimer: I have this stupid penchant for making non-trivial changes (OS switches, drive swaps) to my laptops before doing something big like my training class this past week. I’ve fought that urge for the past few months because I’ve been on the road and didn’t want to cause myself unnecessary pain and heartache. Keep this in mind. I should have learned a drive change isn’t simple. And boy, this time I almost completely shot myself in the foot.

The original goal was to just change out the 512GB for the 1TB and leave my current Windows 7/Windows Server 2008 R2 dual boot. I have Paragon Partition Manager, so I was just going to clone the drive and change the partition sizes. Oh, if it had been that easy you wouldn’t be reading this blog post. No matter what I tried, I was getting errors. I ran surface tests – the new drive itself was fine. After about five hours of this, I was deleting the partitions (yet again) on the OCZ. Or so I thought. Unfortunately, I must have not been paying attention because I basically blew away my partitions on the 512GB SSD. Cue sad trombone.

My first thought when I knew I was in a bad state: crap – when was the last time I made a backup of any of this stuff and would I need to use it? It was somewhat recently, but not completely up to date. Worst case, I would lose minimal data. My work e-mail is all in Exchange and on a server, so I knew I had that (and since the documentation I do I send to customers and such, a lot of newer stuff would be there even if not in a backup). All was not bleak.

Before you ask, “Hey dummy, why didn’t you make a backup first of your data?” Well, the primary reason is that I was working mainly with the new drive and I wasn’t changing anything on the old and I knew I had a lot of it elsewhere where I could get it or recreate it. So in my risk mitigation, I deemed that low. Since I had been doing things right for hours, the odds of me screwing up were small. Well, I hit the magic window alright. To be honest, I’ve done this a lot in the past so again, the risk from my perspective was minimal.

I stopped and took stock of where I was: how would I go forward, regardless of the backup situation and what I had. In any disaster recovery drill, it’s one thing you need to do. Be calm, have a level head, and make smart decisions. I already did something monumentally stupid. The first decision I made was to take the 512GB out, put the 1TB in, and then put an OS on it just to get started (should have done that to begin with in hindsight). I installed Windows 7, and then made “the” decision – since I basically was starting fresh and we’re at RC, I’ll install Windows 8 – Metro UI warts and all. No time like the present. I got my OS up and running, and then installed Windows Server 2008 R2 for dual booting. I even got most of the Panasonic utilities working under Windows 8 – the only thing that isn’t working at the moment is the touch pad which is a shame because of the way the rounded touchpad should work. But the important stuff – function keys changing brightness, audio volume, selecting display for presentations – all work. Most of the hardware (sound card, display, USB 3) had drivers built-in. This was arguably better than the Windows 7 experience. In the roughly 24 hours since installing Windows 8, I still dislike Metro but have found ways to work around most of the stuff I hate and overall, it’s much faster than Windows 7 on this laptop. I’m surprised by it because Windows 7 was pretty zippy. If I didn’t bring up a menu to select between the two OSes (8 and Windows Server 2008 R2), it would boot even faster. This switch has also gotten rid of a nagging issue I had with the Crucial M4 where it would randomly spin up I/O for a minute or two (usually right after logging in, but sometimes before) and I’d have to wait until I could do anything on the laptop.

However, at this point was the difficult part: where the hell was I going to get my files from and what, if anything, would I lose? Paragon has an Undelete Partition feature. Fingers crossed, I ran it on my 512GB drive. The thing felt like it ran for 10 years. When it was done, I was able to get back two of the three partitions – my old Windows 7 with most of my files (so no data loss there – yay!) and the one with my Hyper-V VMs and various installs I use when using VMs. What I did lose completely was my Windows Server 2008 R2 partition and any of the recent scripts I had written for Windows Server 2012 (including my TechEd ones and one I was working on to change the binding order of the NICs). I lost a few other files, but in the grand scheme of things, I got very, VERY lucky. It could have been much worse than things turned out.

Moral of this story: even HA guys have bad days and do stupid things to their own equipment or installs where I felt “invincible”. Had this been a customer environment, I would have definitely made a backup before doing anything, but I decided to be a bit more reckless with my own setup and I paid the price. I was fortunate. Not everyone is in these scenarios. At least I’m honest enough to admit my mistake. Outside of some of the scripts, I may need to recreate some of my VM environments but I enjoy that so it’s not a big deal. I needed to reconfigure my multi-site cluster demo anyway.

Last night none of this was funny, but now I can look back and chuckle, roll my eyes, and proverbially slap myself for falling into the trap many of us do. Although to be fair, things, while they were not working, were not going poorly all night. I needed to find a way to get from A to B, and my mistake was a completely honest one. I selected the wrong thing once and all hell broke loose.

EDIT: As a friend pointed out on Twitter, I had a D/R plan that worked. To a degree, that’s very true. I knew what I had to do, where I could get things, from, etc. Can you say the same thing? If not, you would really be in trouble in this scenario. I’m being very critical of myself because I did make a super dumb mistake.

Going forward and doing my own post-mortem, I’m going to take my own advice and look for a way to not have this happen again. It will probably involve putting things like decks and scripts up on the SQLHA SharePoint site Ben and I use for our business stuff. Lesson learned. I may run a program I’ve used in the past to retrieve files from a pretty messed up SD card to see if I can get the remaining files back from the SSD, but I may leave well enough alone at this point, take my own lumps, and be done with things. I’m up and running with my new 1TB drive … albeit about a day late.

Oh, as an aside, I have both Hyper-V enabled and VMware Workstation installed and both work under Windows 8 (unlike Windows Server 2008 R2). This is good news, but I’ll still need to dual boot since there’s no software routing in Windows 8 (client OS; Windows Server 2012 does).

April 13, 2012

Allan’s AlwaysOn Availability Groups FAQ

I’ve been working with the new AlwaysOn Availability Groups (AGs) feature for quite a long time now. As of late, I’m doing more and more SQL Server 2012 talks as well as consulting work and I figured I’d take the time to do a quick “trim the fat” blog post and answer a lot of the most popular questions I get and hear regarding AGs.

Disclaimer: Many of the answers could (and should) be blog posts or chapters in a book of course. I’m not trying to cover every “what if” and rat hole scenario here nor do a very deep dive into all of the minutiae of everything. For that, I may follow up some in blog posts over time, but all will be covered in depth in my upcoming book Mission Critical SQL Server 2012 which is tentatively due later this year (or early 2013) and will cover SQL Server 2012 on both Windows Server 2008 flavors (with a focus on Windows Server 2008 R2 with SP1) as well as Windows 8 Server. Since no RTM date has been announced yet for Windows 8, I want to ensure that I get the latest information in there before giving you guys a more definitive publication date.

And now without further ado, my AG FAQ. If I update this, I’ll note the date next to any updates over time.

Q: Is it true that AGs require a Windows Server failover cluster (WSFC)?

A: Yes.

Q: Why do I need a WSFC when DBM did not need it?

A: A WSFC is basically used for two things: quorum and the Listener. With database mirroring (DBM), the SQL developers essentially coded their own quorum mechanism for DBM in the Witness instance. With AGs, they are using a proven one built into the underlying OS. More on the Listener in a few questions.

Q: What edition of Windows do I need to deploy a WSFC? (added 4/16/12)

A: A WSFC requires that you’re using Enterprise Edition of Windows. However, pricing for EE of Windows is vastly different (at least through Windows Server 2008 R2; nothing has been announced for Windows 8 Server). Clearly cost matters, but you’re going to pay more for SQL than you will for Windows.

Q: What edition of SQL Server do I need to deploy an AG? (added 4/16/12)

A: You need Enterprise Edition of SQL Server 2012 to deploy an AG. Unlike DBM, there is no limited form of AGs in Standard.

Q: Do I need Active Directory?

A: Yes. A WSFC requires AD for various reasons, including the fact some cluster objects get a corresponding entry in AD. A WSFC also uses DNS, but your infrastructure already probably has DNS servers. Not everyone is currently using AD, especially if they are mostly a non-MS shop that happens to use SQL Server.

Q: Can I put AD on one of my cluster nodes if I don’t have AD elsewhere?

A: No. A node in a cluster cannot be a domain controller. SQL flags this during Setup, and it would be unsupported even if possible. See KB327518.

Q: Do all of my WSFC nodes need to be part of the same domain?

A: Yes. This will be a change for those who are looking to upgrade existing log shipping or DBM deployments where Instance A and Instance B are not in the same domain.

Q: Doesn’t having a WSFC mean I need shared storage?

A: No. This is where most people get confused when it comes to AGs. While the server participating in an AG is a node of the WSFC, it can just have a standalone installation of SQL Server. FCIs are not required for an AG. FCIs do have a shared storage requirement (even if just using SMB shares with SQL Server 2012). You can combine FCI + AG. That would require some sort of shared storage for obvious reasons for the FCI(s) involved.

Q: Does the WSFC have to be on physical hardware?

A: No. You can do this all with VMs with a supported hypervisor. The WSFC has to meet the supportability requirements and you must abide by the SQL Server virtualization support policy as outlined in KB956893.

Q: What is the Listener?

A: The Listener (sometimes referred to as the Virtual Network Name [VNN]) is similar to the network name and IP address(es) created when you install a FCI. Instead of connecting to the nodes, you would connect to the name or IP of the Listener which abstracts where the instance is. This means that applications and end users need only worry about one point of entry.

Q: Is there one Listener per WSFC?

A: No. A Listener is dedicated to a single AG. Different AGs do not and cannot share a Listener. A single WSFC can have multiple Listeners.

Q: Are AGs supported with a SQL Server 2012 deployment using Windows Server Core?

A: Yes. It doesn’t matter what flavor of the OS you use. However, with Windows Server 2008 R2 SP1, you would need to make a choice of all Server Core or full UI; you can’t mix modes and have a fully supported WSFC. Windows 8 Server introduces the concept of being able to switch between Server Core, MinShell, and full UI. It remains to be seen how SQL Server will support that.

Q: Is planning for and deploying an AG architecture easy?

A: It depends. Some are, others are not, and yet others may be deceptively simple. SQLHA can help you with this!

Q: Is quorum important? Isn’t that the Windows admin’s job?

A: When it comes to AGs, quorum is very important. DBAs need to get their heads out of the proverbial sand and learn more Windows stuff, especially WSFC if they want to deploy AGs properly(or FCIs for that matter). The bottom line with quorum is this: if you have enough voters up, you’re up. If not, everything is down (no matter how many instances of SQL Server may be running in that configuration – FCI or not). Quorum is not a straightforward discussion and one I will be addressing fully in most likely its own dedicated chapter in my upcoming Mission Critical SQL Server 2012 book. Things change even more (for the better IMO) with Windows 8 Server, which will be part of the book.

Q: Is it true I can actually use the secondaries/replicas for other purposes such as read-only queries and backups?

A: Yes. Depending on how you configure things, all of the replicas in an AG can be used for read-only purposes without any magic tricks or difficulty (such as database snapshots with DBM). The data would be near real time, meaning as soon as it’s there, it’ll be available. Full and t-log (not differential) backups can be made from a replica if the COPY_ONLY option is used.

Q: So what’s the trick to readable replicas?

A: Temporary stats are used, which means tempdb usage. Also, to minimize blocking for the writeable transactions, the queries against the readable replica use snapshot isolation no matter what and ignores any lock hints. That’s really the secret sauce under the covers, but it also means that you can’t have underpowered hardware and disk subsystems if you’re going to be doing read only queries on a replica.

Q: I had a bad DBM experience with regards to performance of synchronous mirroring. Will AGs fix that?

A: Probably not. AGs build on what DBM was (essentially), so if you’ve got poor I/O and network throughput, you will most likely see many of the same problems with AGs. There is no free lunch. If you want to configure synchronous (DBMs or AGs), you need the right underlying architecture to support it.

Q: Can I combine failover clustering instances (FCIs) with an availability group (AG) when using synchronous AGs?

A: Yes, but you cannot have automatic failover in this combination. This is a big improvement over FCI + database mirroring (DBM) where you had to futz with things like timeout values and even then it didn’t always play well. There are other concepts that may come into play with combining FCIs and AGs (such as asymmetric storage or quorum if we get into advanced configurations).

Q: Can an AG have multiple databases in it?

A: Yes. A single AG can encompass multiple databases (think of it like a folder, if you will).

Q: Can databases in an AG span multiple instances?

A: No. All databases in a single AG must be in the same instance. So if you have and AppDB_A in Instance_1 and AppDB_B in Instance_2, they cannot be part of the same AG.

Q: Are distributed transactions supported for a DB participating in an AG?

A: No. This is the same as DBM and log shipping.

Q: If there are multiple DBs in an AG, are they all kept in lockstep?

A: No. While they will fail over as a unit, the process for replication/mirroring the data in each DB is handled separately. You’ll need to sort out where they are from a data standpoint.

Q: Can a DB participate in multiple AGs?

A: No. A DB can be only a member of one AG.

Q: Do I need physical hardware to implement AGs?

A: No. A WSFC can be comprised of all VMs. As long as your WSFC is supported (see the aforementioned KB327518 as well as KB943984 for the official support stance on what constitutes a supported cluster from both a SQL and Windows perspective), you’re fine.

Q: Can I upgrade an existing DBM or log shipping implementation to an AG?

A: Yes. You can create the WSFC after SQL Server is already installed (assuming you meet all the other prerequisites). So even if upgrading from SQL Server 2005/2008 to 2012, you will have a migration path. That said, you still may have some things to do to ensure the underlying configuration meets the qualifications for a supported WSFC since the configuration may have never been thought to have been converted to a cluster node.

Q: Do I still need to worry about things like logins and SQL Server Agent jobs?

A: Yes. An AG is database-level protection (like DBM or log shipping), so anything residing outside the DB must be part of your planning. If you use contained databases (new to SQL Server 2012), it could potentially account for logins.

Q: How will AGs change my licensing for SQL Server?

A: I have no idea. I’m not a licensing expert nor do I play one on TV. Talk to MS or whoever is responsible for your SQL Server licenses to determine what your licensing will be based on your configuration.

Q: Is HADRON the same as an AG?

A: HADRON was an early name for availability groups. You may also see HADR as well (and is what is used for many of the DMVs).

Q: Is AlwaysOn the same as an AG?

A: No. AlwaysOn (no space, thank you very much) is a designation that covers both the AG and FCI availability features in SQL Server 2012. It is incorrect to refer to AlwaysOn as an AG. At one point during SQL Server 2012’s development AlwaysOn was used only for the AG feature, but that is no longer the case. I have a feeling this will be the new active/passive and active/active.

Q: Do AGs replace FCIs?

A: No. See my earlier blog post on this topic.

Q: So what about DBM? Is it going away?

A: Yes. DBM has been deprecated in SQL Server 2012, but don’t push the panic button. See my earlier blog post on this topic.

<shameless plug>

SQLHA can help you with your planning and deployments of AGs and we’re doing that already for customers (and have been since helping a customer in the TAP program since early last year). We’ve already got the real world expertise to jump start your SQL Server 2012 deployments. Check out our brand new Availability Group service offerings, and feel free to contact us even if you want something other than one of our offerings.

</shameless plug>

April 3, 2012

The Tube: A Paradigm for Capacity Management, Performance, Availability, and Disaster Recovery

Howdy everyone! I’m back in the good ol’ US of A after my little jaunt in Europe and trying to get caught up with the e-mail and work that piled up. I posted a bit about Germany in my last blog post, but I have to say that when I got back to the UK, SQLBits X was wonderful. Dare I say it was better than SQLBits VIII in Brighton last year. Thanks to everyone who came out for my Training Day and to my one session last Friday. I hope to attend and speak at another SQLBits next year.

Anyway, while I was in the UK I was up one night going over my presentation and lo and behold, I saw a show on the Tube (nee London Underground). It was one episode of an ongoing series on BBC Two. As a train enthusiast, I was enthralled. I’ve been into trains since I was a little kid. The show is based around what happens behind the scenes to make I then went online and saw I could watch previous episodes online. By the time I checked out of my hotel room on Sunday I had seen all six available. It’s a fascinating look at all the things that need to be done to ensure that millions of people can continue to be transported every day. One episode in particular – number 4 – stuck out at me.

However, in spite of my own mind which wanted to enjoy it only at a surface level, I started thinking about the day job and how similar it was. First and foremost, the Tube is in the middle of a 10 billion pound upgrade to its system to improve service that will take years to complete. All of the work needs to be done while still servicing customers, but at the same time, look at the future and increase its capacity. Of course there are going to be inconveniences and closures at times on parts of the system. This is something we need to account for in IT, too.

Currently I’m finishing up the HA chapter of the SQL Server 2012 Upgrade Guide which will hopefully be out soon and it was like kismet watching this. At some point you’re just going to incur downtime and the business is going to have to deal with it. You need to carefully schedule these outages, but it still may be an inconvenience. Seeing the Underground closing lines on weekends to replace sections of track and then stressing out only hours before having to open on a Monday morning for people to be able to get to work is the same as we have to do. Any number of things can happen between the time you start and finish to slow you down or derail you.

Capacity management is one of those things very few do well out there, be it sizing for new systems or understanding where you are today to better understand how to manage right now. The Tube has to account for the number of riders every day and in each episode they gave specific numbers of strain on the system. I forget what they were referring to exactly (a station/line/whole system), but at one point they were estimating 4,000,000 riders by 2016), but they hit that in 2011. So even planning and estimating doesn’t help sometimes. All throughout the episodes they were giving statistics on riders and how the numbers have increased in recent years. It’s the same for us – acquire a company, hire more people, whatever – the more people that use a system, the higher the load. EVERYTHING needs to work right to be in balance or else it could all go south very quickly. Consider these two quotes from Episode 4:

“The Tube, in a sense, generates its own traffic. As soon as you upgrade something, as soon as you put in another couple of trains per hour, you find that the capacity is taken up … The more you expand, the more people use it.”

“As soon as you fix this congestion point, there’s another one along the line somewhere else to fix. So it’s a never ending task. What we have to sometimes do is deliver on the impossible.”

Do these quotes sound familiar to you? They should. It’s what you live every day. The first quote addresses what I was talking about above. The second quote is not unlike that traditional bottleneck triangle we always talk about with computer systems (I/O and disk, CPU, and memory). Fix one, and some other will appear. This is why you’re constantly monitoring and dealing with your systems. But of course you never need a DBA or sysadmin – SQL Server manages itself, right?

What was fascinating to watch was the central control around the number of trains in the system on any given line and how a backup or delay in one place causes a downstream effect. Also, if they have too many trains in play to handle capacity, if there is a backup, they may not have enough stations to accommodate them. I forget which line they were showing, but they had 31 trains in play but capacity for 27 at stations. Things stack up. This speaks to what I talk about with multiple clustered instances of SQL Server on a Windows failover cluster – you need to think of the failover condition. Could one node run all instances? And if not, which won’t work?

Speaking of not needing a DBA, it was very interesting to see the daily maintenance they do on the trains and stations just to keep the system functioning – again, not unlike the tasks we need to account for. From picking up trash and cleaning windows to removing dead animals and fixing trains themselves (and everything inbetween, including fatalities), it ALL needs to be done. It’s not a matter of if, but when these things need to be done. You can’t ignore them or else you’ll have to take a bigger outage to fix it all.

Last, but certainly not least, testing loomed large. They were rolling out new trains on the Victoria line and elsewhere. They had two major problems after introducing them into the system:

The new Victoria line trains have sensitive edges to ensure that no one gets caught in the door, but the settings were found to be too sensitive (we’re talking the difference of millimeters) which caused a lot of delays which had that downstream effect I was talking about. If the doors can’t shut right at one station, the whole line gets backed up. The example shown was at the Seven Sisters station, but the problem manifesting itself at Oxford Circus where they had to shut the gates to not allow anyone else into the station there or onto the platform, causing unhappy riders in addition to the backups and delays. No one is happy there – including those responsible for the Tube. This one problem caused nearly 25% of all delays that one week.
There was a software glitch on another set of new trains on another line that was identified but needed to be fixed.

Train Testing

The upper management from the Tube got on the phone with the builders (also in the UK) and the meeting was like some of those I had and continue to have with customers. I always say production should never be a guinea pig (especially when it comes to things like patching and updating; this happens often in clustered environments). Clearly the Tube management was not happy and this one quote stuck out when the manufacturer was talking about figuring out the problems, and the management retorted:

“I know that’s what we’re doing but we’re doing it in service!”

AMEN! What that conversation led to was one that things need to be better tested. Testing is crucial, and something I espouse all the time in anything I write or do. It’s one of the most important things you can do before going into production to ensure minimal-to-no downtime. ’nuff said.

The funny thing is to see the customers of the Tube saying things like “I bet I could do this in an hour”. I’m sure your end users and the business think that what you do is simple – just hit a button or install a widget and you get the /faster, /morecapacity, or /betteravailability switches. What they don’t see, for example, is the complexity of completely removing miles of track over a weekend, replacing them, and then having things working on Monday like nothing happened. What they do on the UNderground and what we do is not always a walk in the park.

This series is HIGHLY recommended to watch if you’re in the UK (especially Episode 4). Look beyond the Tube and see its meaning for your daily job. Unfortunately we can’t watch it outside of the UK which is a real shame.

February 20, 2012

Will SQL Server 2012’s Availability Groups Replace Clustered Instances?

Happy Monday, everyone!

SQL Server 2012 is right around the corner, and it’s a release that is right up my alley in the mission critical space with features like availability groups (AGs) as well as Windows Server Core support. I’m looking forward to its RTM and subsequent release into the world.

However, I keep getting one question over and over from various people so I thought I’d address it to a wider audience. The question is a simple one: Will AGs replace or do away with the need for a clustered instance of SQL Server? If you want the short answer, it’s a resounding NO, but read on if you want to know why I’m saying that. For the record, if you were in doubt, both are branded under the designation marketing gave to the mission critical features of SQL Server 2012: AlwaysOn (with no space). Make no mistake about it: they are two different features with a single marketing designation.

First and foremost, let me say this: because this is going to be a quick, down-and-dirty post, there will be no pictures. I’m breaking one of my rules here, because this topic could use one (or more). Hopefully without the pictures all of this will still make sense.

Availability groups and clustered instances of SQL Server (also known as FCIs) have one thing in common: Windows failover clustering. AGs use Windows failover clustering (WSFC) as an underlying platform for things like the listener and to provide quorum (like the optional Witness configured on an instance did for database mirroring [DBM]). It makes sense – use a standard, well-proven process like WSFC and not reinvent the wheel. The cool thing about availability groups is that you do not have to cluster the instances; you can use standalone instances on servers that are clustered (those servers are also known as nodes).

[Edie and side note – This is the one aspect that seems to confuse folks with AGs: requiring WSFC but not needing an FCI. I hope you now understand why.]

Here’s the main difference: FCIs protect an entire instance of SQL Server, while an AG – just like log shipping or DBM – only protects at the database level. AGs are much better than DBM in terms of what they offer (example: readable replicas), but you still need to worry about things like logins at the instance level, SQL Server Agent jobs, or any other object that is not in the database. With SQL Server 2012 you could use contained databases to solve the login issue, but that won’t be an option for everyone. You need to think about both levels of protection, and there are a ton of other factors which would influence how a final solution is built. I do not want you to think this is going to be a black and white decision.

The bottom line: it depends on what your goals are for availability and/or disaster recovery. One nice thing about FCIs and AGs is that they play well together, unlike FCIs and DBM. That latter combination, depending on what you wanted to do, could work well together but it was never 100% ideal. There are certainly some caveats to the FCI/AG combination, but that’s a whole separate topic I’m not going to even touch here. I don’t want to give you the wrong idea – combining isn’t bad nor impossible, but like anything else, you do need to plan properly to have them work optimally.

I hope this clarifies things. If not, let me know in the comments and I’ll address any questions there or in another blog post.

October 31, 2011

So You Say You Want To Be Mission Critical? Hire a DBA

I don’t know what’s in the water in IT departments, and it’s definitely not the fact that today is Halloween. No tricks or treats in this blog post! I’ve had a lot of conversations with companies over the past year that are trying to be mission critical. By mission critical, I mean near 24×7 uptime (give or take). While I think that’s great and definitely an area I’m well suited to help them with from a consulting perspective, what I don’t like to hear is one of the following:

We don’t have any SQL Server DBAs
Our <insert worker already doing other things> is our part time DBA

I’m finding many companies are simply lacking a wholly owned, full time SQL Server DBA on their staff. I’m perplexed. Those two things – not having a DBA of any sort and being mission critical – are at odds. Let’s dispel some myths and deal with some realities.

SQL Server Manages Itself

I used to work at Microsoft and am lucky to still have a close relationship with them. Years ago Microsoft had a five nines campaign that launched around the same time we were doing a week long SQL Server 2000 high availability event. They were basically trying to show that Windows was ready for the enterprise and five nines (99.999% uptime – 5.24 minutes of downtime per year). The ad I remember most had the picture of a data center with a lone, empty rolling chair and balloons floating around insinuating everyone is out having fun and things managed themselves basically. They couldn’t have picked a worse time to send such, in my opinion, a wrong message. By the way, here is proof of that campaign. I can’t dig up the ad anywhere. If you have it or know where it is, please link below or e-mail it to me! Anyway, anyone who has done any production work knows that message in that campaign is wrong. Ever get paged at 2AM? ’nuff said.

SQL Server marketing has done an excellent job over the years of making it seem like SQL Server manages itself. To a point that is true with some of the stuff internally in the engine such as statistics (although there are cases where you may have to deal with those manually), etc. All IT shops that use SQL Server have the same basic care and feeding needs, but the scale is different in a small shop vs. a large enterprise. If you think you can effectively manage hundreds or thousands of databases and along with their accompanying instances (and all of the associated hardware) with no DBA or at least having someone on staff who knows SQL Server, I have a bridge to sell you in Brooklyn.

Sure, you can automate some tasks like monitoring and some tasks to take care of known issues, but you still need human beings to interpret what that information is saying. Some things are not obvious. A big part of monitoring is noticing trends – it’s not just for break/fix scenarios. A good DBA automates what he or she can, and deals with the rest. A good example is getting a report in the AM telling you what backups ran successfully or failed. You don’t want to connect to every instance to get that information. It’s time consuming. Spend your work hours dealing with the problems (like making sure the backups are done if failed) instead of figuring out of they ran or not.

Let’s talk about maintenance plans for a moment. I know they are popular and certainly have use. Heck, it’s easy to click some checkboxes, click Next a few times, and bam! Instant maintenance. In my estimation, 9 out of 10 times, it’s always better to code that stuff yourself and implement via SQL Server Agent jobs. That way it’s not a black box. Plus, you can easily alter things to meet your needs. For example, do you really need to rebuild ALL of your indexes at whatever interval you specify? Probably not. You most likely need to monitor what’s going on, anticipate the need based on history, and then have a job that is either manual or scheduled to fend off a problem based on that information. A maintenance plan isn’t going to work there.

Bottom line here is that over time, a DBA should be able transform a shop from being reactive to proactive. HUGE benefit and win for all. That’s why you need someone who knows what they’re doing. They make it look easy even when it’s not.

A Multi-Tasker is Better than Dedicated Staff

I think we’ve all been in roles at some point in our career where we’ve worn multiple hats. Knowing different disciplines can help any role, but you should have a primary focus. For example, a DBA should be able to understand Windows, storage, and networking, but they don’t need to be gurus. However, if you are the lone administrator responsible for everything from Active Directory and Exchange to SharePoint, SQL Server, and beyond, do you really think you’ll be efficient and a deep expert at any one of those – let alone all of them? I’m not saying it’s not possible … just improbable.

The argument, especially in very small shops, is that one person should be able to handle it all. I don’t necessarily disagree. In a small IT shop where there are not a lot of systems, the load is manageable, and nothing is very complex, a jack-of-all-trades may be perfect if they’ve got some experience under their belt. However, if you have complex deployments and numbers, that argument fades fast. For example, do you think your jack of all trades can handle multi-subnet clusters or SAN replication between sites? That’s some heady networking and storage infrastructure that needs proper planning and administration going forward. The wrong person doing the wrong thing could give you lots of downtime and problems.

Look at all of the dimensions of what a person brings to the table, not just the cost in terms of dollars.

Oh, and your developer – not such a good choice for a production SQL Server DBA for numerous reasons. I’ll leave that one alone in this blog post. Could make good fodder for another one.

Yes, Virginia – SQL Server is Mission Critical in Your Environment

That phrase is a spin on “Yes, Virginia – there is Santa Claus”. I’m never surprised anymore that some environments do not consider SQL Server essential. They’ll talk about Oracle or DB2. Yet when I go in and do some work – especially an assessment or consolidation – lo and behold we find out that there tons more SQL Server databases and instances (and the applications that use them) than they thought existed. They only knew about a subset since they were not under any kind of central administration (a different issue I’m not going to touch here). SQL Server may not be powering your Peoplesoft implementation (maybe it is; substitute your favorite application if you’d like), but it’s everywhere else under the sun and used by nearly everyone in one capacity or another. So I would say based on sheer numbers of DBs and instances alone (not even marrying that info to the apps which may be used 24×7), SQL Server is mission critical in your environment whether you like it or not. Welcome to 2011. SQL Server is ready for enterprise class stuff.

As such, it needs to be treated in the same way you do the other RDBMS choices you have. I bet you have Oracle DBAs – why not SQL Server?

The Economics

I also understand the math behind all of this. Hiring folks is not free, be it a FTE, a consultant, or an outsourced DBA service. How much is your data worth to you to ensure you have peace of mind? It’s arguably more important than your applications since your data is a big part of your IP. If you’ve got tight service level agreements (SLAs), recovery time objectives (RTOs), and recovery point objectives (RPOs), having that jack-of-all-trades-master-of-none may work in some cases, but what happens when it doesn’t? The cost to benefit ratio of having someone skilled is not a straightforward calculation that can be done by the bean counters behind a desk .

What I also see is that for whatever reason, SQL Server folks are valued lower than, say, their Oracle counterparts in enterprise IT shops. An enterprise DBA is an enterprise DBA, period. Even in terms of contract work, I sometimes chuckle at what gets sent my way. People want the sun, the moon, and the stars all in one person for very little money. I always tell whoever sends these requests that if they find that person for that rate, hang onto them.

The reality is that someone with a BI background may know very little about relational (outside of data warehouse stuff) and a relational person may know nothing about Analysis Services, Reporting Services, et al. Each one of those things is really a different role that just happens to fall under the banner of SQL Server because that is the branding. An operational DBA may do some of those other tasks, but their primary function is the care and and feeding of your SQL Server environment to ensure it remains up, healthy, and performing. Anything else in my mind is a bonus. SQL Server is a big product – understand that when you set out to find someone for your needs. You may need two or three people, not just one.

EDIT: After I posted, I saw this tweet on the #dba hashtag:

Oops!

Think this means less productivity? That’s a cost … and in this case, having the right person either may have prevented this OR would get them back up and running. Factor that in – you may cost others a lot of time and money with the wrong person, too.

Where Does This Leave Us?

I know this post is going to be a little controversial for some, and you may disagree with me (would love to see you post if so), but in the near 20 years I’ve been involved with SQL Server, I’ve never seen where having someone who really knows what they are doing is a detriment. It’s usually a benefit. 2011 is not different than 1990, 1995, 2000 or any other year – you need to know your stuff as best you can to do a good job. That person coming right out of college with no real world experience may be cheap in terms of cost, but would you trust your 24×7 system to them and no one else? Probably not. Ideally you would have a mix of senior and junior folks because both have value. The senior resources can help and mentor the junior ones.

At the end of the day, realize that if you want to be (or think you are) mission critical, you cannot ignore the simple fact that not having at least one qualified, dedicated SQL Server DBA is a surefire method that will most likely prevent you from ever achieving your goal.

1 2 345

Categories