October 6, 2017 at 6:44 am
Hello guys. I was tested this replication and have several questions.Imagine i have a publisher db (source) which is 50 gb db and have alot CRUD in real time i need to separate this operations so i have to use replication.I need to have on source db only inserts and updates and select must be on subscriber. But i have a web api and this snapshot agent lock my tables sometimes. How can i avoid this locks, and is it possible to use 3rd party software to make schema changes because before this replication i used to Visual Studio tool to schema compare and works fine . With replication i cant do it because "IT IS Being used to for Replication. " Can i disable this snapshot job or i only need in first time to intilize subscriber or another stuff can initilize from backup and how it works. I read something for schema changes ssmo refer only t-sql alter table and must be running into UI.Thank you.
October 10, 2017 at 11:55 am
iseedeadpeople - Friday, October 6, 2017 6:44 AMHello guys. I was tested this replication and have several questions.Imagine i have a publisher db (source) which is 50 gb db and have alot CRUD in real time i need to separate this operations so i have to use replication.I need to have on source db only inserts and updates and select must be on subscriber. But i have a web api and this snapshot agent lock my tables sometimes. How can i avoid this locks, and is it possible to use 3rd party software to make schema changes because before this replication i used to Visual Studio tool to schema compare and works fine . With replication i cant do it because "IT IS Being used to for Replication. " Can i disable this snapshot job or i only need in first time to intilize subscriber or another stuff can initilize from backup and how it works. I read something for schema changes ssmo refer only t-sql alter table and must be running into UI.Thank you.
The snapshot agent is typically only used to initialize the publication. Then the log reader agent runs to replicate changes after initialization. It seems something may not be right if you have the snapshot agent running continuously for transactional replication. This documents the process:
Transactional Replication
You can initialize the subscriber with a backup or other processes such as SSIS. This explains the initialization process: \
Initialize a Transactional Subscription Without a Snapshot
Schema changes are done on the publisher and propagated to the subscriber. They need to be done using t-sql and not done through the GUI. More information on schema changes are in this document:
Make Schema Changes on Publication Databases
Sue
October 11, 2017 at 6:46 am
As Sue_H said snapshot should run continuously and used only to initialize entire publication or new articles added to publication.
About locking we had similar issue but when we moved to 2014. We used sync_method ='concurrent' on our publications (which is default) after we changed publication to sync_method= 'database snapshot' locking is gone even on the busiest systems. to check this you need to check syspublications on publisher, sync_method=3 is concurrent and 5 is database snapshot.
BartL
Replication Blog
October 27, 2017 at 1:42 am
Thanks alot guys !! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply