SQL Server 2012 Archives - Page 2 of 6

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

January 24, 2013

Mission Critical SQL Server 2012: A Book Update

Can you believe it’s been almost four years since the publication of Pro SQL Server 2008 Failover Clustering (Apress; Print | Kindle | PDF) and six since Pro SQL Server 2005 High Availability (Apress; Print | Kindle | PDF)? I can’t. I want to thank everyone who has purchased it and contacted me (coming up to me at a conferecec, e-mailing, etc.) or written a positive review of them, especially the 2008 book. It’s very humbling as well as gratifying to know that all that time spent is worth it. Technical books are not vanity projects nor do you get rich – these are not New York Times bestsellers, which makes them hard propositions for publishers to begin with.

To be honest, I wasn’t sure I’d write follow up. With all of the changes in SQL Server 2012, doing just a book based on FCIs wouldn’t do, so it would need to be bigger. If it was going to be bigger, I wanted to do it more like the 2005 book and tell the whole story but that would be even bigger than just covering features. I first visited possibly writing a book in 2011 when I took a stab at an outline and submitted it to Apress. We went through 6 revisions and at the end of the day, it was just not what they wanted (which was something smaller like my 2008 book). I didn’t want to compromise the vision and integrity of the book. Don’t send them hate mail; they are in the business of making money and my books as noted are not NYT bestsellers. Over time they sell well. It was then I started looking into self publishing. I also would love to be able to be agile and correct things (like some stuff I either dislike or now is wrong – like the DTC thing which I blog about here).

I have been on the fence but did utilmately commit to doing something. My inner writer couldn’t not (I know, double negative) do a book. But it would be a lot of work, especially on my own and with the scope I was looking at, a printed book was no guarantee mainly for cost and size reasons. Timing is everything, and with a topic like mission critical, you need lessons learned so having something out by RTM of SQL Server 2012 wasn’t necessary. The perfect time is usually 12 – 18 months after RTM (which is where we are now). Fast forward to fall. SQL Server 2012 SP1 is released and Windows Server 2012 RTMs, which means that I can write one book and cover everything including all supported OSes (and their variants like Server Core) as well as the patching story. RTM-based book wouldn’t have that. Because I was busy and on the road most of last fall, I shelved things for a bit.

Over the past few weeks I have finally felt inspired again to pick this project back up. I put together a revised outline. I had some people look it over and boy was I over ambitious. Apress was right in a way; that book would not only NOT be digestible, but it’d never get done. I want to tell the whole story, but I’m also not stupid. Here’s where I am:

Based on the outline comments, I have enough content for three (or four) books. That is the approach I am going to take: split things up into chunks. Right now I’m only committing to the first book, which is the HA technologies and related topics (let’s call that Volume I). Volumes II and II may get written, but let me get through I first!
Breaking this up allows more agility to get content out quicker and be more focused. As a reader, you will arguably be less overwhelmed, too!
It will definitely be available as ePub and mobi (for Kindle).
I may be able to offer a print version (even if on demand) if the size is manageable and cost isn’t bad. Or, if any tech book publishers are interested in picking the book up, contact me.
I have no formal ETA on Volume I, II, or III. I would like to get Volume I out by TechEd in North America which is about 6 months from now, but no promises.
I will not announce a release date or give anything like pre-order information until I am at a point where I know I can give a real date.

That is the update. It’s all good news. I hope you’re as excited as I am and the final product will live up to my previous efforts which have been well received.

<PSA>

I appreciate people who have e-mailed me that my 2008 book is out there on sites which shall not be named. Sigh. As you may guess, I do not get rich off of writing. If I calculated the time spent in getting it out the door, I’d lose money. You can’t keep people from doing what they do, but all I ask is that you should try to support authors by buying books. I know times are tough out there and whether it is $29.99 or $69.99, that can be a lot of money to some. That doesn’t make downloading it for free right. It just inhibits people like me from writing future books.

</PSA>

January 14, 2013

SQL Server FCI Setup Problems 2: Windows Server 2012 and VCOs Part 2

When I last left you in Part 1, I showed you the new functionality in Windows Server 2012 where the CNO and VCO does not have to be put in the default Computers OU automatically if you do not pre-stage things. The examples I showed were all using a Windows Server 2012 DC. This time around, I’m going to try the same thing but with a Windows Server 2008 R2-based DC with a functional level of W2K8 R2.

First, what I did was force the DC to put the computer objects into the W2012 Servers OU using the command shown in Figure 1. I also gave the cluster administration account the CCO right on that OU.

Figure 1. Creating a default OU

I then added DENNIS and TOMMY to the MHZ domain, and as expected, they automatically were placed in the proper OU as shown in Figure 2.

Figure 2. DENNIS and TOMMY after being added ot MHZ

I created a WSFC with the same name, STYX. Just as with the Windows Server 2012 DC, the CNO got created in the proper OU. You can see it in Figure 3, but if you look at Figure 4 which is the output of the WSFC creation, I did not have to go to AD to look – it told me right there it happened. To this point, everything is working as expected.

Figure 3. OU after creating the WSFC named STYX

Figure 4. Cluster creation report showing the proper OU

Up next was creating the FCI. Remember that in the previous post, I showed you with a Windows Server 2012 DC at a Windows Server 2012 functional level, I needed to give the CNO computer object CCO rights on the OU. I’m happy to report that no such thing needed to be done with the W2K8 R2 DC. Just having the cluster administration account with those rights created the VCO for the SQL Server FCI just fine. See Figures 5 and 6.

Figure 5. Successfull starting of the EQUINOX FCI

Figure 6. The Equinox VCO in W2K8 R2 AD

This is all good news. While there are clearly slight differences in your OS choice for AD as to how to go about this, the bottom line is that you do not need to have a completely new infrastructure to take advantage of this feature. You should be able to leverage your existing AD infrastructure with no problem. If your AD admin has complained to you in the past about extra work he or she has had to do to create and move AD objects for clusters, this may go away if you start using Windows Server 2012 for your nodes.

I’m just getting warmed up with Setup-related weirdnesses, oddities, and problems. See you next time!

January 11, 2013

SQL Server FCI Setup Problems 1: Windows Server 2012 and VCOs Part 1

Happy New Year everyone! I hope the holiday season treated you well, but like everyone else, it’s time for me to roll up my sleeves and get back to things – including blogging.

This is the first of a few SQL Server failover clustering instance blog posts centered around Setup. I’ve been noticing a few things as of late and as I’m writing the new Mission Critical SQL Server 2012 book, I wanted to share some of what I found (and there will be much more detail in the book around some of these).

There’s a great new feature associated with deploying a Windows Server 2012 failover cluster (WSFC). When a WSFC is created, a corresponding Cluster Name Object (CNO) also gets created in Active Directory (AD). Similarly, when SQL Server (or any other clustered application requiring a name) gets created, it also gets an AD object called a Virtual Computer Object (VCO). These require certain rights to happen automatically, or these objects can be known as what is “pre-staged” – meaning they can be created ahead of time. I’m oversimplifying a bit just to present the general concepts to understand what I will be showing later.

The problem for AD admins is that many of them have a customized structure to place objects – like computers – into. For example, all of the SQL Servers may go in an organizational unit (OU) – think of a folder – called SQL Servers. By default, a Windows Server 2008 or 2008 R2 WSFC would stick the CNO and VCO in the Computers OU. The only way to have it placed elsewhere would be to pre-stage it. In Windows Server 2012, this behavior is different. There’s a bit more intelligence behind where CNOs and VCOs get created. If you have another default OU, it will try to use that. However, even if you don’t change the default OU but you move, say, the cluster nodes into another one, it will try to create the CNO where the nodes are, and the subsequent VCOs there as well because the CNO will now be there as well. It may not be optimal for everyone but it sure is light years from where we were with Windows Server 2008 and 2008 R2.

So in theory, SQL Server’s Setup – 2008 R2 or 2012 – when installing a clustered instance of SQL Server (FCI) should be able to use this properly, right? It can, but there are some slight differences to the way you did things in the past which I discovered through testing. The domain controller I used in this testing is Windows Server 2012 at a W2012 functionality level.

I have an OU named W2012 Servers (see Figure 1; for all figures in this post, click on them to make them larger). It has two computer objects in it – my nodes DENNIS and TOMMY. The WSFC administration account I will use to create the WSFC also has the requisite Create Computer Objects (CCO) right on the W2012 Servers OU – basically the same as if I would have added it to the Computers OU.

Figure 1. AD OU with the two nodes

Then I created a WSFC named STYX as shown in Figure 2.

Figure 2. Newly created WSFC

As expected, the CNO got added in that OU because it saw the two nodes in there. The OU now looks like Figure 3. So far, so good.

Figure 3. AD OU with the newly created WSFC in it

I then tried to install a SQL Server 2012 FCI named Equinox. Everything was great until Setup tried to bring the network name resource online. Cue sad trombone, as shown in Figure 4.

Figure 4. Equinox cannot come online.

So what’s wrong? Setup barfed before it could complete (no SQL Server Agent was created) because it couldn’t configure SQL Server itself due to not being able to come online. Remember that there are dependencies when it comes to clustered resources, and in the case of SQL Server, if the name isn’t up, neither is SQL. This can be seen in the SQL Server Setup log as shown in Figure 5.

Figure 5. Error in Detail.txt

Now I need to figure out why this happened. Because of what I know about WSFC, I already had a suspicion it had to do with permissions or something related to the VCO. The first place I look is at the errors in Failover Cluster Manager (FCM), which is a good place to start. It aggregates the cluster related events for you. The one in Figure 6 stuck out at me like a sore thumb as root cause or close to it.

Figure 6. The error in Failover Cluster Manager

This error confirmed my suspicions. What was notable is the wording. Now, I know I had the account used to create the WSFC set up with CCO. In the past, that’s all I’ve ever needed unless I was pre-staging. That first bullet is the telling one. Remember that the STYX object was created just fine, so I had no issues. But after I created the WSFC, I did not explicitly give the CNO any additional rights on the W2012 Servers OU. Since I didn’t need to do anything like that in the past, I decided to try an experiment. Before trying to reinstall SQL Server and waste time there, I would try to cluster something as a generic applicatioon and see if I could reproduce the error. What I was trying to do was rule out SQL Server’s Setup as having a bug. Before going any further, I ran the Remove Node functionality of Setup to clean up the failed install of Equinox.

I then tried to create Notepad (yes, that Notepad) as a generic application as shown in Figure 7. The same issue happened – the network name could not come online. The error was the same. So at this point – for now anyway – I was ruling out SQL Server’s Setup. But I had to get it working. Note that in Figure 7, the Wizard calls out the fact that the WSFC detected where te VCO should probably be placed based on the location of the CNO. I did not input that. Pretty cool.

Figure 7. Creating Notepad as a "clustered" application

Based on this, I granted the CNO the CCO right on the OU to see if that would fix my issue. I then tried to create Notepad again in the WSFC. This time, it worked as shown in Figure 8. Notepad was created properly in the cluster and its associated VCO was created in the W2012 Servers OU as shown in Figure 9.

Figure 8. Successfully clustered Notepad

Figure 9. Notepad in the OU

Now that I know Notepad worked, I was going to try SQL Server. I was fairly confident I solved my issue by granting the CNO the proper rights on the OU. If it didn’t work, there was a problem with Setup itself that was probably trying to put it in the Computers OU and bypass whatever Windows behavior is. I was hoping this was not the case. Before installing SQL Server, I deleted Notepad (not from the node, just the WSFC). Fingers crossed, I reinstalled the Equinox FCI. Figures 10 and 11 tell the story.

Figure 10. Equinox FCI

Figure 11. Equinox as a VCO in the OU

Whether you are installing SQL Server 2008 R2 or SQL Server 2012 on a Windows Server 2012 node, as long as the permissions are right, the new VCO functionality of Windows Server 2012 works like a charm. No more Computers OU by default if Windows detects things elsewhere. Yay.

So how does this new VCO behavior work with a non-Windows Server 2012 DC? Stay tuned for a future installment of this series.

Before I wrap up this post, I wanted to point out something else I observed. After creating the FCI, FCM was showing the following in Figure 12.

Figure 12. Name resolution message

Name resolution not yet available? What? This is something I had never seen before that did go away. I’m going to do some digging into this, but what I am assuming is that DNS had not been propagated so while the object was in AD, something still wasn’t resolving. This was non fatal because it went away, but I found it interesting nonetheless.

That’s it for today, kids. Class dismissed!

123 4 5 6

Popular Posts

Categories