Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts

Friday, March 30, 2012

Impact on replication when a server goes into standby mode

We have the following scenario:

Server A replicates Database A to Server B.

Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.

Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?

Thanls

In SQL 2000 unless you

1) have configured Server C to also ship the master, msdb, distirbution and publication databases
2) restore the last log with the keep_replication switch
3) take Server A completely offline
4) rename ServerC to ServerA and reboot ServerC(now ServerA)
5) run sp_replrestart
6) use the continue on data consistency error profile

it will not work.

If you also follow the sequence in a failback scenario it will also work.

In SQL 2005 you can configure Server A and C as log shipping failover partners (use a remote distributor and set the publisherfailoverpartner accordingly).

Note that it is possible to do the following in SQL 2000

1) ship logs to ServerC from Server A.
2) on failover restore the tail of A to C with the keep_replication switch
3) use the sp_replrestart command to pick up where you left off

There is more potential exposure to data loss with the above method.

Impact of Existing replication

Scenario.
A db published for transactional replication will be backed up and restored
on a server in a different facility. There, all new replication will be
defined (completely different articles, completely different targets)
Question.
Will pre-existing replication prevent correct functioning of the db at the
time of restore and new replication being defined? Dropping replication
prior to backing up the db is not an option.
Thank you for your help
Oscar
Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
reconfigure replication...
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Oscar" wrote:

> Scenario.
> A db published for transactional replication will be backed up and restored
> on a server in a different facility. There, all new replication will be
> defined (completely different articles, completely different targets)
> Question.
> Will pre-existing replication prevent correct functioning of the db at the
> time of restore and new replication being defined? Dropping replication
> prior to backing up the db is not an option.
> Thank you for your help
> Oscar
>
|||Actually you want to execute:
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"Chris" wrote:
[vbcol=seagreen]
> Once restored, execute sp_removedbreplication @.dbname = 'MyPubDB', then
> reconfigure replication...
> ChrisB
> MCDBA OCP
> www.MyDatabaseAdmin.com
> "Oscar" wrote:

Immediate Updating Subscriber not Queuing updates

Hey all,
I have a problem with replication that I was hoping someone might be able to
shed some light on. Heres the scenario.
Server A, setup as a publisher + distributor. A publication was created and
marked for transaction replication with immediate updating with queued as a
failover.
Server B is the only subscriber. I had to run sp_link_publication in order
to get it to work.
I can successfully replication changes from A - B, and B - A. If B goes
down, and comes back up, it will re-sync no problem. My problem is that when
A goes down, I am not getting anything saved into the queue. When I try to
do any updating, all I get is 'SQL Server does not exist, etc'. There are no
other triggers on the table.
Thoughts?
One more thing, this is SQL 2000 SP3 (.818)
"MJM" wrote:

> Hey all,
> I have a problem with replication that I was hoping someone might be able to
> shed some light on. Heres the scenario.
> Server A, setup as a publisher + distributor. A publication was created and
> marked for transaction replication with immediate updating with queued as a
> failover.
> Server B is the only subscriber. I had to run sp_link_publication in order
> to get it to work.
> I can successfully replication changes from A - B, and B - A. If B goes
> down, and comes back up, it will re-sync no problem. My problem is that when
> A goes down, I am not getting anything saved into the queue. When I try to
> do any updating, all I get is 'SQL Server does not exist, etc'. There are no
> other triggers on the table.
> Thoughts?
|||you have to manually failover your subscriber from using immediate updating
to using queued updating. There is no automatic failover.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MJM" <MJM@.discussions.microsoft.com> wrote in message
news:EDD81CC4-9696-4AAB-A468-B8734DC08473@.microsoft.com...[vbcol=seagreen]
> One more thing, this is SQL 2000 SP3 (.818)
> "MJM" wrote:
able to[vbcol=seagreen]
and[vbcol=seagreen]
as a[vbcol=seagreen]
order[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
are no[vbcol=seagreen]