As I have tried a lot of combinations in to find out an effective method for implementing Transaction Log Shipping between servers.
Here's the steps that I finally found that works. Let's assume there are 2 servers with SQL Server 2005. Make sure both servers have latest SP.
In this example I am going to explain the log shipping between two servers, connected through vpn, server A location Europe and server B location USA.
1. Make sure you have created same user accounts with full permissions on both servers, if you are the DBA sa is preferred.
2. Make sure File Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.
3. Make sure the local network connection is not regular LAN. Recommended be a gigabit card with near zero data corruption. Both cable and switch needs to be perfect. If possible, connect both servers using Fibre optic cable directly on the NIC in order to avoid a separate Switch.
4. Now create a folder named sqldata on both servers. Let's assume the folder is on E:\Transfer.
5. On Primary Database server, share the folder sqldata and allow windows account Full Access to it. Then allow windows account FullAccess on TranLogs folder. So you are setting the same permission from both "Sharing" tab and from "Security" tab.
6. On Secondary database server, allow windows account Full Access right on Transfer folder. No need to share it.
7. Test whether windows Account ( in our case sa account) can really connect between the servers.
8. The sql account needs to be set up with full access permission. In the example below syncaccount is given as an example.
9. Now connect the secondary server in the primary server in the ssms and make sure the connectivity is working fine.
10. Now backup the database from the primary database say test.bak and the transaction log back up as test.trn.
11. Copy the file the test.back and the test.trn to the secondary server and restore the database test on the secondary database. please see image below
12. Once you restore the test database in the secondary server the database will be in read only mode, but the advantage is this database can be accesses through sql query analyzer, where as in mirroring this option is not available.
13. Now let us come back to the primary server, right click on the test database and click properties --- transaction log shipping.
14. Click on the check box Enable this as a primary database in a log shipping configuration.
15. Click on back up settings and specify the back up of the transaction log files to be backup, please note this can be a network folder or you local folder. So if you need to do on the local folder say for example f:\vijay\ .... as shown in the example below.
16. Now we have the transaction log files copies on this location.
17. Next step is to add the secondary server, click on add, connect to the secondary server, as we have already set up the test database on the secondary server click on No the secondary database is initialized.
18. Now click on the copy files tab and enter the location of the secondary server file location where the transactin log files will be copied. Please note the user should have full access to this location with read, write permissions.
19 . Next step is to click on the Restore Transaction Log tab and check the Standby Mode and check the Disconnet the users in the database when restoring backups.
20. Now go back to the secondary server and check if the following sql jobs are created and they are running fine. I have xxx just to remove by company name.
LSRestore_xxx-HOST\GOES_test
LSCopy_xxx-HOST\GOES_test
I hope this helps in setting up the sql server 2005 log shipping.
