SQL Server Performance Forum – Threads Archive

SQL Server on Win 2003 2 Node Cluster Install

Hey guys. I am new here, and was just wanting to ask a question. I just got my 2 Node Win 2003 Enterprise Edition Cluster running, and need to install SQL server. I can use any version of SQL server, so whatever is best is what I want to do. Could someone tell me the steps to install it? I cannot find any info on this at all and would appreciate any help I can get. Just basic steps would be great. I havent used SQL or clustering on anything but NT 4, so this is all kind of new to me. Thanks!

You need to use SQL Server 2000 Enterprise Edition for clustering. Some links below. How to Install SQL Server 2000 Clustering
http://www.sql-server-performance.com/sqlserver2000_clustering_install_main.asp How to Install Windows 2000 Cluster Services (still usefull for windows 2003)
http://www.sql-server-performance.com/wndows2000_clustering_install_main.asp SQL Server 7.0, 2000, 2005 Clustering Resources
http://www.sql-server-performance.com/clustering_resources.asp What is New with SQL Server Clustering on Windows 2003
http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_6.asp

I’m in the same boat here. Setup the MS clustering on windows 2003 and then went to install SQL 2000 EE. The first link you show was one that I had read through in preparation for this and it was the MS DTC that completely stuffed me up. Running comclust.exe didn’t keep me in the dos prompt like the article shows, instead it immediately started the cluster administrator in windows. The MSDTC wasn’t automatically created so I went down the path of trying to manually install it. I then realised that I didn’t have a separate physical disk to install it on and was reluctant to use the quorum disk so I backed the entire cluster installation out to ensure that the servers were available again for use. Do I need to setup a separate disk for DTC, which I presume should have some form of fault tolerance, i.e mirrored. My shared disks at present are E: (3 x 33.6/RAID5), F: (4x 72/RAID5), Q: (Quorum, 2 x 9.1/RAID1+0). Also regarding the IP Addresses, why so many? 4 for the cluster, 1 for the SQL virtual server, 1 for MS DTC, what else? Any info greatly appreciated.

MSDTC is OS specific and not SQL specific. MSDTC is a bit different in cluster on win 2003. Check: How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
http://support.microsoft.com/kb/301600 If you don’t expect heavy MSDTC usage use the quorum drive instead of a seperate drive. Not sure what you mean with many IPs. They are there for a purpose. Typical setup:
Node 1 – Frontend IP
Node 1 – Backend IP (if you have seperate management network)
Node 1 – Heartbeat IP
Node 2 – Frontend IP
Node 2 – Backend IP (if you have seperate management network)
Node 2 – Heartbeat IP
Virtual Cluster IP
Virtual MSDTC IP (unless you use the cluster IP)
Virtual SQL Instance IP(s) You could add a second heartbeat network card for even more redundancy but generally you allow heartbeat traffic on your frontend or backend cards too.

I have only seen active/passive failover clustering mentioned here. Is it possible to have any other form of clustering, in particular a load-balancing cluster that also functions as a failover cluster ?
I have been told that Windows 2003 includes load-balancing in their latest version of MCS (Microsoft Clustering Service). Is this true and is this type of clustering recognised by SQL Server 2000 or SQL Server 2005 ? My company wishes to implement a load-balanced cluster using two 64-bit Intel Itanium servers, Windows 2003 64-bit and SQL Server 2000 64-bit EE. Both servers will connect to the same external disk subsystem, an IBM Profibre array, via optic fibre and Qlogic cards. Is it possible to configure a load-balancing cluster with SQL Server 2000 64-bit EE or SQL Server 2005, using the hardware above ? Also, for anyone familiar with the hardware mentioned above, is it possible to at least configure an active/passive failover cluster using this configuration ?
With regard to the external Profibre subsystem, is it possible for both servers to use the same physical file system (not the OS files, but the same physical mdf/ldf data files), or would the database file system need to be mirrored on two different partitions on the external disk and be independent of each other ?

SQL server clustering is a failover cluster and not a load balancing cluster. There is no way for two nodes to share the same disks and database files. Microsoft Network Load Balancing works for services like IIS but not SQL Server. Active/active clustering only means that you have one or more sql instances running on multiple nodes in the cluster. Say you have installed 10 virtual SQL servers in your two-node cluster. If all are running on one node you have active/passive. If 5 are running on one node and 5 on the other you have active/active. All instances are seperate from eachother and use their own disks in the shared storage. About loadbalancing it’s often not something you can apply afterwards. It’s a something you need to think about during the design of each database and application. There are many different ways to achive it. For example you could have one master database that is read/write on a failover cluster and a number of single servers that keep a read only copy of the database. The application will write to the master but read from the others. It could be a simple round robin selection when the application needs to read data. Depending on the application and database the master could replicate the data with snapshot or transactional replication or you could detach/copy/attach the databases during night. You could implement the same thing in your cluster. You could have one instance running on a node as master and then have a read only copy on another instance on the other node.

Many thanx for the reply, Argyle. That was valuable information. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by davidfarr Many thanx for the reply, Argyle. That was valuable information. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] If I understand you correctly: I can create an active/passive failover cluster by configuring each of my two physical servers as Virtual SQL Servers and placing them both on the same node. One virtual server is then active and the other is passive. Each virtual server requires its own disk and data files. My external disk subsystem would therefore at least need to be split into two partitions to allow each virtual server to have its own independent disk and data files. Is the above correct ? <hr height="1" noshade id="quote"></blockquote id="quote"> As a minimum each instance would need one logical disk. But as with any SQL Server you usually have more logical disks available. One for Data files, one for log files, maybe one for tempdb or backup files. Example: Data – 4x18GB RAID0+1 Log – 2x18GB RAID1 TempDB – 2x18GB RAID1 Backup – 4x18GB RAID5 <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by davidfarr I have another question though: How does a SQL server failover cluster maintain a duplicate, synchronised copy of the database on each virtual server if each has their own disks and database files ? Does each virtual server receive and process SQL Server data (inserts, updates, DTS) in perfect synchronisation and write independently to their respective data files ? The physical connection between these servers is, at best, a 1GB LAN connection. Are there no "bottleneck" issues with regard to keeping the data synchronised ? Forgive my ignorance, but the architecture of clustering is new ground for me.[:I] <hr height="1" noshade id="quote"></blockquote id="quote"> There is no "keeping in sync" part here. One sql instance does not know of another instance and the physical nodes do not operate a single instance at the same time. Say you have one virtual SQL Server instance in a two node cluster. The SQL Server and Agent services will be installed on both physical servers but the services will only be running on one server at a time. And the server running the sql services will be the only server that can see the disk that this sql instance is using. During failover the services are stopped on one node and started on the other. During failover other virtual resources will move over like SQL Server IP, name and disks. The process during failover is the about the same as stopping and starting the services on a single stand alone server with the difference that you start the services on another physical machine. You could install for example merge replication to keep two database copies in sync on two different sql instances. But setting up merge replication between two instances in a cluster is no different than setting it up between two stand alone sql servers. Replication is another subject though and you have a lot of planning to do if you are going down that road.

Oh dear, I am now more confused than I was before. Are you telling me that a SQL Server failover cluster has absolutely no benefits at all over two stand-alone servers unless I have replication in place ? To re-state my goal:
I currently have 2 physical servers to work with. I want to be able to pull out the power cable (for example) on one server and still be able to continue operations on the other server, on the same database, without losing significant data and without requiring my client applications to change their connection string.
Are you saying that this is impossible to achieve, even when using shared external storage ?
Is there absolutely no way to configure two physical servers in such a way that if one of them crashes, then the other will have access to the latest data without the need for replication ? If so, then this seems to contradict another thing you mentioned. You say that, in a cluster, the SQL services will only be running on one physical server at a time. In order for replication to succeed, the services would need to be running on both physical servers, surely ?
If they are not both running, then no replication occurs, and in the event of a crash on my active server I am left with a database that is very different from the production database I had just a moment ago. Surely this cant be right ? What am I missing here ?

quote:Originally posted by davidfarr Oh dear, I am now more confused than I was before. Are you telling me that a SQL Server failover cluster has absolutely no benefits at all over two stand-alone servers unless I have replication in place ? To re-state my goal:
I currently have 2 physical servers to work with. I want to be able to pull out the power cable (for example) on one server and still be able to continue operations on the other server, on the same database, without losing significant data and without requiring my client applications to change their connection string.
Are you saying that this is impossible to achieve, even when using shared external storage ? Is there absolutely no way to configure two physical servers in such a way that if one of them crashes, then the other will have access to the latest data without the need for replication ?

This is exactly what a cluster allows you to do. It helps you achive availiblity in the event of hardware failure or maintenance on one of the nodes. The failover process will, without manual intervention, fail over all services to the working node. It ususally takes less than a minute. This is why it’s called a failover cluster. This is a totally seperate function than load balancing or keeping two databases on two sql instances in sync. There is no need to keep data in sync since all the databases are on disks in the shared storage that also will move to the other node in the process.

quote:Originally posted by davidfarr
If so, then this seems to contradict another thing you mentioned. You say that, in a cluster, the SQL services will only be running on one physical server at a time. In order for replication to succeed, the services would need to be running on both physical servers, surely ?
If they are not both running, then no replication occurs, and in the event of a crash on my active server I am left with a database that is very different from the production database I had just a moment ago. Surely this cant be right ? What am I missing here ?

No the services for one specific instance can only be running on one node at a time. But the services for another instance could be running on the other node. And you could have replication between these two instances the same way as you can have replication between two instances on stand alone servers.

]]>