Considerations for Configuring Log Shipping for a cross-domain clustered instance of SQL Server over a Firewall

Last week, one of my customers had engaged me for consulting to configure Log Shipping on clustered instance of SQL Servers which are running in different domains. In other words, the primary and secondary servers are in different domains.

While Log Shipping is pretty easy to configure but configuring log-shipping for a cross-domain clustered instance of SQL server over a Firewall can be trickier which requires some consideration so I plan to discuss these consideration in this blog post for the benefit of larger audience

First Let us discuss Clustering and Log Shipping as HADR Solution

Clustering and Log Shipping
=======================

Until SQL 2012 AlwaysON, we didn’t have a good HADR solution with SQL Server. Clustering serves as a very good High Availability Solution due to minimum failover time as well as SQL Network Name allowing the Application a transparent failover.  However due to the shared storage, Clustering cannot be used as a Disaster recovery solution. While Log Shipping/Database Mirroring serves as a good DR Solution but may not be a good High Availability solution since RPO and RTO depends on the frequency of tlog backup, Network latency.

So Clustering along with Database Mirroring or Log Shipping together serves as a Good HADR Solution where we can take the best of both the worlds

Let us now discuss various considerations in Log Shipping scenario

Considerations for Log Shipping for a Clustered instance
=============================================

For a clustered instance, there is one important consideration. The network share for the Log Shipping should be created as a Fileshare resource on the Cluster.

Further, the fileshare resource should be created in the SQL Group while the shared folder should be created on the shared drive. The dependency of the Fileshare resource should be on Shared Drive in the SQL Group and SQL Network Name.

The reason for creating Fileshare in the SQL Group of the Cluster is so that when the instance is failed over to Node 2, the Log shipping continues to run transparently without any impact. Further the UNC path for the share would be \\<SQL Network Name>\<Folder Name>. This will ensure the copy job will always be able to access the network share on shared drive.

How to create file shares on a cluster
http://support.microsoft.com/kb/224967

 

Considerations for Log Shipping on a cross domain servers
================================================

While it is possible to establish Log Shipping on a cross domain servers, the only requirement is the two servers should have network connectivity to each other and should be able to access each other.

However the major challenge while establishing Log Shipping on a cross domain servers is permission on the network share which should be accessible to copy job running as under SQL Agent account on the secondary server.

For the SQL Agent account which will be a domain account, to be authenticated by the Primary server, there should be two way trust between the domains.

However if you are not able to establish the two way trust between the domains due to some reason, we can still configure Log Shipping by using Network PassThrough Authentication.

With Network PassThrough Authentication, the SQL Agent account for the servers should have same name and same password.

More information on Network Passthrough Authentication
http://support.microsoft.com/kb/102716

 

Considerations for Log Shipping over a Firewall
=======================================

While Log shipping uses SMB communication to copy the log files from the Network Share to the local destination folder and hence in order to configure over Firewall, following ports should be open

If,

Netbios over TCP/IP is enabled

TCP ports 137,139
UDP ports 137,138

Direct Hosting of SMB over TCP/IP

TCP port 445

More information on port requirement for Windows Server
http://support.microsoft.com/kb/832017

Further, with Cluster, the above ports should be open in Firewall on Virtual IP address for inbound rules while for outbound rules, it should be open on Physical IP address of the Nodes.

More information for Firewall configuration on Cluster
https://msmvps.com/blogs/clustering/archive/2005/08/11/62372.aspx

While in my case, we had to take all the above consideration to ensure we are a able to setup Log shipping for a clustered instance of SQL Server across cross domain server over a Firewall.

Hope this Helps !!

Parikshit Savjani
Premier Field Engineer

1 comment

  1. Thanks Parikshit..We have a similar setup and we were facing problems because of improper configuration of fileshare .I think i need to check again 🙂

Leave a comment

Your email address will not be published. Required fields are marked *