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 over VPN

I currently have a client who needs to replicate a database between a server
in the office and a computer at home using a VPN connection.
Here is the currnet setup:
-Merge Replication
-Both the server and the client have SQLSERVERAgent logged in with the same
username and password for pass-through authentication
-Subscriber impersonates SqlServerAgent
-Anonymous subscription
The snapshot has been created successfully on the server and I am able to
register the client on the server's Enterprise Manager (so they can see each
other). The replication starts and runs for quite a while (most of the work
is done). Eventually, it fails with the message
//<servername>/<sharename>/<filname>.dri could not be propagated to the
subscriber. With no further details. I don't think it is security related,
because most of the work is already done.
Any ideas what I'm missing?
You might try, from the client, to see if you can browse this share. When I
have set it up, it required an entry in the HOSTS file to map the IP address
to the NETBIOS name.
HTH,
Paul Ibison
|||The share is visible. I fixed that with an entry in the HOSTS file in the
beginning.
"Paul Ibison" wrote:

> You might try, from the client, to see if you can browse this share. When I
> have set it up, it required an entry in the HOSTS file to map the IP address
> to the NETBIOS name.
> HTH,
> Paul Ibison
>
|||OK, if it is running, transferring the snapshot and fails after a while, you
might try increating the -QueryTimeout parameter of the merge agent's
profile.
HTH,
Paul Ibison
|||I did that already as well after having a timeout problem. The current
problem just says that the file could not be propagated to the subscriber.
"Paul Ibison" wrote:

> OK, if it is running, transferring the snapshot and fails after a while, you
> might try increating the -QueryTimeout parameter of the merge agent's
> profile.
> HTH,
> Paul Ibison
>
>
|||Problem Solved:
It turns out that there is no guarantee that the order that records are
inserted during replication will be the same as when they were inserted
originally. Therefore, it is possible that a chile record can be inserted
before its parent, causing a conflict with the foreign key constraint.
After setting all foreign key constraints to "not for replication", the
client took the snapshot without a problem.
"b1@.ckh013" wrote:

> I currently have a client who needs to replicate a database between a server
> in the office and a computer at home using a VPN connection.
> Here is the currnet setup:
> -Merge Replication
> -Both the server and the client have SQLSERVERAgent logged in with the same
> username and password for pass-through authentication
> -Subscriber impersonates SqlServerAgent
> -Anonymous subscription
> The snapshot has been created successfully on the server and I am able to
> register the client on the server's Enterprise Manager (so they can see each
> other). The replication starts and runs for quite a while (most of the work
> is done). Eventually, it fails with the message
> //<servername>/<sharename>/<filname>.dri could not be propagated to the
> subscriber. With no further details. I don't think it is security related,
> because most of the work is already done.
> Any ideas what I'm missing?

merge Replication across Internet

I'm want to setup merge replication ( sql 2000 ) where one sql server would
be installed with a hosting provider & other would be in our office. I'm
able to setup a test setup in my office between 2 servers which are in same
NT domain.
I have queries about how to setting up merge replication over internet. I
request all sql replication expertes to please answer my query.
1. While setting up merge replication is it a must to have point to point
connect i.e do we need to have a leased line connectivity between hosting
provider where we would be hosting sql server & our office ?
2. The server hosted in hosting provider should be made to log in to our NT
office domain or can i keep this server in standalone mode & achieve merge
replication ?
2.My office has internet connectivity via some isp. If leased line
connectivity to hosting provider is not possible then how can i setup merge
replication on internet ? At my server hosted in hosting provider do i need
to setup vpn ( l2tp ) connectivity to my server in office ? Please note that
in my test setup in office both servers were in same NT domain & connected in
a LAN.Idea of vpn connectivity is to extend my office network over internet
to the server hosted in hosting provider. is my direction of thinking right ?
Please share your experiences if some one in this forumn has implmented a
similar setup.
tanks.....
1) You need some form of connectivity - whether it is across the internet or
through a dedicated lease line doesn't matter. You might want to use a VPN
for security requirements.
2) You don't need to create a trust relationship between your domain and the
hosting company server/domain. You would use SQL authentication and FTP for
this. Consult this kb for more information.
http://support.microsoft.com/default...b;en-us;321822
2) As long as you can connect to both servers using Enterprise Manager you
will be ok. Note that you will probably want to register both servers using
the NetBIOS names (what is returned via select @.@.servername and which should
match xp_cmdshell 'hostname'). Use Client Network Utility to build an alias
which will match the Fully Qualified DomainName of the SQL Server i.e.
(SQLServer1.ResourceDomain.Microsoft.com).
Using a VPN is not essential. Microsoft does recommend it for security
reasons. If you are concerned about security (i.e. are a bank or have
sensitive data) use a VPN, if you are not concerned you don't have to use a
VPN. Using a VPN slows down communication, because of this many companies
choose not to use it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:470A0759-D7D8-4275-BDB1-58F4B4BCC3B2@.microsoft.com...
> I'm want to setup merge replication ( sql 2000 ) where one sql server
would
> be installed with a hosting provider & other would be in our office. I'm
> able to setup a test setup in my office between 2 servers which are in
same
> NT domain.
> I have queries about how to setting up merge replication over internet. I
> request all sql replication expertes to please answer my query.
> 1. While setting up merge replication is it a must to have point to point
> connect i.e do we need to have a leased line connectivity between hosting

> provider where we would be hosting sql server & our office ?
> 2. The server hosted in hosting provider should be made to log in to our
NT
> office domain or can i keep this server in standalone mode & achieve
merge
> replication ?
> 2.My office has internet connectivity via some isp. If leased line
> connectivity to hosting provider is not possible then how can i setup
merge
> replication on internet ? At my server hosted in hosting provider do i
need
> to setup vpn ( l2tp ) connectivity to my server in office ? Please note
that
> in my test setup in office both servers were in same NT domain & connected
in
> a LAN.Idea of vpn connectivity is to extend my office network over
internet
> to the server hosted in hosting provider. is my direction of thinking
right ?
> Please share your experiences if some one in this forumn has implmented a
> similar setup.
> tanks.....
>
|||Many thanks for reply.I suppose my office network has firewall & suppose
hosting provider also has firewall, then for merge replication which ports
would be involved?Based on your inputs will talk to my network team in office
regarding the same.
"Hilary Cotter" wrote:

> 1) You need some form of connectivity - whether it is across the internet or
> through a dedicated lease line doesn't matter. You might want to use a VPN
> for security requirements.
> 2) You don't need to create a trust relationship between your domain and the
> hosting company server/domain. You would use SQL authentication and FTP for
> this. Consult this kb for more information.
> http://support.microsoft.com/default...b;en-us;321822
> 2) As long as you can connect to both servers using Enterprise Manager you
> will be ok. Note that you will probably want to register both servers using
> the NetBIOS names (what is returned via select @.@.servername and which should
> match xp_cmdshell 'hostname'). Use Client Network Utility to build an alias
> which will match the Fully Qualified DomainName of the SQL Server i.e.
> (SQLServer1.ResourceDomain.Microsoft.com).
> Using a VPN is not essential. Microsoft does recommend it for security
> reasons. If you are concerned about security (i.e. are a bank or have
> sensitive data) use a VPN, if you are not concerned you don't have to use a
> VPN. Using a VPN slows down communication, because of this many companies
> choose not to use it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
> news:470A0759-D7D8-4275-BDB1-58F4B4BCC3B2@.microsoft.com...
> would
> same
> NT
> merge
> merge
> need
> that
> in
> internet
> right ?
>
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:1A422957-611D-45FC-8D3E-A734340ECC27@.microsoft.com...
> Many thanks for reply.I suppose my office network has firewall & suppose
> hosting provider also has firewall, then for merge replication which ports
> would be involved?Based on your inputs will talk to my network team in
office[vbcol=seagreen]
> regarding the same.
> "Hilary Cotter" wrote:
internet or[vbcol=seagreen]
VPN[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
you[vbcol=seagreen]
using[vbcol=seagreen]
should[vbcol=seagreen]
alias[vbcol=seagreen]
use a[vbcol=seagreen]
companies[vbcol=seagreen]
I'm[vbcol=seagreen]
internet. I[vbcol=seagreen]
point[vbcol=seagreen]
hosting[vbcol=seagreen]
our[vbcol=seagreen]
note[vbcol=seagreen]
connected[vbcol=seagreen]
implmented a[vbcol=seagreen]
|||ports 1433 and 21 (for ftp).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:1A422957-611D-45FC-8D3E-A734340ECC27@.microsoft.com...
> Many thanks for reply.I suppose my office network has firewall & suppose
> hosting provider also has firewall, then for merge replication which ports
> would be involved?Based on your inputs will talk to my network team in
office[vbcol=seagreen]
> regarding the same.
> "Hilary Cotter" wrote:
internet or[vbcol=seagreen]
VPN[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
you[vbcol=seagreen]
using[vbcol=seagreen]
should[vbcol=seagreen]
alias[vbcol=seagreen]
use a[vbcol=seagreen]
companies[vbcol=seagreen]
I'm[vbcol=seagreen]
internet. I[vbcol=seagreen]
point[vbcol=seagreen]
hosting[vbcol=seagreen]
our[vbcol=seagreen]
note[vbcol=seagreen]
connected[vbcol=seagreen]
implmented a[vbcol=seagreen]