How to add new tables to Transactional Replication.

Question

How to add new tables to Transactional Replication.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 17, 2010 at 2:18 am

#209145

Hello Experts
Could you please suggest me how to add a new article to the transactional replication?
The scenario is given below.
I have established a local publication (Publication-AdventureWorks) and added many tables which are going to the subscriber end. Now due to the business demand I have to add few more tables to the same publication.
How do I proceed with it??
Let me know the steps.
In BOL, I found the below code snippet. Is it the only thing which I have to run?
Please suggest me in this regard.
==========================================================
DECLARE @publication AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'Publication-AdventureWorks'; (===> change the publication name accordingly)
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL';
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';
-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle
@publication = @publication,
@article = @table,
@source_object = @table,
@source_owner = @schemaowner,
@schema_option = 0x80030F3,
@vertical_partition = N'true',
@type = N'logbased',
@filter_clause = @filterclause;
-- (Optional) Manually call the stored procedure to create the
-- horizontal filtering stored procedure. Since the type is
-- 'logbased', this stored procedures is executed automatically.
EXEC sp_articlefilter
@publication = @publication,
@article = @table,
@filter_clause = @filterclause,
@filter_name = @filtername;
-- Add all columns to the article.
EXEC sp_articlecolumn
@publication = @publication,
@article = @table;
-- Remove the DaysToManufacture column from the article
EXEC sp_articlecolumn
@publication = @publication,
@article = @table,
@column = N'DaysToManufacture',
@operation = N'drop';
-- (Optional) Manually call the stored procedure to create the
-- vertical filtering view. Since the type is 'logbased',
-- this stored procedures is executed automatically.
EXEC sp_articleview
@publication = @publication,
@article = @table,
@filter_clause = @filterclause;
GO
====================================================================
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rspskumar-704119 SSC Veteran Points: 232 More actions · Answer 1

The best option is disable publication and enable with new article[If it is samll db] Even in 2005 we can intialize schema with backup so we are disabling publication ,after code deploy we are creating publication

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

But are you sure, we can't add\delete a new table\sp\view(object) after the replication is setup?

Thanks.

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 3

you can add article using

sp_addarticle

HTH

vinay

http://rdbmsexperts.com/Blogs/

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

Vinay Thakur-585143 (1/18/2010)

you can add article using

sp_addarticle

HTH

vinay

http://rdbmsexperts.com/Blogs/%5B/quote%5D

We have many requests coming in to add articles every week. As vinay mentioned ... use sp_addarticle to add article.

This is how I do:

1. rt click your replication local publication --> generate Create script

2. Look for -- Adding the transactional articles and copy sp_addarticle ... for any one article in notepad

3. Change the value for @article --> to the article you want to publish.

4. Now in the generated script Look for -- Adding the transactional subscriptions and copy sp_addsubscription

5. look for @sync_type --> this should be 'automatic'

Note: you should also look for publication, subscriber, etc if that are different from your generated script.

6. Then, run the snapshot agent to create a snapshot and then launch rep monitor and then sync it.

You are done ! 🙂

Hope this helps,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Donald Burr Ten Centuries Points: 1056 More actions · Answer 5

Nice answer from SQL-Quest.

We script ALL of our replication and NEVER use the interface.

It allows us to break-down are rebuild all of our replication publications and subscriptions very very quickly.

If we have a number of new articles to add, we add them to the script (alphabetically of course) then we pick a "quiet time" (after hours when there is no data being replicated) delete the existing publications and then fire off the updated scripts.

Works like a charm.

If we ever need to resync tables we ues Red-Gate SQL Data Compare.

This method has served us well and keep our butts out of the fire.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 6

Donald Burr (1/19/2010)
Nice answer from SQL-Quest.
We script ALL of our replication and NEVER use the interface.
It allows us to break-down are rebuild all of our replication publications and subscriptions very very quickly.
If we have a number of new articles to add, we add them to the script (alphabetically of course) then we pick a "quiet time" (after hours when there is no data being replicated) delete the existing publications and then fire off the updated scripts.
Works like a charm.
If we ever need to resync tables we ues Red-Gate SQL Data Compare.
This method has served us well and keep our butts out of the fire.

Glad could help ... 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

forsqlserver SSCoach Points: 18902 More actions · Answer 7

After adding 7 new views on publication side ,Till the time publication is synching with subscriber will my user able to get the data?

Thanks

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 8

forsqlserver (4/18/2012)
After adding 7 new views on publication side ,Till the time publication is synching with subscriber will my user able to get the data?

You wont be having data only when u run a snapshot. If the data is flowing to the subscribers, the subscribers will be having data that has flown to them.

You have to count for your n/w latency and the amount of replicated transactions that you are flowing down to your subscribers.

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂