MS SQL 2005 Active - Active Cluster

I have two MS SQL 2005 servers and an HP MSA 2000 hard disk array that is shared between the two.  I would like to setup an Active - Active cluster and would like some advice on the best way to set this up for maximum performance.  I also want to retain full failover capability in the event that one of the servers fail.

Also, upgrading to SQL 2008 is also an option.

Who is Participating?
jogosConnect With a Mentor Commented:
You start with a single node, add extra nodes seperately.

But mind this considerations while you want to reuse both instance-name and old server.
- instance-name: you cannot test your new setup before because installing the new can only be done when the old is down (no duplicate names)
- reuse old server: is the hardware of the old server  (memory/cpu/...)  the same as the new server? Consider if this is a healty situation
Now setting up an environment ... why still choosing 2005?

Try to not to put sql instances on same 'prefered node' that peak with activity on the same moment. See (try out) if they actually run when faillovered on a single node, also backup/recovery must work.  Peak is in #factors: reads, writes,  transactionlog-writes (recovery model!), temp-db usage, #connections ...
For spreading files same story as for a single server setup: spread where possible .
Raja Jegan RSQL Server DBA & ArchitectCommented:
As jogos mentioned, go for Clustering directly in SQL Server 2008..
And this step by step guide would help you out in creating SQL Server 2008 clustering..
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

TedgClAuthor Commented:
Our security Audit team is a little behind the times is why I put sql2005 as the default.  RRjegan - Thanks for the links to the setup for sql 2008!

Ok assuming that I decide to switch over to 2008 here is my current situation.  I currently have one server in production running sql 2005 and have another new server coming in sometime next week.  I need some advice on how to get from one sql 2005 in production to two machines running in a cluster on 2008 with minimal downtime.

Do you think the following would work?
1) Install Windows 2008 and SQL 2008 on to new server
2) Somehow move service from the sql 2005 box to the sql 2008 box
-> This is going to be a bit tricky.  For one, all of the clients are configured to connect to the database by the machine name, for the moment let's say it is not an option to update the connection string in the clients.  From what I know about the cluster service, I can setup the cluster to look like a machine name on the network.  Would it be possible to take the sql 2005 machine off of the domain and then create a cluster name with the name that was previously used by the sql 2005?  If not, what other options do I have?

For moving the data over, is it possible to just take the 2005 box offline and move the MDF and LDF files over to the 2008 box and attach the databases?  If not, do I rebuild that databases from a backup?

3) Assuming that the DB service is now running only on the 2008 box, I guess it is safe to format the win 2003 server (was running sql 2005) and bring it up to date with win 2008 + sql 2008?

Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I need some advice on how to get from one sql 2005 in production to two machines running in a cluster on 2008 with minimal downtime.

So you have one another machine currently running SQL Server 2005.
Just follow the steps below:

1. Install your another server with SQL Server 2008 and Windows Server 2008.
2. Do the clustering between your two SQL Server 2008 servers.
3. Take a backup from your SQL Server 2005.
4. Restore it in your SQL Server 2008 in the cluster..

So the answers as your requested:

1. Yes, you can do it.
2. Would prefer the Backup and Restore option compared to Detach and Attach files
3. No, You have to take the Databases backup first and do your Installation Windows 2008 + SQL 2008 and then do the Clustering and then Restoring Backups..

Hope this clarifies.
TedgClAuthor Commented:
Thanks for the response.  I actually only have two servers not three.  One of the two is currently in production running sql 2005 and the other one is a new one that just arrived.  This causes a problem in step 2 as you have outlined because I can't cluster two windows 2008 servers because the server I need to cluster is currently running mysql 2005 & windows 2003.  Also, all of the clients are currently pointing to so the new cluster needs to respond at  How can I best get from the current state to the future state with minimal production downtime?

Please see figure.
Raja Jegan RSQL Server DBA & ArchitectCommented:
As I mentioned earlier "You have to take the Databases backup first and do your Installation Windows 2008 + SQL 2008 and then do the Clustering and then Restoring Backups.."

Taking backups and restoring it will not take much time but Formatting your Windows Server 2003 + SQL 2005 to Windows 2008 + SQL 2008 will take some time based upon the people who would be doing it..
Also the Clustering configuration depends upon the peoples expertise..

In my experience the whole task can be done in a single day, provided everything is available in hand..

And hence I would request you to plan for a downtime of 2 - 3 days ( planning for the worst case)..
And recently we had Christmas holidays which we actually missed out for this activity.
TedgClAuthor Commented:
Would the following be possible?
1) Take SQL 2005 server offline
2) Restore databases onto new SQL 2008 server
3) Bring everything back online using the single new SQL 2008 machine
4) Format SQL 2005 machine to SQL 2008
5) Then form SQL 2008 cluster between the two machines

Do you think it is possible?

Raja Jegan RSQL Server DBA & ArchitectCommented:
No.. Because SQL Server Installation needs to be in Clustered mode to support the Cluster..
Hence you have to

1. Uninstall SQL Server 2008 and 2005 in both the servers
2. Create Cluster.
3. Install SQL Server 2008 in First Server with the Cluster created.
4. Install SQL Server 2008 in Second Server joining to the Clustered instance..
TedgClAuthor Commented:
I am sorry to ask again but I am confused now.  Based on what you are saying, if I can't do the scenario I outlined in 2615121 then this would imply that all nodes would need to be connected to the cluster before service could begin.  If this is true, then this would also imply that it would not be possible to add a third or fourth node to the cluster at a later date in time.  Is this correct?
TedgClAuthor Commented:
Why can't you put it in clustered mode as a stand alone service and add nodes at a later date?
TedgClAuthor Commented:
The Old server was purchased a few months back so the new server will be the same specs as the old.  So Jogos I could concievably do the following then, correct?

1) Take Old SQL 2005 server offline
2) Backup Old SQL 2005 server
3) Take Old SQL 2005 server off of the domain thus freeing up the machine name and instance name
4) Bring the new SQL 2008 server online and somehow (don't know how to do this) give the cluster name the same name as the old machine name in active directory
5) Make sure the cluster instance is the same
6) Restore the old SQL 2005 databases onto the SQL 2008 server
7) Bring sql 2008 online
8) If things go very wrong, take cluster off of active directory and put the old sql 2005 machine back online.  If everything appears to be working then continue to step 9
9) Upgrade the old server from SQL 2005 to 2008
10) Add the old sever with SQL 2008 to the SQL 2008 cluster (currently hosted on the new server)
11) Test that the failover cluster actually works

Does this sound correct?
jogosConnect With a Mentor Commented:
I don't see a step test SQL2008-migration in a test/accceptation-environment.

Backup if server is offline? Depends on what you mean with offline. Same with online.

Mind the difference between name before and after.  If I read connection-string '' I see only a server-name without an instance-name (so default instance). Mind that there is a difference between a cluster-name and the name of an instance on the cluster.  Speeking from active-active you automaticly say 2 or more active SqlServer instances spread on the nodes of the cluster.  Pops up the question : 'What's in the second (and other) sqlserver instances?'. (It gives me the feeling that you want to spread databases now on the default instance of server MainDB to different sql instances in a clustered environment so that the heavy databases can be split on different nodes/machines in good times. Not touching connect-string does not fit in that scenario)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.