SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transactional Replication in 2008 R2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

368 Posts

Posted - 04/14/2014 :  20:05:35  Show Profile  Reply with Quote
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

USA
2 Posts

Posted - 04/15/2014 :  12:16:45  Show Profile  Reply with Quote
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

368 Posts

Posted - 04/15/2014 :  15:22:10  Show Profile  Reply with Quote
Thank you, Brandon. That helps a lot. I may return with a followup question or 2.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000