Database backup and Replication

Document directory

• 1.1. Database Replication

1.1. Database Replication 1.1.1. Basic Concepts

Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites.. With the replication technology, users can publish one copy of data to multiple servers, so that different server users canShare the data within the permitted range of the permission.The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency..

1.1.2. Heterogeneous Database Replication Technology 1.1.2.1. Basic Principles

It is mainly used in Distributed Database Technology and mainly used inSQLReproduction Method.

SQLReproduction MethodEach source table needs to be createdTriggerWhen the source table is modified, inserted, or deleted, the trigger is started and restored to an SQL statement by calling the stored procedure, insert the change time and restored SQL statement into the change track table corresponding to the source table, which can be roughly divided into change capture and data distribution steps.

1.1.2.2. Change capture

1.1.2.3. Data Distribution

Apply changes in the source table to the target table. After successful execution, delete the records of the sequence numbers in the change track table.

1.1.3. SQL Server database replication technology 1.1.3.1. Basic Principles

SQL Server uses publisher and subscriber to process replication.Source dataThe publisher server is used to provide data. The Publishing Server copies all changes to the data to be provided to the subscriber server. The Subscriber server contains a subscriber database, which can receive all changes to the data and save the changes, then, distribute the changes to the subscriber server.

1.1.3.2. Technical Type

Based on the replication target:

1. Snapshot)

Create a new snapshot and place it under the specified directory for subscriber access.

2. Transaction Replication)

Back up logs and place them in the specified directory for subscriber access.

3. Merge and copy (merge)

"Based on SQLReproduction method ",Creates a change track table for the replication object (source table or view) in the source database. When the source table changes, the change track table records the changes and passesObtained from the change track table afterwardsSQLStatementTo copy data from the source table to the target table.

Divided by Replication mode:

1. the SQL Server Agent provides scheduling-based replication.

2. Manual replication (SQL writing)

1.1.4. Case

Cross-Region DevelopmentInventory Management System, HowTo achieve data synchronization in different regions, how to maintain data consistency and timeliness becomes the key to the success or failure of the system.. We have successfully solved this problem and will introduce it as follows.

System Environment Database System:SQL Server 2005

Operating System:Windows NT 4.0, iis4.0

Network:The sales center connects to the Internet through a leased line, and the distribution points in each region are connected to the Internet through MODEM dialing.

Replication PolicyConsider a variety of factors, suchData closeness, network reliability, and costAnd so on.Distributed Data Processing and regular data synchronization. Therefore, we divide the data into two types:Only one-way data replication is required (for example, basic data of the system and data processed by a part of daily work),Data that requires two-way replication (some of the data processed daily).

Different replication policies are required for different types of data..

Data that only needs one-way replication can be divided:Sales CenterMaintenance, distribution of data to each sales point; maintenance by each sales point, summarized to the sales center data.

Maintained by the sales center, the data distributed to each distribution point is set on the SQL server of the sales center as the Publishing Server and distribution server, and the publications are defined, the transaction replication model and anonymous subscription method are used.

On the SQL server of the distribution pointSet pull subscription. Maintained by each sales point, data collected to the sales center is set as the Publishing Server and distribution server on the SQL server of the sales point, and the SQL server of the sales center is used as the subscription server. Define publications and adopt the transaction replication model. And setPush subscription to copy data to the sales center.

Data that requires two-way replication is set on the SQL server of the sales center as the Publishing Server and distribution server. The publications are defined and usedMerge and copy modelsAnd anonymous subscription. Set the pull subscription on the SQL server of the distribution point.

Why is the above replication policy adopted?This is because the computer in the sales center is fixed.IPThe distribution point only has dynamic IP addresses on the computer.And data has different classifications.

1.2. Database Backup 1.2.1. Basic Concepts 1.2.2. Backup Solution

• Full backup

Back up all the data in the database.It can restore the entire database to the end of the backup time..Maximum backup file, RequiredLongest TimeThe actual business has the greatest impact(If the database is large)

• Differential backup

Changes made to the database since the last full backup.Before differential backup,A full backup is required.When restoring, you must first recover the full backup, and then recover the differential backup on the basis of the full backup. You cannot use it to recover the database separately. Differential backupThe generated file is smaller than the full backup file., RequiredShort Time

• Log backup

The changes made to the database since the last backup, before the log backup,A full backup is also required.. During restoration, the latest differential backup (if any) after the full backup is restored before the log backup is restored, and then the log backup is restored in sequence.Minimum backup file size,Minimum Processing Time,Little impact on business, Can be done frequently

• File/File Group Backup

This type of backup is rarely used,Generally applicable to backup of important dataThis requires that,Tables that store important data,Put in a separate file group,In this way, the new table can be backed up separately.This backup method is rarely used, and it requires a full backup.

1.2.3. backup solution comparison

	Backup File Size	Processing time	Impact on business	Data importance	Remarks
Full backup	Max	Longest	Max	Any data
Differential backup	Relatively small	Shorter	Relatively small	Any data	One full backup is required.
Log backup	Minimum	Shortest	Minimum	Any data	One full backup is required.
File/File Group Backup	Large	Long	Large	Important data

1.2.4. backup solution Selection

To sum up, a reasonable backup solution should be as follows:

•  Idle business processingComplete backupThe backup interval is set based on your data volume and importance, for example, once a month.
•  Service Processing is relatively idleWhen,Differential backupFor example, backup once a week
• RegularBack up logsFor example, if your data is very important and the data changes frequently, you can set a backup time of 5 minutes or even less, the key is determined by the importance of your data and the duration of data loss.
• Backup hardware configuration,In addition (Worst Condition ),You should also consider backing up to a tape drive or another server.And regularly burn the backup data to the CD for permanent backup. Another advantage of burning the CD is that you can delete the backup file after burning and free up space for new backup, this reduces investment in backup devices.
• For important data,To back up to more

1.2.4.1. Objectives

• The impact on business processing should be as small as possible, and the backup that takes a long time to complete should be put in the idle time of business processing.
• For important data, make sure that any damage acceptable can be recovered.
• When the database fails, it is required to restore data as much as possible.
• Make reasonable choices for backup files, and do not blindly waste backup hardware.