Replication based on subscribers informations

Hello all,
I want to use replication between one main (publisher/distributor) SQL server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over the internet.
1)
There is some tables on the main SQL server that need to be propagated to the other servers based on (Insert/Update/Delete) transactions. I called that kind static tables.
Example: there is a table called 'Events' wich have to propagate its data to ALL the subscribers.
2)
And there is another tables on the main SQL server that have data that must be propagated to clients (subscribers) based on their informations. I called that kind dynamic tables.
Example: there is table called 'Operations' wich have a column called 'Entreprise_ID', that have data about all operations concerning some entreprises. Those real entreprises have also SQL servers (wich shall be used as subscribers) to get data from the m
ain server, but Entreprise X must receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
I'm new into replication, i've searched over the web and understood it a little bit, so i thought that in my case i should use Transactional Replication if we suppose that data is a bit critical and must be refreshed on all the servers.
i configured the main SQL server with a subsriber to test transactional replication over the internet concerning what i called 'static tables' , it works very well.
But for applying filters on the 'dynamic tables' i don't know how to do it, i've seen that i can apply 'vertical filters' on those tables, but i don't know how to associate it with subscribers informations in order to send only appropriate data to each su
bscriber.
Thanx in advance for any comment.
a vertical filter is where you replicate a subset of the columns, a
horizontal filter is a where you replicate a subset of the rows in the
table.
I take it you want to do horizontal filtering or partitioning. To do this
right click on your publication and select publication properties, click on
the filter rows tab. locate the tables you wish to filter, and click on the
browse button to the right of the table/article. You will get the specify
filter dialog. Enter your filter there.
After your have made your changes, regenerate your snapshot, and distribute
it to your subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> Hello all,
> I want to use replication between one main (publisher/distributor) SQL
server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
the internet.
> 1)
> There is some tables on the main SQL server that need to be propagated to
the other servers based on (Insert/Update/Delete) transactions. I called
that kind static tables.
> Example: there is a table called 'Events' wich have to propagate its data
to ALL the subscribers.
> 2)
> And there is another tables on the main SQL server that have data that
must be propagated to clients (subscribers) based on their informations. I
called that kind dynamic tables.
> Example: there is table called 'Operations' wich have a column called
'Entreprise_ID', that have data about all operations concerning some
entreprises. Those real entreprises have also SQL servers (wich shall be
used as subscribers) to get data from the main server, but Entreprise X must
receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
>
> I'm new into replication, i've searched over the web and understood it a
little bit, so i thought that in my case i should use Transactional
Replication if we suppose that data is a bit critical and must be refreshed
on all the servers.
> i configured the main SQL server with a subsriber to test transactional
replication over the internet concerning what i called 'static tables' , it
works very well.
> But for applying filters on the 'dynamic tables' i don't know how to do
it, i've seen that i can apply 'vertical filters' on those tables, but i
don't know how to associate it with subscribers informations in order to
send only appropriate data to each subscriber.
> Thanx in advance for any comment.
|||Hello,
Yes Hilary, indeed, i made a mistake talking about 'vertical filtering', i must use 'horizontal filtering' but, the problem is not how to do it, by the wizard or something like that, i already know how to do it, my problem is a conception problem. How to
define an 'dynamic horizontal filter' for an article of a Transactional Replication, and this filter must propagate data dynamically based on subscribers informations.
For example:
Let's suppose that i have a table called 'Operations' that have 3 columns.
Op_ID--Date--Entreprise
1--01/05/2004--1
2--02/05/2004--2
3--15/05/2004--1
4--15/06/2004--3
5--19/06/2004--3
6--01/07/2004--2
so in this case let's suppose that we have 3 subscribers, hence 3 SQL servers each server in one Entreprise.
My need is, if a transaction happened in the table 'Operations' it have to be automatically propagated to the concerned entreprise. eg: there is one new row added '7--04/07/2004--2', this row have to be sent only to Subsriber number 2 because the o
ther subscribers don't need this data.
then, we'll have as a result, after synchronisation:
At Subscriber #1:
1--01/05/2004--1
3--15/05/2004--1
At Subscriber #2:
2--02/05/2004--2
6--01/07/2004--2
7--04/07/2004--2 (the new row)
And at Subscriber #3:
4--15/06/2004--3
5--19/06/2004--3
If anyone can tell me how to manage that problem, it will be a really Great help for me.
Sorry about my english level, it's my third language.
Thanks in advance for your comments.
"Hilary Cotter" wrote:

> a vertical filter is where you replicate a subset of the columns, a
> horizontal filter is a where you replicate a subset of the rows in the
> table.
> I take it you want to do horizontal filtering or partitioning. To do this
> right click on your publication and select publication properties, click on
> the filter rows tab. locate the tables you wish to filter, and click on the
> browse button to the right of the table/article. You will get the specify
> filter dialog. Enter your filter there.
> After your have made your changes, regenerate your snapshot, and distribute
> it to your subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
> news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
> the internet.
> the other servers based on (Insert/Update/Delete) transactions. I called
> that kind static tables.
> to ALL the subscribers.
> must be propagated to clients (subscribers) based on their informations. I
> called that kind dynamic tables.
> 'Entreprise_ID', that have data about all operations concerning some
> entreprises. Those real entreprises have also SQL servers (wich shall be
> used as subscribers) to get data from the main server, but Entreprise X must
> receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
> little bit, so i thought that in my case i should use Transactional
> Replication if we suppose that data is a bit critical and must be refreshed
> on all the servers.
> replication over the internet concerning what i called 'static tables' , it
> works very well.
> it, i've seen that i can apply 'vertical filters' on those tables, but i
> don't know how to associate it with subscribers informations in order to
> send only appropriate data to each subscriber.
>
>