Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server 2005 Replication: What are the Schemas and how they are replicated? - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server 2005 Replication: What are the Schemas and how they are replicated?

You may be aware SCHEMA carries 2 different meaning within SQL Server 2005 version that were introduced, first one is the definition of an object (object.schema) and next one is owner of an object (user.schema). In the first case replication copies the definitions of all replicated objects to the Subscriber, (in some cases) the permission for that schema which is used in the multi-part name should be scripted out in order to apply on the subscriber side.

Using SQL Server 2005 publication wizard for schemas and ownership you can enable the articles in merge publications with a compatibility level of 90 or higher for snapshot publications and transactional publications. The changes within this version will have the object owner at the Subscriber is the same as the owner of the corresponding object at the Publisher. In the case of any discrepancy at the subscriber such permission will be created automatically.

In case of pre-2005 version such as with compatibility level less than 90 the articles in merge publication will have the owner as blank and will be specified as dbo during the creation of the object on the Subscriber. Same behaviour will be applied for other providers such as Oracle publications. If needed such owner permission tasks can be changed using Article Properties or using the stored procedures such as sp_addarticle, sp_addmergearticle, sp_changearticle, and sp_changemergearticle.

As per the documentation process within 2005 version it supports a wide variety of schema changes on published objects, including adding and dropping columns. For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column, fyi refer to  Making Schema Changes on Publication Databases link.

Published Friday, February 15, 2008 9:28 AM by ssqa.net


No Comments