SQL Clustering Driving Me Nuts
-
This is my first shot at a SQL Cluster. I'm not interested in DB mirroring and whatnot...just running SQL on two nice and beefy Clustered Windows 2003 R2 SP2 servers.
Parameters:
- In terms of this server, we cut the DB's into 2 categories. Customer Stuff and Internal Stuff.
- We don't want one [physical] server sitting around and doing nothing during non-failover...active-active. Either machine can handle the full load, but will normally be doing about half.
- Customer Stuff is not equal to Internal Stuff either in required resources or importance
My idea was simply this:
1) Cluster the servers. Done.
2) Four instances. Customer01, Customer02, Internal01, Internal02.
3) Run x01 instances on Box1 (under normal conditions)
4) Run x02 instances on Box2 (under normal conditions)
Boom. Easy.
Here's the problem:
*I HATE INSTANCE NAMES*. I cannot tell you what a pain in the *** they turn out to be when you have to deal with a few hundred different developers and all manner of connection strings. I need to be able to move a DB from one spot to another, update a DNS alias, wait for DNS to replicate, and move along. Period. Since you cannot assign a DNS alias to anything but a host name (w/o an instance name), this is a problem.
I initially figured if I was installed each SQL Instance under a different (which is required) "Virtual Server Name" (within a matching Cluster Group) and each has its own TCP/IP address assigned to it, you might get away with using "Default" (blank) instance names for all four - the separate virtual servers would be enough to keep them from trampling on each other. Wrong. Only one "default" instance can exist on an entire cluster.
Then I figured, I'd install each one to its own Virtual Server Name, give it its own IP, and assign instance names like "CUST01", "CUST02", "INT01", "INT02". From there, I could connect to the SQL instance via just the Virtual Server Name (or its IP). Wrong. Now, it is requiring instance names to be passed with the virtual server name.
I have had a hard time finding documentation on how one can utilize SQL Instances without having to pass instance names from the client. I imagine it surrounds using distinct IP's (which I am because each "Virtual Server Name" has its own IP) and/or ports(?).
Whatever it takes on the back-end, I don't care. I want to be able to pass my developers a simple DNS Alias through which they can connect to a given SQL DB instance and, subsequently, to a database thereon.
I do appreciate your help. Thank you.
-
Does anyone know how I can utilize Named SQL Server Instances without having to use Instance Names in the connection strings? Is there a way to make the instances respond to distinct IP Addresses?
-
Have I placed this question within the wrong forum?
-
I don't think anyone's tried this or knows, but I'll ask a few people.
From my understanding, you can't run two clusters as you're trying. If you have two machines, you'd set up two instances, and could make them both active, but you don't get 4 real instances to get two virtuals, you set 2 real instances with 2 virtuals. Then let them failover if needed. Why do you need to separate out the two applications if there's a failover? If the machines can handle it, then you'll be ok. Plus it's a situation and people should understand that there might be conflicts.
This is one of those places where I think the extra complexity you're trying for will end up causing you more problems over time than it's worth.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Have you tried creating alias(s) on your local machine in the configuration tool? i.e.
Tommy
Follow @sqlscribe -
Why the requirement to to make connection property changes so easily? Once the instance is virtualized, the connection string doesn't change.
If your virtual server name is VirtServ1, then the connection string for the Customer01 instance is VirtServ1\Customer01 regardless of the physical server in the cluster on which it is running.
-Eddie
Eddie Wuerch
MCM: SQL -
I'm afraid you won't be able to do this and remain supported by MS. There's only one primary default instance per cluster and then everything else falls under instance names. The only way to simplify this is roll out SQL Server aliases using some type of SMS software. I agree though a bit with the last poster, once you create the connection string once, the end user won't have to worry about them. SQL just fails over and it retains the same name from node to node. If you do more than that, you're adding additional complexities and risk levels. For example, if you're app is dependent on a DNS entry or an alias, you may forget it one day and the app goes down to a given group. Just my .02 but I don't mind aliases though too :).
Brian Knight
Free SQL Server Training Webinars -
Thank you for the replies.
Our reasoning is to create different instances on the robust clustered SQL that is in line with what we already have.
For example, I have dozens of production SQL boxes now, all running under the "default" instances. Moving a DB from one to the other is just a matter of changing a DNS alias.
I suppose I was hoping that the SQL named instances would function, sort of, like web servers...each assigned its own IP address and able to be reached that way.
It was my hope that the cluster would allow for the creation of different instances for different needs. We have some internal (read: low priority) DB's that I want separated out so their processing is capped. I have some reporting DB's that I'd like separated out so that if they "crush" them, the transactional PROD DB's are not affected. In the spirit of going "active/active" and not wasting 1/2 the hardware, each would be split arbitrarily between the two boxes.
Without requiring instance names to be contained within connection strings, one could move a DB from one bucket (instance) to another down the road with minimal effort and no client-side reconfiguration...DNS aliases have been very kind to us heretofore.
In the end, yes, it looks as if I will have to resort to client-side aliases to be created which can at least be centrally managed via SMS. Seems a step backward from what we currently have, though...I always considered those to be an additional (and unnecessary) level wherein connectivity can be interrupted.
Again, thank you for your replies. I'll maintain the simplistic view, though, that in the future it will be nice to have SQL instances treated like web sites currently are - each assigned an IP and able to be moved anywhere with the IP and/or DNS alias the only change required.
Just for kicks, though, I'm sitting on a call to MS. I suspect I'll gain nothing from it, though. I'll let you know if it turns out to be otherwise. 🙂
-
To me, it sounds like you are trying to achieve a Load Balancing solution, by using a High Availability solution. Remember, the sole purpose of a Microsoft Cluster is to provide high availability with automatic (and to the user, invisible) failover.
With a High Availability Cluster, you will always have 50% of your hardware sitting there doing nothing. Whether you split the load so that Cluster1 is doing 50% and Cluster2 is doing 50%, or configure it so that Cluster1 is doing 100% and Cluster2 is sitting around doing nothing, is largely irrelevant.
In your scenario, where you have "low priority" databases, why go to the expense and hassle of clustering them in the first place?
Microsoft Cluster Service was never designed to make it simpler for you to move databases from one instance to another. Personally, I would much rather change a DSN, rather than a DNS, when I want to move a database. I feel that a DSN is much more transparent and easier to manage. [Note, however, I am talking about client-server applications, where only the server has a DSN to the database. If you have a distributed application, where each client requires a DSN, then that is awkward.]
Andy
-
You can only have one default instance of SQL Server in a cluster. That is because you will have conflicting names if they all failover to one server. Use the clustername\virtualname to make the connections.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply