Walk-through 5 : Log shipping between two SQL Servers

If you paln to set up , maintain a standby server, you can create log shipping job for your database.
This walk-through will show you:

  • How to create a log shipping job
  • How to schedule the job
  • How to bring up standby server (secondary server)

In this walkthrough , ServerA is primary server , as source , ServerB is secondary server , as target server. The primary database is TestA, and the standby database is also TestA (you can also use another DB name in target server) . The log shipping job is scheduled to run every 4 hours , daily .

Steps : 1, 2, 3, 4, 5, 6, 7 , 8 , 9, 10, 12, 12, 13


How to create a log shipping job ?

1) To create a log shipping job, please open Log Shipping child window , and click the "New Job" button:


2) Fill the general information for the job , job name is "My-log-shipping-job" in this demo:


3) Please see following , primary server on left hand side , and target server on the right side . in this step :

a.) Server A is source server , and we use Server B as target server;
b.) SQL server on target machine should has the same edition as source or has higher edition than source server's;
c.) Database name and path on target server can be changed , but we use same dbname and path as source's in this trial.


4) Log shipping is backup and restore procedure in fact, so it needs a device to save backup , you can save backup on primary server or secondary server , depends on you . Putting backup on server A is better if you need to restore database on server often:


5) In Strategy tab :

a.) You can select the "no strategy" if you have other job dose full or differential backup on same database;
b.) If you have no other job do full or differential backup job, you can specify when full or diff backup job here.
In this trial , we has no other job doing full and diff backup on TestA , so we scheduled the log shipping job runs every 4 hours , then do a differential backup once every day , and do a full backup every week , strategy setings as below : (schedule setting on step12)


6) Completion state means the target database state after a log shipping job runs .

The default state is standby state after job , this state is read only for target databases , you can read the data but can not edit or delete the data on secondary server. Logshipping job firstly gets backup from primary server and then restores it to secondary server .

If you specify the completion state is ready state ,that is you can edit data on target database after job , when the next run of log shipping job ,in restore stage of log shipping job, it restores from the latest full backup to the latest transaction backup to target database (restore stage start from the latest full backup).

In this trial , we just use default setting , standby state as secondary database state after job .


7) Use compression and encryption options if you want , please notice that , compress and encrypt just happen in backup stage of a log shipping job.


8) If you have specified encryption password for log shipping job or you have another backup job backing up on same database with password ,please also add these passwords here .


9) We don't expect the software send notify email every run , just want to recieve mail if job fails.


10) No command specified here , just skip this tab

Click OK button to save the job.


How to create schedule for logshipping?

11) To create schedule for this created log shipping job , please open schedule window :


12) Run the job every 4 hours on every day, accordding to the strategy above, this log shipping job will run on every 4 hours , and then run a differential backup on every day, then run full backup once a week.


How to bring up the database when primary server failes?

13) In general , secondary server is in standby state , the target database is read only . When primay server failes , the secondary sever should take over the work, it need to be brought up to ready state .

To bring up secondary server , just click "Bring up secondary database" button in log shipping job window .

Click to enlarge

There are three options when you bring up secondary database.

The best situation is the first option , if this software is able to get last transaction log from primary server , you will not lost any data , so even the primay server failed it is worth trying to get last transaction log item , as option2 indicates .

The worst thing is that the software is not able to get last transaction log data from primary server, you lost 4 hours' data( since last job runs.) .

Recommend is to try all options from 1 to 3 ,see which one works , thus secondary server gets all possible data from primary server .