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 |