SQL Server data synchronization scenarios-log shipping

1 function Description

This plan uses the log transfer mode, the core database (main database) synchronizes regularly to the disaster Recovery database (secondary server) and the backup library (secondary server, easy to use other systems, reduce the main data pressure), during the period, if an exception caused the synchronization, will be e-mail, SMS notification to the management staff.

2 System Environment

2.1 Hardware

Main database: Sqlha

Disaster Recovery repository Server: Disaterdbsvra

Backup Library server: BACKUPDATASVR

2.2 Software

Primary database:

Win2008 x64

SQL2005 SP4 x64

Disaster Preparedness Library:

Win2008 x64

SQL2005 SP4 x64

Backup library:

Ditto

2.3 Network

The network bandwidth between the primary database and the disaster preparedness database is 20M.

3 Deployment Process

3.1 Preparation

3.1.1 Setting the database recovery model (Recovery model)

Set the recovery model for the primary database and all backup databases to: Full recovery mode (fully), by right-clicking the database name, properties->options

3.1.2 backing up the primary database

Fully back up the primary database.

3.1.3 copy backup Files

It is a time-consuming process to copy the backup files of the primary database to all secondary servers, limited to the bandwidth between the two places, and the database backup files are larger.

3.1.4 recovering a secondary database

Restore the backed-up database to the disaster recovery database and the backup database (note that all databases have the same name).

When restoring the backup library, select "Leave the database non-operational and do ..." (Restore with NORECOVERY), and after completion the databases display restoring:

3.1.5 setting up a shared directory

1. set up the shared directory \\Primary backup on the primary database server , the user must have read and write permission to hold the log backup file generated each time

2. set the shared directory \\Secondary DB backup on all backup servers, and the user must have read and write permissions to hold the backup files copied from the primary database.

3.1.6 Preparing the Dispatch schedule (JOB)

Backup jobs: 2:00am executed once a day

Copy job: 2:30am executed once a day

Recovery jobs: 3:00AM per day

3.2 tail log processing

1. Backup Tail Log

A tail log is a newly added log in the primary database after the last full backup of the primary database. To ensure that no tail logs are generated, you must ensure that all primary database connections are disconnected before backing up the tail log.

Run the Backup tail log command in master server master:

Backup log XXX to disk= ' Z:\PrimaryBackup\XXX-001.bak '

2. restore the tail log to the backup database

In the secondary server master sequence, run the following command:

Restore log XXX from disk = ' Z:\SecondaryBackup\XXX-001.bak ' WITH NORECOVERY;

Tip: Before the deployment is complete, you must ensure that no data is written to the primary database!

3.3 Configuring log Shipping

Right-click the main database, properties->transaction Log Shipping

Tick enable this as a primary the database in a log shipping configuration.

3.3.1 Configuring the transaction log backup schedule

Click the Backup Setting button to go to the transaction log Backup Schedule Configuration window.

1. In the network path to backup folder input box, fill in the native shared directory you just created, this is \\XXX\PrimaryBackup
2. In the If the backup folder is located on the primary Server,type a local path to the folder input box to fill in the shared directory of the native paths, here is the C:\PrimaryBackup
3. In the alert if no backup occurs within input box, fill in the Copy alarm interval (note: default hours)
4. In the delete files older than input box, fill in the length of time to delete the history file
5. Click the Schedule button to set the transaction log backup schedule. Select occurs every and enter the time of occurrence, such as 2:00am, after the setup is complete, click the OK button to return to the Database Properties window.

On the right side of the Backup setting button, you can see the scheduling schedule description information for the backups job.

3.3.2 Configuring a secondary server

Click the Add button to start deploying the secondary server:

1. Connect

Click the Connect button to connect to the secondary database:

2. Initialization

After connecting to the database successfully:

In the Initialize Secondary database page, select: No, the secondary database is initialized.

3. Copy the transaction log

On the Copy Files page:

1) Enter the shared path of the secondary server, which is the log file that is copied from the primary server .

2) Enter the time interval for deleting history files

3) Click the Schedule button to enter the Copy transaction log job Scheduling Schedule Setup window

With the backup job scheduling lie, here also choose occurs every, and enter the time 2:30am.

4. Recovering the transaction log

Click to enter Recovery Transaction log page:

1) Select Standby mode

2) Tick disconnect users in the database when restoring backups　

3) Enter the transaction log recovery alert interval in the alert if no restore occurs within input box

Click the Schedule button to set the recovery transaction log job scheduling schedule:

3.4 Configuring warning notifications (this part is slightly)

3.5 Inspection and verification

3.5.1 Monitoring the operation of the job

Management Studio->sql Server agent->jobs-> Right-click Job History Monitor

3.5.2 Job detailed operation information

Right-click the job name Lsbackup_xxx->view history

3.5.3 Check if synchronization

Management studio-> Right-click the server name->reports->transaction Log Shipping Status

The status is good that is, synchronization is normal.

At this point, the log shipping scheme has been configured, no configuration, please cool solution. If you have any confusion about the place, you can leave a message, I can send you the part.

I would take this opportunity to thank the friends of the park for providing us with a lot of valuable information during the initial survey of our project. Thanks. We also calculate open Source:).