All geek questions in one place

Script to sync subscribers

When I want to force the renewal of subscribers to publish a merge replication, I can enter the Replication Monitor, right-click on the subscription and select “Start Synchronization”. I would like to be able to script this (using vba / vbscript or command line). I would also like for users to be able to run the script (what permissions would be required if they exist?).

I am sure that they asked about this and spoke many times before, but my attempts at Googling were empty.

+3
source share
2 answers

Assuming the publication already exists (and a valid snapshot is available in a valid folder), here are the T-SQL and command line instructions that you must initiate and maintain a subscription to both the publisher and the subscriber. We use this code to start web replication from scratch (without a database on the subscriber side). After the subscription has been announced on both sides through stored procedures, you need to run (on a regular basis) the synchronization command line command on the subscriber side (see below).

-, , .. , , . , . (, ), , . , , , , . , .

PS: SQLEXPRESS . SQL . SQLEXPRESS .

  • : sp

    exec sp_addmergesubscription 
    @publication = N'myPublication', 
    @subscriber = N'mySuscriber\SQLEXPRESS', 
    @subscriber_db = N'myDatabaseOnMySubscriber', 
    @subscription_type = N'pull', 
    @subscriber_type = N'local', 
    @subscription_priority = 0, 
    @sync_type = N'Automatic'
go

  • : sp

    exec sp_addmergepullsubscription 
    @publisher = N'myServerName', 
    @publication = N'myPublicationName', 
    @publisher_db = N'myMainDatabase', 
    @subscriber_type = N'Local', 
    @subscription_priority = 0, 
    @description = N'', 
    @sync_type = N'Automatic'

exec sp_addmergepullsubscription_agent
    @publisher = N'myServername', 
    @publisher_db = N'myMainDatabase', 
    @publication = N'myDatabaseOnMySubscriber', 
    @distributor = N'myServerName', 
    @distributor_security_mode = 1, 
    @distributor_login = N'', 
    @distributor_password = N'', 
    @enabled_for_syncmgr = N'True', 
    @frequency_type = 4, 
    @frequency_interval = 1, 
    @frequency_relative_interval = 1, 
    @frequency_recurrence_factor = 0, 
    @frequency_subday = 8, 
    @frequency_subday_interval = 1, 
    @active_start_time_of_day = 0, 
    @active_end_time_of_day = 235959, 
    @active_start_date = 0, 
    @active_end_date = 0, 
    @alt_snapshot_folder = N'', 
    @working_directory = N'', 
    @use_ftp = N'True', 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1, 
    @publisher_login = N'', 
    @publisher_password = N'', 
    @use_interactive_resolver = N'False', 
    @dynamic_snapshot_location = N'', 
    @use_web_sync = 1, 
    @internet_url = N'https://mySecuredWebPage:myOpenPort/myPublicationName/replisapi.dll',
    @internet_login = N'myDomain\myUserName', 
    @internet_password = null, 
    @internet_security_mode = 0, 
    @internet_timeout = 300
go

  • BAT

    "C:\Program Files\Microsoft SQL Server\90\COM\replmerg.exe"
    -Publisher [myServerName]  
    -PublisherDB [myMainDatabase]  
    -Publication [myPublicationName] 
    -Distributor [myServerName] 
    -Subscriber [mySubscriber\SQLEXPRESS] 
    -SubscriptionType 1 
    -SubscriberSecurityMode 1 
    -SubscriberDB [myDatabaseOnMySubscriber] 
    -InternetURL [https://mySecuredWebPage:myOpenPort/myPublicationName/replisapi.dll] 
    -InternetLogin [myDomain\myUserName] 
    -InternetPassword [myPassword]
+8

, SQL Server Profiler - SQL ?

0

Source: https://habr.com/ru/post/1732069/

More articles:


All Articles