1

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.

  1. Drop Table, Create Table, Create Indexes
  2. use the MERGE statement to insert/update/delete records

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.

Josh Miller
  • 620
  • 3
  • 11
  • Have you looked into transactional replication? I'm don't have enough know-how on it to actually help more but it might be an option worth looking at – mxix Apr 14 '15 at 16:19
  • I have not looked into that in the context of this question, because log shipping is the method that our vendor supports for getting us a copy of the data. I assume that since the log-shipped copy is read-only, I could not use it for transactional replication. – Josh Miller Apr 14 '15 at 16:25

1 Answers1

3

TRUNCATE and INSERT would be more efficient than either dropping or merging.

Jon Tirjan
  • 3,556
  • 2
  • 16
  • 24
  • I like that. I will give that a go, and compare it to my drop/create. Thanks! (I will re-visit this question later today once I kick the tires on that). – Josh Miller Apr 14 '15 at 16:46
  • 1
    Thanks for this. From a clock perspective, both methods complete in ~12 seconds. I like how clean `TRUNCATE` and `INSERT INTO` are compared to rebuilding all of the indexes. I had to make a slight modification to my stored procedures to use CTE's rather than #Temp tables to get the 'INSERT INTO' to work but with that hurdle cleared I now have cleaner code. Thanks. – Josh Miller Apr 14 '15 at 17:44