June 2017 - SQLHA

June 27, 2017

Demystifying Microsoft Distributed Transaction Coordinator and SQL Server HA Configurations

One topic that seems to vex people when it comes to highly available configurations of SQL Server is the use (or lack thereof …) of (the) Microsoft Distributed Transaction Coordinator. It seems to be something I’ve addressed at least five times in the past few weeks, so I decided to do a blog post. Sometimes you will see the feature abbreviated as any one of three ways: DTC, MS DTC, and MSDTC. As an aside, I’ve written a few posts already on DTC – including two (this and this) on how to cluster DTC, so I’m no stranger to DTC land.

Back in the dinosaur era when we only had log shipping, SQL Server failover clustering (when Windows Server failover clusters [WSFCs] were called server clusters or something other than WSFC which it is today, and there was no naming conflict with the words failover and cluster together), and (heaven help us) replication, DTC was around, too. DTC is a component of the OS (i.e. Windows Server), not SQL Server.

Going back to those bygone days, the story was this: you had to cluster DTC if you were deploying a clustered SQL Server installation (this is before availability groups [AGs], kiddies). It was not optional. It only became optional for failover cluster instances (FCIs) starting with SQL Server 2008 (but you got and to this day, still get a warning on FCI installs if you do not; you can ignore that for reasons I’m not getting into deeply in this blog post). Clustered instances of SQL Server were the only way to get high availability support for distributed transactions until SQL Server 2016 (more on that in a minute). That means every feature – log shipping, database mirroring (DBM), replication, and yes, even AGs did not support distributed transactions.

What exactly is a distributed transaction? It’s one where the work needs to be completed in more than one database so data is kept in sync everywhere. For example, if you need to update data in Database A and in Database B, and they need to be kept in sync, that’s a distributed transaction. Database A and Database B can be in the same SQL Server instance, or they could be in different instances … or even in different data sources, such as Oracle or DB2. This whole shebang is often referred to as a cross-database transaction. DTC is based on the principle of a two phase commit – for the whole thing to get done, all the little bits need to be committed everywhere before claiming complete success. Otherwise stuff needs to be rolled back so that things stay in sync and all is right in the world.

Why is all of this important? With the HA features of SQL Server, it all boils down to failing over or switching to another server. It has nothing to do with when things are up and running and there are no issues. DTC plays well with everything in those cases. What happens when data commits in A but not B and you have a failover of one of those and the commit did not happen everytwhere? You are out of sync. Now you need to manually deal with the data issue – if you even know you have it. That is the problem with all of the features besides FCIs not supporting DTC, and one reason why FCIs are still popular today (among other reasons).

When I first started talking to customers about AGs even before SQL Server 2012 was released, many assumed that because an AG could be configured with more than one database that it supported distributed transactions. Bzzt! Thanks for playing, we’ll send you home with a lovely parting gift. The story remained unchanged until fairly recently.

At PASS Summit in I believe in 2015, the HA feature PM at the time announced that DTC support was coming for AGs and there was much rejoicing in the land … until it was realized that what was done only covered half the story. What was implemented in SQL Server 2016 was the case where if you had a database in Instance A and another in Instance B (or in another data source), not if you had two databases in the same instance (whether or not they were in the same AG).

Microsoft had originally intended on the full functionality being shipped in a CU or SP for SQL Server 2016, but it turned up in SQL Server 2017 as I blogged about recently. Given the short dev cycles and the amount of effort it took to do, I’m not surprised. To get DTC support for AGs was a cross effort between the Windows and SQL Server teams – and a lot of work. I’m OK with it being in 2017+.

DTC is a huge red flag/lightning rod/insert your euphemism here for some (IT DOESN’T WORK WITH AGs! MS SUCKS!). Do you actually need it? Do you even know if any of your applications use it? Most of my customers do not. I ask the question all the time when I’m helping them architect and implement HA solutions. And if you want help with your HA stuff for SQL Server, contact us. Max and I have decades of experience in this arena – literally.

Anyway, anecdotal evidence: in the near 30 years into my career with SQL Server going back to SQL Solutions in Burlington, MA pre-Sybase, the number of applications I’ve encountered that actually use DTC is probably under 25. The use and importance of DTC is vastly overrated in my opinion, but it’s nice for folks who want to bash on SQL Server and Windows dev to say it’s not supported.

Having said that, and for those of my customers over the years that rely on distributed transactions in their applications, it’s super important to them and their HA story has been not so great. I can see why it is a dealbreaker for those people, but this one is more like a 90/10 or 95/5 rule – the no DTC use case covers most, but for those who it doesn’t work for, if they hate FCIs, and, say are using VMware and really hate RDMs, well … you get the picture.

Bottom line: Do not make a mountain out of a molehill as I see many do with this topic. There’s so much FUD out there with DTC. Here’s a quick bullet list of what you really need to know:

Know if your app uses distributed transactions, and if you need anything special like XA transactions which is a more detailed configuration of DTC.
If you deploy apps using log shipping, replication, database mirroring, and AGs (pre-2016), DTC won’t kill you until you have a bad failover and your data is out of sync. So it will work … until it doesn’t. Graceful failovers won’t kill you if you coorindate things.
Always test your apps in a failover situation with DTC – supported or not. Shame on you if you don’t and assume it will all be honky dory.
If you require DTC for a highly available configuration of SQL Server, you have two options: FCIs or AGs.
For AGs, whether you go with SQL Server 2016 or 2017 depends on what flavor of DTC you require.
DTC support is not enabled by default on an AG in 2016+.
DTC support for AGs can only happen when an AG is created, and is currently T-SQL only. Want to add it later? Delete and reconfigure the AG.
DTC support on an AG is enabled by an option (DTC_SUPPORT = PER_DB), and it’s on or off. There’s nothing else you have to configure.
DTC support for AGs requires Windows Server 2012 R2 or later. Windows Server 2012 R2 requires a hotfix.
There is no support for DTC at all for SQL Server on Linux. If you need DTC, you’ll need to deploy SQL Server on Windows Server.
Clustering DTC is not required for AGs or FCIs. Period. There are advantages for doing so with some FCIs, but not getting into that here. That’s a story you’ll have to read my book to get (and it’s coming along nicely, thankyouverymuch) or attend one of my classes.

Hope that clears things up.

June 16, 2017

Styx – The Mission Album Review

I know I usually do much more serious posts, but anyone who has seen me present may have picked up on the fact that Styx is one of my favorite bands with the way I’ve named some servers, WSFCs, FCIs, and AGs in demos. I have a long history with the band … ’nuff said.

Music is a big part of what I do both personally and professionally. I figured it’d be fun to do a review of the new Styx album The Mission that was released today. I’ve had it for a few days (thank you – you know who you are), so I’ve had a chance to digest it. The Mission is the first Styx album of all original tunes since 2003’s Cyclorama, which is good but uneven effort. If you know Styx at all, you may be saying “But they’ve released …” – yes they have. I’m discounting all live albums, compilations, and even the covers album. Sure, one of those albums had a few new cuts but they were bad so let’s not speak of them *cough* “Just Be” *cough*.

The Music

Many bands have had what have been billed as “return to form” albums – Aerosmith has had a million of those since the 90s (spoiler alert: none were a return to 70s form Aerosmith). The Mission has basically been billed as the return to 70s form for Styx, and while not totally that, it is closer to the glory years than Cyclorama ever was. It’s a short album by modern standards – clocking in at just over 42 minutes.

The Mission is partially the brainchild of Tommy Shaw and producer Will Evankovich, who is the third man in Shaw Blades’ live show and has worked with Tommy on various other projects including his orchestra show last year in Cleveland (which I also attended). While it may be driven by those two, it’s clear the whole band made it a Styx album and it’s not just a Tommy Shaw solo project featuring Styx-as-backing-band album.

“Gone Gone Gone” is a throwback to the early Wooden Nickel days of Styx, and is almost a bit of a cross between “Southern Woman” (compare the riffs) and “Rock and Roll Feeling”. “Hundred Million Miles From Home” is a fun song, which evokes some Eagles-esque influences especially in the guitar playing. It also features original bass player Chuck Panozzo, one of my bass influences. It has a bounce that makes it feel more like classic Styx than anything else on the album. Speaking of bass playing, Ricky Phillips does a great job (probably my favorite on the album being “Trouble At The Big Show”). “Locomotive” sounds more like Wish You Were Here-era Floyd than Styx, but that’s not a bad thing. “Radio Silence” has echoes of the Grand Illusion classic “Man In The Wilderness”, is one of the strongest songs on The Mission, and is very clearly a Styx tune.

There’s some nice odd meter stuff on the album such as “Time May Bend” and “The Red Storm”. Todd Sucherman is a beast on drums. Lawrence Gowan’s piano playing on songs like “Ten Thousand Ways” and “Khedive”, which also features some nice nods to Queen, is good. There’s even a nod to the Who on “The Outpost” right around 2:33. I’m glad they used what appears to be a vintage Oberheim (OB-X? OB-XA? OB-8?) along with other analog synths on songs like “The Outpost” which gives many songs that classic Styx keyboard sound (from Grand Illusion on, the Oberheim is closely associated with Styx and former member Dennis DeYoung).

The Mission is not without its flaws to my ears. The effect on Tommy’s voice at the end “Overture” reminds me of the voice effect used to introduce the Main Street Electrical Parade at Disney (that’s not a good thing; it’s one of my favorite Disney things but jarring here). While “Hundred Million Miles” is currently my favorite song on the album, the weird effect used on Tommy’s vocals during the verses seems unnecessary. Since they were going the old school way, having some touches of Taurus pedals would have been nice, too, at points.

What I’m not completely buying into is the concept album aspect of The Mission. For a band that went kicking and screaming into doing a concept album like Kilroy Was Here (and had two loose concept albums prior to that – Grand Illusion and Paradise Theater), the fact they’d embrace one now is baffling. Apparently it’s just as hard to write good songs about going to space as it is about robots and censorship. Many of the songs on The Mission stand on their own (and may become concert staples, such as “Gone Gone Gone”). Others do not work out of context (“Mission to Mars”, which if I’m going to be honest has some pretty bad lyrics and is probably my least favorite song followed by “The Greater Good” which has good vocals and musicianship, but the song is just limp for me). That said, the whole is greater than the individual parts. I don’t feel myself wanting to skip and jump around to different tracks.

The Sonics

Anyone who knows me knows I care about sound quality. Much has been made of the fact that The Mission was tracked to (analog) tape. That doesn’t mean anything if it’s engineered, mixed, and mastered poorly. I’m happy to report that the album sounds very good. It is not brickwalled (but some may still say TOO LOUD), and has benefits from starting out all analog. The Mission breathes more than a lot of albums I’ve heard in the past few years. Drums and bass have a sound that you cannot totally get from an all digital recording. The stereo mix has a nice soundstage, nice and wide, with the instruments all having great placement. While it has touches and nods to the classic Styx sound (such as the way the harmonies are mixed), it’s still a modern mix to me but has a vintage vibe that I dig.

For folks like me, I’m happy to see it’s available in high resolution at 88.2/24 from places like Quobuz, ProStudioMasters, and HDTracks. Comparing the CD quality (44.1/16 non-MP3 thank you very much) to the hi-rez, the hi-rez is clearly the winner. There’s more depth and air than the CD; it sounds much more natural and pleasing. Having listened to the CD-quality tracks for a few days, I hear the difference immediately. If you’re into such things as I am, I’d recommend the hi-rez over the CD. I am curious why they chose 88.2/24 versus 96/24 or 192/24 given they had an analog starting point. No matter, it sounds good. I don’t like vinyl, so I have not heard it nor do I plan on buying it. Having been recorded to analog, it should sound good since it should be analog all the way through. If vinyl is your thing, the LP isn’t terribly expensive.

My only really major disappointment is I have not been able to hear the 5.1 surround mix that was completed. I think it is a huge missed opportunity not to release it as the same time as the regular album or as part of a deluxe package which had the CD (or LP) and that. It is supposedly being released later this year later on Blu-ray (and maybe DVD). I hope it is not shelved.

The Inevitable Comparison

Being a Styx nerd, the question will come up for some: “Is The Mission better than Dennis DeYoung’s 2007 album One Hundred Years from Now?” (and then you could ask … which version of that album – the original Canadian or the later US version which had slightly different tracks). DDY didn’t make a concept album, so doing a straight 1:1 comparison is not easy. Truth: I like both The Mission and One Hundred Years From Now. The fact we have two solid studio efforts that do not suck is a happy problem to have. Both tip their caps to the classic Styx sound, but are also modern at the same time.

However, there is no song on The Mission that is better than “Crossing the Rubicon” on One Hundred Years From Now, arguably the most “classic Styx”-like song (and probably the best track) released on any Styx-related studio album since the 80s. There’s also nothing on either album that’s going to convince you to like and/or hate Styx or DDY more or less if you’re predisposed to one side or the other. For example, if you do not like DDY’s more mid-tempo or ballad-y things, songs like “I Believe In You” or “There Was A Time” are not going to change your mind; also don’t believe the hype, Dennis can still rock with songs like “Private Jones”.

The Verdict

The bottom line: The Mission is a strong showing from Styx and a solid 7/10 for me. It’s a fun listen end-to-end, and with its short running time, goes by quickly. Placing it in the list of “where does The Mission sit in the pantheon of Styx studio albums?”, it’d be somewhere in the middle of the pack. If it is the last studio effort we see from Styx, this is a worthy final Mission from the band.

June 14, 2017

Supporting Our Troops – Win a Seat in the Upcoming Classes In Chicago

Nearly every year since we’ve been offering public training, we’ve given away one seat in a class for a good cause or to people who would not ordinarily be able to attend. As an example, last year was WIT. Max and I really believe that giving back is important. Sure, we’re taking money out of our own pockets but it’s the right thing to do. SQLHA really does put our proverbial money where our mouth is; we do not take giving back lightly.
We’re happy to announce that this year, we’ll be giving away one seat for each of the upcoming Chicago classes: SQL Server Availability Fundamentals on August 7 and the Always On Availability Groups Boot Camp from August 8 – 10. This year, we are also focusing on a specific group like last year with WIT. This time around it’s active duty or retired military members (gender does NOT matter). As long as you did or currently do serve in a branch of the military, you are eligible.

Good luck if you enter!

The Rules

You have to be able to prove that you are or were in one of the branches of the military.
Send an e-mail to sales at sqlha dot com with the subject Supporting Our Troops and tell us which class you are interested (it’s possible for one person to get both, but if you put both, specify a preference) along with why you think you deserve the seat and how it would impact you. You really have to demonstrate that you truly would benefit from attending.
You do not have to send a tome, but one or two lines won’t cut it either. The grammar police won’t hold it against you if your e-mail is not up to snuff; we prefer heartfelt over perfect. Having said that, see #10 of The Fine Print. There is one exception.
Entries must be in by Friday, June 30, at 5PM Eastern. A winner will be chosen and notified by Wednesday, July 5.
Do not make or send a video, write a Word document, etc.; that will disqualify you. This should be e-mail only.

The Fine Print

One entry per person.
Winners will not be eligible for a free seat in a future class and are ineligible for winning any other free SQLHA LLC giveaway for 12 months after winning the seat in the class (excluding any giveaways in the class). If you cannot attend the class where you are chosen as a winner, you forfeit the prize.
Do not enter if you cannot attend; it is not fair to those who can and a waste of everyone’s time.
You (or your company) are responsible for all travel and expenses including, but not limited to: airfare, taxis, food, hotel, and so on. If you cannot meet this obligation for the class you are thinking of entering, please save it for one you can. We’re just providing the class, not the whole shebang.
Entries without the proper subject will be disqualified. Sorry.
While we do not have delicate sensibilities, keep your entries clean.
You are responsible for any taxes you may need to pay as a result of winning this contest.
You must be eligible to win. For example, some who work in certain jobs or roles would be ineligible. Know if you can before you enter. I apologize in advance if what you do rules you out, but we don’t want to waste anyone’s time or cause issues for you OR us.
All entries must be in English.
While we understand that writing is not everyone’s forte, anyone who uses text speak such as ur will be disqualified as well. We have to have some standards, you know.

Not in the military?

We have a few seats left in each class, so don’t miss your opportunity. Use the discount code HOORA20 to get 20% off (which is bigger than the current built-in discount) before you miss out.

Why SQLHA for your training needs?

Show your boss this. We offer the best in person training which includes labs. Keeping our class sizes smaller, our classes have great interaction. Between the labs and being in the room with one of the world’s recognized experts for availability on SQL Server, it doesn’t get much better.

June 7, 2017

Full DTC Support for AGs Now in SQL Server 2017

SQL Server 2016 introduced support for the Microsoft Distributed Transaction Coordinator (DTC, or MS DTC) and Always On availability groups (click here for more info). However, what is in SQL Server 2016 is only half of the story. For a cross database transaction, it can happen either in the same instance of SQL Server (and therefore, as databases participating in a single AG), or to another database in another instance of SQL Server or some other data source like Oracle. The functionality in SQL Server 2016 covers the latter. That means that if you have two databases in an SQL Serer 2016 AG, you still have the old way – meaning, distributed transactions won’t work properly.

This has now been fixed as of SQL Server 2017 CTP 2.0. Somehow I missed this line in the release notes for CTP 2.0:

DTC support for Always On Availability Groups for all cross database transactions among databases that are part of the availability group, including for databases that are part of same instance.

As far as I know, this will not be backported to SQL Server 2016, but if you need full DTC support with AGs, it’s now there in SQL Server 2017 – a good reason to consider looking at it now. Want help with planning a SQL Server 2017 deployment on Windows Server or Linux? Contact us.

Popular Posts

Categories