Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Need a little clarification regarding Sql Server CLUSTERING

    A and B are 2 Physical Servers having both Sql Server 2005 instances clustered(active/active).
    Can I have the same database, say db_cluster , available on both nodes (A and B) of cluster??such that oltp run on B and reports run on A on the same database.???i.e. db_cluster would be read-only on A.
    Is it feasible or rather achievable through clustering???

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've seen some atrocious kludges that approximate active/active clustering with SQL 2005, but I don't know of any way to do this that Microsoft will support.

    I think you'll need to look at another technology than clustering. I'd recommend replication.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can certainly do it through a linked server. In fact, even if A fails over to B, - it will still work. And MS will support that too.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does the linked server suggestion somehow relate to an active/active SQL Server cluster? I don't understand what you are driving at with this.

    Windows 2003 will support an active/active cluster for Windows Services. As far as I know, SQL 2005 will only support an active/passive (or failover) cluster. There are third parties that do have active/active cluster kludges for SQL 2005, but there are none that I'd use.

    Did I miss something and Microsoft will support an active/active SQL Cluster, or will a linked server allow the OP (original poster) to work around this limitation?

    -PatP

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    This seems like a two part question:
    1. Can you run two SQL Server instances on a 2 node cluster? Yes

    Microsoft will support you running a two node cluster and having two instances of SQL Server installed. What you do to make it "active\active" is set the "preferred owner" setting for the group in cluadmin to the two different nodes. So for SQL instance 1 set preferred owner to node A, and SQL instance 2 set preferred owner to node B. Now you are running an active\active cluster.

    2. Can you setup some sort of replication like log shipping between the two instances? Yes
    Each instance will have its own IP address and "virtual server" name, so it doesn't matter on which node it is running on. Just like how the application doesn’t know\care which node SQL is running on, the same holds true for the replication.

    Please note that there is a lot more to take into consideration before implementing this solution, I am only stating that it is possible.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    Does the linked server suggestion somehow relate to an active/active SQL Server cluster? I don't understand what you are driving at with this.

    Windows 2003 will support an active/active cluster for Windows Services. As far as I know, SQL 2005 will only support an active/passive (or failover) cluster. There are third parties that do have active/active cluster kludges for SQL 2005, but there are none that I'd use.

    Did I miss something and Microsoft will support an active/active SQL Cluster, or will a linked server allow the OP (original poster) to work around this limitation?

    -PatP
    OK, I tried to do it privately, god be the witness

    First, here's a link that from the very beginning clarifies what clustering configurations can be created. It is pretty thorough and leaves no questions unanswered (including the ones that Pat posed):
    http://searchsqlserver.techtarget.co...232340,00.html#

    The following short but very concise answer from SQL Development team further clears out all concerns about what can and cannot be done in terms of A/A and A/P failover configurations:
    http://www.sqlmag.com/Article/Articl...ver_44938.html

    Now back to the original question (my interpretation): can I have 1 database to be READ_ONLY from instance A, and READ_WRITE from instance B? This technology exists only for READ_ONLY purposes. 1 database can be accessed for READs from more than 1 instance, if all instances share I/O subsystem, like in multi-instance failover clustering case. However, this requirement can be simulated by having instance B hosting the OLTP database, while instance A has a linked server to instance B with READ_ONLY permissions. This way no matter which way the failover occurs, - it will continue working because of the nature of linked server.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Reghardt
    ...Please note that there is a lot more to take into consideration before implementing this solution, I am only stating that it is possible.
    Thanks, Reghardt!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please point me to anything that indicates a way to have a single instance of SQL Server simultaneously active on multiple cluster nodes (a true active/active cluster) that Microsoft will support? I'd love to see a way to make that happen.

    What I think you have described is two active/passive instances of SQL Server on the same cluster. That is a mind game, it is nothing like real active/active clustering where you can combine multiple machines into a single logical machine in order to accomplish a job that no one machine could do by itself.

    I'm not sure if you are being a troll, if you've simply drunk far too much marketing cool-aid, or if the problem is something else. I'm ready to go on vacation, so I'm not willing to pursue you too hard on this issue, but I'm also not going to let you try to confuse people with what strikes me as pure smoke and mirrors.

    If you can find me a truely supportable active/active clustering solution for Micorosft SQL, so that one instance of SQL Server is simultaneously active on multiple machines, I'm willing to pay attention. There are third parties working on this, but I don't know of any that Microsoft will support and certainly none that I'd put into production.

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I see you have no problem demonstrating your incompetence in public...OK, I'm done here
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I didn't think you could support your assertion. Thanks for being done here.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Flaming? OK! Coming up...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...according to you, the keeper of your 401K, as well as technology market in US, as well as largest European financial market are running on "smoke and mirrors"...And you call it "an assertion"? I suppose your "true active/active" alternative is Oracle Grid? Now, if it was working as advertized, - I'd call it a successful implementation of HA. But it's still under heavy R&D from what I heard. 3rd-party products??? Name me one that you looked at. Pretty please...You would have by now, if you knew, so I don't expect a coherent answer here. But let's say there is one, then why do you insist on calling it "active/active"? Shouldn't it be just active? And at the same time what you are describing is not a high availability configuration. What you are describing is a basic primitive definition of a clustered super-computer in lame terms, that was done in mid-90's by combined horse power of linux boxes. Are you confusing definitions along with everything else you've mixed up so far?

    I might have been forced to take a sip of MS cool-aid (I am not there, so the taste is long gone), but I am not arrogant either. First, you need to make a clear distinction for yourself, - what's high avalability? As opposed to what you just described? And only then condescend down to insults and names calling. Otherwise, - you're just too dangerous with SQL for yourself
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2007
    Posts
    2
    Thanks Guys for the discussion.
    So my final say goes like tht.....
    ServerA and ServerB are clustered, 2 active nodes.
    Set replication from A to B.....
    Oltp to A and Olap to B.
    I hope it seems preety realistic?????
    Or let me know if you have some other set up

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •