I came across a very strange issue with replication this weekend.
As a part of a release a new table was being created with extra columns and a view was created with the old table name that referenced the old column set (so as not to break old legacy applications). This table was replicated. The dev team provided us with scripts to setup replication. During the attempt to perform the snapshot I ran into some problems and so dropped the publication.
All of a sudden users started to complain that they could no longer see the view and couldn’t query the data. I looked, and sure enough the view was not there. Very strange. I told the users that I’d get it created again once replication was done. I redid the publication and the users called me saying not to worry, the view was back.
What the heck?
After digging through I found the cause of the issue. The dev had created the replication scripts and included a filter to eliminate certain columns. This replication then gets wrapped into an article view. In this case the dev had manually named the @view_name in sp_articleview to be the same as the view in the database. This caused SQL to drop the existing view and create it’s own. When the publication was dropped so was the view.
Here are the steps to repro this behavior:
Firstly I’m creating a view against the local table SQLMonitor.dbo.SQLBuilds
CREATE VIEW Bananas
AS
SELECT Build ,
[KB Article] ,
URL ,
Description ,
MajorBuild ,
MinorBuild
FROM SQLMonitor.dbo.SQLBuilds
A quick select shows data:
Now let’s create a publication for replication with column filters against the SQLBuilds table. We’ll specify the @view_name to be Bananas. I’m also including an @filter_clause to eliminate rows (I’ll explain that in a bit).
-- Enabling the replication database
USE master
EXEC sp_replicationdboption @dbname = N'SQLMonitor', @optname = N'publish',
@value = N'true'
GO
EXEC [SQLMonitor].sys.sp_addlogreader_agent @job_login = NULL,
@job_password = NULL, @publisher_security_mode = 1
GO
EXEC [SQLMonitor].sys.sp_addqreader_agent @job_login = NULL,
@job_password = NULL, @frompublisher = 1
GO
-- Adding the transactional publication
USE [SQLMonitor]
EXEC sp_addpublication @publication = N'Bananas Test',
@description = N'Transactional publication of database ''SQLMonitor'' from Publisher ''local''.',
@sync_method = N'concurrent', @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'false',
@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'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
EXEC sp_addpublication_snapshot @publication = N'Bananas Test',
@frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = NULL,
@job_password = NULL, @publisher_security_mode = 1
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'sa'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT AUTHORITYSYSTEM'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT SERVICESQLSERVERAGENT'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT SERVICEMSSQLSERVER'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'distributor_admin'
GO
-- Adding the transactional articles
USE [SQLMonitor]
EXEC sp_addarticle @publication = N'Bananas Test', @article = N'SQLBuilds',
@source_owner = N'dbo', @source_object = N'SQLBuilds', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'SQLBuilds', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'true',
@ins_cmd = N'CALL [sp_MSins_dboSQLBuilds]',
@del_cmd = N'CALL [sp_MSdel_dboSQLBuilds]',
@upd_cmd = N'SCALL [sp_MSupd_dboSQLBuilds]'
-- Adding the article's partition column(s)
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'Build', @operation = N'add', @force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'KB Article', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'MajorBuild', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'MinorBuild', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
-- Adding the article synchronization object
EXEC sp_articleview @publication = N'Bananas Test', @article = N'SQLBuilds',
@view_name = N'Bananas', @filter_clause = N'[KB Article] >0',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
This does it’s job, creates the publication with the article and everything. Now let’s query the view again…
Several columns are now missing and the view matches what’s specified in the replication script. The old view has been dropped and a new system view has been created:
Now dropping the publication also drops the view
-- Dropping the transactional articles
USE [SQLMonitor]
EXEC sp_dropsubscription @publication = N'Bananas Test',
@article = N'SQLBuilds', @subscriber = N'all', @destination_db = N'all'
GO
USE [SQLMonitor]
EXEC sp_droparticle @publication = N'Bananas Test', @article = N'SQLBuilds',
@force_invalidate_snapshot = 1
GO
-- Dropping the transactional publication
USE [SQLMonitor]
EXEC sp_droppublication @publication = N'Bananas Test'
GO
This appears to be a bug and not the behavior I would expect.
If I recreate the view and then attempt to create the publication again, this time without the @filter_clause I get an error stating the view already exists:
USE [SQLMonitor]
EXEC sp_addpublication @publication = N'Bananas Test',
@description = N'Transactional publication of database ''SQLMonitor'' from Publisher ''local''.',
@sync_method = N'concurrent', @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'false',
@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'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
EXEC sp_addpublication_snapshot @publication = N'Bananas Test',
@frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = NULL,
@job_password = NULL, @publisher_security_mode = 1
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'sa'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT AUTHORITYSYSTEM'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT SERVICESQLSERVERAGENT'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'NT SERVICEMSSQLSERVER'
GO
EXEC sp_grant_publication_access @publication = N'Bananas Test',
@login = N'distributor_admin'
GO
-- Adding the transactional articles
USE [SQLMonitor]
EXEC sp_addarticle @publication = N'Bananas Test', @article = N'SQLBuilds',
@source_owner = N'dbo', @source_object = N'SQLBuilds', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'SQLBuilds', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'true',
@ins_cmd = N'CALL [sp_MSins_dboSQLBuilds]',
@del_cmd = N'CALL [sp_MSdel_dboSQLBuilds]',
@upd_cmd = N'SCALL [sp_MSupd_dboSQLBuilds]'
-- Adding the article's partition column(s)
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'Build', @operation = N'add', @force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'KB Article', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'MajorBuild', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
EXEC sp_articlecolumn @publication = N'Bananas Test', @article = N'SQLBuilds',
@column = N'MinorBuild', @operation = N'add',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
-- Adding the article synchronization object
EXEC sp_articleview @publication = N'Bananas Test', @article = N'SQLBuilds',
@view_name = N'Bananas', @filter_clause = N'',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 3: There is already an object named ‘Bananas’ in the database.
Msg 21745, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 272
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the WHERE clause of a SELECT statement to produce a valid query.
Msg 3931, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 401
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
Something to be aware of if you are going to be playing with replication.
I’ve opened a Connect bug with Microsoft to try and get this fixed. Please go vote on it. And be careful with your replication.
SirSQL.net 