After a few days of research, some tests on data replication is summarized as follows:
Successful data copy work, some conditions are essential: 1> SQL Server Agent must start, MSDTC must start. 2> All tables to be copied must have a primary key. 3> If there is a TEXT or Image data type in the table, you must use the with log option and you cannot use the with no_log option. In addition, the Max TEXT REPL SIZE option controls the maximum size of the text and image data that can be copied, and the operation that exceeds this limit will fail. (This is not known to understand?) 4> On the computer to be copied, it should be at least implicit sharing, ie the shared name is C $ or D $ .... 5> Windows NT accounts used for SQL Server agents cannot be a local system account because local system accounts do not allow network access. 6> If you participate in the copy server in another computer domain, you must establish a trust relationship between these domains.
Three methods of copying: 1, snapshot replication.
2, transaction copy. By publishing data on the publishing end, the subscription end is updated immediately returned to the published end. This approach is a successful type without IAMGE, TEXT, NTEXT, and may be more reasonably for our project, but this data type is involved in the management of office automation. The error that appears when copying this method:
When the release end, after a record, it will be copied to the subscription, and if a record is added in the subscription, an error message appears: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Updatable subscriptions: Thetext / NText / Image VALUES INSERTED AT SUBSCRIBER WILL BE NULL. Save not in, copy (update immediately) is not successful. I don't know how to solve this problem?
Procedure: In the Publish: New-> Publishing -> Display Advanced Options -> Select a Database -> Transaction Publishing -> Select Instant Update -> Select the table you want to copy in the selected item, this table should meet some of the above condition. Complete the operation in accordance with the wizard. If there is a need to select the row in the publishing property, you will write the WHERE statement to filter the row to copy. In your subscription: New -> Request Subscription -> Display Advanced Options -> Select a machine name -> Login account -> After you need to modify the cycle of the copy. The subscription setting is complete according to the wizard.
3. After the problem that the transaction copy cannot be resolved, it is now looking out that another copy method: merge replication.
Preliminary imagination is to make a publishing end on the headquarters database server, do a subscription on the member company's database server, copy different data to different member companies according to the filter line. Merger replication characteristics: 1. If there is a primary key in the published end with the subscription end, it will copy the recorded end key data with the records of the published end. 2. Data synchronization during the merge process (data update of all servers)
Procedure: In the Publish: New-> Publishing -> Display Advanced Options -> Select a Database The Merged Release -> Select the table you want to copy in the selected item, this table should match some of the above conditions . Complete the operation in accordance with the wizard. If there is a need to select the row in the publishing property, you will write the WHERE statement to filter the row to copy. In your subscription: New -> Request Subscription -> Display Advanced Options -> Select a machine name -> Login account -> After you need to modify the cycle of the copy. The subscription setting is complete according to the wizard.
