Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replicating Schema Changes
Recall that with previous versions of SQL Server you had to run the Snapshot agent to deliver replicated code modules’ definition changes. This is no longer the case with SQL Server 2005: the replication propagates ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE, and ALTER TRIGGER statements to the subscribers in real time. Replicating triggers isn’t one of the options you can pick within the article types, but you can copy triggers defined on tables and views in the publication database. Keep in mind that you cannot replicate data definition language (DDL) triggers.
Let’s see how we can replicate a change to an indexed view. I created a very simple indexed view on the publisher using the following commands:
CREATE VIEW [dbo].[View_DimCustomer_Young] WITH SCHEMABINDING AS SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate FROM dbo.DimCustomer WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1980’, 101)) GO CREATE UNIQUE CLUSTERED INDEX [ix_DCY_CustomerKey] ON [dbo].[View_DimCustomer_Young] ( [CustomerKey] ASC )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
This view returns a few columns from the DimCustomer table for customers who were born after January 1, 1980. I added this indexed view to a transactional publication and created a subscription to it from another server.
Now let me change the view slightly to return customers born after January 1, 1978. I executed the following ALTER VIEW statement:
ALTER VIEW [dbo].[View_DimCustomer_Young] WITH SCHEMABINDING AS SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate FROM dbo.DimCustomer WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1978’, 101))
Now if I execute sp_browserplcmds system procedure against the distribution database, I’ll find the same ALTER VIEW statement being delivered to the subscribing database.
Replicating code modules is particularly useful if you work in a multiserver environment in which you balance the application load across multiple servers with identical views, UDFs and stored procedures. Instead of running scripts to apply the same schema changes on 30 different servers, you can simply deploy changes to a single publishing server and let those changes replicate to multiple subscribers. In previous versions of SQL Server you had to run the Snapshot agent to deliver the schema changes, but with version 2005, schema changes will be delivered much like data changes. This should make your deployments considerably faster and easier.
In rare cases, you might not want to replicate schema changes until the Snapshot agent runs; for example, you might want to test your stored procedure changes against a single server before deploying these changes to all production servers. You can turn off replicating schema changes in the subscription options’ page of publication properties dialog box shown below.
)