Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Modifying Publications
Now that you know how replication procedures work, let's learn how to modify existing publications.
Adding Columns to Published Tables
In the second article of this series, I showed you how to create a publication with a single article: authors. Now let's see how we can add a column to this table. We open the Replication Monitor with SQL Server Enterprise Manager and click Properties. Enterprise Manager warns us that many properties of this publication cannot be changed because this publication has subscriptions. After I click OK on that initial message, I get the Publication Properties screen; let me navigate to Filter Columns tab, as shown in the following figure.
)
Figure
2Here, let's click the Add Columns to Table button. That opens up a new screen, in which I can specify the name, data type, default, or check constraints for the column we want to add, as shown following.
)
Figure
3Notice that we're allowed to add an IDENTITY column or unique identifier column (ROWGUIDCOL) to the table. Furthermore, Enterprise Manager automatically detects that the authors table is already published and advises us that the new column will be included in the pubs_authors publication. I'll make the name of the new column new_column of INT data type without specifying defaults or other constraints and click OK. This will close Add Column To Replicated Table dialog box and take us back to the previous screen. We'll simply click OK there as well—that's it! Now, if we examine the properties of the authors table on the publisher or the subscriber, we see that replication has added a column called new_column with integer data type. Furthermore, if we examine the sp_msupd_authors stored procedure on the subscriber now, we notice that the following line has been added to both UPDATE statements:
[new_column] = CASE SUBSTRING(@bitmap,2,1) & 2 WHEN 2 THEN @c10 ELSE [new_column] END
In addition, this procedure has a new parameter @c10 to catch any changes in values of the new_column. Similarly, the sp_msins_authors procedure will have a new parameter to account for new_column. In the background, SQL Server executes sp_repladdcolumn system procedure to add a column to an existing article.
What if you have added a column using the ALTER TABLE statement? Such changes do not move automatically to subscribers. If we used ALTER TABLE statement to add new_column to the authors table, the Filter Columns tab of the publication would look as shown in the next figure.
)
Figure
4To add an existing column to the replicated article, we can check the check box next to new_column. Notice, however, that we have to add new_column to the subscriber tables and modify replication stored procedures manually.
Removing Replicated Columns
We're much more likely to add columns to the published tables than remove columns. But occasionally a particular column is no longer needed; for example, we might change our data model and move a column from a replicated table to another table.
SQL Server won't let us drop a column while it is replicated; the ALTER TABLE command will fail. Before you remove a column, you must remove it from publication.
Removing a published column from publication is just as easy as adding new ones: Simply uncheck the check box next to the column on the Filter Columns tab. We can click the Drop Selected Column button to drop the column from the table.
Unlike the case with adding columns, replication requires a new snapshot to be applied on subscribers to reproduce schema changes and change stored procedures on subscribers. Hence, when we uncheck the replicated column, Enterprise Manager displays the following warning:
)
Figure
5Enterprise Manager warns us that subscriptions that do not support automatic initialization have to be reinitialized manually. This statement includes a new term that you need to learn: initialization is a process of marking a subscription for applying the most recent snapshot. Subscriptions that are marked for initialization will get the new set of schema and data next time the snapshot agent runs.
Recall that when we set up our publication and subscribed to it, we had an option to initialize schema and data or advise the Replication Wizard that the subscriber already has schema and data. The latter option creates a subscription that does not support automatic initialization.
To initialize a subscription that does not support automatic initialization, we right-click Subscription within the Replication Monitor and choose Reinitialize, as shown in the following figure.
)
Figure
6One further note on reinitializing subscriptions: Creating and applying a snapshot of a large publication can take a long time (perhaps several hours). If you have a large publication and you need to make relatively minor changes to it, then it makes sense to make changes manually on the publisher and subscriber instead of bringing the system down while the snapshot is transferred from publisher to subscribers.
Adding Filters to Articles
During the initial setup or perhaps sometime down the road, you might decide that not all rows of a table need to be published. For example, suppose that you have a central server in which you make changes to your data. Transactions from this central server replicate to regional servers that are used for read-only purposes. The server in California cares only about the authors that reside in that state. Therefore, you can add a filter to the authors article so that only the rows that have ca in the state column will be published for replication.
To filter an article horizontally (that is, limiting rows rather than limiting columns), navigate to the Filter Rows tab of the Publication Properties dialog box. Click the ellipsis button (...) next to the filter clause, and you will be presented with the following screen:
)
Figure
7Here, we can enter the WHERE clause for our filter:
WHERE state = 'ca'
After we click OK, Enterprise Manager will warn us that if we save the filter, we just added all subscriptions supporting automatic initialization will be reinitialized. After the snapshot agent runs again, we find that the authors table on the subscriber only has 14 rows—those that have a value of ca in the state column.
NOTE
The screen shown above won't let us modify any of the other portions of the SELECT statement included in the filter.