Merge Replication with only a subset of data at BOTH subscriber and publisher

I have a merge replication process (on test data) that is moving a
subset of data from one region to a central office. Now the central
office has it's own existing data, prior to initializing the merge
replication from this publisher.
Basically, when a row that existed prior to initialization is updated
at the subscriber, one that does not meet both a direct row filter and
a join filter, it is still being replicated back to the publisher, the
publisher looks like it then deletes all related records based on the
join filters because that row did not meet the criteria.
Am I trying to make merge rep do something that it does not do? I hope
that I am able to keep one subset of data in the merge process, and
have independent data on both the publisher AND subscriber.
Any help/direction is greatly appreciated.
Tony,
to have independant sets of data without truely editing the merge triggers
you really need to partition it and have separate publications. Views can be
used to amalgamate the data if needed. You can use 'Instead Of' triggers or
Partitioned Views to make them updatable.
HTH,
Paul Ibison
|||Paul,
Thanks for the information, I (stupidly) did not even consider that
possibility. I am going to set up a test here, and I might get back to
you if I run into any issues doing so.
Thanks for the insight!
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Paul, I did setup a test using views to partition off the data that I
want to publish, however it looks like when I publish those alone with
Merge replication that the data is not being transferred. The schema for
the views was initialized properly, but I think I am missing something.
You reference 'partitioned views'. Do I need to do something to the
views on the publisher in order to make changes to the data replicate
over?
Thanks in advance,
Tony
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
I didn't intend you to create the view on the publisher :-). This is an
avenue you could go down if you use an indexed view, but it is an overhead
you don't require. All you need to do is to create separate publications.
Each one has a filter to take the rows you are interested in - effectively
to partition the table. These publications will be sent ot the subscriber
and created there as 2 separate tables. If you need to report/query these
tables on the subscriber as though they were one table, you can use views on
the subscriber for this. These subscriber views will be unions and if they
need to be updatable then you could use 'instead of' triggers or partitioned
views.
HTH,
Paul Ibison
|||Paul,
The one problem is that I can not change the schema at the subscriber
nor the publisher, as they are established as well as the data that we
are working with. Obviously, I can add to the schema, which is why I
took the indexed view comment from your response. Currently applications
access the tables directly, and they expect this replicated data to end
up there one way or another.
Basically, if I could replicate just a view from each Publisher to the
central Sub, and have the views seperate the data logically from one
another, then the Subscriber could still work with the data in the table
underneath without having to worry about filters which are not being
evaluated.
This make any sense to you, or am I off the beaten path here?
Thanks again,
Tony D
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
on the publisher you won't need to change the schema, as you can separate
the table logically into two publications using row filters. On the
subscriber you'll have schema changes (additions) which can be transparent
to the user. Each publication replicates to a separate table. These could be
tables X and Y. The original table name is recreated on the subscriber as a
view which amalgamates (unions) the X and Y data. So from the subscriber's
point of view nothing has changed. However this view will only be editable
if you use an 'instead of' trigger or use a partitioned view. Either of
these mechanisms will filter the change into the respective replicated
table.
You mention having the 2 indexed views on the publisher, but they cannot
(easily) be replicated to the same table on the subscriber. You'll also lose
control of which changes are sent back to the publisher.
HTH,
Paul
|||Ok, I understand that so far. One question about the view on the
subscriber which amalgamates the data. You say to make this editable I
could make it a partitioned view. Is that just using 'With
Schemabinding', or do I need to index it also?
Thanks for your time Paul, this has been a help!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||One other hitch using different table names, currently all involved
tables at both the sub and pub have the same names. Is it at all
possible to publish a table so that it is replicated to a table with a
different name at the subscriber?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Anthony,
have a look at the @.destination_table parameter in sp_addarticle.
HTH,
Paul Ibison
sql

Merge replication security

I am doing merge replication to PocketPC devices using SSCE over the
internet and I'm wondering if I can increase security by moving the
distributor into the DMZ so that IIS will not need to connect all the way
back into our internal network (where our SQL is). We are curently using
standard security and have holes in our firewall to allow traffic from dmz
to sql. If we put the distributor into DMZ could and made the internal
domain trusted in the DMZ, could we close the firewall holes so that all
communication needs to be initiated from the internal network? That way if
someone hacked into the DMZ they couldn't take advantage of the open ports.
Would this also allow us to use integrated security with DMZ domain
accounts?
Alternatively, is there a way to use VPN on PocketPC with merge replication?
What is the best solution for this problem?
Thanks.
I'm not totally sure if I understand this question, so let me see if I have
it correct.
You are using "standard security" which I take to mean SQL Authentication or
do you perhaps mean Basic Authentication?
Currently you have port 1433 open on your firewall so your web server can
connect to your SQL Server which is on your internal network. You want to
move SQL Server into the DMZ so you can close port 1433.
Whether you have Windows Authentication or SQL Authentication port 1433 has
to be open. You will not be able to use Windows Authentication across the
DMZ, or you shouldn't if your firewall is configured correctly. Your idea of
an internal domain is a possibility.
I think you want to use certificates. Rob Tiffany's book on SQL CE
development does some pretty good coverage of this area.
"Elmer Miller" <millere@.empireco.nospam> wrote in message
news:epS5QaaxEHA.260@.TK2MSFTNGP11.phx.gbl...
>I am doing merge replication to PocketPC devices using SSCE over the
>internet and I'm wondering if I can increase security by moving the
>distributor into the DMZ so that IIS will not need to connect all the way
>back into our internal network (where our SQL is). We are curently using
>standard security and have holes in our firewall to allow traffic from dmz
>to sql. If we put the distributor into DMZ could and made the internal
>domain trusted in the DMZ, could we close the firewall holes so that all
>communication needs to be initiated from the internal network? That way if
>someone hacked into the DMZ they couldn't take advantage of the open ports.
>Would this also allow us to use integrated security with DMZ domain
>accounts?
> Alternatively, is there a way to use VPN on PocketPC with merge
> replication? What is the best solution for this problem?
> Thanks.
>
|||I know port 1433 needs to be open for OUTBOUND traffic, but I'm trying to
find out if it can be closed for INBOUND traffic, thus greatly improving
security. By having a separate SQL server in the DMZ I can use Windows
Authentication from the Web server to the SQL server since both are on the
same domain (in the DMZ). You can use Windows Authentication across the DMZ
securely as long as it is only from the Internal network to the DMZ as long
as you enable a one-way domain trust or a forest trust with Selective
Authentication enabled. My biggest question is this: Does the Distributor
need to initiate any communication with the Publisher? If it does then it
wouldn't work because inbound traffic will be blocked on port 1433. But if
the Publisher initiates all communication with the Distributor, then it
should work since outbound traffic will be allowed on port 1433. I think
this makes sense, but I've not heard anyone else discuss this option or
recommend it and I'm no security expert.
Thanks for the ref on the book. Use of certificates may solve this and many
other problems too, but I'm still curious if my idea could work.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om2hHdfxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'm not totally sure if I understand this question, so let me see if I
> have it correct.
> You are using "standard security" which I take to mean SQL Authentication
> or do you perhaps mean Basic Authentication?
> Currently you have port 1433 open on your firewall so your web server can
> connect to your SQL Server which is on your internal network. You want to
> move SQL Server into the DMZ so you can close port 1433.
> Whether you have Windows Authentication or SQL Authentication port 1433
> has to be open. You will not be able to use Windows Authentication across
> the DMZ, or you shouldn't if your firewall is configured correctly. Your
> idea of an internal domain is a possibility.
> I think you want to use certificates. Rob Tiffany's book on SQL CE
> development does some pretty good coverage of this area.
>
> "Elmer Miller" <millere@.empireco.nospam> wrote in message
> news:epS5QaaxEHA.260@.TK2MSFTNGP11.phx.gbl...
>
sql