TedgCl
asked on
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.
Thanks!
Also, upgrading to SQL 2008 is also an option.
Thanks!
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..
http://www.mssqltips.com/tip.asp?tip=1687
http://www.mssqltips.com/tip.asp?tip=1698
http://www.mssqltips.com/tip.asp?tip=1709
http://www.mssqltips.com/tip.asp?tip=1721
And this step by step guide would help you out in creating SQL Server 2008 clustering..
http://www.mssqltips.com/tip.asp?tip=1687
http://www.mssqltips.com/tip.asp?tip=1698
http://www.mssqltips.com/tip.asp?tip=1709
http://www.mssqltips.com/tip.asp?tip=1721
ASKER
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?
Thanks!
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?
Thanks!
>> 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.
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.
ASKER
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 MainDB.company.com so the new cluster needs to respond at MainDB.company.com. How can I best get from the current state to the future state with minimal production downtime?
Please see figure.
Win2008.jpg
Please see figure.
Win2008.jpg
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.
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.
ASKER
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?
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?
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..
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..
ASKER
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?
ASKER
Why can't you put it in clustered mode as a stand alone service and add nodes at a later date?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 .