Table lock in snapshot replication

Question

Hi Guys .. i  am new to replication . I need your help.

My scenario:

  I have to replicate data from 12 Source database to one Destination database. Replication should happen once in a day.

  I found that transactional and merge replication will not work in my scenario. I choosed snap shot replication.

  After some day i found that tables are getting locked in snap shot replication.

  Is there any other way to use snap shot replication without table lock ?

 

 

Answer 1

Unless you use the sync_method of database snapshot in sp_addpublication  - no there is no way to avoid the locks.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Thanks for your  reply Hilary.

I will try..

Answer 3

BTW  - this is an Enterprise Edition only feature.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hi Hilary,

 Can it be achieve by setting below property in source database?

  ALTER DATABASE [DATABASE_NAME] SET ALLOW_SNAPSHOT_ISOLATION ON;

 ALTER DATABASE [DATABASE_NAME] SET READ_COMMITTED_SNAPSHOT ON;

  

Answer 5

No, that may help however.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941