SQLServer log shipping
SQL Server log shipping is a mechanism built into SQL Server that uses incremental backups to replicate the data from one database to another (often at another location). The destination database can be queried as a read-only during the windows between restore.
It's built from existing components of: backup, restore & scheduled jobs.
- Log shipping is implemented using 4 SQLAgent jobs. 1 on the primary server, 3 on the secondary server. Settings appear to be stored in MSDB on the primary server.
- Backup (primary): Performs transaction log backups.
- Copy (secondary): Copies the file from the primary server to the secondary.
- Restore (secondary): Restores the transaction log backup on the secondary.
- Alert (secondary): Checks that the restores are happening in a timely fashion (seconary is reasonably up to date).
Under the bonnet
- Log shipping uses relies on account authentication not SQL authentication
- This is because it does not save any passwords - it just uses the credentials of the agent job step.
- The agent jobs just call sqllogship.exe with a couple of parameters.
- sqllogship.exe then:
- Connects to the primary to retrieve settings (as far as I can tell)
- Does its designated task (backup/copy/restore/check) - see above
- Check the SQLAgent jobs - did they succeed?
- No? View failed agent job log - this is your starting point.
Common problems setting up
Secondary server cannot connect to the primary
- When you setup log shipping, you connected to the primary server using server name "primary", but secondary can't find "secondary" on the network - it can only access it by IP address or "primary.mydomain.com"
- When you login in to primary to configure log shipping, use "primary.mydomain.com" as the server name - the secondary will then use this DNS name to find the primary.
- The windows security account that the agent job is running under does not have permission on the primary server
- Ensure the windows account passwords are the same at both ends.
- Ensure the agent job step is running as the correct account (The last agent job log entry says "Executed as user: xxxxx\yyyy")
- Fix this using a "proxy account" or by changing the SQLAgent service "Run As" account.
- Ensure the job windows account has been granted permissions on both servers
SQLServer cannot access the transaction log files
- Even if the SQLAgent job had permission to copy the .trn files, the restore is done under the context of the SQLServer service - so the SQLServer service account must also be able to access the .trn files locally, after they are copied over.