December 12, 2007 at 8:44 am
Hello SQLers,
I am using snapshot replication on an SQL Server 2005 machine to replicate Postal/Zip Code data and other very static data from one central database to another.
To keep the admin overhead low and because changes are so infrequent, I decided to go with Snapshot Replication (PUSH).
Because of the size of the Postal/Zip Code table (almost 900,000 rows), I am worried about the TLog swelling at the Subscriber database.
Can I change the Recovery Model for the Subscriber database before the distribution agent runs, i.e.
ALTER DATABASE MySubDB SET RECOVERY BULK_LOGGED
so that the TLog doesn't swell, and then change it back after?
Has anyone tried this, or had any similar experience?
Any input would be greatly appreciated.
Thanks,
Simon Doubt
December 15, 2007 at 4:42 am
Hi,
The recovery model of the subscriber is irrelevant. I use transactional replication with subscribers with simple recovery model and works fine, so don't worry about it.
-- Erik http://blog.rollback.hu
December 17, 2007 at 7:30 am
Thanks so much for your reply, Erik.
When you say that the recovery model for the replicated db is irrelevant, I'm assuming you mean because the entire db is being replicated. Unfortunately, that's not my case. The subscriber DB is only receiving a few tables from the publisher, the rest are independent.
After some searching and tinkering, I think I may have found the right place to set the recovery model to simple, and then back to full again: in the Publication Properties page, on the SNAPSHOT page, in the Run Additional Scripts boxes (attached screenshot).
I figure that as long as I do a full update after the snapshot is applied and the recovery model is set back to full, I've covered my bases..... right?
- Simon Doubt
December 17, 2007 at 9:00 am
Hi,
The recovery model of the subscription database is absolutely irrelevant from replication perspective (of course you may have to set it to full from business reasons), because the subscription database basically just receives insert/update/delete commands from the distributor - these will work independently of the recovery model or the filtering of the replication. Just give it a try with a test db and you'll see that it works with all the recovery models. Recall, I use it with simple recovery model, I have 14 different subscriptions in a single db. What more: I republish some tables, with filtering, and the recovery model is still simple...
-- Erik http://blog.rollback.hu
December 17, 2007 at 11:33 am
Thanks for the reply, Erik, your setup definitely sounds interesting.
Maybe I didn't make myself very clear regarding my situation. I understand that Replication uses IUD statements on subscriber tables, but for tables in the subscriber database that are not controlled by replication (i.e. are not articles in any publication), I would like to use the full recovery model. I think I have found what I need though - I was just curious to know if anyone else had been alternating their recovery models prior to and after applying snapshots.
Cheers,
Simon Doubt
December 18, 2007 at 1:38 am
Oh, sorry, I missed the fact that you'd like to run your db in full reco. In this case, your solution is a good solution if it's ok with the other tables of the DB 🙂
-- Erik http://blog.rollback.hu
December 20, 2007 at 4:16 pm
Simon,
I use BULK_LOGGED before applaying the snapshot and it *DOES* makes a difference, Simple is good too. Full and snapshot is a bad combination for large tables...
Just my $0.02
* Noel
December 21, 2007 at 6:55 am
Great, thanks for your input, Noel - it's good to know someone is doing what I'm doing!
I'm assuming that you are using scripts to change your recovery model - are you inserting them through the Publication Properties page | SNAPSHOT | Run Additional Scripts option?
(See my attached JPEG earlier in the post for clarification)
Or are you scripting elsewhere?
- Thanks for your reply, and Joyeux Noel (... sorry, .... I'm sure you've heard that one about a million times...)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply