| Author |
Topic |
|
Tindjin
Starting Member
6 Posts |
 Posted - 2006-09-14 : 17:50:12
|
| Due to a poorly planned schedule while I was on vacation one of our clients had their replication setup. The database is 60+GB and already in production. Unfortunatly it was found that on one of the tables we need to add 4 columns (order of columns does not matter). Using SQL 2k5 what would be the easiest way to get them added so they fit into the publication and replication without having to spend a few days breaking replication and redoing it? Is it even possible?Thanks for any help. |
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-09-18 : 09:58:20
|
| Whats king of Replication do u have ?Transactional or Merge ?ThanksRishi Maini |
 |
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-09-18 : 09:59:28
|
| Sorry spelling mistakes :-( I mean to say What kind of Replication do u have ?Transactional or Merge ?ThanksRishi MainiThanksRishi Maini |
|
|
|
Tindjin
Starting Member
6 Posts |
Posted - 2006-09-19 : 13:44:57
|
| We are using Merge replication. This setup has 4 subscriber servers. |
|
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-09-19 : 14:40:45
|
| Just add the columns to the Tables and in SQL 2005 Merge should take care of it. While doing so you may get a warning message for "rowguid" Column which you can ignore.Having said that please test it first in your test environment. It should work.ThanksRishi Maini |
|
|
|
Tindjin
Starting Member
6 Posts |
Posted - 2006-09-19 : 15:57:20
|
| Ah so it will add the rowguid information itself after the column has been created? That is going to make life much easier for me.. Thanks. |
|
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-09-21 : 03:46:02
|
| Row guid is for per row. So rowguid column should already be there in the table if its part of replication. Adding a new Column to the Table will not at all touch the rowguid column, but after you add the column to the table Merge will detect the schema changes and will replicate the newly added columns to the Subscriber as well.But as I earlier mentioned please test it before u implement the same in production as Replication is very sensitive to schema changes.ThanksRishi Maini |
|
|
|
abdul
Starting Member
28 Posts |
Posted - 2006-09-21 : 10:02:33
|
| I have the same question but I'm using Sql Server 2000 and when I'm trying to add a column to a table which is already is being used merge replication I get an error.- Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add columns to table 'Certificate' because it is being published for merge replication.So what I understand from what Rishi has written is I can just add new columns. But I can't :( Do u you have any idea about how to add new columns to tables that are being used by Replication, namely Merge Replication.Thanks |
|
|
|
Rishi Maini SQL2K5 Admin
Yak Posting Veteran
80 Posts |
Posted - 2006-09-21 : 11:31:54
|
| Hi Abdul,The above answers were for SQL 2005 , for SQL 2000 you can use SP named as "sp_repladdcolumn"FROM SQL 2005 BOL====================="Adds a column to an existing table article that has been published. Allows the new column to be added to all publishers that publish this table, or just add the column to a specific publication that publishes the table. This stored procedure is executed at the Publisher on the publication database.Important: This stored procedure has been deprecated and is being supported mainly for backward-compatibility. It should only be used with Microsoft SQL Server 2000 Publishers and SQL Server 2000 republishing Subscribers. Remarkssp_repladdcolumn has been deprecated and is supplied for backward-compatibility only. Adding a column to a replicated table article should be done by executing data definition language (DDL) commands against the published table. Replication automatically replicates these DDL commands as long as DDL replication has been enabled. For more information, see Making Schema Changes on Publication Databases.sp_repladdcolumn is still required when propagating DDL changes from republishing Subscribers running on an earlier version of SQL Server 2000 Subscribers.sp_repladdcolumn is used for all types of replication.When using sp_repladdcolumn, if a schema change is made to an article that belongs to a publication that uses a Data Transformation Services (DTS) package, the schema change is not propagated to the Subscriber, and the custom procedures for INSERT/UPDATE/DELETE are not regenerated on the Subscribers. The user needs to regenerate the DTS package manually, and make the corresponding schema change at the Subscribers. If the schema update is not applied, the Distribution Agent may fail to apply subsequent modifications. Before making a schema change, make sure there are no pending transactions to be delivered. When typetext is assigned a default value that is a nondeterministic function (for example, 'datetime not null default getdate()'), non-convergence can occur after adding the new column because the function is executed at the subscriber in order to load a default value into the column. Timestamp and computed columns are filtered out for character mode publications. If adding a timestamp or computed column using sp_repladdcolumn, subscriptions of such publications do not receive this new column.Important: A backup of the publication database should be performed after sp_repladdcolumn has been executed. Failure to do so can cause a merge failure after a restore of the publication database. ThanksRishi Maini |
|
|
|
|
Adding column w/Merge replication