Exclude the MSmerge* objects in Schema Comparison for Merge Replication(SQL Server 2005 and above)
System that uses Merge replication in SQL server (2008 R2)
2 databases to perform a Schema Compare have merge replication enabled. The issue is that SQL server creates a very large number of database objects (tables, procedures, triggers, etc.) related to the merge replication configuration. These objects are unique to each instance and when running a schema comparison it would be great if I could exclude them completely. I understand I can go through and uncheck each object to exclude it prior to running the compare, however this is very time consuming due to the number of objects.
-- Notes --
Looks like SQL Server 2000 and below doesn't have a way to see if an object is replicated, but SQL Server 2005 and above does. See the links below. I believe that the replication status of the Triggers and Indexes are bound to the Table they are associated with. But the Procedures need to have a status. We can probably filter for the replication objects by status and move the replicated objects to either the System Tables are just not show them at all.
sys.tables : is_replicated, has_replication_filter & is_merge_published
http://technet.microsoft.com/en-us/library/ms187406.aspx
sys.views : is_replicated & has_replication_filter
http://technet.microsoft.com/en-us/library/ms190334.aspx
sys.procedures : is_execution_replicated, is_repl_serializable_only & skips_repl_constraints
http://technet.microsoft.com/en-us/library/ms188737.aspx
sys.triggers : is_not_for_replication
http://technet.microsoft.com/en-us/library/ms188746.aspx
@Ivan:
-- Look at ExtractSchema line 3042 section. The code should be using sys.tables for SQL Server >= 2005 to determine whether it is a system table or not -- this is a bug. For reference, take a look at MSSystemTablesNode line 39
-- for views, procedures and triggers, SQL Server also provides an is_ms_shipped column. Lets discuss these items.
Changes the extraction so that schema objects will no longer extract system tables. Looking into other objects.
Changes the extraction so that schema objects will no longer extract system tables. Looking into other objects.
table changes made for 14 but rest of the changes are going to require a bit of regression testing as the core extraction tables are been changed. Will move issue to version 15.
table changes made for 14 but rest of the changes are going to require a bit of regression testing as the core extraction tables are been changed. Will move issue to version 15.
Issue #10890 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
No fixed build |
No time estimate |
@Ivan:
-- Look at ExtractSchema line 3042 section. The code should be using sys.tables for SQL Server >= 2005 to determine whether it is a system table or not -- this is a bug. For reference, take a look at MSSystemTablesNode line 39
-- for views, procedures and triggers, SQL Server also provides an is_ms_shipped column. Lets discuss these items.