Clustering SQL Server 2000 (different versions)

[Duncan Edmonstone]

I'm looking at doing a potential consoliadation exercise for a bunch of SQL Server instances.

What I want to do is take a number of SQL Server 2000 instances currently running on different Windows 2000 systems with the SQL server instances on different patch levels and consolidate them onto a cluster running on Windows Server 2003.

My question is, can I have different instances of SQL Server in the cluster entirely independent of each other running different SP levels?

Thanks

Duncan

[Jerome Henry]

Theorically you can, but my advise is don't ! On some queries, the heartbeat system may detect an instance with an older SP as being working whereas the query will require another SP feature... which'll make the whole system hang !...

hth

J

[Duncan Edmonstone]

Hmm maybe I didn't make myself clear, I don't mean different SP levels for Windows Server 2003, I mean different SP levels (and therefore different binaries)for SQL Server 2000

Thanks

Duncan

[Jerome Henry]

Yes, that's what I understood.
If you pass only queries that are to be interpreted the same way by the different instances, then you shouldn't face problem.
But if you pass a query that will be interpreted differently by the different instances / associated SP, then you will face the risk that the query is spread to 2 different instances interpreting and getting result back differently... which'll make the whole stuff hang.
There are so many compatibility problems in importing and exporting from different SP on SQL server 2k (not talking on getting from 7.0 to 2k) that I wouldn't take that risk.
Wouldn't it be easier to upgrade all versions to same SP ?

J

[Duncan Edmonstone]

Sorry for being thick, but I don't really follow what you mean here - Can you give an example of where I would run into a problem with this?

Thanks

Duncan

[Jerome Henry]

For example, SP3 disables listening on port 1434 when network is disabled.
We didn't take this feature into consideration, up to the time we had a crash on that. A script we were running, for safety reasons, was to address the server on that port, and that one only. Turning network down for IP address management on some servers just turned them off as we hadn't re-started sql service afterward. All the 8 servers were responding to the heartbeats, but only five were 1434 opened. Queris were sent to 3 servers, that were not responding, though they were considered as alive. Result was that query result was incoherent. It took to us hours to discover the source of the trouble (network works, hearbeats say 8 nodes, 5 replies only... ??)
That's an example of SP impact. If all our servers had been updated with SP3, we wouldn't have got any answer, and would have guessed network impact on the server and, most important of all, we wouldn't have received any answer, which is better than partail replies (false) as you guess...

Do you see what I mean ?

J

[Duncan Edmonstone]

OK, I follow you now... let me just make sure I have this straight with the following example:

1. I have two nodes nodeA and nodeB, both with a local C: and D: drive
2. I have some shared storage - seperate disks seen as X: and Y: drive
3. I have 2 SQL Server instances mydbA and mydbB. mydbA is on SQL Server 2000 SP3, mydbB is on SQL Server 2000 SP2
4. I install SQL Server so that:
-mydbA has its binaries in C:\Program Files\sql server\ and its data files on X:
-mydbB has its binaries in D:\Program files\sql server\ and its data files on Y:

And then I apply the relevant SPs to the instances?

And as long as I am aware of the differences between these versions I should have no problem.

Thanks

Duncan

[Jerome Henry · Accepted solution]

Right Duncan !
X and Y should be different disks, and, as you certainly know, logs from mydbA should preferably not be on X, nor logs for mydbb on Y (for performance reasons).
Once SP applied, and as long that you know paths, there shouldn't be any problem.

Have fun !

J