Replication between never-connected SQL Servers

Is it possible to set-up replication between two servers that are never
directly connected?
We have a production server that is not connected to our network for
security reasons. I am doing testing on a local server and then want to
periodically apply the updates to the production server via CD. I haven't
been able to do this with replication as it seems to require a connection to
the subscription db for the initial snapshot.
At the moment, the only way I have been able to do this is by creating a
backup and recreating the db on the remote server.
Thanks!
James
James,
replication will require a connection between servers at some point. Where a
connection is not possible, you can use a derivative of log shipping or
database shipping as you are doing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very much Paul.
James
"Paul Ibison" wrote:

> James,
> replication will require a connection between servers at some point. Where a
> connection is not possible, you can use a derivative of log shipping or
> database shipping as you are doing.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Replication Architecture/Design advise

I have been asked to set-up replication in the current two server
enviornment. My understanding is that Log-Shipping requires
the Enterprise version of Sql Server 2000 (Which they do not
have the budget for).
They have 70 Databases that have a total space of aprox 30gb.
They want to replicate the data to the standby server so that
in the case of a failure they can redirect their Web Sites
to the standby Server.
Both servers will be in a co-lo facility so bandwidth between
the servers should be adequate.
Do I do central publisher with the distributor on the standby
server? I am not sure what kind of load this will have. I
understand that people recommend to put it on the publisher
server and only move it if load becomes an issue.
I understand that security (ie, users and permissions) will
not replicate. Other than having to set things up twice is
their other solutions to minimize this?
Sincerely,
Dave
you do not need the Enterprise Edition of SQL Server for log shipping. This
is a mistake in BOL.
Ideally what you want is automatic failover which means hardware or software
data mirroring - however it can be accomplished using log shipping, but you
will have to redirect your clients to the failed over server. You can then
log ship back to the original primary.
If you choose to use replication - I would use bi-directional transactional
replication keeping in mind
1) that every table you are replicating needs a pk
2) system objects and logins are not replicated
3) bi-directional transactional replication is not resilient to schema
changes (i.e. you have to drop your subscriptions and publication when you
want to make schema changes). Other replication types support partial schema
changes using sp_repladdcolumn and sp_repldropcolumn.
For log shipping you will find that
1) its not really scalable - I think you will find the logistical burden log
shipping 70 databases to be a bit overwhelming
2) your standby servers' databases will be read only
For replication you would be using a single publisher and subscriber. It is
a central publisher with one subscriber.
"David Gresham" <gresham@.panix.com> wrote in message
news:d50tct$5je$1@.reader1.panix.com...
>I have been asked to set-up replication in the current two server
> enviornment. My understanding is that Log-Shipping requires
> the Enterprise version of Sql Server 2000 (Which they do not
> have the budget for).
>
> They have 70 Databases that have a total space of aprox 30gb.
> They want to replicate the data to the standby server so that
> in the case of a failure they can redirect their Web Sites
> to the standby Server.
>
> Both servers will be in a co-lo facility so bandwidth between
> the servers should be adequate.
>
> Do I do central publisher with the distributor on the standby
> server? I am not sure what kind of load this will have. I
> understand that people recommend to put it on the publisher
> server and only move it if load becomes an issue.
>
> I understand that security (ie, users and permissions) will
> not replicate. Other than having to set things up twice is
> their other solutions to minimize this?
>
> Sincerely,
>
>
> Dave
>
|||In article <umaTbQdTFHA.2172@.tk2msftngp13.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>you do not need the Enterprise Edition of SQL Server for log shipping. This
>is a mistake in BOL.
>Ideally what you want is automatic failover which means hardware or software
>data mirroring - however it can be accomplished using log shipping, but you
>will have to redirect your clients to the failed over server. You can then
>log ship back to the original primary.
>If you choose to use replication - I would use bi-directional transactional
>replication keeping in mind
>1) that every table you are replicating needs a pk
>2) system objects and logins are not replicated
>3) bi-directional transactional replication is not resilient to schema
>changes (i.e. you have to drop your subscriptions and publication when you
>want to make schema changes). Other replication types support partial schema
>changes using sp_repladdcolumn and sp_repldropcolumn.
>
Can you expand on what replication model this is? Is this Merge?
Does this mean that the standby server can update the primary?
My situation for this project is that the primary will update
the standby server and will only be used when the primary fails.
My other question is: Lets say we need to rebuild the primary
server. We re-build it and need to bring the databases back
from the standby server. Do we have to start over from scratch?
Or do we need to some-how rebuild everything? I am going to
be ordering your book today. I have worked with replication
before when all we were doing was creating a reporting database
which was read-only.
The standby concept seemed to follow this except for when we
needed to switch over to it. The client hosts several
web sites.
Thanks
Dave

>For log shipping you will find that
>1) its not really scalable - I think you will find the logistical burden log
>shipping 70 databases to be a bit overwhelming
>2) your standby servers' databases will be read only
>For replication you would be using a single publisher and subscriber. It is
>a central publisher with one subscriber.
>"David Gresham" <gresham@.panix.com> wrote in message
>news:d50tct$5je$1@.reader1.panix.com...
>
|||This is transactional, bi-directional transactional replication to be
specific, not to be confused with transactional replication with immediate
updating, transactional replication with queued updating, transactional
replication with immediate updating with queued failover, or merge
replication. However merge replication might work for you, although it will
add a guid column to each column you are replicating.
It does mean that your standby could update the primary, and it is an
excellent choice when the primary goes offline and all database activity
occurs on the standby server then.
Ideally you would use bi-directional replication in a case where your
publisher goes offline for an extended time period. When it comes back
online you should backup the standby by restore it on the publisher,
recreate the publications and subscriptions on the publisher and then do a
no sync,
Review this link for more info on these options.
http://support.microsoft.com/default...b;en-us;820675
"David Gresham" <gresham@.panix.com> wrote in message
news:d52qja$4j4$1@.reader1.panix.com...
> In article <umaTbQdTFHA.2172@.tk2msftngp13.phx.gbl>,
> Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Can you expand on what replication model this is? Is this Merge?
> Does this mean that the standby server can update the primary?
> My situation for this project is that the primary will update
> the standby server and will only be used when the primary fails.
> My other question is: Lets say we need to rebuild the primary
> server. We re-build it and need to bring the databases back
> from the standby server. Do we have to start over from scratch?
> Or do we need to some-how rebuild everything? I am going to
> be ordering your book today. I have worked with replication
> before when all we were doing was creating a reporting database
> which was read-only.
> The standby concept seemed to follow this except for when we
> needed to switch over to it. The client hosts several
> web sites.
> Thanks
>
> Dave
>
>
|||In article <eOqh7CmTFHA.544@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>This is transactional, bi-directional transactional replication to be
>specific, not to be confused with transactional replication with immediate
>updating, transactional replication with queued updating, transactional
>replication with immediate updating with queued failover, or merge
>replication. However merge replication might work for you, although it will
>add a guid column to each column you are replicating.
>It does mean that your standby could update the primary, and it is an
>excellent choice when the primary goes offline and all database activity
>occurs on the standby server then.
>Ideally you would use bi-directional replication in a case where your
>publisher goes offline for an extended time period. When it comes back
>online you should backup the standby by restore it on the publisher,
>recreate the publications and subscriptions on the publisher and then do a
>no sync,
>Review this link for more info on these options.
>http://support.microsoft.com/default...b;en-us;820675
>
In reading this document I got a 'little' intimidated by the need
for all these custom stored procedures. Since the server will
only be used in such a way that there would never be any
conflicts do I need to worry about this?
I also wanted to clarify where the distributor is in this set-up,
the Subscriber?
Thanks
Dave
sql

Replicating very large articles but need to avoid locks from snap-

I have a very large and very active table which I want to replicate to
another server. How can I set-up a Publication for this table and avoid the
locking during the initial snap-shot in SQL 2005? SQL 2000 allowed
configuring replication without generating a snap-shot - assumed data was
already present. I used this technique and would update unique columns on
the source or primary key to send over complete row delete & inserts to
finally achieve sync'. I'm looking for a similiar work-around in SQL 2005
but don't see it. I'm aware of initializing from a backup but that seems
extreme for just a ~5 of 400 tables I want to replicate. Any ideas how to
skin this cat would be appreciated. Thanks. -CqlBoy
The locking which occurs in SQL 2005 is much less than the default locking
which would occur in SQL 2000.
In SQL 2005 you have the no-sync option as well. To do this create your
publication and then create your subscription as per normal.
Then when you get to the initialize Subscriptions dialog uncheck initialize.
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:47464A31-3AD8-4A5A-9558-3009CE84A498@.microsoft.com...
>I have a very large and very active table which I want to replicate to
> another server. How can I set-up a Publication for this table and avoid
> the
> locking during the initial snap-shot in SQL 2005? SQL 2000 allowed
> configuring replication without generating a snap-shot - assumed data was
> already present. I used this technique and would update unique columns on
> the source or primary key to send over complete row delete & inserts to
> finally achieve sync'. I'm looking for a similiar work-around in SQL 2005
> but don't see it. I'm aware of initializing from a backup but that seems
> extreme for just a ~5 of 400 tables I want to replicate. Any ideas how to
> skin this cat would be appreciated. Thanks. -CqlBoy
|||Is it also possible to force at the Publisher like in SQL 2000 to send a pair
of DELETE/INSERT statements for an entire row by performing an UPDATE on a
primary key or column that was unique? This was an issue in SQL Server 2000
when the PK was an identiy column, I couldn't update the PK and force the
DELETE/INSERT but their was a trace flag that would trigger this behavior on
any column, thus making it possible to sync' an entire row.
Thanks.
-Blake
"Hilary Cotter" wrote:

> The locking which occurs in SQL 2005 is much less than the default locking
> which would occur in SQL 2000.
> In SQL 2005 you have the no-sync option as well. To do this create your
> publication and then create your subscription as per normal.
> Then when you get to the initialize Subscriptions dialog uncheck initialize.
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:47464A31-3AD8-4A5A-9558-3009CE84A498@.microsoft.com...
>
>
|||I think this is what you are referring to in SQL 2000. It is my
understanding that in SQL 2005, deferred updates are now replicated as an
update not a delete/insert pair, however I can't seem to find any official
documentation supporting it.
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...[vbcol=seagreen]
> Is it also possible to force at the Publisher like in SQL 2000 to send a
> pair
> of DELETE/INSERT statements for an entire row by performing an UPDATE on a
> primary key or column that was unique? This was an issue in SQL Server
> 2000
> when the PK was an identiy column, I couldn't update the PK and force the
> DELETE/INSERT but their was a trace flag that would trigger this behavior
> on
> any column, thus making it possible to sync' an entire row.
> Thanks.
> -Blake
> "Hilary Cotter" wrote:
|||Hi Blake, being the person responsible for most of snapshot processing in
transactional\snapshot replication, I must admit that it is a bit difficult
for me to read your posts. That said, in the interest of product
improvement, I must ask what sort of problems you have experienced in
SQL2000 (I can tell locking being one of them) that causes you to develop
such an intense aversion against using replication snapshot processing at
all.
As Hilary had mentioned, locking during snapshot generation for
transactional replication is much reduced by default in SQL2005, and you can
further reduce it by using the new 'database snapshot' sync_method if you
are running Enterprise Edition. In addition, I had also put in quite a few
performance improvements for snapshot processing in SQL2005 which should
hopefully make replication snapshot processing less painful to use. As such,
I would really appreciate if you can give replication snapshot processing a
try in SQL2005.
Thanks much,
-Raymond
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...[vbcol=seagreen]
> Is it also possible to force at the Publisher like in SQL 2000 to send a
> pair
> of DELETE/INSERT statements for an entire row by performing an UPDATE on a
> primary key or column that was unique? This was an issue in SQL Server
> 2000
> when the PK was an identiy column, I couldn't update the PK and force the
> DELETE/INSERT but their was a trace flag that would trigger this behavior
> on
> any column, thus making it possible to sync' an entire row.
> Thanks.
> -Blake
> "Hilary Cotter" wrote:
|||Raymond, thank you for the response and tip regarding snapshot processing.
I need to investigate/experiment more with this. I am admittingly not
familiar yet with all of SQL 2005's offerings. Also, let me add, I'm not at
all averse to replication and used SQL 2000 Transactional replication
extensively at my former employer, Experian Corp.
Key things noted from my replication experience is this:
1) Taking an initial snap-shot against several large and important
order-related tables incurring heavy insert/update/delete activity
effectively took the company down until the snap-shot completed. This could
be hours, something upper management would never tolerate. Heaven forbid if
we lost sync' !
2) Publications containing hot articles presented significant loads to the
disk IO subsystem, something that very few DBA's are aware of or understand.
For example, if your publication contained articles responsible for much of
the server IO you need to be aware that this resource drain will effectively
double via the Distributor when replicated. This was a surprize to me when I
first discovered it but it makes perfect sense in hindsight.
3) The last issue came from the Distributor clean up job. Whenever this
executed, it exercised a massive IO spike against its raid group, thus
affecting everything associated with those disks. Yep, I tinkered with the
Clean-Up job schedule and tweaked the agent profiles but could never seem to
alleviate these massive spikes. I never made it this far, but I was
seriously contemplating customizing the code in the Distributor Clean-up
proc' to break up and spread the IO demand over a longer interval, anything
to avoid or reduce this sharp massive hits against the disks.
I learned and became more sensitized to these issues when tasked to
re-architect SQL Server on our EMC Clariion CX500 and CX700 SAN. I captured
file level IO stats using the little understood/known SQL Server function
fn_virtualstats as a source, sampling @. 1 minute intervals. Querying against
one weeks data was very enlightening.
I am aware of moving the Distributor to its own dedicated box/disks but
could never convince management to do so. BTW... it was a heavy bang against
the SAN cache and it seemed we had evidence that the Distributor Clean-Up job
cause global issues via the SAN. Evidence we out grew our SAN, I guess.
I apoligize for this lengthy response but the key answers I am looking for
is how to initialize replication for very large and critical, order-related
articles without taking the company down. Secondly, is their a practical
means of determing the additional burden to the TempDB ? Log ? ... and the
underlying disk subsystem. i.e. can I handle the load? I actually have a
rough idea on this last point but if you have anything to share I'd love to
hear it.
Thanks again for taking the time to respond.
Blake Colson a.k.a CqlBoy
Lead DBA of Operations
RealtyTrac, Inc.
Irvine, CA
"Raymond Mak [MSFT]" wrote:

> Hi Blake, being the person responsible for most of snapshot processing in
> transactional\snapshot replication, I must admit that it is a bit difficult
> for me to read your posts. That said, in the interest of product
> improvement, I must ask what sort of problems you have experienced in
> SQL2000 (I can tell locking being one of them) that causes you to develop
> such an intense aversion against using replication snapshot processing at
> all.
> As Hilary had mentioned, locking during snapshot generation for
> transactional replication is much reduced by default in SQL2005, and you can
> further reduce it by using the new 'database snapshot' sync_method if you
> are running Enterprise Edition. In addition, I had also put in quite a few
> performance improvements for snapshot processing in SQL2005 which should
> hopefully make replication snapshot processing less painful to use. As such,
> I would really appreciate if you can give replication snapshot processing a
> try in SQL2005.
> Thanks much,
> -Raymond
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@.microsoft.com...
>
>
|||Blake, I am probably not reading you previous post (the one before you
latest response) correctly but it seems to me that doing a (full table) bulk
update on the pk or uq will incur rather significant resource\locking
contention at the publisher database, and the logreader\distribution agent
will need to transfer almost twice the amount of data (delete\insert +
command formatting overhead) thereby putting further strain on the network
and the cleanup agent. That seems to be a bit of an extreme measure to avoid
replication snapshot processing. And as I have mentioned before, locking
overhead for snapshot generation is much reduced in SQL2005 by default and
you can use sync_method = 'database snapshot' to reduce that further. Paul
Ibison also has a nice article up on his website
(http://www.replicationanswers.com/BCPPartitioning.asp) talking about an
enhancement in SQL2005 that allows a large table to be bulk-copied by the
snapshot agent in parallel. That said, it is not my intention to be pushy
about this and there are indeed cases where all the enhancement in SQL2005
snapshot processing will be inadequate to scale (it is not a pretty sight
but I have seen people managed 100GBs snapshot on SQL2000). I am merely ...
intrigued... by your comments and I very much appreciate your (extremely
detailed) feedback.
-Ryamond
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:51FEF6A7-F5E5-4D54-BB9E-542EE39D5010@.microsoft.com...[vbcol=seagreen]
> Raymond, thank you for the response and tip regarding snapshot
> processing.
> I need to investigate/experiment more with this. I am admittingly not
> familiar yet with all of SQL 2005's offerings. Also, let me add, I'm not
> at
> all averse to replication and used SQL 2000 Transactional replication
> extensively at my former employer, Experian Corp.
> Key things noted from my replication experience is this:
> 1) Taking an initial snap-shot against several large and important
> order-related tables incurring heavy insert/update/delete activity
> effectively took the company down until the snap-shot completed. This
> could
> be hours, something upper management would never tolerate. Heaven forbid
> if
> we lost sync' !
> 2) Publications containing hot articles presented significant loads to
> the
> disk IO subsystem, something that very few DBA's are aware of or
> understand.
> For example, if your publication contained articles responsible for much
> of
> the server IO you need to be aware that this resource drain will
> effectively
> double via the Distributor when replicated. This was a surprize to me
> when I
> first discovered it but it makes perfect sense in hindsight.
> 3) The last issue came from the Distributor clean up job. Whenever this
> executed, it exercised a massive IO spike against its raid group, thus
> affecting everything associated with those disks. Yep, I tinkered with
> the
> Clean-Up job schedule and tweaked the agent profiles but could never seem
> to
> alleviate these massive spikes. I never made it this far, but I was
> seriously contemplating customizing the code in the Distributor Clean-up
> proc' to break up and spread the IO demand over a longer interval,
> anything
> to avoid or reduce this sharp massive hits against the disks.
> I learned and became more sensitized to these issues when tasked to
> re-architect SQL Server on our EMC Clariion CX500 and CX700 SAN. I
> captured
> file level IO stats using the little understood/known SQL Server function
> fn_virtualstats as a source, sampling @. 1 minute intervals. Querying
> against
> one weeks data was very enlightening.
> I am aware of moving the Distributor to its own dedicated box/disks but
> could never convince management to do so. BTW... it was a heavy bang
> against
> the SAN cache and it seemed we had evidence that the Distributor Clean-Up
> job
> cause global issues via the SAN. Evidence we out grew our SAN, I guess.
> I apoligize for this lengthy response but the key answers I am looking for
> is how to initialize replication for very large and critical,
> order-related
> articles without taking the company down. Secondly, is their a practical
> means of determing the additional burden to the TempDB ? Log ? ... and
> the
> underlying disk subsystem. i.e. can I handle the load? I actually have a
> rough idea on this last point but if you have anything to share I'd love
> to
> hear it.
> Thanks again for taking the time to respond.
> Blake Colson a.k.a CqlBoy
> Lead DBA of Operations
> RealtyTrac, Inc.
> Irvine, CA
>
> "Raymond Mak [MSFT]" wrote:
|||Sort of a (perhaps not so useful) data point, I have seen from internal
testing that the SQL2005 snapshot agent is able to pull 100MB(ytes)/s on an
admittedly high end server locally (8-way 3.2Ghz Intel proc [P4 based?] &
8GB RAM & 3disk RAID0). Granted that the data we used is artificially
generated but still.
-Raymond
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:uc4Q3wSiHHA.1708@.TK2MSFTNGP03.phx.gbl...
> Blake, I am probably not reading you previous post (the one before you
> latest response) correctly but it seems to me that doing a (full table)
> bulk update on the pk or uq will incur rather significant resource\locking
> contention at the publisher database, and the logreader\distribution agent
> will need to transfer almost twice the amount of data (delete\insert +
> command formatting overhead) thereby putting further strain on the network
> and the cleanup agent. That seems to be a bit of an extreme measure to
> avoid replication snapshot processing. And as I have mentioned before,
> locking overhead for snapshot generation is much reduced in SQL2005 by
> default and you can use sync_method = 'database snapshot' to reduce that
> further. Paul Ibison also has a nice article up on his website
> (http://www.replicationanswers.com/BCPPartitioning.asp) talking about an
> enhancement in SQL2005 that allows a large table to be bulk-copied by the
> snapshot agent in parallel. That said, it is not my intention to be pushy
> about this and there are indeed cases where all the enhancement in SQL2005
> snapshot processing will be inadequate to scale (it is not a pretty sight
> but I have seen people managed 100GBs snapshot on SQL2000). I am merely
> ... intrigued... by your comments and I very much appreciate your
> (extremely detailed) feedback.
> -Ryamond
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:51FEF6A7-F5E5-4D54-BB9E-542EE39D5010@.microsoft.com...
>