SQL Server 2012 brings huge improvements for scaling out and high availability. To put these changes into perspective, let’s take a trip down memory road first and look at the history of database mirroring.
SQL Server 2005 first introduced mirroring, although it wasn’t fully supported until a service pack. In many ways, mirroring beat the pants off SQL Server’s traditional high availability and disaster recovery methods. Log shipping, clustering, and replication were known for their difficulties in implementation and management. With a few mouse clicks, database administrators could set up a secondary server (aka mirror) to constantly apply the same transactions that were applied to the production server. In synchronous mode, both servers had to commit every transaction in order for it to commit, giving a whole new level of confidence that no transactions would be lost if the primary server suddenly died. In asynchronous mode, servers separated by hundreds or thousands of miles could be kept in sync with the secondary server being a matter of seconds or minutes behind – better than no standby server at all.
Preparing to Compress the Stream
SQL Server 2008 improved mirroring by compressing the data stream, thereby lowering the bandwidth requirements between the mirroring partners.
In one of the most underrated features of all time, Microsoft even used mirroring to recover from storage corruption. When the primary server detected a corrupt page on disk, it asked the mirror for its copy of the page, and automatically repaired the damage without any DBA intervention whatsoever. Automatic page repair doesn’t get nearly the press it deserves, just silently working away in the background saving the DBA’s bacon.
Database Mirroring’s Drawbacks
While SQL Server was able to read the mirror’s copy of the data to accomplish page repairs, the rest of us weren’t given the ability to do anything helpful with the data. We couldn’t directly access the database. The best we could do is take a snapshot of that database and query the snapshot, but that snapshot was frozen in time – not terribly useful if we want to shed load from the production server. I wanted the ability to run read-only queries against the mirror for reporting purposes or for queries that could live with data a few minutes old. Some companies implemented a series of snapshots for end user access, but this was cumbersome to manage.
Definitely Not Using High Safety Mode
Unlike log shipping and replication, mirroring only allowed for two SQL Servers to be involved. We could either use mirroring for high availability inside the same datacenter, OR use it for disaster recovery with two servers in different datacenters, but not both. Due to this limitation, a common HA/DR scenario involved using a cluster for the production server (giving local high availability in the event of a server failure) combined with asynchronous mirroring to a remote site. This worked fairly well.
Fairly.
The next problem: database failovers are database-level events. DBAs can fail over one database from the principal to the secondary server, but can’t coordinate the failover of multiple databases simultaneously. In applications that required more than one database, this made automatic failover a non-option. We couldn’t risk letting SQL Server fail over just one database individually without failing over the rest as a group. Even if we tried to manage this manually, database mirroring sometimes still ran into problems when more than ten databases on the same server were mirrored.
Database mirroring didn’t protect objects outside of the database, such as SQL logins and agent jobs. SQL Server 2008 R2 introduced contained databases (DACs), a packaged set of objects that included everything necessary to support a given database application. I abhor DACs for a multitude of reasons, but if you were able to live with their drawbacks, you could more reliably fail over your entire application from datacenter to datacenter.
Enter AlwaysOn: New High Availability & Disaster Recovery
It’s like mirroring, but we get multiple mirrors for many more databases that we can fail over in groups, and we can shed load by querying the mirrors.
That might just be my favorite sentence that I’ve ever typed about a SQL Server feature.
I am the last guy to ever play Microsoft cheerleader – I routinely bash the bejeezus out of things like the DAC Packs, Access, and Windows Phone 7, so believe me when I say I’m genuinely excited about what’s going on here. I’m going to solve a lot of customer problems with mirroring 2.0, and it might be the one killer feature that drives Denali adoption. This is the part where I raise a big, big glass to the SQL Server product team. While I drink, check out the Denali HADR BooksOnline pages and read my thoughts about the specifics.
First off, we get up to four replicas – the artist formerly known as mirrors.
Denali also brings support for mirroring many more databases. We don’t have an exact number yet – we never really got one for 2005 either – but suffice it to say you can mirror more databases with confidence.
Preparing to Demo Availability Groups
DBAs set up availability groups, each of which can have a number of databases. At failover time, we can fail over the entire availability group, thereby ensuring that multi-database applications are failed over correctly.
Denali’s HADRON improvements change my stance on virtualization replication. For the last year, I preferred virtualization replication over database mirroring because it was easier to implement, manage, and fail over. Virtualization still wins if you want to manage all your application failovers on a single pane of glass – it’s easy to manage failovers for SQL Server, Oracle, application servers, file servers, and so on. However, the secondary servers don’t help to shed any load – they’re only activated in the event of a disaster.
AlwaysOn Isn’t Perfect
I need to be honest here and tell you that Denali threw out the baby with the bathwater. There’s going to be a lot of outcry because some of our favorite things about database mirroring, like extremely easy setup, are gone. Take a deep breath and read through this calmly, because I think if you see the big picture, you’ll think we’ve got a much smarter toddler.
AlwaysOn relies on Windows clustering. I know, I know – clustering has a bad reputation because for nearly a decade, it was a cringe-inducing installation followed by validation headaches. Some of my least favorite DBA memories involve misbehaving cluster support calls with finger-pointing between the hardware vendor, SAN vendor, OS vendor, and application vendor. This is different, though, because clusters no longer require shared storage or identical hardware; we can build a cluster with a Dell server in Miami, an HP server in Houston, and a virtual server in New York City, then mirror between them. Now is the right time for AlwaysOn to depend on clustering, because the teething problems are over and clustering is ready for its close-up. (One caveat: clustering requires Windows Server Enterprise Edition, but Microsoft hasn’t officially announced how licensing will work when Denali comes out.)
When you’ve got a clustering/mirroring combo with multiple partners involved, you want to know who’s keeping up and who’s falling behind. You’ll also want to audit the configurations. There’s an improved Availability Group dashboard in SQL Server Management Studio, but I’d argue that GUIs aren’t the answer here. For once, brace yourself – I would actually recommend PowerShell. I’ve given PowerShell the thumbs-down for years, but now I’m going to learn it. It’ll make HADRON management and auditing easier.
Do not try what you're about to see at home. We're what you call consultants.
Summing Up Denali AlwaysOn
There’s a lot of challenges here, but as a consultant, I love this feature. It’s a feature built into the product that gives me new ways to handle scalability, high availability, and disaster recovery. There’s a lot of potential in the box, but the clustering requirements are going to scare off many less-experienced users. Folks like us (and you, dear reader, are in the “us” group) are going to be able to parachute in, implement this without spending much money, and have amazing results.
Over the next few months, I’ll be taking you along with me as I dig more into this feature. I plan to implement it in labs at several of my customers right away, and I’ll keep you posted on what we find. If it’s anywhere near as good as it looks, I’m going to be raising a lot of glasses to Microsoft.
If not, I’ll be pointing Diet Coke bottles at Building 35 until they fix the bugs, because this feature could rock.
Brent Ozar
Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.
Follow Me:
Maybe it’s just me, but I live in fear of mixing up the letters when typing “Denali” and “HADRON”.
Oh and fine post by the way, I really do appreciate the reviews of the new features. You’re like the Roger Ebert of SQL Server this week.
Nope, not just you. My first thought is how long would it take for HR to be notified when I mistyped it, the second is whether they really want to evoke images of the LHC and it’s potential to create black holes and/or other space-time continuity issues…
There are a lot of new DMVs related to HADRON that will be very useful for keeping tabs on what is happening.
“SQL Server 2008 R2 introduced contained databases (DACs)”
Hi Brent,
Sorry for being picky but given that contained databases is a term for a future (currently unannounced) feature it might make sense to change that sentence. After all – DAC is (somehow) short for Data Tier Application (I think).
cheers
Jamie
Jamie – interesting, because I used the term self-contained databases back in my original posts last year to describe the DAC packs.
I am very much looking forward to finding out how the Query to the Mirror will work. I have many clients that would really benefit from this that are currently using Transactional Replication to satisfy their reporting needs.
It gets even better, by the way – you can run your backups on the replicas, too!
Brent,
A few questions:
1. Does this include transaction log backups in some sortof fashion for point in time restores?
2. Does HADRON come with limitations that are known of as to how many databases until it doesnot work very well? I know with DB mirroring, once the number exceeded 10 it was not a favorable option.
Thanks,
Dave
David – right now, I don’t have the answer to those yet. HADRON isn’t working in CTP1 yet, so it’s tough to tell, but I’m excited to find those answers out.
for me the Offload of READs is somethign that is of great intrest.. time to roll-up the sleeves and start to try out this puppy dog.. hoping its bite is as good as its bark..
Wow, wow and wow, I want to try this baby… Sounds great and I hope to be part of this new journey. Excellent Brent. Thanks to share this useful note.
[...] And of COURSE Brent Ozar has already blogged 4,000 (approximately) words on this here. [...]
Good post! Mirroring 2.0 will be a great feature, especially if we can read the replicas with substantial load (and not just put a few ad-hoc readers on there). That would almost solve the scale-out problems that large installations have with SQL Server (anyone want to retrofit a federated schema over an existing multi-TB OLTP database? Yeah, I’m trying not to also. This might keep me from ever having to do that.)
BUT BRENT!!! How can you be a PowerShell basher if you don’t know PowerShell yet? It really is great for all sorts of things if you are a scripter / automater. If HADRON gets you to learn PS, I bet you’ll wind up loving it.
Chris – your answer is in your question. I don’t aspire to be an automater – that’s not my idea of a fun job.
Not mine either. My job is not to automate stuff, but I automate everything I can so I can do more stuff. And I bet you do too, unless you like to build everything from scratch every time you use it! I’m just sayin’ … why bash it if you don’t know it?
I don’t need to know Russian in order to have an opinion that it won’t benefit my career to learn it. Cmon.
I work with multi-TB OLTP systems, and retrofitting it won’t be a problem. I replicated and log shipped a 2 TB database and a 1/2 TB database in less than half an hour. No problem.
Brent,
Great post! I think it would be nice if you mention the option of running backups on the replicas in your post. Some people may not read the comments.
[...] Brent Ozar already wrote an excellent post about it, so my suggestion to you will be to check this link. Enjoy the [...]
[...] is one feature I had not heard much about but looks soopherb: SQL Server Denali: HADRON ROCKS. | Brent Ozar – Too Much Information __________________ Testimonial: [...]
[...] SQL Server Denali: HADRON ROCKS – Talking us through the consultants perspective Brent Ozar looks at the new combination of HA, DR and Always-On technologies (HADRON) of SQL Server 2011. [...]
This sounds terrific! But, I just finally got up to speed on transactional replication.
But this definately sounds better.
It sounds safer too. Not like getting soaking wet and playing with liquids on top of something that clearly says, “Danger. High Voltage“.
[...] more info on Brent’s blog. This entry was posted in Random. Bookmark the permalink. ← MacBook Pro dual hard drives (SSD + 7200 RPM) [...]
I just want to correct 1 item int he post. Database mirroring as we know it was not thrown out. You can still do just plain old database mirroring in Denali. You don’t have to use HADRON if you don’t need it.
This also means that if you’re currently using database mirroring extensively, you can continue to use it in Denali and transition to HADRON as you deem appropriate, if at all.
Absolutely agreed – mirroring wasn’t thrown out, but when Microsoft built HADRON, they threw out a lot of the mirroring-related good stuff in that feature. Does that make sense? How should I reword that so that it makes it clear that HADRON doesn’t include the mirroring-ish goodness?
In that case, then I agree completely. When I read the post, I got the impression that you were saying that generic database mirroring didn’t exist anymore.
I think that caveat you mention is HUGE and will be a MASSIVE impediment to upgrades for the 80+% of the installations out there that are using database mirroring on a STANDARD EDITION stack. Yet another way in which Microsoft is leaving the Tier 2-n users (vast majority of licenses) in the lurch recently.
Well, Microsoft has repeatedly hinted that they want to keep the existing functionality the same – if you’re doing database mirroring on Standard Edition now, you can continue to do that. It’s not going away. But if you want the new features, that’s another story.
I hear from more and more of my customers that they’re almost-but-not-quite-tempted to switch to Enterprise Edition for the snapshots, partitioning, data compression, etc. There’s a lot of neat features, but often they don’t quite add up to the Enterprise Edition price tag. The AlwaysOn HADRON features might be the thing to do it.
I don’t begrudge Microsoft the ability to make money. They’re providing new value, and they should get paid for that value.
I am absolutely with you on paying for added functionality if you need it. But I REALLY think that Microsoft is and has been losing LOTS of upgrade revenue by a) not providing important functionality that virtually everyone can benefit from (Windowing functions come to mind here) or b) putting a few key items virtually everyone can benefit from only in Enterprise+ (data compression comes to mind here).
They did down-level backup compression due to strong feedback. Unfortunately that was a direct compete with numerous vendors and was IMHO developer hours spent doing something that was already out there instead of building/IMPROVING existing items in the product stack that only Microsoft can do.
Eventually I suppose there will be enough “EE-only” features to motivate more to pony up the 4X cost, but I don’t know when that will be nor even if they do it will be a net revenue win for them. Difficult calculus for sure.
Kevin – when you say “LOTS of upgrade revenue”, look at it mathematically. Last time I checked, Enterprise was something like 5x the cost of Standard. If just one out of five customers decides the Enterprise features are worth the cost, then the gamble paid off. 20% is a pretty low conversion rate, and I’ve seen higher conversions than that when I count per instance, not per client. (Meaning, one of my clients upgraded *all* their instances to Enterprise, and that skewed the overall numbers by a lot.)
Sorry – by “upgrade revenue” I was on the VERSION upgrade part of the post, not the EDITION upgrade part.
I have standing as-needed clients that have hundreds of servers and tens of thousands of databases. Roughly 20% of those servers are still on SQL 2000, 60% on 2005 and ONE on SQL 2008 R2 and the ONLY ‘advance’ feature that server is using is data compression. Somewhere around 30% of the servers are still 32 bit!! Like I said – I am Mr. Tier 2-n, so I think you and I run in different circles. :-O
I just wish Microsoft would do more for the low-mid range clients instead of focusing soooooOOOOO much on the top 0.X% of the clients or potential clients is all. Hmm, actually it isn’t all. I also very much hope they would stop releasing incredibly buggy/feature incomplete features. Likewise they need to FINISH/IMPROVE features that are released instead of so frequently moving onto the next new “shiny” thing. I suppose that discussion is for another blog post though . . .
Ah, if you mean versions, yeah, I’d agree totally. Most of my clients aren’t upgrading SQL Server no matter what features come bundled in or what the price is, period. As long as the database server continues to work (meaning, not fail) they’re happy. To get performance improvements, they’ve just virtualized onto newer hardware, which buys built-in HA/DR options without touching the application.
Even at my clients with Enterprise Agreements & Software Assurance, when new versions of SQL Server are effectively free, they’re still not upgrading. For them, it’s not a question of the software cost or features – they just don’t have the manpower and project downtime to do version upgrades willy-nilly. I think that’s actually evidenced by your clients sticking on 2000 and 2005, too – ask them if Denali was free tomorrow, how long would it take them to upgrade all their 2000 and 2005 instances?
My clients who do deploy new versions of SQL Server are tending towards Enterprise Edition, which means Microsoft’s higher-priced feature strategy is working.
[...] Check out the HADR books-on-line for the topic at http://msdn.microsoft.com/en-us/library/ff878484(SQL.110).aspx. BrentO also has a great write up titled “SQL Server Denali: HADRON ROCKS.“ [...]
[...] compatibility and functionality more than the Exchange team. Brent Ozar (Twitter: @BrentO) http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/ did a Blog on Hadron which you can read here. What he says about clustering is true. I’ use [...]
Hi Brent,
Do you have any information on how the auditing will work in this new version (Denali)?
Will it be possible to audit SQL Agent/Jobs?
Will it be possible to audit only a column of the table?
Will there be any improvement in this area?
Regards,
Claudio
Claudio – I don’t have any information on auditing specifically, but try checking out the Books Online to find out. I haven’t used auditing myself in my role. It looks like a really interesting feature, absolutely nothing against it, but it’s just not my area of specialty. Good luck though!
[...] Check out the HADR books-on-line for the topic at http://msdn.microsoft.com/en-us/library/ff878484(SQL.110).aspx. BrentO also has a great write up titled ?SQL Server Denali: HADRON ROCKS.? [...]
[...] Engine: HADR is new, of course. Brent Ozar is enthusiastic, Sean McCown is wary. SSMS has some enhancements to the debugger, and intellisense (including [...]
When I first read about HADRON too, I was also excited. I just completed setting up mirroring on a couple of our servers and would love to play with the new version of it. We replaced transactional replication with mirroring, so we lost the ability to query data at the subscriber / mirror. We found an alternative, but being able to query the mirrors would be a big plus.
[...] than I had previously. At least, this time I’d be the one physically implementing my own HADRON options. It would push me to put all the skills I had built over the past decade to the test. I [...]