locked
Configure Peer-Peer Replication with T-SQL RRS feed

  • Question

  • Hi Team,

    How to configure Peer-Peer Replication with T-SQL in SQL Server 2016?

    I am familiar with GUI but not with T-SQL.

    Please help us.

    Tuesday, July 28, 2020 6:14 AM

Answers

  • Hi VijayKSQL,

    Is the reply helpful?

    BR,

    Mia

    • Marked as answer by VijayKSQL Monday, August 3, 2020 7:45 AM
    Friday, July 31, 2020 1:30 AM

All replies

  • Every GUI assistant do have a scripting option, so setup replication using GUI and let the script generate, that you have the complete T-SQL statement for it; see Script objects in SQL Server Management Studio

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 28, 2020 6:41 AM
  • Correct.

    As i know this is only possible with other replications (Transaction,Megre and Sanpshot).

    I tried But in Peer-Peer i can only script publisher config. but not Subscriber. 

    Tuesday, July 28, 2020 6:46 AM
  • You can choose the Peer-To-Peer Publication and then right click and then choose 'generate scrips' .


    If your issue is solved.You can choose to mark the helpful reply as answer to close your case.Otherwise, your case will always be in the open state.

    Tuesday, July 28, 2020 7:23 AM
  • Reference :initialize-sql-server-replication-using-a-database-backup

    Following next steps to add subscription and then generate scrips: 

    Backup Database on Publisher

    BACKUP DATABASE test TO DISK = '\\PUB\files\test.bak' WITH FORMAT

    Restore Database on Subscriber

    USE [master]
    RESTORE DATABASE [test] FROM  DISK = N'\\PUB\files\test.bak' WITH  FILE = 1,  
    MOVE N'test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSUB\MSSQL\DATA\test_Data.mdf', 
    MOVE N'test_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSUB\MSSQL\DATA\test_Log.ldf',  
    NOUNLOAD,  REPLACE,  STATS = 5

    Create Subscription Using T-SQL

    --At the publisher, run the following command 
    USE [test]
    GO
    EXEC sp_addsubscription 
    @publication = PTPTEST, 
    @subscriber = 'SUB\SQLSUB', 
    @destination_db = test,
    @sync_type = 'initialize with backup',
    @backupdevicetype ='disk',
    @backupdevicename = '\\PUB\files\test.bak'

    Generate the scripts:


    If your issue is solved.You can choose to mark the helpful reply as answer to close your case.Otherwise, your case will always be in the open state.


    • Edited by MIAOYUXI Tuesday, July 28, 2020 8:59 AM
    Tuesday, July 28, 2020 8:58 AM
  • Hi VijayKSQL,

    I'm following this open case now, and is the reply helpful?

    BR,

    MIAOYUXI


    If your issue is solved.You can choose to mark the helpful reply as answer to close your case.Otherwise, your case will always be in the open state.

    Wednesday, July 29, 2020 1:04 AM
  • Still i didn't find the script.

    If you don't mine if you have one, Please provide, so that i will modify based on my requirement.

    Wednesday, July 29, 2020 4:35 AM
  • Hi VijayKSQL,

    Code on my side as next:

    -- Enabling the replication database
    use master
    exec sp_replicationdboption @dbname = N'test', @optname = N'publish', @value = N'true'
    GO
    
    exec [test].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
    GO
    -- Enabling the replication database
    use master
    exec sp_replicationdboption @dbname = N'test', @optname = N'merge publish', @value = N'true'
    GO
    
    -- Adding the transactional publication
    use [test]
    exec sp_addpublication @publication = N'PTPTEST', @description = N'Peer-to-Peer publication of database ''test'' from Publisher ''PUB\SQLPUB''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'true', @enabled_for_p2p = N'true', @enabled_for_het_sub = N'false', @p2p_conflictdetection = N'true', @p2p_originator_id = 100, @p2p_continue_onconflict = N'true'
    GO
    exec sp_grant_publication_access @publication = N'PTPTEST', @login = N'sa'
    GO
    exec sp_grant_publication_access @publication = N'PTPTEST', @login = N'DPS\Administrator'
    GO
    exec sp_grant_publication_access @publication = N'PTPTEST', @login = N'NT SERVICE\Winmgmt'
    GO
    exec sp_grant_publication_access @publication = N'PTPTEST', @login = N'NT SERVICE\SQLWriter'
    GO
    
    -- Adding the transactional articles
    use [test]
    exec sp_addarticle @publication = N'PTPTEST', @article = N't1', @source_owner = N'dbo', @source_object = N't1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDF, @identityrangemanagementoption = N'manual', @destination_table = N't1', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbot10654681651]', @del_cmd = N'CALL [sp_MSdel_dbot10654681651]', @upd_cmd = N'SCALL [sp_MSupd_dbot10654681651]'
    GO
    
    -- Adding the transactional subscriptions
    use [test]
    exec sp_addsubscription @publication = N'PTPTEST', @subscriber = N'SUB\SQLSUB', @destination_db = N'test', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
    exec sp_addpushsubscription_agent @publication = N'PTPTEST', @subscriber = N'SUB\SQLSUB', @subscriber_db = N'test', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
    GO
    
    

    BR,

    MIAOYUXI

    Wednesday, July 29, 2020 9:55 AM
  • Hi VijayKSQL,

    Do you try the script?

    Is it helpful?

    BR,

    MIAOYUXI

    Thursday, July 30, 2020 12:58 AM
  • Hi VijayKSQL,

    Is the reply helpful?

    BR,

    Mia

    • Marked as answer by VijayKSQL Monday, August 3, 2020 7:45 AM
    Friday, July 31, 2020 1:30 AM