Application of Step6:sql Server data change timestamp (timestamp) in replication

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Scenario (solution)
   1. Scenario I (Solution one)
   2. Programme II (Solution two)
   3. Programme III (solution three)
4. Implementation (process)
5. Precautions (Attention)
6. References (References)

two. Background (contexts)

In the SQL Server database, basic and group Two tables need to provide some fields to read to other programs, and the program caches the data from both tables in memory, but the program wants to know the change information for both table data, including: Insert/update/delete, What is the way to achieve it?

three. Programme (solution)

The above scenario, using SQL Server replication (Replication) is understandable, but how to get the change information in a timely manner?

Programme one:

Use change data to capture the CDC feature, use CDC in the database subscription library, and create a stored procedure, and return a list of table changes by passing the log sequence number (LSN) of the last record (saved by the program) to the stored procedure, which deletes the ID values from memory and then inserts the change data back into memory , this logic can simplify all the processing of insert/update/delete;

(Figure1: Change data capture)

Scenario Two:

With change tracking (Chang Tracking) This feature, change tracking includes unique values for the trace table, as well as field sys_change_operation, enumeration values (I=insert, U=update, D=delete), There is also the version number of the DML operation: Sys_change_version, which is incremented by one version number per DML, so you can filter on I=insert, u=update, D=delete different types plus version number, You can find the data to be updated;

(Figure2: Change tracking)

Programme III:

Using timestamp, add this field to the two tables in the subscription, timestamp record the time of the data change, and read the data that is larger than this timestamp in the program (operation as shown in the idea); but there is a drawback, there is no way to record the deleted record. Unless there is a field in the table that identifies whether to delete, the publication library does not have a delete operation, only insert and update.

The fields that need to be synchronized are as follows:

Basic table: Id,name,category,overseas,groupid,delete;

Group table: Id,name,delete;

The basic use of CDC can be referred to: SQL Server change data Capture (CDC) Monitoring table data, change tracking can refer to: SQL Server change Tracking (Chang Tracking) Monitoring table data, below I talk about the implementation of the idea three;

Four. Implementation (process)

(i) Environmental information

System environment: Windows Server + SQL server R2

Publisher: 192.168.1.152, server name: user-h2b2

Subscriber: 192.168.1.151, server name: User-fjmo

Publish database: Task

Subscription database: Tasksiteinfo

(ii) Overview of implementation

The first is to publish through the task, Tasksiteinfo subscription data, in both tables there is a delete field, to identify whether the data is deleted, in addition to the Tasksiteinfo database in the two tables are added to the timestamp field, The purpose of joining this field is to log the maximum timestamp of the query by the program, and return the data greater than a certain time through this timestamp.

(c) Construction steps

A. The process of building replication refer to the Documentation: SQL Server replication: Transactional publications, viewing the table information at the Subscriber, as shown in:

(Figure3: Table data)

B. Next we modify the basic and Group table structures and add a timestamp type field for each table;

--Modify table structure ALTER TABLE  [dbo].[ Basic] ADD    timestamp timestamp not null--modify table structure alter tables  [dbo].[ Group] ADD    timestamp timestamp not NULL

C. Create an index for this timestamp-type field, respectively;

--Creating an index create nonclustered index ix_basic_timestamp on [dbo]. [Basic]  (    timestamp) with (Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) on [PRIMARY] gocreate nonclustered INDEX ix_group_timestamp on [dbo]. [Group]  (    timestamp) with (Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) on [PRIMARY] GO

D. Query the change data through the timestamp field, if the value of the last time stamp saved is: 0x0000000000163e30, then we can get the record of the change after this timestamp by the following SQL script, get the data to update the memory data;

--Returns the data after a time stamp select * FROM [dbo]. [Basic] WHERE Timestamp > 0x0000000000163e30

(Figure4: A record of changes after a time stamp)

Five. Precautions (Attention)

1. Each database has a counter that is incremented when an INSERT or update operation is performed on a table that contains timestamp columns in the database. The counter is the database time stamp;

2. A table can have only one timestamp column;

3. Note that deleting a data operation is not a way to record the timestamp, so the logic of deleting the record should be to use a field to identify that the row record has been deleted;

4. This attribute makes the timestamp column unsuitable for use as a key, especially if it is not used as a primary key;

5. If the column belongs to an index key, all updates to the data row will also cause the index to update;

6. To return the current timestamp value for the database: SELECT @ @DBTS

7. In the DDL statement, try to use rowversion instead of timestamp, and there is no rowversion data type when you design the table in SSMs;

8. In the CREATE table or ALTER table statement, you do not have to specify a column name for the timestamp data type, and if you do not specify a column name, the Microsoft SQL Server database engine generates a timestamp column name; rowversi The on synonym does not have this behavior. When you use rowversion, you must specify a column name.

9. The non-nullable rowversion column is semantically equivalent to the binary (8) column. Nullable rowversion columns are semantically equivalent to the varbinary (8) column.

Application of Step6:sql Server data change timestamp (timestamp) in replication