How to setup SQL Server 2005 Transaction Log Ship on large database that really works

I tried a lot of combinations in my life in order to find out an
effective method for implementing Transaction Log Shipping between
servers which are in a workgroup, not under domain. I realized the
things you learn from article and books are for small and medium
sized databases. When you database become 10 GB or bigger, thing’s
become a lot harder than it looks. Additionally many things changed
in SQL Server 2005. So, it’s even more difficult to configure log
shipping properly nowadays.

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. There’s Service Pack 1 released already.

1. Create a new user Account named “SyncAccount” 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. 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 SyncAccount “Full Access” to it. Then allow SyncAccount
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 SyncAccount “Full Access”
right on TranLogs folder. No need to share it.

7. Test whether SyncAccount can really connect between the
servers. On Secondary Server, go to Command Prompt and do this:

8. 

9. Now you have a command prompt which is running with
SyncAccount privilege. Let’s confirm the account can read and write
on “TranLog” shares on both servers.

10. 

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 SyncAccount 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 SyncAccount 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 SyncAccount as the account on the Logon tab.
Restart the service. Do this on both servers.

14. 

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.

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.

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. Remember, not different
logical drives, different physical drives. So, you should have at
least 2 hard drives on the server. During backup, SQL Server reads
from MDF and writes on the backup file. So, if both MDF and the
backup is done on the same hard drive, it’s going to take more than
double the time to backup the database. It will also keep the
Disk fully occupied and server will become very slow.

20. After backup done, 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 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 RAR is located. Same reason, read is on one drive and
write is on another drive. Better if you can directly 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. First one is around 3 GB. Second one is around 500 MB.

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. Setup a Monitor Server which will automatically take care of
making secondary server the primary server when your primary server
will crash.

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 SyncAccount 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:

36 Comments

  1. Dear all,

    The granting of new service account for SQL Agent should be from SQL configuration manager, then we don't have to add the service account to the group. Configuratoin manager will do it for us.

    This is the recommended method from Microsoft as the change of SQL server service account also reult do the encryption key as well.

    The log shipping of SQL Server 2005 is just a bit differnet from 2000. The service account assignment and the UI.

    Marrow.

  2. Hi,

    Can i know the the require ports for SQL server 2005' log shipping?

  3. Thanks for an excellent expalnation I did test it and its working when you have two seperate servers.

    But here is my problem

    I'm using sql 2005 on one server but I do have the secondary database on the same server but on a partitioned drive now the problem is that the secondary database is growing how do I shrink it.

    Database engine is on c drive whereas the secondary database and all the data that I'm collecting is sitting on d drive on the same server.

    I will appreciate your help as I'm about to lose my mind because the data is growing fast.

  4. Vincent,

    From my point of view, log shipping doens't work like mirror, they work in slightly different way.

    log shipping just like file copy process using SQL job. so what is the port number of file copying?

    Marrow.

  5. Thank you Omar. This was an excellent document. I was stuck with the user rights issue for a couple of hours. Reading this document helped me fix the problem immediately.

  6. How to make a full backup on primary server, if database on primary server is in log shipping proces.

  7. Are you sure this works in a non-domain environment.

  8. Need details how SQL server startup account communicates across both primary and secondary server via network

    Ex : Two server A and B

    SQL server startup accounts are sql in both servers with same password , log ship works fine , but can u explain me how id communicates during copy process since id communication will be from Asql to Bsql how does it get matched

  9. Great work omar helps alot

  10. Excellent, Great Manual thanks a lot

  11. thanks, Its really good. you have explained each and every steps in detail.

  12. Your work is extremely well laid out, an greatly appreciated.

  13. Thanks omar.its a great article that really allowed me to configure log shipping on my local servers.I would appreciate all your efforts.Request you to publish the article on

    log shipping using Transact sql(TSQL) statements.

  14. I have not the words to say u thanks

  15. Document is very good. But there are few questions unanswered in that the highly important question is “my transaction log on SECONDARY server is growing. What can I do?!!”

    Can some one help on this Query.

    Addanki.

  16. Excellent.. This is my first visit to your place. Will read rest also..

    Again.. this is really good.

  17. how to shink logshipping primary database file log in mssql 2005, please give me solution, if shink _log file to 1 the loshipping restore secondary is failing.

  18. Hi Omar,
    Thanks for the article.
    I have also implemented Log shipping according to your document on 110 GB database. Now i am worried when primary server fail we will restore Secondary server with “no recovery option” and suppose after 2 days primary server is up till that time my secondary server act as primary server.
    But when my primary server is ok i need it back in action.
    My Question is do we need to follow same action again to establish log shipping or is there any other way.

    Thanks
    Rajat

    • Once the primary server comes back online, you will have to setup log shipping from the secondary server to the primary server to get the primary server up to speed with latest data. When the log shipping is in sync and primary server has the recent data, you will have to do a cutover to primary server.

  19. Hi Omar,
    You are very true but i have some query like
    1) Do i need to take full backup from secondary server and restore again on Primary server
    Or is here any way by which i can just copy the downtime backup only. and primary server is up to date that time.

    2) How can i convert a full recovery database to no recovery mode
    or stand by mode easily
    just like if we want a non recovery mode database to become recovery mode. then we just run “RESTORE DATABSE dbName WITH RECOVERY”
    is there any simplest way ?
    can you please add me on your Gtalk if you don’t mind.

    Thanks in Advance

    Thanks
    Rajat

  20. Hi Omar,

    I have two windows 2008 data center OS server with MSSQL 2008 enterprise edition for log shipping. Is there any other way to share the backup files than creating same name user account on both the servers. My problem is that whenever I am doing this windows ask for a password even if I have enabled without password sharing on windows, as I don’t want to create same name user account on both the servers. Both servers are in workgroup.

    Thanks
    Vivek

  21. Hi omar,

    I have implemented on local server but when i tried on production
    I got following error.

    The specified @server_name (‘WIN-xxx’) does not exist.
    SQL Server Management Studio could not save the configuration of 192.168.1.112 as a Secondary. (Microsoft SQL Server Management Studio)

    Please help its urgent.

  22. Hello,
    Nice doc !

    I’m doing log shipping for all the DBs of my SQL instance.
    my question is : do I need to set up for each single Db the Monitor Server ? Or only one is sufficient ?

    Regards,
    Greg

  23. Excellent article…It helped me a lot..If you are facing “Access denied” error message,please use “syncaccount” user as login user for SQL service and re-start the service again..

  24. Hi Omar
    First of all , Thank for the this article.

    My Question: After the logshipping config is it possible to manuel Daily full backup (outside the logshipping) ?

    Thanks
    Bora

  25. Hello Omar.
    This was really a nice and clear explaination. Thank you very much for this!
    But I have a query. After all this, I am encountering non restoration of the trn file on the secondary even though its getting copied. It says:
    The restore operation was successful. Secondary Database: ‘ARSystem’, Number of log backup files restored: 0
    2012-03-04 20:00:12.62 Deleting old log backup files. Primary Database: ‘ARSystem’
    2012-03-04 20:00:12.64 The restore operation was successful. Secondary ID: ’75e1e1b8-a140-4dfe-9839-0198fdbe4a8e’
    2012-03-04 20:00:12.65 —– END OF TRANSACTION LOG RESTORE —–
    and

    Could not find a log backup file that could be applied to secondary database ‘ARSystem’.

Leave a Reply