November 19, 2007 at 6:05 pm
Hi,
I am working on a HA solution for a company where I am likely to implement log shipping in Production. It is a fairly green field project and the servers may be clustered - they will be rack mounted blade servers. I wondered what issues might arise with log shipping on a cluster and how you would have the cluster set up. I am interested in solutions of up to 4 nodes.
I assume that you have to have at least 2 active nodes for this to work because you can't log ship to a passive??
Also, if a server fails over will log shipping fail because the primary to secondary server link is broken or could you have Active / Active / Passive and the Passive picks up and log shipping still works??
I just wondered if anyone can shed any light on this or if any can suggest any useful articles for this situation or list pros and cons (I am waiting on a book from Amazon 'Microsoft SQL Server 2000 High Availability') - any help will be greatly appreciated!
Many thanks,
Phil.
November 19, 2007 at 8:04 pm
You are right wrt log shipping to a passive node not being possible. Log shipping needs to be able to restore the transaction log backups. In its simplest form an Active/Passive cluster with only needs to have one SQL Server instance. Hence, the passive node is just running Windows and waiting for a failure to occur. And since there is no SQL Server instance on the passive node, you will not be able to configure log shipping.
If a server fails, the service will be started on another node in the cluster and log shipping should resume from where it left off. Conceptually, all that is happening is SQL Server (or windows) crashes and is restarted - it just happens that it might be restarted on different hardware but all of the files that make up the databases are the same. So, if you stop / start a normal (non-clustered) SQL Server instance that is part of log shipping, log shipping should simply continue on.
I don't have any particular references for clustering. Start with Books Online and keep looking around. http://www.sql-server-performance.com has a stack of articles about clustering SQL Server which might help you
November 19, 2007 at 8:37 pm
Thanks for validating those thoughts HappyCat. Any thoughts on this scenario?...
Could I have two nodes in the cluster as Active / Active but on the second node have two instances of SQL Server, but only one instance is in the cluster? The second instance would be used to host the log shipping destination db. The server with only one instance would be the primary. I appreciate that this would require 3 licenses but is it possible?
November 19, 2007 at 8:37 pm
Thanks for validating those thoughts HappyCat. Any thoughts on this scenario?...
Could I have two nodes in the cluster as Active / Active but on the second node have two instances of SQL Server, but only one instance is in the cluster? The second instance would be used to host the log shipping destination db. The server with only one instance would be the primary. I appreciate that this would require 3 licenses but is it possible?
November 19, 2007 at 8:37 pm
Thanks for validating those thoughts HappyCat. Any thoughts on this scenario?...
Could I have two nodes in the cluster as Active / Active but on the second node have two instances of SQL Server, but only one instance is in the cluster? The second instance would be used to host the log shipping destination db. The server with only one instance would be the primary. I appreciate that this would require 3 licenses but is it possible?
November 19, 2007 at 9:09 pm
Not sure that you will be able to do that. I don't know whether the Windows Cluster will be detected by the installer and force the issue...anyone else got info on this ?
November 19, 2007 at 9:17 pm
Log shipping is set up to restore log from one SQL server to another SQL server no matter either SQL server is on a stand-alone server or on a virtual server which a cluster case (Active/Active, or Active/Passive).
You may take a look at this to get a look and feel.
How to Perform SQL Server Log Shipping
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
November 19, 2007 at 9:31 pm
happycat59 (11/19/2007)
You are right wrt log shipping to a passive node not being possible. Log shipping needs to be able to restore the transaction log backups. In its simplest form an Active/Passive cluster with only needs to have one SQL Server instance. Hence, the passive node is just running Windows and waiting for a failure to occur. And since there is no SQL Server instance on the passive node, you will not be able to configure log shipping.If a server fails, the service will be started on another node in the cluster and log shipping should resume from where it left off. Conceptually, all that is happening is SQL Server (or windows) crashes and is restarted - it just happens that it might be restarted on different hardware but all of the files that make up the databases are the same. So, if you stop / start a normal (non-clustered) SQL Server instance that is part of log shipping, log shipping should simply continue on.
I don't have any particular references for clustering. Start with Books Online and keep looking around. http://www.sql-server-performance.com has a stack of articles about clustering SQL Server which might help you
ACtually - this is NOT correct according to licensing.... Try this one on for size...
http://www.microsoft.com/sql/howtobuy/activepassive.mspx
As I posted on the OTHER version of this topic... If the ONLY thing the extra server(s) are being used for are to back up the node that is running the "active instance" (meaning - the one with the client connections, trasactions, etc...), then these nodes can be considered PASSIVE nodes, and are NOT subject to additional licensing.
It doesn't matter that SQL server is running as a service - it's whether clients are accessing it. So - if it's only the "backup" and it's not used for anything else - it's a passive node, and is not requirted to be licensed....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2007 at 10:04 pm
You can design your solution following the instructions in Allan Hirt's book Microsoft SQL Server 2000 High Availability; log shipping together with clustering is very much a good and old availability solution.
http://www.amazon.com/Microsoft-Server-2000-High-Availability/dp/0735619204
Good luck
November 21, 2007 at 3:50 am
when setting up log shipping ensure the logshipping directory is a share and make that share a cluster resource. Then both nodes in the cluster can access the directory and logshipping wil work seamlessly following a failover
---------------------------------------------------------------------
November 21, 2007 at 3:22 pm
Thanks for the tips all - I'm starting to get a clearer picture!! One question that springs to mind is - the Monitor server is recommended to be a separate box but what happens if the Monitor server crashes? Does log shipping actually fail? Are people clustering their monitor servers if this is the case - that seems a little over the top to me?
November 22, 2007 at 3:30 am
A cluster for a monitoring server is WELL over the top. If you must have a seperate machine for this you can use any old PC with personal edition on it to save money, a monitor server requires hardly any resources. Most people would not bother and use the primary as the monitor as well.
Apologies in advance if I have misunderstood you but I can see no point in log-shipping between two nodes in an active\active cluster, as the whole instance will fail over anyway. You would cluster and then log ship as well to another geographically distant machine (possiblly stand-alone) to give full DR cover.
HTH
george
---------------------------------------------------------------------
November 22, 2007 at 8:06 am
i just implemented DR/HA with log shipping on sql2000 using a third party tool by Sonasoft. Highly recomended
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply