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.
>
>

Replication based on Primary key, or Unique Key

Hi everybody,
I am looking to establish a merge replication, whereby the row is identified
by a unique key, than might be a combination of multile coloumns
Example If the inique id of a row is column1,column2
Whenever this row is changed in any of teh sites, the replication should
updates records having the same ID (column1, column2) In other sites.
Thanks
merge replication adds a unique key - a guid. So unless I am missing
something - merge replication will by default do what you require.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> Hi everybody,
> I am looking to establish a merge replication, whereby the row is
identified
> by a unique key, than might be a combination of multile coloumns
> Example If the inique id of a row is column1,column2
> Whenever this row is changed in any of teh sites, the replication should
> updates records having the same ID (column1, column2) In other sites.
> Thanks
>
|||Let's take the following example.
Site 1 and site 2 have the same row column 1=x, column 2= y, with a guid=g1
Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g2
Before synchr happens,
Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
have a guid=g3
Once the Data bases are synchronized we will have 2 rows having column 1=x,
column 2= y, guid=2, and guid-3
Zrod
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> merge replication adds a unique key - a guid. So unless I am missing
> something - merge replication will by default do what you require.
> --
> 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
> "zrod" <zrod@.aims-co.com> wrote in message
> news:e9$EnScGFHA.2744@.tk2msftngp13.phx.gbl...
> identified
>
|||Ok - let me get this straight you have a pk of col1 and col2. You whack it
at the publisher and subscriber, and then add it in again with the same
values.
What will happen is when the merge agent runs, you will get a pk collision.
By default the publisher's row will win, and the row on the subscriber will
be overwritten by the publisher's row. The conflict will be logged to the
conflict tables and will be visible using the conflict viewer.
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
"zrod" <zrod@.aims-co.com> wrote in message
news:%23590mteGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> Let's take the following example.
> Site 1 and site 2 have the same row column 1=x, column 2= y, with a
guid=g1
> Site 1, delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g2
> Before synchr happens,
> Site 2 delete the row and recreate it as column 1=x, column 2= y, it will
> have a guid=g3
> Once the Data bases are synchronized we will have 2 rows having column
1=x,[vbcol=seagreen]
> column 2= y, guid=2, and guid-3
> Zrod
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23dL4RgdGFHA.2524@.TK2MSFTNGP15.phx.gbl...
should
>