1

Is it possible to automatically add new dbo objects to SQL transactional replication? Is there some out of the box solution for that or that can be achieved by T-SQL? Thanks

1

I was kinda intruiged by your question, since we use transactional replication a lot. So I tried to come with a script to answer your question (run inside the publication db):

-- your publication name
DECLARE @PubName SYSNAME = 'publication name'

-- your subscriber
DECLARE @subscriber SYSNAME = 'target server name'
DECLARE @subscriberDatabase SYSNAME = 'target db name'

 -- Get publication id 
DECLARE @PubId INT
SELECT @PubId = pubid
FROM   syspublications
WHERE  name = @PubName 

-- Find tables in dbo schema, not in current publication
DECLARE tablecursor CURSOR fast_forward FOR
  SELECT TOP(2) o.name  -- for testing I used a TOP clause
  FROM   sys.objects o
  WHERE  type = 'U'
         AND schema_id = SCHEMA_ID('dbo')
         AND object_id NOT IN (
            SELECT objid
            FROM   sysarticles
            WHERE  pubid = @PubId
         )

DECLARE @tablename AS sysname

OPEN tablecursor

FETCH next FROM tablecursor INTO @tablename

WHILE @@FETCH_STATUS = 0 BEGIN
    -- Adding the transactional article
    EXEC sp_addarticle 
            @publication = @PubName
        ,@article = @tablename
        ,@source_owner = N'dbo'
        ,@source_object = @tablename
        ,@type = N'logbased'
        ,@schema_option = 0x08030F3--0x80030F3
        ,@vertical_partition = N'true'
    -- All columns
    EXEC sp_articlecolumn 
        @publication = @PubName, 
        @article = @tablename;


    FETCH next FROM tablecursor INTO @tablename
END

CLOSE tablecursor

DEALLOCATE tablecursor  

EXEC sp_dropsubscription 
  @publication = @PubName, 
  @article = N'all',
  @subscriber = @subscriber;

exec sp_addsubscription 
     @publication = @PubName
    ,@subscriber = @subscriber
    ,@destination_db = @subscriberDatabase
    ,@subscription_type = N'Push'
    ,@sync_type = N'automatic'
    ,@article = N'ALL', @update_mode = N'read only'
    ,@subscriber_type = 0

exec sp_addpushsubscription_agent 
    @publication = @PubName
    ,@subscriber = @subscriber
    ,@subscriber_db = @subscriberDatabase
    ,@job_login = null
    ,@job_password = null
    ,@subscriber_security_mode = 0
    ,@subscriber_login = N'some login'
    ,@subscriber_password = 'some (matching:)) password'
    ,@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'

-- Re init subscription(s)
--EXEC sp_reinitsubscription 
--   @publication = @PubName
--  ,@subscriber =  @subscriber
--  ,@invalidate_snapshot = 1

-- Start snapshot agent
EXEC sp_startpublication_snapshot @publication = @PubName

Now I found out the hard way, there are some things that can go wrong:

1
  • Many thanks to your response, it gave me a few ideas that I will try. For us is also very important not to snapshot each table again. Will update the post after my shots.
    – DraganB
    Mar 23 '20 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.