I have a "log shipped" copy of a database that lives at a third party. Log shipping runs every 15 minutes at which time all connections to the database are dropped. The database is used for reporting purposes.
I have decided to pull some of the data from the log shipped (read only) database into a new database refreshed nightly. This will allow users to connect to the new database without risk of loosing connectivity due to log shipping. (It also allows more granular security permissions to be used, since the read-only copy can't be edited)
I can think of 2 patterns to accomplish this.
I have implemented the solution using method 1 above, and it works just fine.
It feels a bit heavy to me to drop all of the data every day. Are there any side effects to method 1 above that should push me over to using method 2?
To provide a sense of scale, I am only syncing 3 tables,
Table 1 - 38 columns - 13,110 rows
Table 2 - 82 columns - 17,421 rows
Table 3 - 22 columns - 249 rows
The resulting database is ~1.3 GB. (There are some other tables in there as well)
I would appreciate guidance on Method 1 vs 2, and whether there is another method that I am not thinking about.
TRUNCATE
and INSERT
would be more efficient than either dropping or merging.