SQLServer log shipping

From tekkies.co.uk
Jump to: navigation, search

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.

Components

  • 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

Troubleshooting

  • 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.