![]() Using these procedures we can add a dummy column to hold the data, remove the old column, add in the correct definition of the original column We might use the built in stored procedures sp_repladdcolumn and sp_repldropcolumn to make the changes (note that these procedures limit the subscribers to be SQL Server 2000 only). OK, in some cases the table is large and we don't want to run a new snapshot - either of the individual table (transactional) or of the whole publication (merge) - so there is an alternative method. A nosync initialization is possible,īut this can be extremely restrictive for future changes, and I'll leave thatįor another article. Then try to run sp_dropmergearticle there will be an error if the snapshot hasĪlready been run, so we have to set to 1, make the table change on the publisher then read theĪrticle and subscriptions and initialize which would necessitate a new snapshot generation ofĪll articles in this publication. If we drop the subscription entirely including all other articles (sp_dropmergesubscription), In merge replication, there is no possibility of dropping the subscription on a per article basis using the script above, as there is in transactional and snapshot replication. It's simplest to run the snapshot agent immediately after executing sp_addsubscription and then synchronize. So, to avoid all the issues and complications mentioned above, In our case, it'll also create a snapshot of the 'tEmployees' table. Usually, when you add a new article to an existing publication in transactional replication, running the snapshot agent will create a snapshot of just the new article. This is not ideal, and there is also a hidden problem here waiting to be discovered. during this (quick) change there shouldn't be any alterations made to the publisher's data and all the subscribers should be completely synchronized. They can be generated at the publisher using sp_scriptpublicationcustomprocs but this would of course require the system to be quiesced, i.e. For the example table above, these 3 procedures Itself as above would require the stored procedure arguments to be modified. Addition of a default would be fine, but changing the datatype The related stored procedures on all the subscribers. By changing the column definition, we may need to change To the subscriber in the form of a stored procedure call. By default, an insert, update or delete statement performed on the publisher is propagated ![]() However, we must be moreĬareful in this case. The next time the snapshotĪgent is run, it'll pick up the new schema without any issues.įor transactional replication we may choose to proceed using the script above. We drop the subscription to this article,ĭrop the article, then change the table. The method we choose depends in part on the replication type and size of the table, but there are 2 main options: (a) altering the subscriptionsĮxec sp_dropsubscription = 'tTestFNames'Īlter table tEmployees alter column Forename varchar(100) nullįor snapshot replication this is the obvious choice. So, how to change an existing column without breaking replication? Consider if we wanted to make the following schema change: "Cannot alter/drop the table 'tablename' because it is being published for replication". Attempting to change the table schema directly will result in the error There are many reasons this might be the case eg possibly the datatype has been incorrectly chosen, or a default is missing, or we want to rename a column. Sometimes the schema of a replicated table needs altering. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |