Synchronization quasi-real-time scheme of SQL Server database

Source: Internet
Author: User
Tags copy file system log modify oracle database backup
server|sqlserver| Data | database 1. Demand
1 How to synchronize the online SQL Server database data to another SQL Server database in a timely manner.
2 data extraction of data Warehouse system will cause great pressure to the source system, which can seriously affect the performance and response speed. How to quickly extract production data to historical data warehouse and improve the performance of business system.
3 distributed database, how to copy the branch of the database data to the headquarters database, to facilitate the headquarters summary statistics.
Common SQL Server database synchronization requirements in these business applications require a professional replication system to complete.

2. Synchronization Scheme
Brief introduction of Wave -engine and mirror system
Wave/mirror System is the industry's mature application system replication solution, without source and target hardware specifications or configuration of the same, with quasi real time replication, system and network resources occupy less, flexible application mode and so on. Wave/Mirror system supports the quasi real-time synchronization and backup disaster of SQL Server database, Oracle database, file system and other application systems.

Product Components
SQL Server database Mirroring agent (for SQL Server 2000, 2005).
Mirrored storage server (for Windows 2000, 2003).

Copy Principle
Wave/Mirror System The SQL Server database Mirroring agent monitors the source SQL Server database and captures its change transactions in real time and transfers the capture results to the destination SQL Server database and then through the storage process to the destination SQL Server database.
The target SQL Server is in the running state, can read and write, and the running target system guarantees the consistency and integrality of the business data. The system is not affected by the operating system, hardware platforms, and storage devices and is not consistent with the source and target.

Apply Deployment

Deployment architecture Diagram

Install the SQL Server Mirroring agent on the source SQL Server database servers, and install the mirror server on the destination SQL Server database servers.
System administrators can configure mirroring policies, full volume and incremental jobs, and so on through the management side.

Configuration Policy : After the first run of baseline synchronization, the source SQL Server database data is replicated to the target database in real time.
First Run baseline synchronization : You can perform a database backup of SQL Server, then restore on standby SQL Server, and complete mirroring for the file system to copy all files to the destination server.
SQL Server quasi real-time incremental mirror : The mirror agent's monitoring transaction changes, and real-time capture database operations, and then transfer the operation to the mirror server. When the mirror server receives it, it submits it to the standby SQL Server servers to complete a mirroring process.


SQL Server Synchronization technical parameters

Technical parameters Description
Copy principle Capturing incremental transactions for SQL Server replication
Copy granularity Database records
Copy DDL language Copy the database definition language, such as:
1) Add, delete, modify table
2 Add, delete, modify field definitions
3 Add, delete, modify triggers, stored procedures
Copy DML language Copy the data submitted by the application
Save transaction log The system holds the captured transaction log
Source and target hardware specification requirements No need to be consistent
Network bandwidth consumption Far less than storage-layer replication
Replication Latency Asynchronous replication, second-level delay
Copy Maximum distance Unlimited
Impact on system performance Source system CPU Occupancy rate is no more than 5%
Support Application Mode A pair of one or one pairs, more than one
Transactional integrity Complete, consistent
Target Application System Status Running state, able to read and write
Copy direction One-way, from source to target
Support filtering Filter conditions can be set
Supported versions SQL Server 2000, SQL Server 2005


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.