Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transactional Replication in 2008 R2

Author  Topic 

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-04-14 : 20:05:35
I last used replication in version 2000 (yeah). It seems that in 2008, many of the weaknesses are still there.
For example,
1. when a replicated tables column length is changed, is that schema change automatically replicated to the subscriber or will I need to reinitialize all articles?

2. When we install software updates from the vendor, they sometimes include changes to a table - adding a new column for instance. I imagine this can become a pain. How do you handle such things?

Thanks for your tips. Jack

Brandon Williams
Starting Member

2 Posts

Posted - 2014-04-15 : 12:16:45
To change a column length you'll need to use ALTER TABLE <Table> ALTER COLUMN syntax at the Publisher. By default the schema changes will be propagated on the next synchronization, publication property @replicate_ddl must be set to true. No new snapshot or reinitialization required in most cases.

For example, I can change my SalesLT.Customer Title column from nvarchar(8) null to nvarchar(10) null by executing on the publication database:
ALTER TABLE SalesLT.Customer ALTER COLUMN Title NVARCHAR(10) NULL

In most cases the schema change will replicate out on the next synchronization, there are some exceptions that are covered in Making Schema Changes on Publication Databases (http://msdn.microsoft.com/en-us/library/ms151870.aspx).

To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE <Table> ADD <Column> syntax at the Publisher. By default the schema change will be propagated to subscribers on synchronization, @replicate_ddl must be set to true. No new snapshot or reinitialization required in most cases. See Making Schema Changes on Publication Databases (http://msdn.microsoft.com/en-us/library/ms151870.aspx) for more information.

http://www.sqlrepl.com
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-04-15 : 15:22:10
Thank you, Brandon. That helps a lot. I may return with a followup question or 2.
Go to Top of Page
   

- Advertisement -