0

I am setting up a Staging/PreProd environment by taking nightly backups of our Prod environment and applying them to our new PreProd environment. This part is working well. Unfortunately, we have a bunch of Replication set up in our environment. I have set up procs to remove the PreProd Replication right before the nightly Restores, and then re-create it after the Restores are complete. This works ok, but it takes a long time (3 - 4 hours) as some of the tables are quite large. I'd like to compress these large tables, but I don't want to double the work. The first step of applying the snapshot is to drop and recreate the destination table. The second step is to insert all the data. I could compress right after that, but then I'm essentially building the table twice, the first time uncompressed and then the second time with compression.

I have tested the @pre_snapshot_script parameter, but this gets applied before the step that drops and re-creates the table (the .pre and .sch files in the Snapshot folder). This will not work.

Are there any ways I can programmatically ensure the subscriber table is compressed BEFORE the snapshot data is applied to it?

Improve this question
4
  • I have a very similar setup, but one thing I'm not following is why you're dropping and recreating your Replication Publication...are your nightly backup restores against the Publisher or Subscriber?
    – J.D.
    Commented Aug 15 at 19:11
  • The restores are overwriting the Publisher databases.
    – Dave.Gugg
    Commented Aug 15 at 19:21
  • One of the schema_options while adding articles to the publication on the publisher database allows to carry over the compression settings. [0x400000000] [link] learn.microsoft.com/en-us/sql/relational-databases/… Try using the schema_option - 0x00000004080350DF
    – Ritesh Chawla
    Commented Aug 16 at 3:17
  • Although that doesn't perfectly fit the ask @RiteshChawla since I wouldn't necessarily want to just mirror the source compression, it will work for my purposes. I also noticed an option to TRUNCATE the destination table instead of DROP and CREATE, which would provide its own complications but could also be made to work. If you submit your comment as an answer, I will accept it. THANKS!
    – Dave.Gugg
    Commented Aug 16 at 18:35

1 Answer 1

Reset to default
1

One of the schema_options while adding articles to the publication on the publisher database allows to carry over the compression settings. [0x400000000] [link] learn.microsoft.com/en-us/sql/relational-databases/… Try using the schema_option - 0x00000004080350DF.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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