0

We have a database that we changed during the time in terms of adding/removing articles, columns to replication articles in a publication. Now when we script out that publication we have lines of code that are telling us that vertical partitioning is in question, like this:

exec sp_addmergearticle @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @source_owner = N'dbo', @source_object = N'Customer_DELETE_LOG', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'true', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_DATE', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_USER', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'COLUMN_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'ROW_ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'ROW_DELETED_VALUE', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'STATEMENT', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'PROCESSED', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'BATCH_ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'SERVER_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'APP_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

Note here that @vertical_partition = N'true' and also note that we never do any vertical partition at least not intentionally.

Now the real issue here is when there are some computed columns in a table, and those computed columns are at the beginning of the table/article script (that includes vertical partitioning) processing will skip those columns! Because those columns are added to the replication article before their referenced column and thus they will break that sp_mergearticlecolumn statement. To avoid this (without checking every statement among 400) is to replace @vertical_partition = N'true' with @vertical_partition = N'false' and then run the whole thing. And this will work. BUT when you try to script out the whole thing those vertical partitioning statements will come again! WHY?

So again:

  1. what vertical partitioning actually means and why does SQL Server add it to the replication scripts?
  2. how to check if a table is vertically partitioned?

Thanks Dejan

UPDATE

I am digging on this now deeper, and started looking on tables themselves and noticed folowing:

Some tables were missing some indexes, like 1,2,3,5 -> where column at index 4 was probably deleted at a point of time. Could that cause this? Could that cause the publication scripting procedure to think that this is a partitioned table? If so then the Microsoft developers didn't finish this properly.

Dejan Dozet
  • 948
  • 10
  • 26

1 Answers1

1

"what vertical partitioning actually means"

Vertical partitioning for a table article means that only specific columns and not all columns of the table are published.

why does SQL Server add it to the replication scripts

Most likely there are columns which are deselected/dropped from the article/table. Could you check in the article properties if there are columns which are deselected/not published?

Script wise, you could achieve the same by creating an article with @vertical_partition = N'false' and later @operation = N'drop' a column

--no vertical partitioning, all columns of the table are published
exec sp_addmergearticle @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @source_owner = N'dbo', @source_object = N'Customer_DELETE_LOG', @type = N'table', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', 
@vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0;

--drop/deselect one column from the article
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_USER', @operation = N'drop', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
GO

--if you script the publication anew, it will have article @vertical_partition = N'true' and all columns @operation = N'add' except for the LOG_USER column (which is excluded from publication and does not appear in the script at all)
lptr
  • 1
  • 2
  • 6
  • 16
  • thanks a lot for this answer, all columns were replicated in all cases, I've created a script then replaced all vertical_partition = N'true' with vertical_partition = N'false' and run it again and that way created publication (although leaving exec sp_mergearticlecolumn... which produce some warnings that columns already exists). Now when I script that way created publication script comes out again with some vertically partitioned tables, and for me this is very strange. Your thoughts? – Dejan Dozet Jan 05 '20 at 12:06
  • 1
    well, in that case, besides the warning (the column already exists in the VERTICAL PARTITION ), sp_mergearticlecolumn of an existing column changes the article/table to a "partitioned" one. I guess it is a side effect. – lptr Jan 05 '20 at 12:49
  • which is documented: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-mergearticlecolumn-transact-sql : Partitions a merge publication vertically. This stored procedure is executed at the Publisher on the publication database. – lptr Jan 05 '20 at 12:58
  • Ok, then I will drop subscription, create publication script, drop publication, fix the script, execute the script again so we will see. To much effort for nothing I think – Dejan Dozet Jan 05 '20 at 13:09
  • indeed. The table is marked as vertically partitioned but all columns are published. – lptr Jan 05 '20 at 14:28
  • once again thanks for your support, I appreciate that! – Dejan Dozet Jan 05 '20 at 14:37