Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change Article FILTER -- Where Clause -- in transactional replication Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 5:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:29 AM
Points: 658, Visits: 953
Looking for best way to accomplish this. I need to simply change the Horizontal FILTER / WHERE CLAUSE to include a few additional filter values in a SINGLE transactionally replicated article.

My publication consists of over 100 articles, some of them very large so I'd like to avoid regenerating the entire snapshot.

- Should I DROP the existing object via EXEC sp_dropsubscription to drop article in the subscription, then DROP the article in the publication by UN-Checking the box --then Re-Add the article w/ the new Horizontal FILTER?

or

- Should I use EXEC sp_changearticle to CHANGE the existing article?

If anyone has a solid, tested recommendation and perhaps a sample script to attach, it would be very much appreciated! thx in advance


BT
Post #1427922
Posted Sunday, March 10, 2013 9:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:29 AM
Points: 658, Visits: 953
-- This 4 step resolution worked:

--************************************************
-- SQL 2008 R2 -- Change an Article's Horizontal Row Filter
--************************************************
/**************

4 steps required to alter this Articles Horizontal row filter:

1. Drop article from any dependant downstream subscription(s)
2. Drop article on Publisher (with @force_invalidate_snapshot = 1)
3. Re-add the article w/ new Filter
4. Run the snapshot agent

--STEP 1 -- Drop article from dependant subscription(s)
--
-- Execute this at the PUBLISHER....
--
USE [myDB]
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @article AS sysname;
SET @publication = N'myPublication Name';
SET @subscriber = N'mySubscriptionServerName';
SET @article = N'myArticleName';
EXEC sp_dropsubscription @publication = @publication, @subscriber = @subscriber, @article = @article;
GO

--STEP 2 -- Drop article on Publisher
--
-- Execute this at the PUBLISHER.... (key is to set @force_invalidate_snapshot = 1 !!!)
--
USE [myDB]
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = N'myPublication Name';
SET @article = N'myArticleName';

-- Drop the transactional article.
USE [myDB]
EXEC sp_droparticle
@publication = @publication,
@article = @article,
@force_invalidate_snapshot = 1;
GO

-- STEP 3) re-add the article w/ new Filter - by bringing up the GUI and checking the article then adding it's NEW FILTER predicate
--
-- Article FILTER CLAUSE criteria:
-- ==============================
--
-- Keystone AcctCorp:
-- WHERE AcctCorp in (1603,1622,1692,1711,5818,9547,9549,9560)
--
- ADD TO REPLICATION
- Click on NED Minerva Publication: 'myPublication Name'
- Properties
- Go to filter rows
- Copy acctcorp in (…)
- Go to articles
- Uncheck show only checked objects
- Find new table check the table
- Go to Filter Rows
- Add Filter
- Paste acctcorp clause at the end
- Click OK and OK

- Go to Replication Monitor
- Click on Publication
- Warnings & Agent Tab
- Right Click Sanpshot Agent
- Select Start Agent

- Select Subscription
- All Subscriptions Tab
- Double click to see if table is replicating

-- STEP 4) run the snapshot agent - to create the article (which you just checked) on the subscriber

***************/


BT
Post #1428988
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse