Snapshot Replication Locking Publisher

Question

Post reply

Snapshot Replication Locking Publisher

DBANewbie

Hall of Fame

Points: 3095
More actions
May 13, 2010 at 5:33 am

#218890

I have recently set up snapshot replication to take a copy of our production database onto our reporting server each night.
The snapshot is generated each morning at 2AM and the subsriber is scheduled to sync at around 4AM.
Occasionally i will need to sync the subscriber again during a work day but if i do this the database being published is locked out and as this is our production server this is not acceptable.
I would have assumed that once the snapshot is generated there would be no need to lock the publisher while synchronising the subscribers.
Is there some setting which locks the publisher by default which i am not aware of?
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

You probably chose the wrong replication method. Why can't you do transactional instead?

* Noel

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

I kinda suspect that when you sync that databases during the day, you create a new snapshot. The generation of the snapshot will be what is causing the locks.

To ensure that a snapshot is transactionally consistent, the snapshot agent takes out a read lock (can't remember exactly what type of lock but that doesn't matter for this discussion) which is held until the snapshot has been generated and relevant information written to the distribution database. There is no way around this but you may be able to reduce the duration of the lock by taking steps to descrease the time required for the generation of the snapshot.

The least impact step is to ensure that the snapshot is generated using the native formats. This is default so it may not change anything for you.

The next step is to break up the publication into several smaller publications. By doing this, the time required for the generation of each individual snapshot is smaller so the impact on your production users is smaller. It is up to you to determine whether this is an option and to decide how to split up the publication - perhaps on size or letters of the alphabet - whatever works for you.

DBANewbie Hall of Fame Points: 3095 More actions · Answer 3

Thanks for your help so far.

I chose snapshot replication because our reporting database needs to be a snapshot of out live database taken at the start of each working day. Perhaps i should look at simply shipping the logs across at the end of each day rather then taking a new snapshot each day.

I am in agreement that it seems to create a new snapshot each time i synchronise but what i actually want to do is synchronise my subscribers with the snapshot that was created at the start of the day again.

Is this possible?

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

jodieevans (5/14/2010)
Thanks for your help so far.
I chose snapshot replication because our reporting database needs to be a snapshot of out live database taken at the start of each working day. Perhaps i should look at simply shipping the logs across at the end of each day rather then taking a new snapshot each day.
I am in agreement that it seems to create a new snapshot each time i synchronise but what i actually want to do is synchronise my subscribers with the snapshot that was created at the start of the day again.
Is this possible?

That might be a better option. The one "problem" with transactional replication is that it requires all your tables to have primary keys ..... which is a limiter in some of our databases.