- Replication Stored Procedures
- Modifying Published Table Schemas
- Adding and Removing Articles from Publications
- Subscription Expiration and Deactivation
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Modifying Published Table Schemas
One of the main limitations of replication using SQL Server versions prior to 2005 used to be mandatory downtime for making most changes to the replicated table’s schema. Fortunately, SQL Server 2005 allows making nearly all schema changes without removing tables from the publication. Replicating table schema changes is perhaps the biggest and most essential improvement in replication functionality, making SQL Server 2005 a true enterprise class database platform.
SQL Server 2000 enabled you to use the sp_repladdcolumn and sp_repldropcolumn system procedures to add/drop columns to replicated tables. However, if you used these routines for altering table schema(s), you had to wait for the Snapshot Agent to run, create the copy of the new schema and data, and then wait for the distribution agent to apply the snapshot to the subscriber. Of course, while the snapshot was being applied at the subscriber, the affected table(s) would not be available for querying or modifications—thereby requiring downtime. A viable alternative for sp_repladdcolumn was to use the ALTER TABLE command to add a column to the replicated table on both publisher and subscriber; you would then have to manually add the column to the publication and modify replication stored procedures. Similarly, you could filter a column from the existing publication and then execute the ALTER TABLE DROP COLUMN statement on the publisher. But you’d have to wait for the Snapshot Agent to apply the changes to the subscriber, again requiring downtime.
Let’s see how we can add or remove columns to a published table with SQL Server 2005. First, I’ll execute the following statement in the AdventureWorksDW database on the publisher:
ALTER TABLE dimAccount ADD test_column VARCHAR(250)
This statement adds a new column to the published table. Next I’ll execute the sp_browsereplcmds system procedure in the distribution database to find the same command forwarded to the subscriber:
ALTER TABLE [dbo].[DimAccount] ADD test_column VARCHAR(250)
Moreover, if I examine the replication stored procedures on the subscriber, I’ll find references to the newly added column:
Create procedure [dbo].[sp_MSupd_dboDimAccount] @c1 int,@c2 int,@c3 int,@c4 int,@c5 nvarchar(50),@c6 nvarchar(50),@c7 nvarchar(50), @c8 nvarchar(300),@c9 nvarchar(50),@c10 nvarchar(200), @c11 varchar(250),@pkc1 int , @bitmap binary(2) as begin update [dbo].[DimAccount] set /* updating existing columns ** skipped here to save some space */ ,[test_column] = case substring(@bitmap,2,1) & 4 when 4 then @c11 else [test_column] end where [AccountKey] = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end
Similarly, to drop a column from a replicated table, I could execute the following statement:
ALTER TABLE dimAccount DROP COLUMN test_column1
Replication would deliver this command exactly as when I added the column. In the background, SQL Server 2005 does exactly what we used to do manually in previous versions—it scripts the replication stored procedures after schema changes and applies them to the subscriber databases. Although schema changes no longer require downtime, you should not abuse this functionality. If you anticipate having millions of transactions in the distribution database waiting to be delivered to subscribers, don’t expect your schema changes to replicate in a blink of an eye. The table schema changes will be treated just like any other transaction, so if you want your publisher and subscriber to have consistent schemas, try to make table changes when user activity is minimal.
You might wonder whether there is a way to turn off replicating table schema changes. In fact, there is a way to do so: from the Subscription Properties dialog box, shown in the following figure.
)
Note that you need to make schema changes by using the ALTER TABLE statement to replicate them; the graphical tool for modifying tables with SQL Server Management Studio (SSMS) does not work. If you attempt to modify a replicated table using this tool, SSMS will alert you that the table is replicated and must be dropped from publication before you can make schema changes.
The following schema changes on replicated tables are automatically detected and forwarded to the subscribers:
- Adding columns.
Note that you’re not allowed to add an identity column to a replicated table.
- Dropping columns.
You cannot drop primary key column(s).
You cannot drop the column added by replication for immediate updating subscriptions.
- Changing column data type, column name, length, precision, and scale.
Certain changes are not permitted, but this limitation isn’t specific to replicated tables. For example, you can’t change a column with VARCHAR data type to an INT if you already have string values in the table. You’ll have to explicitly drop the column and add it back with the new data type.
You cannot change primary key columns.
- Adding and dropping foreign keys; replication automatically detects if the
referenced table exists on the subscriber.
Note that you can configure replicating foreign key constraints and other schema-related options from the Article Properties dialog box. If you make changes to Article Properties after subscriptions are created, you must create a new snapshot for changes to take effect.
Foreign keys are added on the subscriber with the NOT FOR REPLICATION option.
- Using the ALTER TRIGGER statement; note that CREATE TRIGGER and DROP TRIGGER aren’t replicated until a new snapshot is delivered to the subscriber(s).
Certain commands are not replicated:
- Adding and dropping indexes.
This isn’t a huge issue because you might need different indexes on the publisher and subscriber.
- Constraints that include non-deterministic functions.
To add a column without adding it to the publication (vertical partitioning), turn off replicating schema changes.