Just a little reminder note today on the simplest way to make data-model changes in SQL Server 2005 when the database is replicated via transactional replication. This is from my own personal experience of replication and very-much muddling my way through learning how to use it effectively. Otherwise, this posts presumes you are familiar with Replication Monitor and so on.

Adding Columns to a Replicated table

This is the easiest scenario; as long as the columns are being added with a ALTER TABLE [tableName] ADD [columnName] [columnType] statement, replication will handle the new data with aplomb. Superb!

Removing or Inserting Columns

SQL Server Management Studio is an immensely capable tool, and does a lot of things behind-the-scenes when you use the UI to change table structures. For example, if you insert a new column into the table (and not at the end) or drop a column, what it actually does is:

• Drop constraints on the table;
• Create a new temporary table with the new column structure, add constraints, and copy data in from the existing table;
• Drop referential constraints on other tables to the table we are working on;
• Drop the original table;
• Rename the temporary table to match the original;
• Reconstruct relationship constraints.

It is easy enough to see what it does by making your data-model changes in the UI, and instead of saving them to apply the changes to the table, click ‘Generate Change Script’ to get the SQL to do the same.

When a table is an article in replication, such data-model changes are prohibited. So what we need to do is as follows:

1. Remove the table we want to change from the replication’s Articles;
2. Apply our SQL script to modify the table (this is now allowed because at this point the table is not a replicated article) – or use the UI to make the changes;
3. Add the table back to the Replication Articles (you will be warned that this will not take part in replication until a new snapshot is taken);
4. Generate a new Snapshot for the replication.

What this seems to do in practice is make use of functionality in the subscription side of the replication; once a new snapshot becomes available with articles (e.g. your table) in that did not previously exist in the replication, it is clever enough simply to load up that table, and then continue with transactional replication.

What you must not do, it seems, is ‘Refresh all Subscriptions’. This option too will ask you if you want to take a new snapshot, but what it effectively does is blank out the subscription DB’s and transfer all the tables again from scratch.

On our DB with circa 20GB of data, I find that this procedure saves much time; it may take about 15 minutes to take a new snapshot, and then a few minutes (depending on table size, of course) to refresh the single table. In contrast, if the subscriptions are refreshed, it takes an hour or more to refresh all the tables from scratch, and in addition we need to rebuild a number of indexes that are not replicated.