You may have wondered how to add, drop, or alter (type or size) a column of a table that is an article of a publication. I have seen this question in many forums and discussion boards. Obviously, dropping the replication, making the necessary changes, and then recreating the replication is not efficient. Your customers may not even allow you to drop the replication, as this method will virtually shut down the system. In today’s competitive environment, you cannot afford to shut down your system even for a few minutes. Therefore, you need to come up with a solution that lets you conduct the operation without breaking the replication.
SQL Server 2000 Implementation
If you need to change table structures in a SQL Server 2000 database that is being replicated, you may not be able to run normal ALTER TABLE statements to add, drop or alter a column. If you do try, it will fail, and you will get the following message:
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Cannot add columns to table “Categories” because it is being published for merge replication.
NOTE: You can add a column by using the ALTER TABLE ADD statement in snapshot and transactional replications but it will not be reflected on the subscribers’ end.
SQL Server 2000 has two system stored procedures you can use—sp_repladdcolumn and sp_repldropcolumn—to change a replicated table’s schema that will reflect on the subscribers’ end.
If you want to add a column, execute the following statement at the publisher.
sp_repladdcolumn @source_object = N’Customers’, @column = N’Cat1′, @typetext = ,N’varchar(25)’, @publication_to_add = N’data’,@from_agent = 0, @schema_change_script = NULL, @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1
This will add a new column called Cat1 of varchar(25) to the Customers table in Data publisher, which will be replicated to all the subscribers accordingly.
sp_repldropcolumn @source_object = ‘Customers’ , @column = ‘Cat1’ , @schema_change_script = NULL , @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1
The above statement will drop a column called Cat1 from the Customers table in Data publisher and it will again be replicated to all the subscribers.
However, a problem arises when you want to modify a column, as there is no direct stored procedure to do that. Nevertheless, by combining the above two system stored procedures you can achieve your objective.
CREATE TABLE #TEMP (ID VARCHAR(15), DES VARCHAR(100))
go
INSERT INTO #TEMP Select CustomerID,Fax from Customers – Data will be stored in a temp table
go
sp_repldropcolumn @source_object = ‘Customers’
, @column = ‘Fax’
, @schema_change_script = NULL
, @force_invalidate_snapshot = 1
, @force_reinit_subscription = 1
Go
sp_repladdcolumn @source_object = N’Customers’, @column = N’Fax’, @typetext = ,N’varchar(25)’, @publication_to_add = N’data’,@from_agent = 0, @schema_change_script = NULL, @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1
go
Update Customers Set Fax = (select DES from #TEMP where Id = Customers.CustomerID)
— Data will be restored from the temp table
drop TABLE #TEMP
The above query will change the Fax column to varchar(25) and the previous data will be inserted later. The changes will be replicated accordingly to all the subscribers. However, the column position will be changed. After running the above script, the Fax column will be the last column.
In all three cases, you run the scripts only at the publisher and the changes, including those to the schema, will be replicated to all the subscribers.
SQL Server 2005 Implementation
In SQL Server 2005, certain things have become much easier to do. By default, all the DDL changes are replicated to all the subscribers; thus, reducing headaches for developers.
You can easily find this option under Subscription Options in the Publication Properties dialog box. The option is called Replicate schema changes, which is set to True by default for all publications.
One important point to remember is that all the schema changes should be done at the publisher.
Replication Between Different Versions
Merge Replication can be implemented between SQL Server 2005 and SQL Server 2000 versions. In mixed cases, alter table WILL NOT reflect at the subscriber but sp_repladdcolumn and sp_repldropcolumn will reflect at the subscriber.
Conclusion
Replicating your schema changes is the default in SQL Server 2005, which makes it easier to manage SQL Server Replication.
]]>