SQL Server Log Shipping to a Different Domain or Workgroup
By: Dan Quinones | Updated: 2012-01-05 | Comments (31) | Related: 1 | 2 | 3 | 4 | 5 | More > Log Shipping
Sometimes the requirement arises to provide a copy of your database to an off-site location, such as a disaster recovery datacenter. Log Shipping is one of the most popular, proven technologies used to create a copy of your databases on a remote server. The remote server can be in the same domain, a different domain, or no domain at all (workgroup). How do we configure Log Shipping to work with a secondary server that is in a different domain or workgroup than our primary server? Check out this tip to learn more.
Log Shipping is setup and configured between a SQL Server instance running on a primary server and an instance on a secondary server. Typically the secondary server is in a remote datacenter so that in the event of a disaster at the primary datacenter, a copy of the database can be brought online in another location. You can configure multiple secondary servers in multiple locations to receive copies of the database. Other uses for Log Shipping include backing up to a secondary server in the same datacenter as a cheaper alternative for High Availability solutions such as Microsoft Windows Failover Clustering. This solution will cover the configuration of log shipping on SQL Server 2008 to successfully backup to a secondary server in a different domain or workgroup than the primary server.
You should have an understanding on the basics of log shipping and if possible have an environment to test with. There are many detailed articles out there covering the setup and configuration basics. The tip Step By Step SQL Server Log Shipping or the MSDN article Log Shipping Deployment can be followed to setup your initial log shipping environment.
Service Account Configuration - Part One
The key to successfully implementing log shipping across different domains or from a domain member server to a workgroup server lies in the SQL Server Agent service account. As detailed in the Transaction Log Backup Settings dialogue box when configuring the backup jobs, the SQL Server Agent service account running on the secondary server must have read access to the folder that the log backups are located in. We will explain this more in the "Service Account Configuration - Part Two" section later in this tip.
Backup Folder Security Permissions
The first step is to configure permissions on the log backup folder so that the SQL Server Agent account on the secondary instance has access to the logs. The agent on the secondary instance will copy the logs from the folder that was designated as your log backup folder during the Transaction Log Backup Settings configuration.
Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions:
Also be sure to add the same account to the share permissions as well:
You will also need to add the permissions on the log destination folder on the secondary server, as reminded during log shipping setup:
Make sure the secondary instance SQL Agent service account has permissions to the local folder that it will copy the logs to:
Service Account Configuration - Part Two
Now that you have granted proper permissions to the log backups source folder, we need to configure the SQL Server Agent account that is running on the secondary instance. You should also configure the primary server with the service account that was added to the folder permissions above for consistency.
The question becomes, how do we configure a service account for an account in a different domain on a server that is in a completely separate domain or a workgroup? The "trick" here is to create an account in your local domain, or on the local computer in the case of a workgroup, that has the Same Name AND the Same Password as the account we granted permissions to above. If you look closely at the security configurations in the previous step you will see a sqlsvc account in SQL_LAB_DOM1 and a sqlsvc account in SQL_LAB_DOM2. Although these accounts are in two totally separate domains, since the usernames and the passwords match identically, they are able to copy files between the servers. The drawback is that you need to ensure that the passwords stay in sync.
Use the SQL Server Configuration Manager on the secondary instance to configure the SQL Server Agent to use the account that you just created:
Change the SQL Server Agent from using Local System in the "Log on as:" configuration to the service account you are configuring:
You will need to restart SQL Server Agent after changing its log on account:
SQL Server Agent running under a named service account:
VerificationCheck the destination folder on the secondary server, it should still be empty:
Note: Wait until there are log backups created in the primary server log backup folder or run the LSBackup job on the primary server using the same procedure that follows for the LSBackup job.
To manually run the log shipping copy and restore jobs Open SQL Server Management Studio on the secondary instance and expand SQL Server Agent to open the Job Activity Monitor:
Right click the LSCopy job and select Start Job At Step...
The job should run to success - the SQL Server Agent account must have proper access to the source and destination locations:
Check the destination folder on again and it should now have Transaction Log Backup files there:
- Congratulations you have just configured a cross-domain or workgroup log shipping solution.
- Review the databases you manage to see if log shipping would be an attractive solution for off-site backup.
- Further explore the capabilities of Log Shipping in your environment. Follow Step By Step SQL Server Log Shipping for detailed instructions on setup and how it functions.
- Check out the Tip SQL Server 2008 High Availability Options to learn more about the right solution for your scenario.
Last Updated: 2012-01-05
About the author
View all my tips