SQL Server strategy for high availability with batch drop/create updates 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.
First, don't drop the table to do this. You have two better options:


Use the MERGE command
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.