locked
CNAMES and SQL Clustering on Windows 2008 RRS feed

  • Question

  • Hi All ,

    I am planning an Active-Active SQL cluster (i.e. named instances cluster) and for DR I have a standalone server at the DR site. We are using NetApp SAN replication for replicating the disks.

    At the DR site, I will use the same Instance name and port number as its in production. And the event of a failover, I want to use CNAME to be used and changed to the DR server, so that all the traffice is diverted to DR site. and Vice Versa in case of FAILBACK.

    I have heard that there is an issue with using CNAMEs with Windows 2008 clustering environment. Just wanted to verify the same, before I start working on this design.

     

     

    Tuesday, February 15, 2011 9:43 AM

Answers

  • If only user databases is replicated, and login is sync'ed with periodically, then you shouldn't worry too much. As your failover will be quite lot human interaction.

    Since you are using CNAME, that you can have different instance name/virtual name in PROD and DR. But you have to make sure all users to use CNAME not the virtual names.

    Single node cluster is a very common design in 2+1 setup (active+passive in prod, 1 in DR), at this case, depends on whether the SAN replication used,

    I've seen places replication OS system disks, which is not good I'd think.

    I've seen replicating all databases including master/msdb etc, then the SQL Cluster Group will have to be offline and have to be same as PROD, in this case,there is no need to sync logins as master is replicated.

    I've also seen the user databases replication, then PROD and DR will be up+up . so you can run jobs to sync logins. and failover has to be manually invoked. And all you need is just attach databases and fix logins.

     

    There are many solutions, just choose the best way for your current environment.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    • Marked as answer by deepakuniyal Tuesday, February 15, 2011 3:48 PM
    Tuesday, February 15, 2011 12:48 PM
  • Hi Deepak, thats a hard question to quantify in terms of "simplest and least complicated" since depending upon who you ask the answer might be different.

    But let me just answer like this...

    As far as I am aware I am/ was the first person to come up with this idea of Group Policy pushing SQL Client Alias entries as an enterprise solution and it works REALLY well with less hidden problems than the other techniques (primarily due to the Kerberos aspect). I HAVE however seen a real push in the direction of A or CNAME records for redirection in the community (perhaps because its the most obvious) so if you can be sure of your Kerb setup correctly then there is every reason for you to consider that as an option. However let me stress that the biggest reason I came up with the solution is because it easily allows me to not just redirect a Server name but also can direct an Instance to another eg. myserver1 sqlclient alias can be pointed to realserver (default instance), realserver1\sql1 OR even realserver2\anotherinstancename for example. If you look at the section in my vid you will see how easy it is to do my technique, but what is right for you really depends on you I guess.

    Lots of ways to skin a cat as they say :)


    Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk
    • Marked as answer by deepakuniyal Tuesday, February 15, 2011 3:47 PM
    Tuesday, February 15, 2011 2:01 PM

All replies

  • This is actually quite a complex subject you are raising.

    I think the problems you refer to relate to Kerberos and although this complicates your solution it is by no means a show stopper - just something you need to plan for. In fact I have recently heard of several people using CNAMEs for their redirection solution.  I'll provide a few links below which might help you but I'll highlight again that your Kerb setup on all servers must be configured correctly.

    The redirection solution I try to push is using Group Policy deployment of SQLClient Aliases, you do not get the same Kerb issues to worry about. I did a presentation in part covering that item here http://sqlbits.com/Sessions/Event7/Thinking_outside_the_Box_Learning_a_little_about_a_lot probably around 20 mins in or so.

    Anyway here are those other urls if you want to go the DNS route.

    http://sqlblog.com/blogs/linchi_shea/archive/2009/12/28/bad-database-practices-allowing-apps-to-connect-to-the-hostname.aspx

    http://toddmcdermid.blogspot.com/2009/09/using-cnames-for-flexibility.html

    http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx

    http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/dbad8904-eda8-45d1-9a49-03327726e314


    Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk
    Tuesday, February 15, 2011 11:07 AM
  • Couple of things:

    1. You are using SAN replication, what's involved in the replication? system databases + user database or just user databases?

    if only user databases, the standalone is fine.

    if not, you need create single node cluster and using same cluster name for your dr instance

    2. CNAME, where would you put it point to? the Network Name for MSSQL or else?

    Are you planning to allow failover to make the CName change at the same time or it will be a manually process?

    These all depends on your current infrastructure.

    above all, it is a possible solution and I've seen this in many places.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Tuesday, February 15, 2011 11:44 AM
  • Thanks Marc. I was worried about Kerberos creating a problem. Thanks for the links as well. Would like to know what is the simplest and least complicated way SQL Aliasing through Group Policy or Using CNames ?
    Tuesday, February 15, 2011 12:34 PM
  • Hi SevenKnights,

     

    Just to answer your questions :

    1. I would like to replicate just the User databases and run a login creation script periodically on the DR server.

    2. CName will point to the Virtual Name of SQL Cluster.

    3. CNAME change will be a manual process.

     

    Additionally, I would like to know about a Single Node cluster at the DR, would'nt it conflict with the exisiting Production Cluster Virtual Name ? and how would I keep a standalone server having two Virtual Names , as there are two named instances with two virtual names.

     

    Regarsd,

    Dee

     

     

     

     

    Tuesday, February 15, 2011 12:38 PM
  • If only user databases is replicated, and login is sync'ed with periodically, then you shouldn't worry too much. As your failover will be quite lot human interaction.

    Since you are using CNAME, that you can have different instance name/virtual name in PROD and DR. But you have to make sure all users to use CNAME not the virtual names.

    Single node cluster is a very common design in 2+1 setup (active+passive in prod, 1 in DR), at this case, depends on whether the SAN replication used,

    I've seen places replication OS system disks, which is not good I'd think.

    I've seen replicating all databases including master/msdb etc, then the SQL Cluster Group will have to be offline and have to be same as PROD, in this case,there is no need to sync logins as master is replicated.

    I've also seen the user databases replication, then PROD and DR will be up+up . so you can run jobs to sync logins. and failover has to be manually invoked. And all you need is just attach databases and fix logins.

     

    There are many solutions, just choose the best way for your current environment.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    • Marked as answer by deepakuniyal Tuesday, February 15, 2011 3:48 PM
    Tuesday, February 15, 2011 12:48 PM
  • Hi Deepak, thats a hard question to quantify in terms of "simplest and least complicated" since depending upon who you ask the answer might be different.

    But let me just answer like this...

    As far as I am aware I am/ was the first person to come up with this idea of Group Policy pushing SQL Client Alias entries as an enterprise solution and it works REALLY well with less hidden problems than the other techniques (primarily due to the Kerberos aspect). I HAVE however seen a real push in the direction of A or CNAME records for redirection in the community (perhaps because its the most obvious) so if you can be sure of your Kerb setup correctly then there is every reason for you to consider that as an option. However let me stress that the biggest reason I came up with the solution is because it easily allows me to not just redirect a Server name but also can direct an Instance to another eg. myserver1 sqlclient alias can be pointed to realserver (default instance), realserver1\sql1 OR even realserver2\anotherinstancename for example. If you look at the section in my vid you will see how easy it is to do my technique, but what is right for you really depends on you I guess.

    Lots of ways to skin a cat as they say :)


    Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk
    • Marked as answer by deepakuniyal Tuesday, February 15, 2011 3:47 PM
    Tuesday, February 15, 2011 2:01 PM
  • Many thanks Marc and SevenKnights.
    Tuesday, February 15, 2011 3:52 PM