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.

More Posts - Website

Follow Me:

You can refresh this page every couple of moments to see what’s happened.  The most recent updates are at the bottom.

You can watch the keynote live online too.

Tina Turner Onstage at #SQLPASS

8:21AM – Today opened with Tina Turner singing “Simply the Best.”  It’s either that or I drank entirely too much last night and Rushabh opened with a musical number, I’m not quite sure.

8:28 – Rushabh keeps talking about how we’ve touched a lot of people.  I think I read about this in the harassment complaint.  They’re aiming to touch more people – or maybe be touched by more people, I’m not quite sure – by webcasting the keynote live for free.

8:35 – Microsoft’s Mark Souza is playing doctor onstage to talk about the 400+ Microsoft development team members here to answer your questions.

8:39 – Talk to Microsoft folks, and you’re eligible to win all kinds of prizes including an XBox Kinect.

8:40 – “Everybody, look under your chair – if you’ve got an envelope, you just won a Dell Alienware laptop!”  Dang, all I got was chewing gum.  And of course you know I just felt under there without looking.  Ewww.  I won some used chewing gum.

SQLPASS Keynote

8:42 – Rushabh’s introducing Ted Kummert to talk about some upcoming news.  WOOHOO!

8:46 – Playing a video explaining the history of SQL Server.  Best quote so far: “I figured if I could build an even halfway decent database, Microsoft could sell the heck out of it.”  Oh, the jokes are just zipping around.  This is a great video, though – really helps build a connection with the people building this tool, makes them personal and identifiable.  This is one of the best marketing-ish videos I’ve seen.  Nice work.

8:49 – Ted Kummert took the stage, and immediately started off by saying that a lot of the SQL Server people live & work in Seattle, and having the Summit here helps them send more people.  Oooo.  Okay, got the message there.

8:55 – SQL Server 2008 R2 Parallel Data Warehouse is now released to manufacturing.  This is a little tricky, though – it’s only sold through manufacturers along with hardware.  It isn’t available for download & testing on your own machine.  It’s sold as a big honkin’ appliance that you just plug in and go like hell.

9:00 – Demoing Parallel Data Warehouse with a 100TB copy of the TPC-H benchmark.  Eagle-eyed watchers will notice that this isn’t using SQL Server Management Studio – PDW has its own admin tool still.

Ted Kummert Onstage with a Parallel Data Warehouse

9:01 – Running PowerPivot queries to suck a subset of the billions of rows – showing off the integration between PDW, Excel, PowerPivot, etc – talking about the whole-stack story.

9:04 – Here’s my thoughts on Parallel Data Warehouse from last year’s announcements and interviews.  I like the idea, but people need to remember that it’s not managed with traditional SQL Server tools or techniques or staff.  It’s a sealed box, and you can’t do anything with it – it’s manufacturer maintained.

9:05 – Yahoo’s onstage talking about their 12TB Analysis Services cube loading 60 50GB files per hour, 1.2TB per day, 3.5b events per day.  Less than 10 second average query times.

9:10 – Announcing Premier Mission Critical, Microsoft Critical Advantage Program for Parallel Data Warehouse.  No details on it though.

9:11 – Bob Ward announced & demoed Microsoft Atlanta – I’ve got a separate post with my thoughts on Microsoft Atlanta.  It’s a cloud-based SQL Server monitoring system that only works for SQL Server 2008 and R2 (and newer) that detects configuration problems.

9:18 – Cloud time!  Kummert says Azure needs to be self-managed, have elastic scale, and be agile & familiar.  It’s solving real business problems today.  Next up: we’ve got Community Technology Previews announced originally at PDC for Web Admin, Reporting, and Data Sync.

Demoing Azure DataMarket

9:24 – Available now – the Windows Azure Marketplace DataMarket, something like the iTunes Store for data sets – both commercial and freely available.  They’re demoing how to add internet-accessible datasets from the DataMarket with, say, weather data, to enrich your reporting.  On rainy days, you sell less bicycles. I’m a big fan of this.

9:32 – Ted’s encouraging the audience to go sign up and get engaged in the cloud.

9:33 – “What’s Next for SQL Server?”  The next version is called SQL Server Denali for now as a code name, and I’ve got some quick information about SQL Server Denali.  They’ll be demoing it later, plus handing it out to attendees after tomorrow’s keynote.

9:37 – The changes in SQL Server Integration Services will be huge, he says – big improvements in management and servers.  R2 was a big release for reporting & analytics, plus Office 2010 focused on managed self-service analytics.  Excel users were empowered to build BI applications, and PowerPivot was embedded into SharePoint.

9:39 – Project Crescent is a new web-based reporting system letting end users tell their own stories about the data.  Amir Netz is demoing PowerPivot, then saying that’s good, but we need something bigger for enterprise datastores.  The new BI Development Studio, running on top of Visual Studio 2010 Premium, hooks into the same column-oriented storage engine that PowerPivot used, but now you can use it on a server so you get centralized security and bigger horsepower.

Demoing Column Store Indexes

9:49 – SQL Server Denali will have columnar indexes built into the database engine.  Columnar indexes are what makes PowerPivot so insanely fast.  More on this later.

9:52 – They’re demoing Project Crescent to build reports.  Unfortunately, I gotta bail – off to set up for my session!

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.

More Posts - Website

Follow Me:

At big conferences like the Professional Association for SQL Server Summit in Seattle, attendees can pay extra to attend a pre-conference or post-conference session.  Select speakers talk all day on just one topic, so the attendees get really deep coverage on it.  Competition among speakers is tough for these slots because there’s money involved – they get paid per attendee.  A few of us co-submitted a performance tuning abstract this year, but we didn’t get accepted.  (I’m completely okay with that – the quality of the accepted sessions was outstanding.)

Free-Con 2010 at the Hugo House

I decided to offer my own free invitation-only pre-conference instead, and the Free-Con was born.  I wanted to make sure I didn’t compete with the paid pre-con sessions in any way, so I ran mine on Sunday, a day before the paid ones.  I emailed about a dozen bloggers from various walks of life and said, “Get your free-con.“  I really wish I could have invited a huge number of people, but for the first draft of this experiment, I wanted to keep it a very small group.  I really like the chemistry that a 15-person group seems to produce: just big enough to create small pockets of discussion, yet small enough that everyone can get to know everyone else’s viewpoints.

Seattle native Kendra Little (Blog – @KendraLittle) offered to help pick the location and arrange the food, and frankly, she did a better job of that than I did on the presentations!  We gathered at the Richard Hugo House, a downtown Seattle spot for writers to improve their craft, and I performed in the cabaret.  We feasted on healthy and tasty local food – well, except for the Trophy cupcakes brought by Yanni Robel (Blog – @YanniRobel), which were just tasty.

No SQL Server talk here – we’ll get enough of that during the PASS Summit this week.  Instead, we covered topics like how the employment market is changing, how to create and manage your personal brand, how to work with vendors and publishers, how consultants achieve a work/life balance, and why content creators are so valuable.

This event really reinforced to me that the most valuable things you can get from a conference are the conversations from people who share your troubles and beliefs.  I left inspired to do more work to help more people achieve their dreams, and empowered by thoughts from people like Karen Lopez, John Robel, and Steve Jones who challenge me to do a better job.

Richard Hugo House, Seattle

The Free-Con is still too fresh in my mind to know if it was a real success – the real proof will be seen in what the attendees took away from it.  I’m going to be following up with them over the coming months, challenging some of them to take their online persona to the next level, and challenging others to mentor bloggers, presenters, and podcasters.  If I think it helped (and especially if they tell me it helped) then I’ll do one at Connections next spring too.

Even if nothing comes of it, I’m proud that I tried it.  I trace the heritage of this idea back to my coworking time at the Caroline Collective and to my time working with Christian Hasker and Andy Grant in the Quest Software marketing team.  Those two times in my life taught me the importance of taking gambles and creating stuff.  In 2010, I passed the Microsoft Certified Master tests the first time I took ‘em, wrote my free Twitter e-book, launched SQLCruise with Tim Ford, gave up the stability of a paycheck to start consulting full time with SQLskills, taught my first paid pre-con at SQLbits, and tried this Free-Con.  As the year starts to come to a close, I feel incredibly grateful to have so many wonderful people in my life who support and enable my crazy ideas – or at least humor me.  (That includes you, Erika!)

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.

More Posts - Website

Follow Me:

Just a few quick notes about security before anyone shows up at the PASS Summit.

First, set up lock codes on your phones, iPads, and laptops.  Those of us who are security-conscious tend to play pranks on those of us who aren’t.  (Ask Tom LaRock about the year he found a shirtless fireman on his desktop wallpaper and couldn’t get it back off – we play for keeps.)

Second, install the Firefox plugin HTTPS Everywhere.  This forces popular web sites like FaceBook, Live, and Twitter to use HTTPS instead of HTTP, thereby encrypting your web surfing instead of SSL.

Third, when you’ve done the above two, check out Firesheep.  It’s a Firefox extension that listens to unencrypted HTTP traffic on your WiFi network and lets you instantly log in as any sucker who isn’t using HTTPS.  This works great at hotels and conferences where lots of your friends (or enemies, whatever) are surfing during keynote addresses.

Consider yourself warned.

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.

More Posts - Website

Follow Me: