November 9, 2010 at 5:29 pm
Got a situation in which I need to filter an article using data from a table that I don't want the filter applied to.
Table 1 has a date that I want to use in order to filter out records from table 2 which has a huge blob field. However, I want every record from table 1 to replicate.
I've been doing this for a number of years but I haven't run across a need like this before, so it's somewhat newbie, somewhat not.
I understand you can publish a view then de-construct at the subscriber, but I'm hoping there is an easier solution.
I'm assuming you could create 2 publications, 1 that doesn't include table 2 and has no filter thus getting all of table 1 and the other applies the filter with table 1 keeping the existing object as is instead of dropping it thus preserving pub 1. However, when you do that, both tables must be of the same update type, so you can't have 1 read only and 1 bidirectional. With both being bi, I'm concerned about a performance hit.
Another way I looked at it was is there a way to filter an article using a column from a table which is not an article in the publication? (99% sure ya can't but I'd hate to find out later you could and I was too arrogant to float it out here)
This is a massive DB (20 Terabytes and growing) and I only need to do this for a short while as I work on getting this behemoth I inherited whittled down to a manageable size so before I get started running through various tests and generating snap shots that run several hours, I wanted to get some feedback.
Thanks in advance
Specifics and Scripted Tables
2 SQL Server 2008 Enterprise, single node/instance, Merge Replication
CREATE TABLE [dbo].[Table_1](
[PimeKey] [nchar](10) NOT NULL,
[Text1] [nchar](10) NULL,
[Text2] [nchar](10) NULL,
[DateFiled] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[PimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Second Table
CREATE TABLE [dbo].[Table_2](
[ForeignKey] [nchar](10) NOT NULL,
[Number1] [int] NOT NULL,
[Number2] [int] NOT NULL,
[ImageBLOB] [varbinary](max) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([ForeignKey])
REFERENCES [dbo].[Table_1] ([PimeKey])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GO
November 9, 2010 at 5:42 pm
Oh, and I forgot to mention, there is already a publication which includes Table 2 and is Read Only. Which I'll have to change in order to have bidirectional updates in the other publications unless there is a way to publish 1 table multiple times with some read only that I'm not aware of.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply