3

I have set up an MSSQL merge replication on a large database. One of the tables has 1.5 billion records and causes the snapshot agent to timeout on this table. So first I exported the data of the table to a .csv file, truncated the table and then run the snapshot agent again. Now with success. After the snapshot was made, and the subscriber is synchronized, I want to re-import the data from the .csv file. However, the merge replication uses automatic "Identity Range Management" with fixed values which I cannot change. One of these values contain the next starting value. Each new record inserted into the table will be incremented by 1 starting from this value. However, the exported data, of course, already has their ID value and I want to import these corresponding ID's again too. I already set the Publisher/Subscriber range size to 10 billion but only this won't fix the problem.

How will I ever be able to re-import this data with automatic identity range management?

Thanks in advance.

user1067807
  • 31
  • 2

1 Answers1

1

You can do the following:

  1. Ensure that IDs in .CSV file within range assigned to Publisher.

  2. Run SET IDENTITY_INSERT <Table Name> ON - this will allow to insert identity values

  3. Insert data into the table. There 2 ways you can do it:

    • Insert whole file in one go

OR

  • Insert a subset of rows, then make sure it is replicated: create SSIS package, which for every 100k or 1M rows will insert them, then run replication agent.

Here is a trick: rows inserted with Bulk_Insert or BCP are not picked up by replication agent. So before running replication agent execute sp_addtabletocontents Table_Name or run BCP with FIRE_TRIGGERS option.

4 . Run SET IDENTITY_INSERT OFF` - to disallow inserting identity values

You can test it on 10 sample rows and then reset both systems.

Stoleg
  • 8,972
  • 1
  • 21
  • 28