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
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:
- First of all, its obvious to restart the snapshot agent to pick up new articles. However, on my test machine the snapshot agent didn't pick up newly added articles. I did some googling and found simular issues (Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script). I ended up with dropping and re adding the subscription.
- The @schema_option parameter for sp_addarticle is a bit of obscure to me. I used some tables that had timestamp columns and these were propagated to the subscriber as BINARY(8) and the rows could not be inserted at the subscriber ("Field Size too large"). See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56433c1-7f6f-4d2c-9593-3a11035fb1fe/cannot-update-a-timestamp-column-on-subscriber-using-transactional-replication?forum=sqlreplication and https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15