That pretty much summarizes my feelings on this topic.
I have seen this proposed as a configuration before many times. The web guys say they need HA SQL for their web site data and then they figure that since they are getting a cluster that they can use it for IIS, too. No, don’t do it. While at first glance it makes some sense, if you start thinking big picture, then you start seeing the problems. First off, the big picture for an HA website with HA SQL backend should look like this:
- Clients connect to the Internet to access the web site.
- The client then connects to the web site through the firewall. The firewall protects the IIS servers in the web site. This is where best practices start to kick in. Using NLB with IIS provides a scalable and highly available web platform.
- The IIS server communicates with the SQL MSCS cluster behind another firewall. Again, a best practice to keep the SQL data from being compromised in the event the web server is compromised.
- IIS is protected, scalable, and highly available
- SQL is extremely well protected and highly available
The alternative solution (meant to save money) is to use one MSCS cluster to host SQL and IIS. In this solution:
- Clients connect to the Internet to access the web site
- The client then connects to the web site through the firewall. The firewall protects the IIS servers in the web site.
- The IIS server communicates with SQL on the same cluster perferrably on the same node at the same time.
The problems with this configuration are:
- SQL is not as well protected
- IIS is no longer scalable
- IIS 6.0 is not MSCS cluster aware
In order to make it work (IIS and SQL on the same MSCS cluster), you need to:
- Create a resource group
- Use the generic resource script for IIS 6.0
- Create your SQL instance in the same resource group
Since SQL and IIS are in the same resource group, they will failover together and be on the same node all of the time. You will want to do this because if there are problems with a node causing one of the resources to fail, you probably will want all of the resources to fail, too.
However, again, I strongly recommend against putting IIS and SQL on the same MSCS implementation.
What in the world was Microsoft thinking?
I was installing MOM 2005 and found out that it is not possible to install the MOM 2005 Database on a SQL Server 2000 server with SP4. WTF? That just doesn’t make sense.
Supposedly this will be fully supported with MOM 2005 SP1. Umm, that just doesn’t work for me. So, time to research. Thanks to Neil Hobson, a MOM MVP, a workaround has been published in the MOM 2005 public newsgroup. It is a registry hack, but at least it is a supported registry hack. What needs to be done is that you have to alter the CSDVersion entry located at:
If SP4 is installed, the version number will be 8.00.2039. To get the MOM 2005 Database to install, you need to change the version number to 8.00.761 which is the same one for SP3A and then change it back again after the installation is completed.
Somebody needs to be slapped over this one.
Updated 7/28/05 – Microsoft provides another workaround for this same problem. You can use the msiexec command with the PREREQ_COMPLETED=1 switch to skip the MOM Prerequisite Checker. See KB 902803
for more information.
Greg Page of the Microsoft Cluster Group and I had a mini-discussion today on the microsoft.public.windows.server.clustering newsgroup, as well as followed it up with a phone conversation, regarding the implementation of the MSDTC resource.
In the past, Microsoft has clearly stated that the MSDTC resource and its dependent resources should be in their own cluster group separate from the default cluster group that contains the quorum. The reason behind that is that in the past, read: NT 4.0 clustering days, the MSDTC could consume enough disk time that the quorum would not be able to read/write to its disk, and it would result in a failure.
Recently, the Exchange team has stated that the MSDTC resource is OK if it is in the default cluster group along with the quorum. The Exchange team’s reasoning is that the MSDTC is only used when upgrading and patching Exchange and during the install of Exchange in a cluster. To put it in its own cluster group with its own resources is a waste for Exchange clusters unless work flow applications are run on the cluster.
Well, obviously disk technology has progressed considerably since NT 4.0 days. So based on increased performance and the general lack of use of MSDTC on most clusters where it is installed, the Microsoft Cluster Team has decided to change their view of the MSDTC resource being in the cluster group with the quorum. Basically, their view is morphing to a “we really don’t care if MSDTC is in the cluster group.” Of course this view also allows for adminsitrators to create a separate cluster group for the MSDTC resouce and its dependent resources that is separate from the cluster group holding the quorum and its resources based on performance needs.
I, personally, believe the MSDTC should be in its own cluster group in some cases, all of which are performance related. MSDTC may be heavily utilized, in which case it makes sense to give it its own spindle. While in many cases it is unlikely to impact the quorum enough to cause harm if it were in the default cluster group with the quorum, I am more concerned about those few cases where it might cause harm by there being many requests in the queue and the quorum would be unable to read/write and then cause failures. Failures of the default cluster group would result in the MSDTC resource offline for a few moments during the failover process.
Exchange work flow can cause a heavy load and SQL two phase commit transactions can also cause a heavy load. However, those are just a couple of examples where there may be problems.
I am a big advocate of HA being done right in that if there is a possibility of something happening that can have a negative impact, and it is easy to avoid the problem, then you should do it. So, I would rather avoid any potential problems by giving the MSDTC is own resources if it is heavily used.
So to sum it up:
View 1 – New disk technologies have improved performance to the degree that the MSDTC can share resources without negatively impacting the cluster group.
View 2 – The cost to avoid this risk is low enough that if the MSDTC is heavily used it is best to avoid the problem completely by giving the MSDTC its own resourcse.
I got brave and decided to fix the problem. The issue, for those who missed the first show, was that I needed to reconfigure the MSDTC on a SQL cluster. The MSDTC needed to be moved to a different drive because we are retiring one of the EMC frames.
The more I read on this the more complex it seemed. I read through Q 301600 and Q 294209, and reviewed several other sources. These articles made it sound like I was going to have to rip out DTC on each node and then rebuild it on each node and restart SQL on the cluster. I just refused to believe it was so complex.
The MSDTC resource was configured as part of the initial cluster group with the cluster name and Q: as dependencies. Previously, the quorum was moved from Q: to I:, but the old Q: could not be removed until the MSDTC was reconfigured.
So, the more that I thought about it, the more sense it made to me, so I:
- Stopped the MSDTC resource
- Copied the MSDTC folder from Q: to I:
- Stopped the Q: resource
- Deleted the MSDTC resource
- Created a new MSDTC resource with the clustername and the new I: as dependencies
- Brought the MSDTC resource online
I don’t see any problems with it so far.
More like, it is semi-broken. Ok, it isn’t broken at all, but it isn’t configured the way I want it configured so I have a desire to fix it.
It all started last Friday night. We are migrating from one EMC frame to another one. The storage guys (they really do a great job here) added new LUNS to the SQL cluster. We then added the drives as resources in cluster admin and then put the drive resources into the proper SQL cluster groups. We shut down the SQL services, did a quick file copy to the new drives, changed the drive letters to match the old drives and restarted the SQL services. We also pointed the quorum to its new drive. Everything works.
Problem. The quorum is now the I: and not the Q: Yes, I know it is a stupid issue, but the company wants all quorums to be Q:’s. So, we try to make the change. Nope, the old Q: won’t let us change it. Why? Because of the MSDTC is using that resource. Time to go to bed.
Topday, we revisit the issue of the Q:. We have to do something because this one drive is holding up the retirement of the old EMC frame. The MSDTC is in the cluster group along with the quorum and the cluster IP and cluster name. So, there are two physical disks, one being the Q:(old quorum) and the other being the I:(new quorum) in the cluster group. We try stopping the MSDTC, copying all the msdtc folder info from Q: to I:, adding the I: as a dependency, and removing the Q: as a dependency. As you can guess, this just doesn’t work. Yes, I know the solution is to uninstall MSDTC and reinstall it. No, I don’t want to do that. I want a better way.
Back to thinking… I will probably just do it the right way, but I have a nagging feeling I am missing something really easy.