SQL Server Performance Forum – Threads Archive

Setting Up an Active/Active SQL Server 2000

I hope somebody can clarify things for me. I want to setup an active/active virtual server configuration with SQL server 2000 using 2-node under Windows Server 2003, I will call them as NODE1 and NODE2. Assuming that I have tested the OS Cluster and hardware setup are fine, I should be ready installing the SQL server instance. I have below disks E (data), F (log), J (quorom), G (msdtc). Below are my clarifications:
1. From my understanding, to achieve an active/active, i just need to install separate sql server instances to NODE1 and NODE2. Basically, install sql virtual server #1 on NODE1 and install sql virtual server #2 on NODE2. Am I right?
2. Where do I install the system databases for NODE1 and NODE2?
3. Can I have both NODE1 and NODE2 point or use drive E and F for my user databases data (MDF) and log (LDF)? Can I do query and see same user databases when I connect to either NODE1 or NODE2?
4. Do I need to have 2 different sql server virtual names? Let’s say SQLVS01 and SQLVS02. Thanks,
Jon M

1. yes, although the node that you install the instance on is nto that relevant. Once it is installed you can nominate a preferred node for running that instance
2. Each instance must have it’s own raid set, at least one preferably more to split data/logs, etc.
3. no as per 2, you need to have different raid sets for each instance. Each instance is totally independent from the client’s perspective. So it is like having two sql servers
4 yes you will need a virtual server name for the cluster itself and then also a new per instance Cheers
Twan

Thanks Twan.

quote:2. Each instance must have it’s own raid set, at least one preferably more to split data/logs, etc.

You mean the system databases for each instance should not be stored on the node’s local drive?

quote:3. no as per 2, you need to have different raid sets for each instance. Each instance is totally independent from the client’s perspective. So it is like having two sql servers

If I have different sets of disk for each sql server instance, let’s say
SQL Server #1 SQLVS01 E (data) and F (log)
SQL Server #2 SQLVS02 G (data) and H (log)
Does this mean that users who are logged-in to SQLVS01 will not be able to access data in SQLVS02? I want to be able to use both sql servers and give user access to a common user database either through SQLVS01 or SQLVS02. Is this possible? If yes, how? Thanks again,
Jon M

Hi ya, 1 yes that’s right the system databases cannot be stored on local disk, since the cluster needs to be able to failover to another node when a node dies. users will be able to access data from both as long as you’ve set up the access for them, but the two instances are totally separate, it is exactly as if you had two physical sql servers Cheers
Twan

As mentioned the two instances are totally seperate. The term Active/Active has nothing to do with load balancing. A SQL Server cluster is a so called Failover Cluster, if one server fails the other takes over it’s services. The term Active/Active here really just mean that you have one or more instances running on both nodes in the cluster. You can have up to 16 instances in a SQL Server 2000 cluster that you can divide among your two (or more) nodes. If all 16 instances where running on one node you would have an Active/Passive cluster. This setup requires less licenses.

Can I have one instance of sql server 2000 Ent. Ed. running on a 2-node cluster?

HI Jon, yes you can have a single instance running on a 2-node cluster, but it is only ever running on one of the nodes at any point in time. i.e. clustering is not load balancing and vice versa Cheers
Twan

quote:yes you can have a single instance running on a 2-node cluster, but it is only ever running on one of the nodes at any point in time.

Twan, I think you are referring to active/passive. I was asking if it is possible to have a single instance with active/active. Thanks,
Jon M

Jon, Active/active means 2 clustered servers each running their own instance of SQL. So active/active is 2 SQL instances by definition. So, no you cant just run one instance of SQL in an active/active cluster setup. Running only 1 instance of SQL with a 2-node cluster would be an active/passive. Just make sure each server is powerful enough to handle the load of 2 SQL instances and the DBs that go along with them. STEVE

quote:So, no you cant just run one instance of SQL in an active/active cluster setup.

In other words, I could not have data sharing with users logged-in to an active/active sql server cluster. Am I right? Thanks,
Jon M

Jon, You can setup user access to the multiple SQL instances/databases just as you can for a set up access to multiple file servers/folders. The fact that they are in a cluster or active/active doesn’t stop that. When you install a cluster, it is setup to run within a domain as a requirement. If your users are part of that domain (or another domain with a trust relationship between the two), you can provide those users rights to data on either or both servers. Users are not accessing the "active/active cluster", they are accessing "sqlinstance1 on virtualserver1" and "instance2 on virtualserver2" depending on what rights you assign. To the user, it is like them accessing 2 different independent SQL servers. The cluster has nothing to do with the authentication to the user. As a side note, replication can allow you to have a database/table replicated to a second server (a source/target type of synchronization). This could be helpful for you if you have data on one server that you need to build a select statement for (a join or ???) on the other server (since joins cant span across 2 servers). You can manage the rights that users have from Enterprise Manager for both servers. STEVE STEVE

Please tell me the right direction. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] I would like to achieve the following: 1. I have 100 users who will access my user database named for example MYDBASE using sql server instance 1. 2. Another 100 users will access same database, MYDBASE, using sql server instance 2. If the above scenario is not possible, what is an alternative solution? Thanks, Jon M

Any reason that you can’t have all users accessing the same instance?

Argyle, We have 3,500 heavy users that need to connect to the same tables in the same database and we want to balance the load. Per MS Official Academic Course Textbook 70-290 Managing and Maintaining a Microsoft Windows Server 2003 Environment
"A server cluster is a group of servers that function as a single entity, providing high availability for a particular set of applications. High availability in this case means that application processing is distributed among the servers in the cluster, reducing the load on each computer and providing fault tolerance if any of the server fails. The servers in a server cluster, which are called nodes, have shared access to a common data source, usually in the form of a storage area network (SAN), enabling all of the nodes to maintain a current information base…" So, how do we balance the load or users while at the same time they are connected to the same database in the cluster? We need a single database that is being shared by our users. Thanks,
Jon M

This is not possible with any database vendor… you’d have to find a way to either mirror the data e.g. replication/logshipping or separate the data i.e. fedarated servers…? Cheers
Twan

Thanks everyone. Below link also helped me understand things clearly:
http://www.eggheadcafe.com/ng/microsoft.public.sqlserver.clustering/post213700.asp Jon M

]]>