Have a production SQL Server 2017 db that is populated nightly using sqoop from another source beyond our control. Would like to fully sync certain tables without bringing down the app (right now we upsert but this is insufficient as there are deletes in the original data source). The quantity of data is fairly small (a dozen tables totaling records in the single digit million rows)

Is there a replication strategy that will allow us achieve this? Maybe something along the lines of having a pair of rotating DBs, where at any point in time one of which is pointed to production and the other is getting batch updated.

  • 1
    You need to provide more details: why is upserting insufficient? What version of SQL server? Quantity of data involved? – kevinsky Jan 20 at 15:21
  • Thanks! Updated with SQL Server 2017, small data, and note about source records being deleted – Eugene Jan 20 at 15:46
  • Why do you think you need to bring down your application while doing inserts, updates, or deletes? – mustaccio Jan 20 at 15:51
  • 1
    Maybe I should ask a more general question. What is a good strategy for making sure table B is identical to table A when table A is out of our control? – Eugene Jan 20 at 16:27
  • 1
    Maybe Partition Switch? blog.pragmaticworks.com/… codingsight.com/switching-out-table-partitions-a-walkthrough This allows you to do a bunch of updates on Table B, and then use a meta statement to move that partition to Table A with practically no downtime. – Conrad S. Jan 20 at 18:39

First, don't drop the table to do this. You have two better options:

  1. Use the MERGE command
  2. TRUNCATE the table before inserting, don't DROP and CREATE.

The main limitation on the TRUNCATE...INSERT pattern is permissions, since a simple read or update permission grant will not allow for TRUNCATE. ALTER permission on the table is needed to use this command. It also has some other specific limitations, since you can't use TRUNCATE IF:

  • The table is referenced by a FOREIGN KEY constraint (unless that FK reference is from the same table)

  • The table that is a participant in an indexed view.

  • The table is a source for transactional or merge replication.

If none of these conditions are true for the table, and the login performing the task has sufficient permissions, then you are good to go with TRUNCATE...INSERT processing.

MERGE only needs insert, update, and delete permissions. The syntax can be a bit (understatement) confusing until you understand what is happening. However, a quick search on the net will reveal a number of good tutorials for learning the command.

You can also technically write separate DELETE, UPDATE, and INSERT commands for the DUI update. However, this needs to be within an explicit transaction, and is more difficult to maintain than the MERGE, which does not need to be explicitly within a transaction.

|improve this answer|||||
  • Thanks! How do you do the TRUNCATE...INSERT without bringing down the app? – Eugene Jan 20 at 21:51
  • The only way I can think that you can do that is a trick I used in a previous job. There was a table that was central to their web app that needed to be rebuilt from scratch each evening (interfacing with third party DBs, I'm afraid.) The DB literally had two copies of the table Table_1 and Table_2, and a view Table. When the build started, it checked which table was in use by examining the source of the view, then rebuilt into the other table, and as the final step, recreated the view pointing to the new table. Code always pointed to the view, not the actual tables. – Laughing Vergil Jan 20 at 23:53
  • 1
    The MERGE logic will not require taking down the application, and so should be your first choice. However, depending on how the Merge is coded, you could have locking/blocking issues during the MERGE. – Laughing Vergil Jan 20 at 23:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.