PowerShell Archives

October 16, 2015

Failover Cluster Manager Can Be Deceiving

GUIs are good for some things, but at other times, they are not always what we need or should be using. Case in point: Failover Cluster Manager in Windows. I use it fairly extensively, but there are times I want to strangle it because what you see in the UI doesn’t match what you may see in PowerShell. One or the other is accurate – but which is it?

A great example of this is when you want to enable the Availability Groups feature with the PowerShell cmdlet Enable-SqlAlwaysOn (see my rant about AlwaysOn). If you use the -Force option, it will stop and start the SQL Server service, but not SQL Server Agent (another annoying issue that is a “feature”). Executing this, you will see what is in Figure 1 in Failover Cluster Manager.

Figure 1. Stopped status for the FCI Role

You can clearly see that SQL Server Agent is offline and the LabFCI role has a status of Stopped. Now, anyone looking at that who was not looking at the resources tab may have a heart attack thinking SQL Server is down. It clearly isn’t.

In PowerShell, if you see what’s going on with the role, you can see that it has a status of PartialOnline. Diving further, if you look at the resources, you’ll see in Figure 2 that SQL Server Agent is offline (just as it shows in FCM). PartialOnline is much more friendly and less threatening than Stopped.

Figure 2. PartialOnline role status and resources

The point here is that sometimes what you see is not really 100% accurate to what is going on. Yes, one resource in the role is offline, but is the whole role stopped? No!

So as you are putting together your plans to go live in production and monitoring your WSFC and the roles running in it, keep these types of things in mind. You may need to scratch below the surface to find out what’s really going on as to not cause yourself some undue stress.

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.

1. From the Start Menu, select Administrative Tools, and then Component Services (or just run DcomCnfg).
2. Under the Console Root folder, expand Component Services.
3. Expand Computers.
4. Expand Distributed Transaction Coordinator.
5. Expand Clustered DTCs. You should now see something that looks like Figure 2.
  Figure 2. Newly created DTC in Component Services
6. Right click the clustered DTC you just created and select Properties as shown in Figure 3
  Figure 3. Opening the Properties of the DTC
7. Select the Security Tab
8. 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
9. 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
10. 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.

Setting 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

August 23, 2012

When Is It Time To Stop Using a Deprecated Feature?

Twitter conversations can sometimes lead to interesting things. Jonathan Kehayias (Blog | Twitter) and I were going back and forth about something, and in some cases, how we differ on how we teach (some of that was based on teaching or not teaching a deprecated feature). It was a very healthy discussion if you ask me. I really respect Jon and he’s one of the smartest folks on the planet when it comes to SQL Server in nearly every respect. I don’t say that lightly. Paul, Kim, and the rest of the crew over at SQLskills are very lucky to have him on their team.

Be it Windows, SQL, SharePoint, whatever – all of these products at some point release a new version and as part of the lifecycle of that product, some features are deprecated. A good example is that database mirroring has been deprecated as of SQL Server 2012. It’s still in the product, but its development is over and it will disappear from the product in a few versions. Another easy example for me to mention (and what sparked the debate between Jon and I) was the use of the aforementioned cluster.exe in Windows, which was deprecated in Windows Server 2008 R2 and is technically gone in Windows Server 2012 (disclaimer: you can add it back by enabling a certain feature, but it hasn’t seen any improvement or development since Windows Server 2008; more on cluster.exe in a bit). In the immortal words of Hall and Oates, “She’s Gone” (following the Daryl Hall theme from my last post; I was challenged to use “Rich Girl”, but this was more of a natural fit – sorry Rick!).

http://www.youtube.com/watch?v=bnVXIUyshng

That begs the question: when do you stop actively using/teaching/recommending/etc. a certain feature you either know has been deprecated or will be going away (but may not be public knowledge although you have good NDA information which you really can’t share)? The answer, not surprisingly, is “it depends”.

Let’s take the case of my good friend cluster.exe who I’ve lived with for the better part of 10+ years. At this point, I’m not seeing any new deployments of SQL Server 2008 (RTM branch or R2) on the original Windows Server 2008 (no R2); it’s all basically Windows Server 2008 R2 with SP1. W2K8 R2 introduced the first round of PowerShell cmdlets for Windows Server failover clustering (WSFC). When I wrote my SQL Server 2008 book, W2K8 R2 was just being wrapped up so I documented both. As time has marched on since 2008/9, not only have I personally used cluster.exe less, when I teach my classes or do presentations, I don’t even bother showing it anymore. Why? In 2012, to me it makes no sense to show something that is deprecated and outdated, and is gone in Windows Server 2012. If you’re using Windows Server 2008 R2, you really should be using PowerShell to administer your clusters (one man’s opinion, of course; feel free to disagree). However, there are still exceptions to that rule. For one specific thing in Windows Server 2008 R2, there is no PowerShell equivalent (it’s basically related to availability groups in SQL Server 2012). This is fixed in Windows Server 2012. Similarly, some things that have a PowerShell cmdlet (Test-Cluster), have no cluster.exe equivalent. The pros outweigh the cons for me of not showing or teaching cluster.exe these days. Would I if someone asked? Sure. I’m not so stuck on not showing it; I just think it’s better to learn the stuff you can use now AND in the future when you want to go to Windows Server 2012.

The story is not so cut and dry with a feature like database mirroring. Unlike Windows which really makes it clear cut you need to move on – and quickly, SQL Server keeps a feature around and intact for at least two versions before it goes away (another really good example was DB-Lib). That could possibly translate into almost 6 – 10 years of shelf life, giving you a false sense of security. Sometimes there’s a clear cut feature that replaces it. In the case of database mirroring, a lot of people use it now. That’s one factor, so it makes the announcement that much harder. But the real crux of the problem as I see it is a case of the haves and have-nots. If you are using Standard Edition of SQL Server, Microsoft is recommending log shipping as your replacement. For Enterprise (now Datacenter) Edition, it’s availability groups. AGs are a great evolution of what was started with DBM, but with the new dependence on WSFC for deploying AGs, it may be a non-starter for some. But people who want to upgrade to SQL Server 2012 or whatever vNext (or vNextNext) may be, they need to start planning a roadmap otherwise it makes it that eventual upgrade harder because your back will be up against the proverbial wall. There’s no easy answer here, but it’s a conversation I’m having with a lot of my customers.

When it comes to having NDA information on something to be announced in the future, I can never spill the beans. Sorry. If I do, that puts me at risk with whoever I have the NDA in place with (and that comes with all kinds of legal problems for me if I do). I love my customers, but it’s not worth putting me out of business by letting something slip. That said, I can still steer customers in the right direction without them even getting a hint that there may be an underlying and different reason I am doing so.

I’ll leave you with this one final thought: as much as deprecated features are important to consider in your planning efforts, don’t let it become an obsession, either. DBM is a great example – it’s not going away tomorrow. If you’re using it now, that’s OK. A deprecation announcement didn’t mean to stop using it this very minute; just don’t get complacent about making a decision, either. A few years goes by in the blink of an eye when it comes to IT (just look at the people still running SQL Server 2000 for whatever valid reason and are stuck supporting it). Don’t be that guy or gal keeping the lights on for the few instances requiring DBM for the next 15 years. Be smart, make the right choices, and stay on a supportability path.

August 3, 2011

MVP Renewal, Schedule and Training Updates

With everything that’s been going on, I realized I haven’t updated anyone on what’s been going on and let you know of some great opportunities to take some training I’ll be delivering.

MVP Renewal
First and foremost, I was renewed on July 1st as a Cluster MVP for the period of 7/1/11 to 6/30/12. I’m very honored. Trust me when I say that I never assume that it will happen, and it’s definitely NOT automatic.

SQL Server Foundations – 2 Day Online eLearning Event August 30 and September 1
I’ll be delivering a six session, two day training webinar in conjunction with Penton Media and SQL Server Magazine to cover some of the basics folks need when dealing with SQL Server. I am very excited to do this, as I often just deliver training that is only cluster and/or high availability related. It won’t be death by PowerPoint – I promise! This will be a fun couple of days.

The sessions break down as follows (full abstracts at the link below):
TUESDAY, AUGUST 30
Session 1: Core SQL Server Engine Concepts, 11 am ET
Session 2: SQL Server High Availability Part 1, 1 pm ET
Session 3: SQL Server High Availability Part 2, 3 pm ET
THURSDAY, SEPTEMBER 1ST:
Session 1: Implementation Strategies, 11 am ET
Session 2: Successful SQL Server Administration Techniques, 1 pm ET
Session 3: Practical Backup and Restore, 3 pm ET

The cost is a really good deal – $218 for all six sessions. With everyone having tight training budgets, this is a really economical way to get training without having to deal with a travel budget!

I believe it is like the others I have done with them in the past where you can view them later as well. You can ask Penton to make sure.

For full details and to register, click here.

24 Hours of PASS
Hot off the presses! Just announced today is the latest incarnation of 24 Hours of PASS, the Summit Preview version. I will be doing the session “Multi-Site Failover Clustering With SQL Server 2008, 2008 R2, and Denali” at 19:00 (7PM) GMT/15:00 Eastern US (3PM)/12:00 Western US on September 7. Click here to register.

Pre-Conference Seminar at PASS Summit
I’m very psyched about this. I was flattered when PASS picked my abstract out of the many they received. – it’s a big deal! I will be doing DBA-499-P Deep Dive into AlwaysOn: Failover Clustering and Availability Groups (click on the link for a full abstract) on Tuesday, October 11. This will be a full day of Denali high availability training focusing on what’s new in failover clustering as well as talking about the new AlwaysOn availability groups feature. The day will have lots of demos and unlike the webinars, it will allow you to be more interactive instead of just a Q&A session at the end.

The lineup for the two days of pre-conference seminars at PASS is quite frankly awesome. Lots of good stuff, and I don’t envy the decisions people will have to make to choose what to go to. Of course, I recommend choosing mine for the second day and something else for the first. But I may be a bit biased …

The cost for the each pre-conference is $395 in addition to the cost of Summit itself. What are you waiting for? Go register!

PASS Summit Spotlight Session
Besides the pre-conference seminar, I’ll be doing a spotlight session (DBA-408-S) entitled “Implementing SQL Server Denali Using Windows Server Core” (click for the abstract). Windows Server Core is a variation of Windows Server 2008 and Windows Server 2008 R2 that allows you to have a GUI-less server. Denali is the first version of SQL Server to support this variant of the underlying OS. If things like command line and PowerShell get you excited, this is the session for you.

Whew! That’s a pretty action packed schedule for the next few months which doesn’t include any of the day-to-day work or private things that I deliver. I haven’t figured out what I will be doing after PASS Summit, so stay tuned. I am eying SQL Saturday 96 in Washington D.C. on November 5th, though.

Hope to see you at one or all of these!

June 2, 2011

Raise Your Game

You are under attack. No, I don’t mean some foreign country is going to invade your city. Your job as a DBA or IT admin is. Why do I say that? Well, you’ve got lots of threats depending on your environment: the cloud, virtualization, skill sets, budget, new technology, etc. There’s a lot you have to do on a daily basis to make sure you still have a job tomorrow and the next day. Fall too far behind and you become irrelevant. New releases keep coming, so you have to keep up even if your company is not using them or will get them in production any time soon to stay relevant. Ignore trends like virtualization and the cloud (and where you will fit into those pictures), and you will be left behind.

This blog post was inspired by a con call I had today. Some people on that call – all very smart and very good DBAs – did what a lot of DBAs I know do, and basically said, “I don’t want to be a Windows failover clustering expert.” Fair enough, but do you have any prayer of being successful with things like failover clustering (or the upcoming AlwaysOn availability groups feature in Denali) if you don’t undertstand the underlying platform which it is built upon? In some ways, you should be smarter than your Windows, networking, security, and storage guys as it relates to your implementations around this stuff because they probably don’t know SQL Server and what you don’t know about their shortcomings will hurt you long term. You must articulate what you need to be successful. I’m not just singling out failover clustering. This would hold true even if you are not using clusters or even a DBA and are just a plain ol’ IT admin. The only way to understand what you’re doing is to know how things work underneath the covers to a point you’re at least comfortable with it. By no means am I telling you to become a Cluster MVP.

If you’ve seen any of my talks, you know I’m a big supporter of PowerShell. Heck, way back in 2009, I wrote a blog post entitled “The Incredible Shrinking DBA Skillset Rant #1 – Scripting” and said this:
“I can’t say it enough at this point: learn PowerShell. And if you didn’t understand me, this should clear it up for you: L E A R N P O W E R S H E L L. You’d be foolish not to. Microsoft has often (and sometimes rightly so) been criticized for not standardizing toolsets between products. PowerShell is becoming that unifier, and you WILL be left behind as an administrator if you don’t learn it.”

On that call today, not only did the DBAs basically say “we don’t do that” with regards to PowerShell in general and for administering Windows failover clusters (do you ever patch your instances of SQL Server 2008/R2?), but one of them said even their Windows guys do not either. I was on a con call recently with another customer who said that where they use Windows Server Core (not for SQL Server – just qualifying), they have some issues with administering it because their guys are not command line or PowerShell knowledgable. So they generally don’t use Server Core because of the skills of their admins and their reliance on a GUI – it wasn’t a Windows issue. That floors me.

Look, I know we’ve all gotten used to some of the GUIs Microsoft and other vendors provide, but you may not get all options in a GUI. Some advanced things may only exist in PowerShell or some command prompt version of the GUI. Do not be scared of the keyboard. Maybe it’s because when I came up in the industry, I had to know command line for OSes like Solaris, HP-UX, Novell, and VAX/VMS (and no, I’m not 60!), but I can’t fathom NOT doing some command line stuff. Command line is sometimes faster, and to be dead honest with you, scripting is the only way to truly manage large environments for tasks to be repeatable. Running Maintenance Plan Wizards for 1000s of databases doesn’t really scale despite what you may believe.

I know not everyone out there is resisting scripting or burying their heads in the sand saying things like, “Dealing with the Windows cluster stuff isn’t my concern.” Kudos if you’re not that person. If you are – even a little – go ahead, continue to think that. You might as well shoot yourselves in the foot, too. If you are a masochist, you’ll be in your element. If you want to have an easier time, wake the heck up and grab a (proverbial) strong cup of coffee. Don’t hit snooze on the alarm.

Consider this your second tough love wakeup call. 2009 wasn’t enough with regards to scripting, but this “It’s not my problem” attitude has to go away. If you don’t understand the basics, how can you do anything advanced? I don’t think asking folks to know a bit of hardware and OS stuff is unreasonable for a DBA.

12 3

Categories