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
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
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
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
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
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 !!
Premier Field Engineer