SQL Server 2005 Transactional Replication without initial snapshot ?!

Question

SQL Server 2005 Transactional Replication without initial snapshot ?!

Viewing 7 posts - 1 through 8 (of 8 total)

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

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

Did you confirm that this is happening? The reason I am asking is that in SQL Server 2005 it will use Concurrent snapshot by default, see http://msdn.microsoft.com/en-us/library/ms151706(SQL.90).aspx.

🙂

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Atze Ackermann SSC-Addicted Points: 427 More actions · Answer 2

Thanks for the quick response - I was reading this document before but somehow missed that point (it seems locking is not really a problem while creating a snapshot for transactional replication - and to answer your question, no I did not confirm / tried that out since it is a production DB).

Nevertheless I still want to know if there is a way to go around creating that snapshot because

a) of the space constraints we have (this would be a 250+ GB snapshot)

and

b) we would not need the snapshot for initializing if we use a backup as base / source for the subscriber (as far as I understand it)

Chandrachurh Ghosh Hall of Fame Points: 3001 More actions · Answer 3

While setting up your replication you surely can use a subscription with no syc......for this typeof setup, only new data will be moved.....

Pitfalls -

1> Sometimes the replication stored procedures are not properly......don't worry they can be created separately

2> Errors might occur......needs monitoring initially.......you can skip those errors or might make proper changes as corrective measures - errors might include : Row not found during deletion or update....

3> If you want a ditto replication, you need to make sure that after you have taken the backup till the replication setup is complete, the primary database is read-only.....

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.

Atze Ackermann SSC-Addicted Points: 427 More actions · Answer 4

Thanks for the heads up about the pitfalls - i will for sure have a look at those ... 🙂

noeld SSC Guru Points: 96590 More actions · Answer 5

noeld

SSC Guru

Points: 96590

November 5, 2008 at 2:47 pm

#894545

You can use init with backup option...

* Noel

Chandrachurh Ghosh Hall of Fame Points: 3001 More actions · Answer 6

Setting up a subscriber using a backup -

1> Setup the publication, if not already setup, as usual.

2> Restore a latest backup of the publishing database as your subscribing database.

3> Setup subscription with the above database

If using GUI (Management Console) - you just need to uncheck the initialize checkbox

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

there is also an option to run the snapshot to a DVD and I think an external hard disk is OK then use that to initialize the sub