October 13, 2010 at 5:23 pm
I have a question about the capabilities of SQL Server 2008 R2 clustering and the feasibility of a project that I am considering. I would like to setup a multi-site, geographically dispersed cluster based on Windows Server 2008 R2. I want to install SQL Server 2008 R2 into that cluster, and set it up in a multi-instance configuration. I am trying to provide geographic load balancing and disaster recovery for an application.
I am planning on doing a four node cluster, with two nodes at each site. Each site will have two load balanced web servers that are communicating with the database.
What I am trying to accomplish is to reduce application latency by putting the databases and application files as close to the users as possible. I want to attach one set of LUNs to Site A and one set of LUNs to Site B. Through the application I can control where the database files are created (A or B). I will use SAN replication software to mirror the LUNs at the opposite sites for failover / DR purposes.
I am not clear on how to handle it from an SQL perspective. The application can only attach to a single instance of SQL server. With a multi-instance cluster, can two active nodes both present the same instance of SQL server?
I am hoping that if both nodes can present the same instance, and I can control database file creation through the application, then I can setup resource groups and associate them with the corresponding physical hardware at the site that they are located at.
I would expect it to work like this: As far as the application is concerned, it is connected to the SQL instance. The SQL instance is actually two separate pieces of hardware, each attached to separate SAN LUNs. The web servers will be configured to connect to the database server at the site that they are located at. In the event of a hardware failure, the resource groups will first fail over to the redundant hardware at the same site. If the site itself goes down, a networking device lower down on the OSI model will take care of routing users to the fail over site.
Does what I have described fall in the realm of what SQL / Windows Server 2008 can handle?
October 13, 2010 at 9:23 pm
Clustering <> load balancing. A SQL Instance installed on a cluster can only be active on ONE node of the cluster at a time. This geographically disbursed cluster is designed to automatically fail over to your DR site... not for load balancing.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 14, 2010 at 12:27 pm
Wayne,
What should I be looking into for load balancing purposes? I've been trying to do some research about distributed servers but have not come up with much.
October 14, 2010 at 1:25 pm
Have you checked out this link? It might provide some help to you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 14, 2010 at 3:10 pm
Thanks for the reply. Did you find something through Google that I haven't? Everything there seems to say that you can't really load balance SQL server. You can spread application servers around the globe, but the database remains centralized. Even though you can span sites with a cluster, you can't have separate copies of the database that stay in sync with each other. Or can you?
October 14, 2010 at 7:20 pm
While technically not load balancing, here is an article on load distribution. I think that this is about as close as what you're likely to achieve with the current product.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 18, 2010 at 6:10 pm
Thanks for the informative reply. I've come to the conclusion that there is nothing that I can do as a sysadmin to work around the limitations of the application. The vendor needs to actively architect their system to support multiple sites. It seems like transactional replication would get the job done.
October 20, 2010 at 2:13 pm
david.armstrong 13072 (10/18/2010)
It seems like transactional replication would get the job done.
providing all the replication objects have a primary key defined!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2010 at 2:34 pm
Perry Whittle (10/20/2010)
david.armstrong 13072 (10/18/2010)
It seems like transactional replication would get the job done.providing all the replication objects have a primary key defined!
Yes, and if the system is architected so that the multiple sites do not have collisions on primary keys. It's not a trivial task to design an application for multiple production databases replicated to each other.
Before you go down that path, realize that you are probably vastly increasing the cost and complexity of the system, and decide if it is really worth the cost.
October 20, 2010 at 3:06 pm
Michael Valentine Jones (10/20/2010)
realise that you are probably vastly increasing the cost and complexity of the system, and decide if it is really worth the cost.
It's surprising how many organisations blindly follow the replication or clustering route and forget about the ongoing support assuming it can be supported by 1st\2nd line helpdesk support.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply