locked
mssql2008 in a Windows Cluster environment and log shipping ? RRS feed

  • Question

  • Hello to all, 

    This is my first post. I'm an Oracle DBA and this question just landed on my desk from my manager.

    Is it possible to install mssql2008 in a Windows Cluster environment and implement log shipping at a secondary clustered site ?

    Im thinking, WHY ?   Am I correct in saying MSSQL2008 clustered solution and log shipping are two separate high availability solutions by themselves ? 

    Or can the two coexist ?

    The following site doesnt mention it at all

    http://msdn.microsoft.com/en-us/library/bb500117.aspx

    thank you in advance for your help.

    Regards

    Paul


    Monday, July 19, 2010 8:57 PM

Answers

  • No, you don't need to implement a cluster on the secondary site for Log shipping deployment, it can goes to a stand-alone sql instance also as a stand-alone secondary server.

    The recovery time objective is very important; as mentioned earlier, your HA-DR implementation will depend on that. Log shipping has some draw back, like in case of your primary database is down, you need to manually promote the secondary server as the primary server. In that senario, Database Mirroring can be more useful. Check the following post for a detailed discussion on Database Mirroring vs Log Shipping : http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/c55e1bbd-5b6f-4961-8e69-3de9ef4fb75f/#1bb9926e-1b39-47ad-9fe9-351d0fbaaf9f

    If you will install a cluster on your secondary site, I recommend that you use Database Mirroring along with the cluster as your HA-DR solution.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    • Proposed as answer by Tom Li - MSFT Friday, July 23, 2010 2:53 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 8, 2010 7:34 AM
    Wednesday, July 21, 2010 10:05 AM

All replies

  • It is possible to have a SQL Server 2008 running on a failover cluster and configure the databases to do either log shipping or database mirroring on another off-site server that can be either clustered or not. Clustering is a high availability solution, much like Oracle RAC. Log shipping in SQL Server is similar to the concept of redo logs being restored on a remote Oracle database concept whereas database mirroring is similar to Data Guard's physical standby concept (hopefully, this helps make it clear)

    bass_player http://bassplayerdoc.blogspot.com/
    • Proposed as answer by Tom Li - MSFT Friday, July 23, 2010 2:49 AM
    Monday, July 19, 2010 10:17 PM
  • Paul

    >Am I correct in saying MSSQL2008 clustered solution and log shipping are >two separate high availability solutions by themselves ? 

    Yes , that is correct

    You can implement log shipping to secondary clustered site....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Friday, July 23, 2010 2:49 AM
    Tuesday, July 20, 2010 5:37 AM
  • Hey guys thank you for the answers.

    Does it even make sense to do this ?

    I've been researching how to do it but there doesnt seem much documentation out there for the two options combined.

    I know MSSQL cluster option is expensive so is it possible to install MSSQL 2008 within a Windows cluster environment without the SQL Cluster option and then implement log shipping to the secondary site ?

    If anyone has any links they would like to share, I would be very grateful

    thank you

    Paul

     

    Tuesday, July 20, 2010 8:58 AM
  • If you feel like implementing a MSSQL Cluster is expensive, then forget about even installing a Windows 2008 Cluster. That in itself requires Enterprise Edition whereas SQL Server only require Standard Edition for a 2-node Failover Cluster. The cheapest way is to check how large your database will be. If it will be less than 10GB, go for SQL Server 2008 R2 Express (the maximum database size it supports is 10GB) and implement some sort of log shipping on another server on a secondary site. You can even run SQL Server 2008 R2 Express Edition on a workstation OS like Windows 7
     
    The way to implement a high availability/disaster recovery solution is to answer the question, "What's your recovery point objective/recovery time objective?" Only after you managed to have an answer for this question do you think about the technologies such as clustering, log shipping, mirroring, etc.

    bass_player http://bassplayerdoc.blogspot.com/
    Wednesday, July 21, 2010 3:41 AM
  • I'm afraid I dont have an input into the objectives of the recovery.
     
    The architect wants "log shipping" setup up on the secondary site.
    The primary site is MSSQL clustered, so my confusion is
    "Do I then also need to setup MSSQL cluster on secondary or can I install stand-alone on the 2 nodes of the secondary and then implement log shipping"  ?
    Wednesday, July 21, 2010 9:47 AM
  • Uri,

    Normally SQL Cluster and mirroring are considered high-availability solutions, while log shipping is more considered as a DR solution.

    Tom


    Tom Van Zele | Blog | Twitter
    • Proposed as answer by Tom Li - MSFT Friday, July 23, 2010 2:52 AM
    Wednesday, July 21, 2010 9:56 AM
  • No, you don't need to implement a cluster on the secondary site for Log shipping deployment, it can goes to a stand-alone sql instance also as a stand-alone secondary server.

    The recovery time objective is very important; as mentioned earlier, your HA-DR implementation will depend on that. Log shipping has some draw back, like in case of your primary database is down, you need to manually promote the secondary server as the primary server. In that senario, Database Mirroring can be more useful. Check the following post for a detailed discussion on Database Mirroring vs Log Shipping : http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/c55e1bbd-5b6f-4961-8e69-3de9ef4fb75f/#1bb9926e-1b39-47ad-9fe9-351d0fbaaf9f

    If you will install a cluster on your secondary site, I recommend that you use Database Mirroring along with the cluster as your HA-DR solution.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    • Proposed as answer by Tom Li - MSFT Friday, July 23, 2010 2:53 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 8, 2010 7:34 AM
    Wednesday, July 21, 2010 10:05 AM
  • Thanks Tom,

    But my question is

    If the primary is SQL clustered, whats best practise for the secondary ?  sql cluster also ? or leave standalone on the 2 nodes ?

     

     

    Wednesday, July 21, 2010 10:07 AM
  • I agree with Sudeepta, your secondary site doesn't need to be clustered, it depends on your RPO/RTO.

    Tom


    Tom Van Zele | Blog | Twitter
    Wednesday, July 21, 2010 10:14 AM
  • Ok, I am/was confused that you say "secondary server is actvie-active"

    Of cause  you do not have to install SQL Server Clustering for log shipping just go throu the regular installation...

    But what if tomorrow your boss tells  you to implement clustering on the seconday, please verify all details


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 21, 2010 10:31 AM
  • Ok, I am/was confused that you say "secondary server is actvie-active"

    Of cause  you do not have to install SQL Server Clustering for log shipping just go throu the regular installation...

    But what if tomorrow your boss tells  you to implement clustering on the seconday, please verify all details


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 21, 2010 10:32 AM
  • The secondary site is new.

    At the moment we just have 2 servers there. They are not yet Windows Clustered and the desicion has not yet been made whether to cluster them or not. 

    Based on their RPO/RTO, they want log shipping.

    Now the desicion is, do we

    1.  Install MSSQL2008 twice, once at each node on the secondary and log ship Primary to here.

    2. Assuming they cluster the servers at Windows level, Install MSSQL2008 with cluster option on secondary and log ship primary.

     

     

    Wednesday, July 21, 2010 11:33 AM
  • As everybody else is saying, no need to cluster the servers on the secondary site. Use one of the two servers and install SQL Server 2008 as a stand-alone server (I'm assuming that it is also a member server of your Active Directory domain).
     
    If they decide to cluster the servers (again, unless you define your RPO/RTO, you are just wasting resources and licenses here), install SQL Server 2008 Failover Cluster and configure log shipping from the primary server to point to this secondary server. As far as log shipping is concerned, clustering doesn't really matter as log shipping is on a "per database level" implementation, not per server/instance

    bass_player http://bassplayerdoc.blogspot.com/
    Wednesday, July 21, 2010 8:53 PM
  • Hello to all.

    So they finally decided/realised that as they have no Business continuity or RPO/RTO defined that the secondary site should not be clustered.

    There are 3 prod servers with 4 instances and about 50 databases on each one.

    I am going to Install MSSQL 2008 stand-alone of each of the two Secondary servers and balance out the instances/databases. 

    I will then setup log shipping from the clustered Primary to the Non clustered secondary.

    They decided that for now the purpose of the secondary should be to have a copy of all databases with the log shipping  implemented i.e DR site.

    Once they define their Business Continuity, maybe next year they will want HA at the Secondary site.

    If they want HA next year, is it possible to convert the stand-alone secondary site to have MSSQL clusters on windows clusters or will the secondary site ?

    thank you once again for all your help.

    Paul

    Thursday, July 22, 2010 9:25 AM
  • Paul

    >If they want HA next year, is it possible to convert the stand->alone secondary site to have MSSQL clusters on windows clusters >or will the secondary site ?

    Be proactive and convince them to install clustering just now :-))))

    You will have to install SQL Clustering and add all nodes .....

    Please search on internet how to add a new node the cluster...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 22, 2010 9:31 AM
  • Hi Paul,

    Converting a stand-alone instance to a clustered instance is not possible.

    You will have to build a Windows Failover Cluster first and install one or more SQL Clustered instances on it.

    Tom


    Tom Van Zele | Blog | Twitter
    Thursday, July 22, 2010 9:33 AM
  • >> Be proactive and convince them to install clustering just now :-))))

    Not possible :)  they dont want to pay for a "Windows person" to setup windows clustering
    and due to strict "segregation of duties" in here, Im not allowed to do it :)

    thanks for all the advice guys !!

     

     

     

    Thursday, July 22, 2010 11:28 AM