Consolidate multiple databases via merge replication

Hi
I am trying to merge a set of tables from 5 separate databases (4 on
separate servers and 2 on the same server) into one 'master' database.
The tables being replicated all have unique ID ranges
How to I organise the replication so that the subscriber database can allow
inserts which fit into the ID ranges for the related Publishers database.
ie All inserts with a Lab_ID = 'L' should ONLY be replicated back to the
associated publisher.
I am not familiar with the automatic identity range handling .. can anyone
point me to an article about how this works ?
any help would be appreciated
cheers
mike
Mike,
as you've got multiple publishers, just ordinary filters will work to
restrict the flow of data to each publisher. For identity range handling,
you'll need to design each publisher's range yourself. The subscriber table
can be created during the first initialization, and all others are nosync
ones. So, the subscriber's identity range is defined after the first
initialization, and subsequent publications need to be sure not to overlap
with this range. Make the ranges large, because assignment of another range
might cause an overlap, so if the range is so big there will never be any
need to reset then this is best.
Alternatively, all subscriptions can be nosync ones.
These might help:
http://www.replicationanswers.com/No...alizations.asp
http://www.replicationanswers.com/ManualIdentities.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Cons to seperate distributor

What are the cons of have the distribution database on its own seperate
server from the publishers with regards to transactional replication ? I am
more concerned from a latency perspective ?
Would the changes be a bit slower to propagate to the subscriber as a result
of this ?
Let me know
Using SQL 2000.
Thanks
Its very slightly slower, but the network hop isn't really detectable. The
real problem is you have introduced a single point of failure and need to
cluster the distributor as if it goes down your tlog on your publisher can
get very large very quickly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:ukDRx3BQGHA.2992@.tk2msftngp13.phx.gbl...
> What are the cons of have the distribution database on its own seperate
> server from the publishers with regards to transactional replication ? I
> am more concerned from a latency perspective ?
> Would the changes be a bit slower to propagate to the subscriber as a
> result of this ?
> Let me know
> Using SQL 2000.
> Thanks
>
|||Hassan,
in terms of overall latency, using a remote distributor will be lower (ie
faster from Pub -> Sub) when there are a lot of transactions. Have a look at
this article for some stats:
http://www.microsoft.com/technet/pro.../tranrepl.mspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Connfig Replication via Internet

I want to replicate data via Internet using FTP Server, i have SQL 2K with SP3. i set up client network as
- Server alias: my local server's name
- Server Name: ip address on internet
- Network libraries: TCP/IP
- Port : 1433
But i connect using Enterprise Manage to SQL Server then it errors:
A Connection could not be established to Dis-server ( Dis-server is my servername)
Reason: SQL Server does not exist or accessed denied
ConnectionOpen(Connect())..
please help me..tks
Regards
NBtri
from the subscriber can you ping your publisher?
On your publisher and subscriber you have to configure the subsciber and
publisher using client network utility.
Make sure that the publsiher and subscriber are listed in the hosts file
(thanks Paul Ibison for this tidbit of tidbitosity)
Then on your Publisher go to Tools - replication - Configure Publishers,
subscribers, distributors, click on the subscribers tab, and click on the
check box to the right of your subscriber server name. Ensure you are using
a SQL Server account, and enter the subscriber sa (or an account in the
system administrator role) onthe subscriber and its password.
Then on your subscriber, set up a pull subscription.
"NBTri" <anonymous@.discussions.microsoft.com> wrote in message
news:8ABF42AE-6921-4A63-8479-CD7910909F89@.microsoft.com...
> I want to replicate data via Internet using FTP Server, i have SQL 2K with
SP3. i set up client network as
> - Server alias: my local server's name
> - Server Name: ip address on internet
> - Network libraries: TCP/IP
> - Port : 1433
> But i connect using Enterprise Manage to SQL Server then it errors:
> A Connection could not be established to Dis-server ( Dis-server is my
servername)
> Reason: SQL Server does not exist or accessed denied
> ConnectionOpen(Connect())..
>
> please help me..tks
> Regards
> NBtri
>

connectivity in sql server with two diff public ip address

I 've two machines (Linux Servers) with firewall installed at two
geographical locations. Now I want to make replication of data using
sqlserver2000 from two other machines which are behind the two servers . Both
of them have have private IP address and they can access the internet
through the firewall . Now I have enabled the ports 1433 on both sides and
had given the IP address of the receiving machine at the sending machine
(Public IP at the network client -alias TCP/IP protocal) and vice versa.
Proper mapping of IP (public to private is done ) at the two servers.
But still I 'm getting the message, Failed to register the Sql Server
Pl help
Soumen,
you'll need to register an alias. Have a look at this article:
http://support.microsoft.com/?id=321822
(these may also be of interest
http://www.microsoft.com/resources/d...rt5/sqc07.mspx
http://support.microsoft.com/default...32&Product=sql)
HTH,
Paul Ibison
|||Did you define these two servers using Client Network Utiltity?
If you did, you can then create ODBC DSN's to them and test general
connectivity, or merely register these SQL Server as enabled Subscribers, or
register them in EM.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Soumen ( India)" <SoumenIndia@.discussions.microsoft.com> wrote in message
news:57461CAD-F259-4D49-8710-7217F7AFFE43@.microsoft.com...
> I 've two machines (Linux Servers) with firewall installed at two
> geographical locations. Now I want to make replication of data using
> sqlserver2000 from two other machines which are behind the two servers .
Both
> of them have have private IP address and they can access the internet
> through the firewall . Now I have enabled the ports 1433 on both sides and
> had given the IP address of the receiving machine at the sending machine
> (Public IP at the network client -alias TCP/IP protocal) and vice versa.
> Proper mapping of IP (public to private is done ) at the two servers.
> But still I 'm getting the message, Failed to register the Sql Server
> Pl help

Connection Problem with the Workstation

Hi all;

I am trying to replicate our database by using the merge replication.
>From server side I tried to connect to the workstation. I have
installed MSDE to the client machine. I can see the machine name on the
SQL Server Group however I wasnt able to create the conncetion between
the machines. I have checked my network connection, there is no problem
I can see the machine and I can connect it through the network. I tried
to use Client Network Utility with Named Pipes and TCP/IP but not a
good news. I couldnt achieve to connect to the machine. So could
someone please help me out with this problem. I am really in trouble I
have to find out this end of this week.

Regards
As"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1104862206.970118.48710@.c13g2000cwb.googlegro ups.com...
> Hi all;
> I am trying to replicate our database by using the merge replication.
>>From server side I tried to connect to the workstation. I have
> installed MSDE to the client machine. I can see the machine name on the
> SQL Server Group however I wasnt able to create the conncetion between
> the machines. I have checked my network connection, there is no problem
> I can see the machine and I can connect it through the network. I tried
> to use Client Network Utility with Named Pipes and TCP/IP but not a
> good news. I couldnt achieve to connect to the machine. So could
> someone please help me out with this problem. I am really in trouble I
> have to find out this end of this week.
> Regards
> As

You don't mention your version of MSDE, but network access is disabled in
MSDE 2000 SP3a:

http://support.microsoft.com/kb/814130

Simon|||yeah I have installed 2000 SP3a then should I install SQL2000 client
version to my workstation?|||"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1104873576.019320.324770@.z14g2000cwz.googlegr oups.com...
> yeah I have installed 2000 SP3a then should I install SQL2000 client
> version to my workstation?

If you have MSDE 2000 SP3a, then by default you cannot access it from the
network, so that is probably why you cannot connect to the MSDE installation
on your workstation from your server (if I understood your problem
correctly). As mentioned in the KB article, you can use the MSSQL network
utility to enable network support, and then you should be able to connect to
MSDE from other computers.

Simon