Replication and secure off-site servers

FYI - I know basically next to nothing about replication.
We have production servers that are located in a secure location entirely
separate from our own location and networkd. We want to set up a hot-copy
that is, at most, a few minutes behind production but that would be located
in our own office, on our own network. Any clues as to whether this is
possible using SQL Server replication or would we need to consider a third
party solution, such as that offered by DataMirror?
Thanks,
Michael MacGregor
Database Architect
Michael,
some people have used transactional replication for this purposes. To keep
the 2 systems entirely in sync will require careful planning though, as not
all objects can be replicated - eg permissions, users and indexes, and by
default you'll lose identity attributes. Certainly I can see queued updating
subscribers being used in this way, which avoids the issues of not having
the identity columns, but it still leaves the matter of the other objects
being manually synchronized. Log shipping on the other hand will take all
the extra objects. In theory the log could be shipped once a minute but in
my experience you'll be pushing it to get a very low latency. I suspect this
might be the best option for your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||How does that work when there is a firewall between the Production server
and the hot-copy?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
> Michael,
> some people have used transactional replication for this purposes. To keep
> the 2 systems entirely in sync will require careful planning though, as
not
> all objects can be replicated - eg permissions, users and indexes, and by
> default you'll lose identity attributes. Certainly I can see queued
updating
> subscribers being used in this way, which avoids the issues of not having
> the identity columns, but it still leaves the matter of the other objects
> being manually synchronized. Log shipping on the other hand will take all
> the extra objects. In theory the log could be shipped once a minute but in
> my experience you'll be pushing it to get a very low latency. I suspect
this
> might be the best option for your case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hello Michael,
It is suggested that you create a VPN connection to the remote network so
that publisher/subscriber are virtually in the same network. You may want
to adjust the repliciation agent profile parameters so that it can work
properly on a low bandwidth.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Mon, 15 Aug 2005 12:53:49 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15827
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How does that work when there is a firewall between the Production server
| and the hot-copy?
|
| "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
| news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
| > Michael,
| > some people have used transactional replication for this purposes. To
keep
| > the 2 systems entirely in sync will require careful planning though, as
| not
| > all objects can be replicated - eg permissions, users and indexes, and
by
| > default you'll lose identity attributes. Certainly I can see queued
| updating
| > subscribers being used in this way, which avoids the issues of not
having
| > the identity columns, but it still leaves the matter of the other
objects
| > being manually synchronized. Log shipping on the other hand will take
all
| > the extra objects. In theory the log could be shipped once a minute but
in
| > my experience you'll be pushing it to get a very low latency. I suspect
| this
| > might be the best option for your case.
| > Cheers,
| > Paul Ibison SQL Server MVP, www.replicationanswers.com
| > (recommended sql server 2000 replication book:
| > http://www.nwsu.com/0974973602p.html)
| >
| >
|
|
|
|||Michael,
I agree with Peter's advice re VPN, but if this is not possible, port 1433
(or whatever your sql port is) will need to be opened, as well as your FTP
port. This can still be locked down by restricting access to certain IP
address subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In SQL 2005, are the replication processes improved, are there better
interfaces, etc., compared to SQL 2000/
Michael MacGregor
Database Architect
|||Hello Michael,
As I know, there are some improvments in replication in such as management,
performance, monitor etc. I suggest that you go to SQL 2005 beta newsgroup
for more details about this.
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
<O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
<zGjivHioFHA.940@.TK2MSFTNGXA01.phx.gbl>
<#x61AvjoFHA.2180@.TK2MSFTNGP15.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Fri, 19 Aug 2005 10:44:49 -0400
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <#y5qXyMpFHA.3536@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15951
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| In SQL 2005, are the replication processes improved, are there better
| interfaces, etc., compared to SQL 2000/
|
| Michael MacGregor
| Database Architect
|
|
|
|||Michael,
I haven't done any performance measurements yet so can't really compare this
side of things.
From the interface point of view, the basic functionality is enhanced (eg
Hostname as a subscription property in Merge, tracer tokens exist in
Transactional), there are a few completely new areas (eg Oracle -> SQL,
Merge over HTTP) and GUIwise, the Replication Monitor is now isolated as a
separate application. I've written a few basic articles on
www.replicationanswers.com on a few of these.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)