If we had two SQL 2000 servers each with 2 NIC's. For each server one network card was pointing to an internal network 172.16.x.xxx we use for production. The other NIC (say its 198.16.x.xxx) we wanted to use just for replication purposes between the two servers in an attempt to lighten the network traffic on the 172 network. My question is, can you set up 2 SQL servers to replicate between themselves on a specific IP or Named Pipe?If both server is in different geographical area or on a WAN, you will face a big threat from anyone in the world who wrote a simple program to guess your user "sa" password or any user account. To post that on a WAN, its preferable to go through a firewall.
There are 2 options:-
1) get a 1gigabit network adapter card and a switch that support 1GB.
2) make use of Client Utility Connection on both SQL server. Configured according to your Server Name with static IP address and specify the port that is use by both server. This will tell the replication agents to call the static ip.
Originally posted by sdonovan
If we had two SQL 2000 servers each with 2 NIC's. For each server one network card was pointing to an internal network 172.16.x.xxx we use for production. The other NIC (say its 198.16.x.xxx) we wanted to use just for replication purposes between the two servers in an attempt to lighten the network traffic on the 172 network. My question is, can you set up 2 SQL servers to replicate between themselves on a specific IP or Named Pipe?
Showing posts with label internal. Show all posts
Showing posts with label internal. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Replication Suggestions
I have an application with a sql backend. My company has an external
database on the webbox and an internal database for inter-company use. I
was given the job of replicating this database between external and internal.
I have 3 sql servers; BOX1 is external box that is used to run web frontend
and house the external backend of app, BOX2 is internal (conduit between
internal and external), BOX3 is another internal box that is used for backend
db of the internal frontend (hope this makes sense). BOX2 can only be a pull
subscription from BOX1 and somehow this has to repl to BOX3. This
application is a third party vendor app so I cannot modify the data
structure. I'm new to replication and I'm not sure what the best way to
accomplish this task. I tried transactional but I ran into a snag with 15 of
the 120 tables that do not have unique id's. Any ideas or suggestions
would be great.
Thanks in Advance,
rob
What you are trying to do is called republishing. Set up Box2 to be a
publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you are
creating your articles you select the snapshot option, keep existing table
unchanged.
Regarding your problem with a lack of unique id's - I think you mean a lack
of Primary Keys. Examine the data to see if you can't put primary keys on
these tables - they are updating and deleting rows on these tables according
to some criteria used to uniquely identify them - if you can figure out what
this criteria is - make this your pk.
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
>I have an application with a sql backend. My company has an external
> database on the webbox and an internal database for inter-company use. I
> was given the job of replicating this database between external and
> internal.
> I have 3 sql servers; BOX1 is external box that is used to run web
> frontend
> and house the external backend of app, BOX2 is internal (conduit between
> internal and external), BOX3 is another internal box that is used for
> backend
> db of the internal frontend (hope this makes sense). BOX2 can only be a
> pull
> subscription from BOX1 and somehow this has to repl to BOX3. This
> application is a third party vendor app so I cannot modify the data
> structure. I'm new to replication and I'm not sure what the best way to
> accomplish this task. I tried transactional but I ran into a snag with 15
> of
> the 120 tables that do not have unique id's. Any ideas or suggestions
> would be great.
> Thanks in Advance,
> rob
>
|||Like Hillary states, some refer this process as cascading from A to B, from
B to C. Works well as long as the transactions are all going in one
direction. With Regards to unique ids, you are probably refering to PKs.
Follw Hillary's advise.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPWgfQCDGHA.1816@.TK2MSFTNGP11.phx.gbl...
> What you are trying to do is called republishing. Set up Box2 to be a
> publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you
are
> creating your articles you select the snapshot option, keep existing table
> unchanged.
> Regarding your problem with a lack of unique id's - I think you mean a
lack
> of Primary Keys. Examine the data to see if you can't put primary keys on
> these tables - they are updating and deleting rows on these tables
according
> to some criteria used to uniquely identify them - if you can figure out
what[vbcol=seagreen]
> this criteria is - make this your pk.
> --
> 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
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
I[vbcol=seagreen]
to[vbcol=seagreen]
15
>
database on the webbox and an internal database for inter-company use. I
was given the job of replicating this database between external and internal.
I have 3 sql servers; BOX1 is external box that is used to run web frontend
and house the external backend of app, BOX2 is internal (conduit between
internal and external), BOX3 is another internal box that is used for backend
db of the internal frontend (hope this makes sense). BOX2 can only be a pull
subscription from BOX1 and somehow this has to repl to BOX3. This
application is a third party vendor app so I cannot modify the data
structure. I'm new to replication and I'm not sure what the best way to
accomplish this task. I tried transactional but I ran into a snag with 15 of
the 120 tables that do not have unique id's. Any ideas or suggestions
would be great.
Thanks in Advance,
rob
What you are trying to do is called republishing. Set up Box2 to be a
publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you are
creating your articles you select the snapshot option, keep existing table
unchanged.
Regarding your problem with a lack of unique id's - I think you mean a lack
of Primary Keys. Examine the data to see if you can't put primary keys on
these tables - they are updating and deleting rows on these tables according
to some criteria used to uniquely identify them - if you can figure out what
this criteria is - make this your pk.
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
>I have an application with a sql backend. My company has an external
> database on the webbox and an internal database for inter-company use. I
> was given the job of replicating this database between external and
> internal.
> I have 3 sql servers; BOX1 is external box that is used to run web
> frontend
> and house the external backend of app, BOX2 is internal (conduit between
> internal and external), BOX3 is another internal box that is used for
> backend
> db of the internal frontend (hope this makes sense). BOX2 can only be a
> pull
> subscription from BOX1 and somehow this has to repl to BOX3. This
> application is a third party vendor app so I cannot modify the data
> structure. I'm new to replication and I'm not sure what the best way to
> accomplish this task. I tried transactional but I ran into a snag with 15
> of
> the 120 tables that do not have unique id's. Any ideas or suggestions
> would be great.
> Thanks in Advance,
> rob
>
|||Like Hillary states, some refer this process as cascading from A to B, from
B to C. Works well as long as the transactions are all going in one
direction. With Regards to unique ids, you are probably refering to PKs.
Follw Hillary's advise.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPWgfQCDGHA.1816@.TK2MSFTNGP11.phx.gbl...
> What you are trying to do is called republishing. Set up Box2 to be a
> publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you
are
> creating your articles you select the snapshot option, keep existing table
> unchanged.
> Regarding your problem with a lack of unique id's - I think you mean a
lack
> of Primary Keys. Examine the data to see if you can't put primary keys on
> these tables - they are updating and deleting rows on these tables
according
> to some criteria used to uniquely identify them - if you can figure out
what[vbcol=seagreen]
> this criteria is - make this your pk.
> --
> 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
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
I[vbcol=seagreen]
to[vbcol=seagreen]
15
>
Friday, March 23, 2012
replication problems with structure/trigger/identity
Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
replication over a wan
Howdy kids. We are moving our servers to a new location here shortly. One of
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
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
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>
Subscribe to:
Posts (Atom)