Synchronization quasi-real-time scheme of SQL Server database

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