none
Snapshot replication issue

    Frage

  • Hi,

    I build snapshot replication from Server-A to Server-B and its working fine. But while building the same replication in reverse order on same database from Server-B to Server-A its giving me error while running Subscription.

    Cannot drop the table '' because it is being used for replication.

    I just want to build replication like both way with same db using snapshot only. Pls suggest me if its possible or not.

    Mittwoch, 27. Februar 2013 07:09

Antworten

  • I can't understand why you would want bi-directional snapshot replication. Basically you are taking a read only set of data, sending it to another server where it might be written to and then at some point in time sending everything back again, changes and rows which are not changed.

    Bi-directional transactional replication would be much more efficient.

    However, if you really want to do bi-directional snapshot replication, use the wizard and when you get to the articles dialog. Click on the table you want to be able to be copied back and forth and select Article Properites, and set properties of highlighted article, then in Action Name if in use property, select Truncate all data in the existing object or Delete data. If article has a row filter, delete only data that matches the filter.

    Both should work. You will need to do this on both sides.


    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

    • Als Antwort markiert MassoudAhmed Donnerstag, 28. Februar 2013 12:47
    Mittwoch, 27. Februar 2013 15:35

Alle Antworten

  • If you want to replicate same table in both ways use merge instead snaphot replication.

    everything is a matter of probability...


    • Bearbeitet pen_2 Mittwoch, 27. Februar 2013 10:04
    Mittwoch, 27. Februar 2013 10:03
  • But i m using SIMPLE recovery model for database. So is there any other way to justify this requirement ?
    Mittwoch, 27. Februar 2013 13:07
  • Recovery models have nothing to do with replication mode unlike mirroring...so, you are good there....

    Hope it Helps!!

    Mittwoch, 27. Februar 2013 13:14
  • I can't understand why you would want bi-directional snapshot replication. Basically you are taking a read only set of data, sending it to another server where it might be written to and then at some point in time sending everything back again, changes and rows which are not changed.

    Bi-directional transactional replication would be much more efficient.

    However, if you really want to do bi-directional snapshot replication, use the wizard and when you get to the articles dialog. Click on the table you want to be able to be copied back and forth and select Article Properites, and set properties of highlighted article, then in Action Name if in use property, select Truncate all data in the existing object or Delete data. If article has a row filter, delete only data that matches the filter.

    Both should work. You will need to do this on both sides.


    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

    • Als Antwort markiert MassoudAhmed Donnerstag, 28. Februar 2013 12:47
    Mittwoch, 27. Februar 2013 15:35
  • we have heavy DML transactions going on to database and thats why we are using SIMPLE recovery, that's d another part.

    But we want to do some DML transactions on other side as well as per requirement and want to back those data to main database. Hence I have setup Merge replication and I think its working but still requirement is snapshot due to some restriction of Merge repl (like Truncate table and Insert data).

    pls suggest.

    Donnerstag, 28. Februar 2013 06:11
  • Now its working with snapshot replication with truncate option.

    Thanks a lot Hilary Cotter....

    Donnerstag, 28. Februar 2013 12:46