In this article I want to discuss log shipping between servers that are not part of a Windows domain. Standalone windows servers are, instead, part of what is known as a workgroup. The authentication mechanisms in a workgroup differ to that of a domain.

In an Active Directory domain, user accounts are centralised for management on what is known as a domain controller. Active Directory uses a multi master replication model catalog to provide centralised administration of objects such as users, groups, computers, printers, etc within a Windows domain across multiple domain controllers. Member servers and workstations are then joined to the domain and become able to share their resources as well as access other resources via domain wide accounts.

In a Windows workgroup, each server is essentially a standalone account management system (SAM) and manages its own users, groups, printers, etc.

How does a server\computer in a windows workgroup seamlessly access resources on another workgroup server?

This uses a technique known as "Pass-Through Authentication". The downside of this is, the user accounts are managed separately on each workgroup server. I'm sure you can see quickly how much management overhead this places on server to server access, as accounts need to be synchronised between the machines. Let's review a quick scenario;

For example I have 2 servers titled AppServer1 and AppServer2. AppServer1 has a share titled "CompanyInfo".

The user Bob is a local user on AppServer1 with a password of "P@ssw0rd1". There is no local  user account for Bob on AppServer2. For Bob to connect to the UNC share "\\AppServer1\CompanyInfo" he would be prompted to provide authentication credentials. This is fine for user access but services are not able to provide alternate credentials, the service account must have explicitly defined access.

To enable seamless access, create a user account Bob on AppServer1 with the same password as used on AppServer2 and the next time Bob opens the remote share, pass-through authentication is used, Bob is no longer prompted for connection credentials.

How does this apply to Log Shipping?

Let's start with a look at exactly how log shipping works first. Log shipping starts with a database on a source SQL Server known as the Primary server. For example DBServer1 has a default instance of SQL Server with the AdventureWorks database attached. We wish to ship transaction logs for this database to the default SQL Server instance on DBServer2. 

Log shipping creates 3 main component parts in the form of SQL Server agent jobs, there are some alert jobs created too but for now we'll concentrate on the core jobs.

• A backup Job on the Primary server backs up the transaction logs to a specified network path, this can be local to the Primary or on a fileserver.

• A copy job on the Secondary server copies the transaction log backups from the network path above to a local folder on the secondary server.

• A restore job on the Secondary server restores transaction logs to the secondary database.

Using the information above we can see that the core process for shipping the logs is the copying of files between the 2 endpoints. Remember that the authentication is performed under the context of the account that the SQL Server agent runs under. This account connects in from the secondary server and must be able to access the backup share unchallenged.

There are various options which will be re iterated later in this article, the files may initially reside on either the Primary server or a central file store\server. Moving on further let's look at the information required for the Log Shipping wizard.

Starting the Log shipping wizard within SSMS requires you to define the following;

Backup settings.

Here you define the transaction log backup settings you wish to use. This includes the schedule\frequency, backup paths, file retention period of backup files on the Primary\file server and compression (if available). The network path is the UNC path to the location where your transaction log backups are stored. As mentioned previously, this can be a local folder on the Primary server and if this is the case you should also supply the local path too.

Example, the share "\\DBServer1\LogShip" in this case is actually a local folder "C:\MSSQL\LogShip" on DBServer1.

Add secondary databases.

Clicking the "Add" button displays a dialog with 3 tabs. The first action is to connect to the secondary SQL Server instance where the logs will be shipped to.

The first tab allows you to choose whether to have the wizard initialise the secondary database or to perform this action yourself (this would be done prior to launching the wizard by restoring a copy of the database WITH NORECOVERY or WITH STANDBY).

The second tab defines the settings to be used for the copy job which will be created on the Secondary server. This requires a local destination folder on the secondary server where the transaction log backups will be copied to after they are retrieved from the network location. Other settings required are the file retention period on the secondary server local folder and the frequency\schedule of the copy job.

Example, "DBServer2" in this case has a local folder "D:\FileCopy\LSNode1", this is where the backup files retrieved from \\DBServer1\LogShip" by the copy job will be stored.

The third tab defines settings for the restore job which will be created on the Secondary server. This defines the database state (no recovery or standby), whether to disconnect users during the restore action, the delay period for the restores and the frequency\schedule.

Once log shipping is initialised and running the following events occur;

• The backup job on the Primary server starts and takes a transaction log backup of the AdventureWorks database. The network location "\\DBServer1\LogShip" now contains a transaction log backup file. The local location is "C:\MSSQL\LogShip".

• The next step is important and usually where the errors occur. The copy job on the Secondary server starts and tries to access the path "\\DBServer1\LogShip". This is performed under the context of the SQL Server Agent. The account that the agent uses must have access to the network share, alternate authentication credentials cannot be provided it must be seamless, in other words Pass-Through.

• The restore job starts and restores any available log backups to the secondary database.

So, if the SQL Server agent service on DBServer2 is run as the local user Bob with the password "P@ssw0rd1", you need to create the same user account and password on DBServer1 and grant this account permissions to the share "\\DBserver1\LogShip".

Again, if you run the SQL Server agent service on DBServer2 as the local user SQL-Svc with password "T0day!", you need to create the same user account and password on DBServer1 and grant this account permissions to the share "\\DBServer1\LogShip".

When granting the permissions you have a dual task to complete. Firstly ensure you set the share permissions, as these default to "Everyone" - Read (Windows 2003 on). Once you set the share permissions, switch to the "Security" tab and set the NTFS permissions too.

Remember, where Share permissions and NTFS permissions combine the most restrictive applies. If you do not change the default, everyone gets read permissions only!

Using these guidelines above you should be able to implement a log shipping scenario between workgroup computers. I hope this provides a valuable insight into log shipping across a workgroup or indeed across domains. It is however worth pointing out that when crossing domains, that domain trusts will usually take care of the authentication requirements.

This is extremely easy to test via the use of a couple of virtual machines, see my guide on this site for creating virtual SQL Server sandboxes\test systems. Have fun and as always, feedback is appreciated. Post back if you have any questions.