SQL Server 2008 R2 Archives

December 30, 2016

2016 in Review, A Few Updates, and Looking Forward

Hello, everyone. Can you believe 2017 will be here in just a few days? 2016 seemed to fly by. It’s been quite a year. I wanted to take the time in my final blog post of the year to recap some of what has happened, talk about some stuff that is coming, and update you on some stuff, too.

The Elephant in the Room

There is the matter of the book. As many of you are (as well as I am) painfully aware, I officially announced in 2013. A lot has happened both personally and professionally between then and now. One thing I haven’t talked about but came to a head in late 2015 was my health. Without getting into specific details, from January of 2014 until almost the end of 2015, I was not in good shape. I hurt myself and over time, it got to the point where the pain was so bad I basically couldn’t sit, stand, or lay down without pain. I am a non-“medicate yourself” person, so I didn’t take anything for it. I tried to soldier through it. That was a huge mistake.

I can tell you that the amount of time I spend on the road speaking and going to customers didn’t help, either. There was just no time to stop and get off the road due to work commitments. Schlepping luggage, airplane seats, and everything else exacerbated what was going on. I was shattered by the time I hit hotel rooms. It didn’t help that my hand was also messed up as well which not only affected my ability to type but also my ability to play bass. Since I couldn’t stop using my dominant hand, that took about seven months to heal.

I did my best to put a brave face on things publicly, but if you saw me at PASS Summit 2015, you would have seen me at my low point in terms of how I was feeling. It was hard to miss the Leaning Tower of Allan. Right after PASS Summit, I went back to the doctor and spent the rest of the year and the first few months of 2016 in physical therapy. Luckily for the first time in nearly two years I had contiguous time I could deal with what was going on with me. Knock on proverbial wood, physical therapy took care of things and to date, I feel great. I haven’t felt this healthy in years. If you saw me at PASS Summit this year, it was clearly night and day.

What does that have to do with the book? Needless to say, as time dragged on, it became harder and harder for me to get my normal work done, let alone sit for hours on top of that writing. I’m not looking for a pity party or sympathy, nor am I absolving myself of anything book related, but anyone who has experienced excruciating pain to the point of it being debilitating knows what I am talking about. I’m not active/active, you know. All kidding aside, don’t be a martyr like me if you’re feeling bad: take care of it. I let things go to the point where I had no choice and if physical therapy did not work, surgery may have been something I needed to explore. Quality of life became a very real issue. To put a capper on 2016, I’m just getting over bronchitis which has sidelined me for a good portion of December.

So where does that leave things? I’m back on track despite the bronchitis. My spare time over the past few months (which hasn’t been much – we’ve been slammed with the day job which isn’t 9 to 5 …) has been spent working on the book and should really, truly be content complete over the next little while barring any unforeseen problems.

Before anyone asks, the book will still be covering SQL Server 2008 R2 especially because Microsoft recently announced that they are (unfortunately) now giving the super duper paid extended support option (Premium Assurance – see this and this) which gives up to 16 years of paid support on that as well as Windows Server 2008 R2. It makes what I’m doing more vital than ever since I’m crossing all the major versions of SQL Server and Windows Server. I’ve also made some other hard choices as to what will/will not be in the book:

SQL Server 2016 and Windows Server 2016 are now in scope
Yes, there will be public cloud-related content – not just on premises/physical stuff
SQL Server v.Next – including SQL Server on Linux – is not in scope. This will be part of the first major update to the book, timeframe TBD since we have no release date for v.Next. If you haven’t been paying attention, the paint is barely dry on SQL Server 2016 and we already have CTPs of v.Next (as of the writing of this blog, we’re up to 1.1).

I’ll wrap up this section with this thought: I never intended for things to go this way. I not only thought I’d be done, but I’d be on the updates by now. The road of good intention wound up being full of potholes which bent my rims and threw my car out of alignment. As I have mentioned before, my 2005 book which was much smaller in scope and size, took 3 years. I’m not happy about the circumstances, but there is light at the end of the proverbial tunnel. They say what does not kill you makes you stronger, right? Again, I’ll reiterate – don’t let health issues build up. Take care of yourself.

Let’s Talk PASS Summit 2016

Once I took care of my health, 2016 really took a big upswing. Besides all of the customer work we did which hasn’t slowed down, some of the highlights of the year included teaching in Australia, precons at SQL Nexus in Copenhagen and SQLBits in the UK, speaking at VMworld in Las Vegas, and the capper of them all: PASS Summit 2016.

I’m very fortunate that I have presented at PASS Summit most years and have had a preconference session for quite a few of the ones in recent memory. I was the first to introduce live labs three years ago and try to push the envelope each time my abstracts are accepted. I am glad PASS took a chance on me then – we had no idea how it would play out – and three years later, it keeps getting bigger and better. By now, we have a lot of the logistics stuff down pat since I keep getting selected. Let me be clear – I don’t assume I’ll get a precon because there are no guarantees. I know I’m lucky.

As with the previous years, we talked about capping the number who could sign up at around 100. There are a few reasons for this, not the least of which is making sure that I have enough proctors (i.e. things are manageable) and the convention center can handle the bandwidth needed. Much to my surprise, at one point when I checked in they had sold over 100 seats – PASS forgot to cap it. We then decided to cap it around 110. People still wanted to sign up, so we upped it to 120. Finally we said the heck with it, and filled the room (capacity: 136).

An additional 36 people does not sound like a lot of people, but from a backend and management perspective, it is. I’ve never done anything that big with labs. I had to talk to the folks hosting the VMs since everyone gets their own set (i.e. hundreds of VMs – not a small backend to have to account for), we conferred with the convention center, and on it went. I had so many people come up to me before and during PASS Summit telling me they wish they could have gotten in – as far as I know, I think I was the only sold out one on Monday (can’t speak for Tuesday). No pressure, right? I remember one encounter going down the elevator at the Hyatt heading over the morning of the precon. One of the conference attendees saw my badge and made the association. He mentioned how he wanted to get in but couldn’t. I mean, what can you say? I’m flattered and humbled by that demand. I never take any of this for granted, and would give the same energy if one person showed up or that sold out room of 136.

Things went off with only minor issues (power, which we took care of in a break; same issue as last year), and it was awesome to see that many people doing labs at once. I snapped this picture during the day.

Figure 1. 136 people doing labs. Glorious!

I also had a half day session on what was new for availability in both SQL Server 2016 and Windows Server 2016. It was an expanded version of a talk I had been doing for over a year, aided by the fact that Windows Server 2016 had just been released so I could demo things I couldn’t before. I had no idea what room I was in, and thought there may be some interest, but there were many good sessions at the same time. Much to my surprise – on the last day of PASS Summit no less – I was in a huge room (400+), and nearly every seat was filled for most of it. Below is a picture of one side as the room was starting to fill. I’d have to look at years past, but it may have been one of the biggest rooms I’ve talked in, and definitely one of the most full. Again, no pressure – just hundreds of people who can skewer you if you suck. Luckily that didn’t happen. I had my best scores ever for a PASS Summit for both the half day and the precon. No complaints, and I can’t say enough how good of an event PASS Summit was – and not just for me.

Figure 2. Room filling up for my half day session

VMware Whitepapers

In addition to contributing to and reviewing this whitepaper from VMware, I wrote one entitled “Planning Highly Available, Mission Critical SQL Server Deployments with VMware vSphere” that was published in November. I didn’t blog about it, and some of you may have missed it. It was my first real return to writing released publicly in a long time. It’s not marketing fluff, and hope you find it useful.

Dual Microsoft MVP

One of the things I am very proud of is that back in July, I was not only re-awarded as a Data Center & Cloud Management (aka Windows Server nee Cluster) MVP, but I was also awarded as a Data Platform (aka SQL Server) MVP. There are other Dual Microsoft MVPs, but it’s nice to be recognized for the two things I do day in and day out.

Selected 2016 Numbers

0 – The number of laptops purchased by me in 2016. Yes, I’m still using the Vaio Z Canvas I got from Japan in June of 2015. More than 18 months is uncharted territory!

3 – The number of noise cancelling headphones employed by me this year. I got two new pair of headphones for travel at the end of the year. We’ll see which one stays. The old pair I used has already not made it back. One of these days I’ll do a blog post on what to consider for noise cancelling headphones.

4 – The number of bottles of Goober Grape I polished off this year.

5 – The number of countries I visited. Besides the USA, I was in these countries: Australia, Bulgaria, China, Denmark, and the UK.

45 – The number of flights I was on and how old I turned this fall.

100,000 – The approximate number of miles I spent in the air this year.

Too many – the number of (in)famous and influential people (not just entertainment folks) who died.

Training, Events, and Public Speaking in 2017

2017 is already shaping up to be a busy year. I’ll be back in the UK for two weeks at the end of March and early April teaching my 4-day Mission Critical SQL Server class in London via Technitrain and then it’s SQLBits 2017, where I’ll also be delivering a Training Day on April 6^th. I will also most likely be speaking at the London SQL Server User Group again the week I’m teaching my class. Register early – both the class and the Training Day are likely to be sold out!

I’ve submitted for a few SQL Saturdays (I usually do about a half dozen a year, give or take), and as those are confirmed, those will be added to the schedule. A few User Groups have approached me, so I’m trying to slot those in as well. Get your requests in early! I’m hope to speak again at VMworld (fingers crossed), and will of course, submit to PASS Summit again.

I’m still working on additional public dates (besides London) for my classes, and should have them nailed down in the next few weeks. Once that’s done, they’ll be posted and we’ll run a special. Stay tuned! In the meantime, if you need some training and would prefer us to come onsite, don’t hesitate to reach out. Get on our schedule early before it’s filled up.

A Note of Thanks

Whether you attended one of my classes or preconference sessions, saw me speak in person or online, read some of my writings, or more sometime this year – including reaching out to me asking where the book is even if you’re annoyed at me – thank you. I do not take anyone for granted and without you, none of what I do is really possible.

I would be remiss if I didn’t give a special thanks to my longtime friend and business partner, Max Myrick. 2016 was SQLHA’s best year yet.

Whew!

I only covered some of what happened. 2016 was a quite the year personally and professionally, and 2017 is looking better. I hope to see many of you next year, be it in person or online. Happy New Year!

May 21, 2015

Mission Critical SQL Server Book Update – May, 2015

UPDATE – December, 2016 – see this blog post

These days, one of the things everyone asks me is related to the book – mainly “How is it coming?” – a nice way of asking “Is it done yet?” It’s been awhile since the last update, and I figured it is time for a new one. If you are subscribed to the pre-release content, look for new chapters starting the week of May 25 (next week). Everyone who is not subscribed will need to wait for the final, completed book. There is a chance I may decide to put a few preview chapters up, but only when some more hit the pre-release folks; it is only fair.

So why the delay? Writing a book and publishing it on your own is much harder. My previous books were all done through a publisher who had editors and other bits of infrastructure I do not have at my fingertips. For the most part, I am a single threaded, one man show on the production of this book.

When I put the book up for preorder in July of 2013, I had an idea what I was signing myself for because I had written books before. Writing and publishing – especially self-publishing – is a whole different ball game if you want to do it right and not just dump raw Word files into the world and call it a book.

The good news is that I recently found an editor so the book will have a nice level of spit and polish. I’ve worked with her in the past, and am looking forward to seeing what she delivers. Having said that, primary writing and reviews must be completed before I can turn it over to her.

The only thing I can equate this to is when I scored a friend’s musical in the mid-1990s. I had played in tons of pit bands going back to when I started playing bass in 1980, so I figured, “How hard could this be?” Oh, how naïve I was. Between learning the scoring program, writing and arranging for multiple instruments, rehearsals, corrections – you name it. This was not a one day or one week process. I learned a lot, but it was very painful. Arranging got a lot easier after that, and so, too, will any future projects I do in the self-publishing arena.

Add to that my normal day job (consulting, training, as well as running SQLHA with Max), presenting, eating, sleeping, and trying to live my life somewhere in there along with the writing – oy! There just is only so much time and energy I have. However, I do believe all of these things play into one reason I believe that my books have been well received in the past. I bring real world perspective and experience to the table. If I am not doing this stuff for real, it’s just lab work.

For example, Max and I spent the better part of last year working with a large customer on a virtualized SQL Server project which also leveraged quite a few new storage and networking technologies not usually used with SQL Server. What was gained there will be filtered into the book, so you will be getting a lot better content than if I was not also actively doing consulting projects.

In more good news, I have pretty much cleared the decks for the next month or two to get all of the primary writing completed. Chapters need to be reviewed, and then reviewer feedback and corrections incorporated. Once all of the chapters are done, they will be handed off to the editor. Last but not least, all of the final formatting needs to be finished on the edited chapters. This is the entire book process in a nutshell.

Below are some specific questions that I want to address now because I have been asked from time to time. I have also updated the FAQ accordingly.

What content/chapters will the book contain?

While it may change a little, this is the near-final chapter list with my working (read: not final) titles:

Basics of Availability
Data Center Principles
Brief Intro to the SQL Server HA Features
Windows
Networking Part 1
Networking Part 2
Storage Part 1
Storage Part 2
Security
WSFC
Quorum Part 1
Quorum Part 2
Planning FCIs
FCI 2008 R2
FCI 2012
FCI 2014
Scripting Installations
Planning AGs
Implementing AGs
Cluster Admin/Troubleshooting
Backups Part 1
Backups Part 2
Admin/Monitoring
Planning Highly Available Upgrades
Solutions
Building Your Own Virtualized Test Environment

As you can tell, this is quite a bit of content. Right now the book will weigh in somewhere between 1000 and 1500 pages in Word prior to any formatting. Many of these chapters are in process, so those of you that have seen the updates may have only seen a few chapters but a lot of these are in various states of completion. If you are subscribed to the pre-release content, you’ll see all of this content soon.

When will the book truly be done?

From a timing perspective, assuming no issues or problems, primary writing should be done by July, edits by late August/early September, and then onto formatting. It really should be released on or before PASS Summit 2015. That is what I am working towards, and I see no reason that cannot be achieved.

I know people have been waiting patiently for Mission Critical SQL Server. My mistake in hindsight was putting it up so soon for preorder and not anticipating some of the roadblocks that popped up since putting it up for sale. I know that now, and will never repeat that again. For that, I apologize and I do believe the wait will be worth it.

I paid forever and a day ago – where is my money?

No one has asked, but I’m being forthcoming here – it’s sitting in a bank account earmarked for paying book costs. It’s not long gone. I made sure this happened for a very good reason. I need to pay people like editors 🙂

I subscribed to get updates to the book once it is released – will I still get them?

Yes! Needless to say, I’ll make sure you are taken care of. See the next question for a touch more detail, too.

If you preorder with updates on or before May 31, 2015, look for an e-mail from me soon. Anyone preordering with the updates option post-June 1, 2015, will get updates for the specified term – one year post-book release.

Will you do anything for people who purchased Mission Critical SQL Server a long time ago and have been waiting patiently?

Yes! I’m figuring out what that will be. This is different than the post-release updates. But the same rules will generally apply: pre-May 31, 2015 and post-May 31, 2015. I do appreciate your patience, and trust me, no one wants this done more than me because it is a big thing on my plate. There are other things I’d like to do which cannot get done until the book is complete.

When you first announced the book, you said it would cover through SQL Server 2012. What about 2014 and 2016 now that they are out or announced? How about Windows Server 2016?

Here is even better news. With the book being delayed, SQL Server 2014 is now included in this version. I will touch on a few 2016 (SQL Server and Windows things) but the first updates to the book will arguably be SQL Server 2016 and Windows Server 2016. Once those are released, I’ll examine what needs to be updated or added, and go from there. It is too soon to tell.

Any current discounts?

Yes. 20% until May 31, 2015 off of anything in the store. Code to be entered at checkout: MAY31

July 11, 2013

Allan’s Mission Critical SQL Server Book Ordering Information and FAQ

(Updated 12/31/2016 – see this blog post)

Mission Critical SQL Server is available for preorder here. Below you’ll find all of the information you need about it. And if this doesn’t answer your questions, post a comment or contact us.

1. Why am I publishing the book on my own this time?

This is not because I had a falling out with Apress; we’re on good terms. We just didn’t agree on the vision for the content. I wanted a larger no-compromises book more like my Pro SQL Server 2005 High Availability one; they did not. It’s really that simple. With all of the changes in Windows and SQL Server just through their 2012 versions (as well as SQL Server 2014), this is not going to be anything approximating a 300 page book; that’s too tiny and would be too incomplete. You guys benefit from my being unwilling to compromise here, but I get to drive myself nuts in the process being the perfectionist I am. After this book I may do another for Apress again if this book doesn’t beat me!

2. What versions of Windows and SQL Server are covered in the First Edition?

See the product description on the store for details.

3. What electronic format(s) will Mission Critical SQL Server be available in?

Initially, the eBook and all associated content will be in the ubiquitous Adobe Portable Document Format (PDF) format. Most platforms have programs that can read this for free even if your preferred platform isn’t Windows. I hope to offer it in other formats like Kindle/Mobi, ePub, and maybe iBook in the future.

4. Will there be digital rights management (DRM) on the PDF file(s)?

This is a sticky wicket. DRM makes life hard and I respect people do not want it. Passwords make it difficult to put on reading devices. Non-DRMed files make it easier to consume. At the same time, I also don’t want to see my content thrown up for free on some torrent site, nor do I want people to be able to easily republish it. Each copy will essentially be watermarked for who buys it and restrictions as laid out in Mission Critical SQL Server Store and Content Terms apply.

5. Will I only be able to buy Mission Critical SQL Server at http://sqlha.contentshelf.com/shop?

For now, yes. I hope to offer the book elsewhere over time. As noted in #2, the goal is to convert the content to other popular formats such as ePub and Kindle/Mobi – maybe even iBooks. That would need to happen before I can offer the content elsewhere. With the number of graphic elements in the book, that may prove to be both challenging and expensive. If you are wanting another format, contact us. The more evidence I have, the more likely it will be to happen. Converting it to other formats will be costly, so if there is not enough interest, I may not be able to do it. I am being realistic here.

6. I live in <insert country here>. Can I buy the book?

Yes. As long as you can check out, it’s a digital download making it easy to get to you. All prices are in US Dollars.

7. I live in <insert country here> and do not read or speak English well. Will you be offering non-English versions of the book?

There are no plans for the book to be translated to other languages.

8. Will there be a physical/print version of the book?

I would love to be able to offer one. Once the book is complete and I know a page count, I may reconsider a print version if it is possible and the cost will not be astronomical. Stay tuned.

9. What is an updated content subscription and why are you offering one?

Books are like snapshots: they are a moment frozen in time. Updating them is difficult and usually there is a long time between releases. A lot can change in a few months, a year, two years.What I’ve decided to do to combat that is to offer a subscription where you will get all the updated content as it is finished. For example, once SQL Sever 2016 and Windows Server 2016 are released, those updates could be incorporated before doing a major edition of the book which would be a year or two out. This gets you newer content quicker – if you want it.

Also, since the release cadence is changing for the Microsoft server products, traditional publishing just may not be possible for specific versions. By the time you’re done writing it, it’s obsolete. I want to have a good foundation to build upon and just extend it. Hope that makes sense.

10. How long does a subscription last?

It’s a yearly subscription which you will manually have to resubscribe to.

UPDATE 5/21/2015: If you preordered the book with an update subscription prior to May 31, 2015, look for an e-mail from me soon. Post-May 31, it will be only one year from the date that this edition of the book is published. So if it is officially released, say, on 10/1/15, your subscription would end on 9/30/16 if not renewed.

11. How much is a subscription renewal?

It’s $25/year. And as pointed out in the previous question’s answer, you elect to renew or not. My goal is to provide high value content so that you want to.

12. Do I need to buy the book again when you publish a new edition and I already have an updated content subscription?

If you have an active subscription, no. The goal is to only have you buy one major edition of the book and then you get updates as they happen. Information changes over time, and I’m trying to be agile and deliver you content ASAP and not have to wait years between releases. If you only want to consume major versions, just buy the new edition of the eBook when it is published. Simple.

13. If I buy the book from anywhere else but your store, can I get a updated content subscription?

No. If I do convert the book to other formats and offer it for sale elsewhere such as Amazon, Barnes & Noble, etc., those stores will only get the major edition (i.e. First Edition, Second Edition, etc.). I have to give myself some sort of exclusive, right? I may change my mind on this once the book is available elsewhere and offer something. Time will tell, but do not hold your breath.

14. Can I get access to pre-release content like other publishers offer?

Yes. It’s not free, but it is pretty cheap considering you’ll get to see things months in advance. Two of the bundles offer this option. There is currently pre-release content with more coming. Remember that the pre-release content has not been reviewed or edited, so it’s going to be raw and could contain errors.

15. Why are things priced the way they are?

I can tell you from authoring books in the past I make pennies on the proverbial dollar taking into account all the work and hours I put into the final product. This time, since I’m self-publishing, I make even less PLUS I’ve got very real, very tangible costs I need to deal with such as editing as well as costs for hosting and selling the book (including fees associated with subscriptions). I am not rich. I can’t operate at a substantial loss. I also know that I can’t charge $100+ for an eBook. I’ve priced the main eBook competitively with other similarly priced books even though it will be larger than most of them. I think it represents good value for the price you’ll pay.

16. Do you offer discounts or coupons?

I run specials, so check the store.

17. Can I use the PDF(s) I get from you directly on any device I own?

Absolutely! See our Mission Critical SQL Server Store and Content Terms for more detail. There’s a reason I chose this format.

18. Can I buy multiple copies of the for multiple people such as a class, seminar, DBA department, etc.?

19. Can I share the PDF(s) of the content with people I know? They’re really nice people and need some information on these subjects. I vouch for them.

NO! The copy you purchase is only licensed for the person buying the book (hence the watermark). I didn’t put DRM or passwords on it. Respect me and ensure that everyone buys their own copy. And if you upload the content to some nefarious site, I’ll see the watermark. It’s things like this that would make me never do this again. I know I can’t stop it 100%, but you hurt me more than you help me.

20. What if I am unsure if I want to buy it? Will you have some sort of a sample for me to look at?

I am planning on providing this as things get finished and posted; nothing is ready yet. If you have read any of my other books or writings, though, you have an idea of what you’re in for. 🙂

[Updated 5/21/15] 21. It’s May 2015. Is the book done?
See this blog post for more details.

March 12, 2013

Creating a Clustered DTC for SQL Server Redux

Last year, I wrote a blog post entitled “How to Properly Configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. Today, I was helping a customer and found a few things. I’ve since updated that original post to reflect these findings and to fix a few things. I also took Windows Server 2008 R2 out of the title and the post’s name is now “How to Properly Configure DTC for Clustered Instances of SQL Server (Revised)” since it applies to Windows Server 2012 as well. The URL is the same as the old one, and that does reference W2K8 R2.

As was originally written, that article was assuming a new, greenfield installation. Not everyone is looking to create DTC with a brand new installation, and I realized that there were some gotchas I needed to outline.when you are already up in production. I also fixed a few things in the script.

1. In the script as it was written, one step added the resource as a dependency to SQL Server. That’s all well and good (more on that in a bit), but this had two issues with a non-greenfield implementation:

When you make sure that DTC has the right permissions (“Step Two – Enable Network Access for the Newly Created DTC”), it needs to take it offline. When you add DTC as a dependency to SQL Server, it will take it offline as well. This is fine for a new installation, but not for one that is already up and running. I revised the script to NOT do this and make it a separate thing later.
It doesn’t happen all the time, but we saw it on their installation and I also saw it once on my test WSFC (but not again – so it seems to be phantom). SQL Server Agent went offline. See Figure 1. That’s definitely not good.This is apparently a timing issue – DTC needs to be up before SQL Server. I explain all of this in a bit more detail in the revised post linked above.

Figure 1. SQL Server Agent down

If DTC goes down, it will cause a SQL Server failover by default. You may or may not want this behavior. I’ve revised the original blog post with more information on this.

2. I forgot to put one important line in the script – namely moving the disk resource into the group with SQL Server! The original script did have a gotcha line in there, but I made sure it was done in this version.

My customer was trying to fix a critical path issue that needed DTC up and running for their application, so they wound up putting DTC in its own resource group which is a valid option still in Windows Server 2008, 2008 R2, and 2012. They only have one instance installed in the WIndows Server failover cluster, so they can ensure DTC lives where the SQL Server instance is all the time to reduce network traffic. This saved them from another possible outage to ensure DTC was up before SQL.

Hope this helps some of you out there …

March 12, 2013

How to Properly Configure DTC for Clustered Instances of SQL Server (Revised)

Configuring the Microsoft Distributed Transaction Coordinator (DTC) for clustered SQL Server instances (FCIs) with Windows Server 2008 and later has been a confusing topic for many. The “definitive” word on the subject for awhile has been both of Cindy Gross’ blog posts ( “Do I need DTC for my SQL Server?” and “How to configure DTC for SQL Server in a Windows 2008 cluster”). In my opinion, with W2K8+, you really should create one DTC for each FCI and put it in with the SQL Server instance. This way it always lives on the node where the instance is currently running.

Having said that, what Cindy wrote doesn’t always work. In fact, the only truly reliable way some of us have found was talked about by my friend and fellow Cluster MVP (also a big SQL guy), Mike Steineke (blog | Twitter) in a post last week entitled “Clustered DTC and Multiple SQL Instances” where he shows how he had to configure DTC. Part of this is ensuring that even though DTC is in the group with SQL, it still gets its own name and IP address; using the SQL Server name can be problematic. To get this done, you really need to script it. I’ve done the work for you. Easy!

This updatated post stems from the blog post “Creating a Clustered DTC for SQL Server Redux“.

A sign that you need to use DTC for a given FCI can be found right in the SQL Server error log. If SQL Server cannot connect to a clustered DTC, you will see an error message like the following:

2013-03-12 10:23:51.670 spid1001 QueryInterface failed for “DTC_GET_TRANSACTION_MANAGER_EX::ITransactionDispenser”:0x8007138f(The cluster resource could not be found.).
2013-03-12 10:23:51.680 spid1001 QueryInterface failed for “ITransactionDispenser”: 0x8007138f(The cluster resource could not be found.).

This blog post now applies to Windows Server 2008, 2008 R2, and 2012 as well as SQL Server 2005, 2008, 2008 R2, and 2012 since depending on the OS you’re running, you may have a little of each …

Step One – Create the Clustered DTC in the Resource Group with the FCI (revised 3/12/2013)

The script below I actually created today and is based on my original blog post I wrote back in 2009 for Windows Server 2008 and takes into account what’s in Mike’s post. I actually used this script on a customer installation, so this isn’t something I wrote just for the hell of it. All you have to do is modify the variables up front and run the script. If you don’t know the name of some of the resources, you can use the PowerShell cmdlet Get-ClusterResource to show a list of everything.

For this update, instead of embedding the code, you can download the script from here. Much easier!

A sample execution is shown in Figure 1.

Figure 1. Running the DTC creation script

I also tested this script in Windows Server 2012 and it works just fine.

Step Two – Enable Network Access for the Newly Created DTC (revised 3/12/2013)

Once DTC is created, you must enable network access. This is where if you did things another way (i.e. not the way shown in this post), there’s a very high probability that when you went to go restart DTC (Step 11 below),the process may decide to step out for lunch and then stay away for a permanent vacation.

The steps below are all GUI. Nic Cain (Twitter | Blog) wrote up this blog post on how to configure this using PowerShell if you would prefer to script things.

From the Start Menu, select Administrative Tools, and then Component Services (or just run DcomCnfg).
Under the Console Root folder, expand Component Services.
Expand Computers.
Expand Distributed Transaction Coordinator.
Expand Clustered DTCs. You should now see something that looks like Figure 2.
Figure 2. Newly created DTC in Component Services
Right click the clustered DTC you just created and select Properties as shown in Figure 3
Figure 3. Opening the Properties of the DTC
Select the Security Tab
Under Security Settings, check the “Network DTC Access” box. Under Transaction Manager Communication, check both “Allow Inbound” and “Allow Outbound”. You should now see what is shown in Figure 4. If you need any other options (you know your app better than me), add them. Click OK when done.
Figure 4. DTC enabled for network access
You will now be prompted to restart DTC as shown in Figure 5. If you are doing this against a production instance of SQL Server and you have added the DTC resource as a dependency of SQL Server, it will take SQL Server offline as well. If you cannot do this, select No, but you cannot really use DTC properly until you restart it. To get around this, remove DTC as a dependency of SQL Sevrer, do the restart, and then add it back. Click Yes to restart.
Figure 5. Prompting for the restart of DTC
Click OK at the dialog shown in Figure 6 which denotes a successful restart.

Figure 6. Confirmation of DTC’s restart

Step Three – Testing DTC (added 3/12/2013)

Microsoft has a tool that will create a dummy transaction and can be found in KB 293799. While the best test is always your application, this will at least see if DTC is working. It requires an ODBC DSN to work.

Start Data Sources (ODBC) from Administrative Tools. Note that under Windows Server 2012, you will specifically have a 32-bit and 64-bit version. Run the 32-bit.
Select the User DSN tab.
Click Add.
On the Create New Data Source dialog, select SQL Server Native Client X, where X is the version of SQL Server. In Figure 7, it is SQL Server 2012, so it has a version of 11.0. Click Finish.
Figure 7. Selecting the version of SQL Server
On the Create a New Data Source to SQL Server dialog which will look similar to the one in Figure 8, enter a name for your DSN and for Server, enter the name of the clustered instance of SQL Server. Click Finish.
Figure 8. DSN creation
On the ODBC Microsoft SQL Server Setup dialog, click Test Data Source. An example is shown in Figure 9.
Figure 9. DSN created
On a dialog similar to the one shown in Figure 10, if the test was successful, you should see the right output. If you don’t, you will see an error. Click OK.
Figure 10. DSN works properly
Run dtctester using the syntax dtctester DSN username password, where DSN is the name of the DSN you just created, username is a valid user in SQL Server, and password is the password for that user. A sample execution is shown in Figure 11.
Figure 11. Runing dtctester

WARNING Do not create a System DSN. If you do, you may see an error similar to the one shown in Figure 12. This error stems from the fact that dtctester is a 32-bit program and the System DSN created is 64-bit.

Figure 12. dtctester error

Step Four – Optional Configuration Steps (added 3/12/2013)

The steps in this section are optional. Read to see if they apply to you.

xSetting the DTC Resources to Not Cause a SQL Server Failover

There are four resources associated with DTC: name, IP, disk, and of course, DTC. If you right click on any one of those and select the Policies tab, you will see what is in Figure 13.

Figure 13. Policies for the DTC Network Name

Notice the option “If restart is unsuccessful, fail over all resources in this service or application.” What this means is that if any one of those resource fails and cannot be restarted, it will cause SQL Server to fail and move to another node. If you are not sure if DTC is necessary but are creating it anyway OR you are OK with DTC failing but SQL still being up and living with whatever not working that DTC affects, deselect this option.

Adding DTC as a Dependency of SQL Server

In the testing my friend Mike did, the only way he could get things to work was to ensure that DTC was up before SQL Server. The only surefire way to do this is to add DTC as a dependency to SQL Server (see the previous section for possible implications of doing this). One of the problems that may occur and was noted in the blog post “” is that there is a chance that SQL Server Agent may go offline. We think this is possibly related to another bug that was fixed, but what is happening is that SQL Server Agent is trying to figure out the name of the instance it is using. If it gets confused, it will go offline. It may start, but it also may not work correctly. The only way it seems to work around this is to make sure that DTC is up before SQL Server starts, hence adding it as a dependency.

Once you get DTC up and running in the group with SQL Server, test your application against it and check in the SQL Server error log as noted above. If you see no DTC errors and everything looks fine in the app, you’re golden. You shouldn’t have to restart SQL Server to take advantage of the newly mapped DTC, but without an application right now, I can’t say this to be 100% true.

Adding a dependency to a resource in Windows Server 2008 and later does not require any downtime.

To add DTC as a dependency of the SQL Server resource in Failover Cluster Manager:

Right click on the SQL Server resource and select Properties.
Select the Dependencies tab.
Click Insert.
On the new line, from the dropdown select the DTC resource you created. An example is shown in Figure 14. If the line is at the bottom, make sure that the AND dependency is selected.
Figure 14. Adding DTC as a dependency
Click OK when done.

To do this in PowerShell, execute the following:

Add-ClusterResourceDependency SQLResourceName DTCResourceName -Cluster WSFCName

A sample execution is shown in Figure 15 along with verifying that it was done correctly.

Figure 15. Creating and verifying the dependency in PowerShell

Mapping DTC to a Specific FCI

Right now I do not have an application to test specifically the combo of DTC and the FCI in the same group that all is well without adding the DTC resource as a dependency. Based on what we know, it may not work. One thing which may solve that issue and/or if you want to truly be anal about things, DTC provides you with a way to specifically map an instance of DTC to only be used with a specific application or service such as a given SQL Server instance. This is done via command line. If you really want to ensure that DTC is mapped to the FCI, you use the tmMappingSet option of the command line for DTC.

NOTE This particular aspect/option most likely will NOT be necessary but I’m putting it in here for the sake of being complete.

Enter the command msdtc -tmMappingView * as shown in Figure X. If nothing is mapped, you will see the output in Figure 16.
Figure 16. No DTC mappings exist
To map the newly created DTC to a specific FCI, use the command: msdtc -tmMappingView -name DTCname -service SQLServerService -clusterResourceName SQLResourceInWSFC, where -service would be MSSQLSERVER for a default instance or MSSQL$NMDINS where NMDINS is the part after the slash and -ClusterResourceName is the name of the SQL Sevrer resource in the Windows Server failover cluster (WSFC). For a default instance it would just be SQL Server, and for a named instance SQL Sevrer (NMDINS). A sample execution for a named instance is shown in Figure 17 and you should see Figure 18 if it is done correctly.
Figure 17. Mapping DTC to an FCI

Figure 18. DTC mapped successfully
To verify things are correct, again enter msdtc -tmMappingView *. You shold now see something similar to Figure 19.
Figure 19. Successful mapping output

12 3 4 5 6 7

Popular Posts

Categories