SQL replication

[SQL_Newbie]

Is it possible to use a backup copy of a database in SQL to create
replication instead of using a snapshot??

[Paul Ibison]

Have a look in books-on-line for 'subscriptions, attachable databases' -
this should be what you require. Alternatively, if you really want to use
the backup, you could do a nosync initialization.

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[jxstern]

Yes.

Kick all users off both publisher and subscriber. Take backup. Ship
backup. Restore backup. Start replication. Allow users back on both
systems.

You may want to script out the entire replication process and run from
the scripts instead of from EM in order to maintain proper control
over all the steps, but it works fine.

The total replication with snapshot system is very capable and allows
you to establish replication even if either or both publisher and
subscriber are 24x7, but if you have the luxury of shutting down
operations for an hour or so, you can use backups instead of
snapshots, which I found many times faster.

Josh

[jxstern]

On Thu, 04 Aug 2005 17:34:16 -0700, jxstern <jxs...@nowhere.xyz>
wrote:

>Kick all users off both publisher and subscriber. Take backup. Ship
>backup. Restore backup. Start replication. Allow users back on both
>systems.

Let me try that again.

Kick all users off publisher.
Take backup.
Ship backup.
Restore backup on subscriber, locking users out.
Start replication.
Allow users on both publisher and subscriber.

Josh

[ChrisR]

See this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320499

"SQL_Newbie" <SQL_N...@discussions.microsoft.com> wrote in message
news:A86ED3F2-EEA1-4EFB...@microsoft.com...

[Paul Ibison]

Josh,
it all depends what you mean by 'Start replication'. After the restore of
the backup there will be no subscription info for that subscriber, so it
needs setting up. Doing a normal setup will simply overwrite all the
articles and you're in the same situation as having created an empty
database. Chris' link refers to nosync initializations, which is one
posibility, but this requires a fair amount of manual interaction in the
case of transactional replication, adding new articles automatically must be
done using scripts and special care must be taken with identities (see
http://www.replicationanswers.com/NoSyncInitializations.asp). I think
attachable subscription databases is by far the easiest solution :)

[Paul Ibison]

Chris, this article is good but also misses some other points -
(a) special care must be taken to remove subscriber identity columns in the
case of transactional replication.
(b) new tables can only be automatically replicated through csript,
otherwise you're restricted to nosync these also.
(see http://www.replicationanswers.com/NoSyncInitializations.asp)
Cheers,
Paul Ibison

[jxstern]

On Fri, 5 Aug 2005 09:13:01 +0100, "Paul Ibison"
<Paul....@pygmalion.com> wrote:

>Josh,
>it all depends what you mean by 'Start replication'. After the restore of
>the backup there will be no subscription info for that subscriber, so it
>needs setting up. Doing a normal setup will simply overwrite all the
>articles and you're in the same situation as having created an empty
>database. Chris' link refers to nosync initializations, which is one
>posibility, but this requires a fair amount of manual interaction in the
>case of transactional replication, adding new articles automatically must be
>done using scripts and special care must be taken with identities (see
>http://www.replicationanswers.com/NoSyncInitializations.asp). I think
>attachable subscription databases is by far the easiest solution :)

Paul,

Yes, I could be clearer.

For one thing, I do basically assume that one abandons all the
wizardly control of replication and does everything by manually tuned
scripts.

After I posted, it occurred to me that, with some care, the latency I
suggest which includes kicking people off both systems, can be
minimized, along the lines you mention. One could:

(a) Start with your operational database before it becomes a
publisher.
(b) By hook or crook, establish the suscriber and get replication
going, develop all the proper scripts.
(c) When it is time to deploy it for real:

* Replication is already running from publisher to subscriber, but it
is not fully in sync, may even be stopped, with updates queueing up
waiting to be resolved and delivered.
* Kick all users off publisher.
* Take backup.
* fail the distribution process so the updates queue up
**** now, let users back on publisher, and let the updates queue up,
shorter period of latency on the publisher!
* restore the backup on subscriber
* apply new scripts as necessary to publisher/subscriber (nosync),
turning on replication
* maybe give it a few minutes to catch up
* let users back on subscriber

Josh