Allan Hirt, Author at SQLHA

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.

March 25, 2012

Reality Is Always Different

There is one thing that is always true: never discuss politics or religion. It’s hard to do that with what I’m going to discuss for a bit – but I promise there’s a method to my madness and I’ll tie computers in later …

I’m over here in Europe before SQLBits X. Last year I went to Germany (Stuttgart) and Amsterdam for the first time before and after SQLBits VIII. I’m very thankful I was accepted and will be back for SQLBits X, so I started thinking of where I could go this year. Paris was a candidate, but I decided to go the road less traveled: Munich. Sure, Paris would have meant Disneyland and it would have checked that box off (and also it would mean the only Disney park I wouldn’t have visited since December would be Hong Kong Disneyland – but I have been there), but I’ll do that another time.

Anyone who knows me is aware I am not ashamed of who I am in any way – including my religion. I don’t wear it on my sleeve or tattoo it anywhere (that is a no-no anyway), but it is part of me. Ever since I was knee high to a grasshopper, I can remember studying and reading about the Holocaust (the Shoah for those who hate the former term). I’ve seen way too many documentaries, read hundreds – if not thousands – of books and articles in 30 or so years. But one thing I always wanted to do deep down was visit a concentration camp. Now, this is not like visiting Disneyland (one of my favorite places on Earth) – you can’t just say, “Let’s go!” You really need to think about it and be in the right frame of mind.

When I went to Stuttgart last year, I saw that it was too far to just do a jaunt over to Munich. This year, I decided to do what I’ve said I would do for at least 10 or 15 years. Especially after the death of my friend Mike, I am of the opinion you need to follow your heart. It was time for me to go.

By the way, my heritage is important to me, but anyone with half a brain knows that many different groups perished at the hands of the Nazis in World War II (as well as the years leading up to it). Gays, the mentally handicapped (is that even the PC word these days? sorry if I offended – I mean no disrespect), the sick, many clergy, other political parties, Gypsies … you name it, they were probably represented. It wasn’t just the Jews, but they were #1 on the list.

Dachau was the first concentration camp, and arguably the easiest to visit (at least for me). It’s located just outside of Munich. As I rode the train from Munich to Dachau (it’s an actual town, not just a concentration camp), you see what you think of Europe: beautiful countrysides. Cute little homes. The town of Dachau itself is one of those right out of a postcard when you think of Germany. It made me wonder what the people who were transported there were thinking on their ride … but they were certainly not going on a vacation. The fact of the matter is that 70 years ago, I would have been sent there for the same reason others were. That’s sobering, isn’t it?

Situated right next to Dachau are some homes. It’s bizarre. Yes, I know it’s nearly 70 years since WW II ended, but still …

Homes on the right, concentration camp on the left

As you approach Dachau, there’s an eerie stillness. That stillness stayed with me my entire vist. Sure, you hear birds chriping but it’s a feeling and sound of near silence you can’t describe. Once you enter through the now-famous gate pictured below, you’re transported into a different world and time. Someone said to me that the picture was wrong because he had always imagined a concentration camp to be a “black and white” place that was absent of color, so the picture made no sense to him. I thought about it and he’s right – we’ve all seen B&W photos and news footage from the 30s and 40s, but seeing it in color is a very jarring experience.

Entrance to Dachau

I’m not going to talk much more about the experience, because I’m sure you can guess how it went. Seeing the places where many unknown are buried (or their ashes),being able to walk into the gas chamber, and see the crematorium are experiences that will stick with me forever. The dichotomy of the beauty of the surroundings vs. what happened there is hard to understand. My only real political comment for the day: anyone in the area HAD to know what was going on when the camp was active. It would have been impossible not to.

Three other comments before I get to my point:

I don’t get some people. Before I got on the bus to go to the camp, a couple was waiting. There’s a sign that says “Concentration camp memorial site” above the bus stop. A couple (from some English speaking country – couldn’t place the accent) was there. The wife/girlfriend was getting the guy to pose under the sign and wanted him to smile. Huh? You’re not visiting a happy tourist attraction. Similarly on my way out, a group of guys was behind me. One of them got on the other side of the door and posed like he was imprisoned with a sad face while his friends took pictures. Both were totally disrespectful. You may disagree with me, but this wasn’t like posing with Mickey Mouse.
The camp/memorial does a great job of telling the story of everyone – not just Jews. Walking around and seeing things like the outlines of where the barracks were, or the partially reconstructed fences to show what they were like, as well as the memorials to those who died, everything was treated with a lot of respect. Admission is free and I’m glad they have it there as a reminder of an event that should never happen again (although despite that, we’ve had similar events all over the world since then …).
Germany today != Nazi Germany. Both my experiences so far have been great (last year and my in-progress trip). I’m not anti-Germany by any stretch of the imagination and that can get lost when you discuss one of the darkest chapters in human history.

View from the corner

Anyway, my point here is that this is a subject I know well, but the reality of it is much different when you’re there. It’s real. It’s visceral. I prepared myself as much as I could since I was a kid, but seeing things like the gas chambers up close and in person is WAY different than seeing a picture in a book, reading how the Nazis came to that method of efficiency, etc. As you’re standing in the chamber (which they put a light in, but I can imagine not much light was there when people were herded in), you can picture how it was. How people suffered. Pictures can tell a story, but there’s nothing that matches the experience.

I can say the same for my career in IT and with things like SQL Server and databases. I never assume, and that has put me in good standing, but too many people assume that reading, say, a whitepaper and actual experience are the same. They are not. There have been times in my career I’ve been pleasantly surprised by my experiences and preparation, and other times you realized that what you have done is woefully inadequate.

Reality is sometimes a swift kick in the pants. You want to be pleasantly surprised more than not both in life and in your job. Put yourself in a position where that happens.

Since we’re all computer geeks, I highly recommend Edwin Black’s book IBM and the Holocaust. It explains a lot of how the Nazis were able to do what they did, and is an interesting take on technology.

And with that, I will just say this: it’s experiences like what I did today that really puts life and work in perspective. If you ever get the chance to go to a concentration camp, don’t be scared to go. Is it heavy? Yes. Will you be sad at times? Probably. But that’s how it should be. Not everything needs to be shiny and happy. (Another lesson you can take away as well – the happy marketing message is rarely our reality in IT :)).

March 21, 2012

New Fixed Price Failover Clustering Service Offerings

SQLHA is happy to announce our first service offerings – and they are all centered around failover clustering. We’ve got five different ones:

Cluster Planning
Cluster Deployment
Cluster Verification
The Works
My First Cluster

For complete details on each offering, click here. We’re working on integrating this page with our existing menu structure, but the page will eventually be reachable under the “Our Services” menu above.

We look forward to working with you on your SQL Server cluster deployments.

March 8, 2012

Database Mirroring Deprecated as of SQL Server 2012

Until now, Microsoft has not said much about the future of database mirroring post-SQL Server 2012. With the introduction of AlwaysOn availability groups, it didn’t make sense to have two very similar features over the long term. The RTM version of SQL Server 2012’s Books Online now confirms that database mirroring will be removed in a later version of SQL Server (see the section “Features Not Supported in a Future Version of SQL Server” of the topic Deprecated Database Engine Features).

Before you push the panic button, remember that it will be at least two major versions of SQL Server before this happens, so you have time to plan your exit strategy for any database participating in a DBM pair. I’ll address this topic more as time goes on, but I felt this is an important change that should be pointed out.

« First ‹ Prev 35 36 373839 40 41 Next ›Last »

Categories