Configuring Log Shipping with SQL 2005

SQL 2005 log shipping can work without AD. It can run on standalone but you will need to create a local user account with local admin rights with same username and password at both source and destination SQL server. Once user created, configure the SQL server service and SQL agent service to start with this account. Always remember to restart the services mentioned before proceeding to the following steps. 

Make sure the following are ready:-

1. Shared folders (with right permission for the SQL account startup account) at both source and destination server.

2. SQL agent service must be started

Steps to configure Log Shipping on SQL Server 2005

1. Create a new user Account named “sqlagent” on both computers. Use the exact same user name and password.

2. Make sure File Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.

3. Optional - Make sure the local network connection is not regular LAN. It must 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 “TranLogs” on both servers. Let’s assume the folder is on E:\Tranlogs.

5. On Primary Database server, share the folder “Tranlogs” and allow sqlagent “Full Access” to it. Then allow sqlagent 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 sqlagent “Full Access” right on TranLogs folder. Share it as step 5.

7. Test whether sqlagent can really connect between the servers. Login with sqlagent and access both file share by typing \\<<servername>>\tranlogs

8. You should be able to write file into the shared folder at both source and destination folders.

9. Now, try to copy some file from source -> destination and vice versa. 

10. Delete the file once you have tested successfully. 

11. This is exactly what SQL Agent will be doing during log ship. It will copy log files from primary server’s network share to it’s own log file folder. So, the sqlagent needs to be able to both read files from primary server’s network share and write onto its own tranlogs folder. The above test verifies the result.

12. This is something new in SQL Server 2005: Add sqlagent in SQLServer Agent group “SqlServer2005SqlAgentUser….”. You will find this Windows User Group after installing SQL Server 2005.

13. Now go to Control Panel->Administrative Tools->Services and find the SQL Server Agent service. Go to its properties and set sqlagent as the account on the Logon tab. Restart the service. Do this on both servers.

14.  SyncAccount is sqlagent. Ignore the typo.

15. I use sa account to configure the log shipping. So, do this on both servers:

a. Enable “sa” account. By default, sa is disabled in SQL Server 2005.

b. On “sa” account turn off Password Expiration Policy. This prevents sa password from expiring automatically. If the password changed, you will need to change the password in this log shipping job.

16. On Secondary server, you need to allow remote connections. By default, SQL Server 2005 disables TCP/IP connection. As a result, you cannot login to the server from another server. Launch the Surface Area Configuration tool from Start->Programs->MS SQL Server 2005 and go to “Remote Connection” section. Choose the 3rd option which allows both TCP/IP based remote connection and local named pipe based connections if needed.

17. On Secondary Server firewall, open port 1433 so that primary server can connect to it.

18. Restart SQL Server. Yes, you need to restart SQL Server.

18. On Primary server, go to Database properties->Options and set Recovery Model to “Full”. If it was already set to full before, it will be wise to first set it to Simple, then shrink the transaction log file and then make it “Full” again. This will truncate the transaction log file for sure.

19. Now take a Full Backup of the database. During backup, make sure you put the backup file on a physically separate hard drive than the drive where MDF is located. 

20. After backup done, ZIP/RAR the database. This ensures when you copy the database to the other server there’s no data corruption while the file was being transferred. If you fail to unZIP/unRAR the file on the secondary server, you get assurance that there’s some problem on the network and you must replace network infrastructure. The RAR also should be done on a separate hard drive than the one where the ZIP/RAR is located. Same reason, read is on one drive and write is on another drive. Better if you can directly ZIP/RAR to the destination server using network share. It has two benefits:

a. Your server’s IO is saved. There’s no write, only read.

b. Both RAR and network copy is done in one step.

21. 

22. By the time you are done with the backup, RAR, copy over network, restore on the other server, the Transaction Log file (LDF) on the primary database server might become very big. For us, it becomes around 2 to 3 GB. So, we have to manually take a transaction log backup and ship to the secondary server before we configure Transaction Log Shipping.

23. 

24. When you are done with copying the transaction log backup to the second server, first restore the Full Backup on the secondary server:

25. 

26. But before restoring, go to Options tab and choose RESTORE WITH STANDBY:

27. 

28. When the full backup is restored, restore the transaction log backup.

29. REMEMBER: go to options tab and set the Recovery State to “RESTORE WITH STANDBY” before you hit the OK button.

30. This generally takes a long time. Too long in fact. Every time I do the manual full backup, rar, copy, unrar, restore, the Transaction Log (LDF) file becomes 2 to 3 GB. As a result, it takes a long time to do a transaction log backup, copy and restore and it takes more than an hour to restore it. So, within this time, the log file on the primary server again becomes large. As a result, when log shipping starts, the first log ship is huge. So, you need to plan this carefully and do it only when you have least amount of traffic.

31. I usually have to do this manual Transaction Log backup twice.

32. Now you have a database on the secondary server ready to be configured for Log shipping.

33. Go to Primary Server, select the Database, right click “Tasks” -> “Shrik”. Shrink the Log File.

34. Go to Primary server, bring on Database options, go to Transaction Log option and enable log shipping.

35. 

36. Now configure the backup settings line this:

37. 

38. Remember, the first path is the network path that we tested from command prompt on the secondary server. The second path is the local hard drive folder on the primary server which is shared and accessible from the network path.

39. Add a secondary server. This is the server where you have restored the database backup

40. 

41. Choose “No, the secondary database is initialized” because we have already restored the database.

42. Go to second tab “Copy Files” and enter the path on the secondary server where log files will be copied to. Note: The secondary server will fetch the log files from the primary server network share to it’s local folder. So, the path you specify is on the secondary server. Do not get confused from the picture below that’s it’s the same path as primary server. I just have same folder configuration on all servers. It can be D:\tranlogs if you have the tranlogs folder on D: drive on secondary server.

43. 

44. On third tab, “Restore Transaction Log” configure it as following:

45. 

46. It is very important to choose “Disconnect users in database…”. If you don’t do this and by any chance Management Studio is open on the database on secondary server, log shipping will keep on failing. So, force disconnect of all users when database backup is being restored.

47. OPTIONAL - Setup a Monitor Server which will automatically take care of making secondary server the primary server when your primary server will crash - This is NOT configured in the current environment and it is OPTIONAL.

48. 

49. In the end, the transaction log shipping configuration window should look like this:

50. 

51. When you press OK, you will see this:

52. Do not be happy at all if you see everything shows “Success”. Even if you did all the paths, and settings wrong, you will still see it as successful. Login to the secondary server, go to SQL Agents->Jobs and find the Log Ship restore job. If the job is not there, your configuration was wrong. If it’s there, right click and select “View History”. Wait for 15 mins to have one log ship done. Then refresh and see the list. If you see all OK, then it is really ok. If not, then there are two possibilities:

a. See if the Log Ship Copy job failed or not. If it fails, then you entered incorrect path. There can be one of the following problem:

1. The network location on primary server is wrong
2. The local folder was specified wrong
3. You did not set sqlagent as the account which runs SQL Agent or you did but forgot to restart the service.

b. If restore fails, then the problems can be one of the following:

i. SyncAccount is not a valid login in SQL Server. From SQL Server Management Studio, add SyncAccount as a user.

ii. You forgot to restore the database on secondary server as Standby.

iii. You probably took some manual transaction log backup on the primary server in the meantime. As a result, the backup that log shipping took was not the right sequence.

53. If everything’s ok, you will see this: