SQL Server replication creation skills

Since 2005, the replication technology of sqlserver has become very mature and effective. Many companies have chosen this technology for read/write splitting. Replication now includes transactions,
Merge and snapshot, each of which has its own application advantages. The most common use is undoubtedly transaction replication, this technology ensures that data changes at the publishing end can be promptly transmitted to the subscription end, network bandwidth, and server configuration.
If there is no difference, the data delay between the two machines is very small, this provides a powerful guarantee for our read/write splitting technology (few newly added data cannot be found), which not only disperses the read/write pressure
It can also be used for machine maintenance, and the user experience is also improved. Next we will take transaction replication as an example to introduce the replication chain creation skills.

There are three methods to create replication:

Method 1: Back up and restore
This method is a good choice for database replication that requires a large amount of data and a very tight downtime. The implementation method is very simple, that is, to back up the database at the publishing end when the station is stopped,
Then, restore it to the subscription end, so that the data on the publishing end is consistent with that on the subscription end. Then, we can create the synchronization chain.
However, this method requires that, after we restore the data to the subscription end, we need to check whether all the tables have auto-incrementing columns. If there are auto-incrementing columns, we need to change the auto-incrementing column to a normal column, the method is as follows:

1. Find the auto-incrementing column on the database restored by the subscription end:

   --  Search for auto-increment columns in a database  
   Select  
Table Name  =  D. Name,
Column name  =  A. Name,
Auto-increment?  =     Case     When     Columnproperty  (A. ID, A. Name,  '  Isidentity '  )  =  1     Then     '  √  '  Else     ''     End  ,
Primary Key  =    Case     When     Exists  (  Select     1     From  Sysobjects  Where  Xtype  =     '  PK '     And  Parent_obj  =  A. ID  And  Name  In  (
  Select  Name  From  Sysindexes  Where  Indid  In (
  Select  Indid  From  Sysindexkeys  Where  ID  =  A. ID  And  Colid  =  A. colid )))  Then     '  √ '     Else     ''     End  
  From  Syscolumns
  Left     Join  Policypes B  On  A. xusertype  = B. xusertype
  Inner     Join  Sysobjects d  On  A. ID  =  D. id  And  D. xtype  =     '  U  '     And D. Name  <>     '  Dtproperties  '  
  Where    Columnproperty  (A. ID, A. Name,  '  Isidentity  '  )  =  1 

The result is as follows:

2. Change the auto-increment column to a normal column:
The method is to first duplicate these tables, and then create a new table with the same name. The table structure is the same, but the auto-incrementing column attribute is removed, and then the renamed table data is imported into the new table, after the operation, we can see that there is no auto-incrementing column.
And then you can create a synchronization chain.

Method 2: BCP and tablediff
BCP, in combination with tablediff, adds a new table for the existing synchronization chain, and requires that the publishing end be kept online, and has the least impact on the publishing end's business. Its implementation steps are as follows:
1. Create the table structure to be added to the synchronization chain to the subscription end;
2. Prepare the BCP Export and Import script on the publisher, and export the newly added table to the file through BCP;
3. Add the new table to the synchronization chain (you can use scripts or directly operate on the Visual Interface) to pause the job for synchronizing data on the chain;
4. Run the BCP import script to import data to the subscription end;
5. Use tablediff to compare the data of the new table on the subscription end and the distribution end, and complete the differential data (after tablediff comparison, a script that cannot afford the differential data will be automatically generated and run on the subscription end );
6. Enable the paused synchronization link job.

The entire process has little impact on system services.

method 3: Initialize snapshot directly
This method is the simplest, but has a great impact on the business and takes a long time, it is basically not recommended (but this method is used by many people. If it is a small library, you can still consider it ).