How to setup automatic Merge replication/How often run replication
-
Tuesday, November 13, 2012 4:38 PM
Doing replication manually is troublesome. How do I setup automatic Merge replication.
Do I do it in the following way during the Publication creation stage:
On the "Snapshot Agent" window put two checkmarks in the boxes next to the following:
( ) Create a snapshot immediately
( ) Schedule the Snapshot Agent to run at the following times
Click the "Change" button and set a "Frequency" of something like 1 day and "Occurs every:" 5 minutes?
How often should I run replication, please give a detailed explanation?
- Edited by Software Engineering Stuff Tuesday, November 13, 2012 7:05 PM
All Replies
-
Tuesday, November 13, 2012 5:13 PMModerator
You should look at setting this up using a powershell script. This will "automate" the deployment of merge replication.
The reason you might want to schedule a snapshot is if you frequently deploy new subscribers and want a snapshot ready to go as opposed to having to generate one when you create the subscription.
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
- Marked As Answer by Software Engineering Stuff Tuesday, November 13, 2012 5:58 PM
-
Tuesday, November 13, 2012 5:16 PMModerator If you mean how frequently should you synchronize - it depends on your synchronization requirements. Some people need up to date data as real time as possible. In this case you will run the subscribers/merge agent on a continuous basis. Others don't need the real time requirements and you can schedule it hourly or something which matches your real time requirements. Running continuous of frequent subscriptions has relatively high overhead, so it is best to schedule your subscription at whatever your needs are.
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
- Marked As Answer by Software Engineering Stuff Tuesday, November 13, 2012 5:58 PM
-
Tuesday, November 13, 2012 5:16 PM
Am I right in saying, the only way to automate Merge replication is through a powershell script.
Where can I find information about a powershell script. How to setup one up and use it.
Thanks,
-
Tuesday, November 13, 2012 5:18 PM
By continuous, you mean after one replication stops start the next one.
Thanks,
-
Tuesday, November 13, 2012 5:19 PMModerator
Here is a sqlcmd script you can use. http://msdn.microsoft.com/en-us/library/ms176103.aspx
By continuous I mean the merge agent for a subscription continually runs as opposed to being on a schedule.
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
- Marked As Answer by Software Engineering Stuff Tuesday, November 13, 2012 5:58 PM
- Unmarked As Answer by Software Engineering Stuff Tuesday, November 13, 2012 6:26 PM
-
Tuesday, November 13, 2012 5:23 PM
Here is a sqlcmd script you can use. http://msdn.microsoft.com/en-us/library/ms176103.aspx
How do I use this to setup continuous replication.
Thanks,
-
Tuesday, November 13, 2012 5:45 PMModerator
The Snapshot Agent is used to generate a snapshot to initialize the subscription. It does not run continuously.
The Merge Agent can be setup to run continuously though. This can be done using the Merge Agent -Continuous parameter or when you create the subscription either using the New Subscription Wizard or T-SQL using sp_addmergesubscription or sp_addmergepullsubscription using the following parameters to define the schedule:
@frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20121113, @active_end_date = 99991231
- Marked As Answer by Software Engineering Stuff Tuesday, November 13, 2012 6:14 PM
-
Tuesday, November 13, 2012 6:27 PM
Here is a sqlcmd script you can use. http://msdn.microsoft.com/en-us/library/ms176103.aspx
I don't see how to set this up for continuous replication. I don't see a "frequency" or "time" parameter.
-
Tuesday, November 13, 2012 6:30 PMModeratorIt is not there. I misunderstood what you meant. I thought you meant - how to setup automatic Merge replication. What I now see you meant was how do I make my merge agent run all the time.
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
-
Tuesday, November 13, 2012 6:38 PM
Why would I use automatic Merge replication if I just have one database which is being replicated.
Thanks
-
Tuesday, November 13, 2012 6:49 PMModerator
I am sorry, its very hard for me to understand that there is only one database involved. Your initial req looks something like you want to :"How to setup automatic Mergre replication" Which to me means - how do I automatically set up merge publications.
My apologies for getting it so wrong:)
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
.png)
