Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:
> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison
Showing posts with label replicated. Show all posts
Showing posts with label replicated. Show all posts
Friday, March 23, 2012
restore strategies...
We have some fairly large sized databases (approx. 200
gigs), and one of the business requirements is to have the
data replicated over to the DRP server at least once a day.
Transactional Replication is not a favourable options as
many tables do not have primary keys and many others have
identity keys.
In order to minimize data and network throughput, I was
thinking of doing a full backup of these databases once
weekly, and differential backups everyday. Further, I
would restore these differential backups everyday to the
DRP server. However, I would still need to restore the
full database once weekly before I can continue to restore
any additional differential backups. Doing a full restore
would tax my throughput to a large extent... meaning, more
network activity, which would slow down the application.
Is there a way I can spruce up this process? Thanks for
all your suggestions in advance.> We have some fairly large sized databases (approx. 200
> gigs), and one of the business requirements is to have the
> data replicated over to the DRP server at least once a day.
Have you thought about Log Shipping for Disaster Recovery Plan?
You only need to restore transaction logs to the secondary database. the
network traffic and time could be minimized.
> Transactional Replication is not a favourable options as
> many tables do not have primary keys and many others have
> identity keys.
Is it possible to force PKs on these tables( eg. replacing unique clustered
indexes with clustered primary keys)?
You can also replicate tables with identity columns although it takes more
effort in tweaking this.
> In order to minimize data and network throughput, I was
> thinking of doing a full backup of these databases once
> weekly, and differential backups everyday. Further, I
> would restore these differential backups everyday to the
> DRP server. However, I would still need to restore the
> full database once weekly before I can continue to restore
> any additional differential backups. Doing a full restore
> would tax my throughput to a large extent... meaning, more
> network activity, which would slow down the application.
> Is there a way I can spruce up this process? Thanks for
> all your suggestions in advance.
There is a third-party backup tool from DBAssociates called "SQLLiteSpeed"
that can shrinks your 200GB db down to 20 GB or less. It has saved me much
time to backup and copy files.
Richard|||I have resently started using log shipping and database's
range from a few gigs to hundreds of gigs. My database is
not setup with any primary keys. Is there an issue with
log shipping and not having primary keys?
>--Original Message--
>We have some fairly large sized databases (approx. 200
>gigs), and one of the business requirements is to have
the
>data replicated over to the DRP server at least once a
day.
>Transactional Replication is not a favourable options as
>many tables do not have primary keys and many others have
>identity keys.
>In order to minimize data and network throughput, I was
>thinking of doing a full backup of these databases once
>weekly, and differential backups everyday. Further, I
>would restore these differential backups everyday to the
>DRP server. However, I would still need to restore the
>full database once weekly before I can continue to
restore
>any additional differential backups. Doing a full restore
>would tax my throughput to a large extent... meaning,
more
>network activity, which would slow down the application.
>Is there a way I can spruce up this process? Thanks for
>all your suggestions in advance.
>.
>|||Log Shipping is basically an automatic backup and restore process. So it has
nothing to do with whether you have PKs or not. As a general rule
a table should have a primary key.
"jnsland" <jlandstrom@.wausaufs.com> wrote in message
news:021801c3507e$7006a190$a401280a@.phx.gbl...
> I have resently started using log shipping and database's
> range from a few gigs to hundreds of gigs. My database is
> not setup with any primary keys. Is there an issue with
> log shipping and not having primary keys?
>
> >--Original Message--
> >We have some fairly large sized databases (approx. 200
> >gigs), and one of the business requirements is to have
> the
> >data replicated over to the DRP server at least once a
> day.
> >
> >Transactional Replication is not a favourable options as
> >many tables do not have primary keys and many others have
> >identity keys.
> >
> >In order to minimize data and network throughput, I was
> >thinking of doing a full backup of these databases once
> >weekly, and differential backups everyday. Further, I
> >would restore these differential backups everyday to the
> >DRP server. However, I would still need to restore the
> >full database once weekly before I can continue to
> restore
> >any additional differential backups. Doing a full restore
> >would tax my throughput to a large extent... meaning,
> more
> >network activity, which would slow down the application.
> >
> >Is there a way I can spruce up this process? Thanks for
> >all your suggestions in advance.
> >.
> >|||::::::::::::::::
::::::::::::::::
>There is a third-party backup tool from DBAssociates
called "SQLLiteSpeed"
>that can shrinks your 200GB db down to 20 GB or less. It
has saved me much
>time to backup and copy files.
I've used and like this product, however, our business
unit thinks that this is an expensive tool though I did
try to explain to them that it will be cost beneficial
over the long run. Well... guess we'll have to live with
this bottleneck. Thanks for your suggestions though.
gigs), and one of the business requirements is to have the
data replicated over to the DRP server at least once a day.
Transactional Replication is not a favourable options as
many tables do not have primary keys and many others have
identity keys.
In order to minimize data and network throughput, I was
thinking of doing a full backup of these databases once
weekly, and differential backups everyday. Further, I
would restore these differential backups everyday to the
DRP server. However, I would still need to restore the
full database once weekly before I can continue to restore
any additional differential backups. Doing a full restore
would tax my throughput to a large extent... meaning, more
network activity, which would slow down the application.
Is there a way I can spruce up this process? Thanks for
all your suggestions in advance.> We have some fairly large sized databases (approx. 200
> gigs), and one of the business requirements is to have the
> data replicated over to the DRP server at least once a day.
Have you thought about Log Shipping for Disaster Recovery Plan?
You only need to restore transaction logs to the secondary database. the
network traffic and time could be minimized.
> Transactional Replication is not a favourable options as
> many tables do not have primary keys and many others have
> identity keys.
Is it possible to force PKs on these tables( eg. replacing unique clustered
indexes with clustered primary keys)?
You can also replicate tables with identity columns although it takes more
effort in tweaking this.
> In order to minimize data and network throughput, I was
> thinking of doing a full backup of these databases once
> weekly, and differential backups everyday. Further, I
> would restore these differential backups everyday to the
> DRP server. However, I would still need to restore the
> full database once weekly before I can continue to restore
> any additional differential backups. Doing a full restore
> would tax my throughput to a large extent... meaning, more
> network activity, which would slow down the application.
> Is there a way I can spruce up this process? Thanks for
> all your suggestions in advance.
There is a third-party backup tool from DBAssociates called "SQLLiteSpeed"
that can shrinks your 200GB db down to 20 GB or less. It has saved me much
time to backup and copy files.
Richard|||I have resently started using log shipping and database's
range from a few gigs to hundreds of gigs. My database is
not setup with any primary keys. Is there an issue with
log shipping and not having primary keys?
>--Original Message--
>We have some fairly large sized databases (approx. 200
>gigs), and one of the business requirements is to have
the
>data replicated over to the DRP server at least once a
day.
>Transactional Replication is not a favourable options as
>many tables do not have primary keys and many others have
>identity keys.
>In order to minimize data and network throughput, I was
>thinking of doing a full backup of these databases once
>weekly, and differential backups everyday. Further, I
>would restore these differential backups everyday to the
>DRP server. However, I would still need to restore the
>full database once weekly before I can continue to
restore
>any additional differential backups. Doing a full restore
>would tax my throughput to a large extent... meaning,
more
>network activity, which would slow down the application.
>Is there a way I can spruce up this process? Thanks for
>all your suggestions in advance.
>.
>|||Log Shipping is basically an automatic backup and restore process. So it has
nothing to do with whether you have PKs or not. As a general rule
a table should have a primary key.
"jnsland" <jlandstrom@.wausaufs.com> wrote in message
news:021801c3507e$7006a190$a401280a@.phx.gbl...
> I have resently started using log shipping and database's
> range from a few gigs to hundreds of gigs. My database is
> not setup with any primary keys. Is there an issue with
> log shipping and not having primary keys?
>
> >--Original Message--
> >We have some fairly large sized databases (approx. 200
> >gigs), and one of the business requirements is to have
> the
> >data replicated over to the DRP server at least once a
> day.
> >
> >Transactional Replication is not a favourable options as
> >many tables do not have primary keys and many others have
> >identity keys.
> >
> >In order to minimize data and network throughput, I was
> >thinking of doing a full backup of these databases once
> >weekly, and differential backups everyday. Further, I
> >would restore these differential backups everyday to the
> >DRP server. However, I would still need to restore the
> >full database once weekly before I can continue to
> restore
> >any additional differential backups. Doing a full restore
> >would tax my throughput to a large extent... meaning,
> more
> >network activity, which would slow down the application.
> >
> >Is there a way I can spruce up this process? Thanks for
> >all your suggestions in advance.
> >.
> >|||::::::::::::::::
::::::::::::::::
>There is a third-party backup tool from DBAssociates
called "SQLLiteSpeed"
>that can shrinks your 200GB db down to 20 GB or less. It
has saved me much
>time to backup and copy files.
I've used and like this product, however, our business
unit thinks that this is an expensive tool though I did
try to explain to them that it will be cost beneficial
over the long run. Well... guess we'll have to live with
this bottleneck. Thanks for your suggestions though.
Tuesday, March 20, 2012
restore replicated databases
I can't restore trans replicated databases because I get message that they
are in use - do I need to stop agents to get exclusive access - then restore
- then restart agents?
Thanks
yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
continuing to use the db and then retry your restore.
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
"DrBob" <DrBob@.discussions.microsoft.com> wrote in message
news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> I can't restore trans replicated databases because I get message that they
> are in use - do I need to stop agents to get exclusive access - then
restore
> - then restart agents?
> Thanks
|||Thanks
"Hilary Cotter" wrote:
> yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
> continuing to use the db and then retry your restore.
> --
> 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
> "DrBob" <DrBob@.discussions.microsoft.com> wrote in message
> news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> restore
>
>
are in use - do I need to stop agents to get exclusive access - then restore
- then restart agents?
Thanks
yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
continuing to use the db and then retry your restore.
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
"DrBob" <DrBob@.discussions.microsoft.com> wrote in message
news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> I can't restore trans replicated databases because I get message that they
> are in use - do I need to stop agents to get exclusive access - then
restore
> - then restart agents?
> Thanks
|||Thanks
"Hilary Cotter" wrote:
> yes, stop Sql Server agent, run sp_who2 to identify and kill spids that are
> continuing to use the db and then retry your restore.
> --
> 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
> "DrBob" <DrBob@.discussions.microsoft.com> wrote in message
> news:F2DC7A4C-D2DB-493B-BBDB-6BBBF95306CE@.microsoft.com...
> restore
>
>
Restore replicated database in a different server?
Hi all,
I have a backup of a repliacted (Merge) database (SQL Server 2000).
What are the setups of restoring this merge database to a new server.
Please provide me with detailed steps on how to do this?
(I have restored this database the MSDB and Distribution database but it
did not work I cannot see the merge agent , also in the Configure Pulisher,
Distrubotor.., I still see the old Server Name)
Thanks for any help.
Abdu.
The servername exists in too much replication metadata for this to succeed.
The only way to have this work is to name the new server identically to the
old server and then restore. There are details in BOL for replication backup
and restore which outline the process.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a backup of a repliacted (Merge) database (SQL Server 2000).
What are the setups of restoring this merge database to a new server.
Please provide me with detailed steps on how to do this?
(I have restored this database the MSDB and Distribution database but it
did not work I cannot see the merge agent , also in the Configure Pulisher,
Distrubotor.., I still see the old Server Name)
Thanks for any help.
Abdu.
The servername exists in too much replication metadata for this to succeed.
The only way to have this work is to name the new server identically to the
old server and then restore. There are details in BOL for replication backup
and restore which outline the process.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Restore Replicated Database
I seem to have run into a problem within an environment I have set up with
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
This is correct - the different server names preclude a successful restore
of the replication settings. The easiest solution is to use the replication
script generated on the origional server and search and replace on the
servername. You might also have to change the job owners if they have not
been set to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm going to create a job that will run after the restore has completed to
recreate the publicaion and subscriptions.
Will the fact that the distribution database is "different" than the
database in production, will recreating the publication on the other server
still work? The articles and everything will still be thae same.
"Paul Ibison" wrote:
> This is correct - the different server names preclude a successful restore
> of the replication settings. The easiest solution is to use the replication
> script generated on the origional server and search and replace on the
> servername. You might also have to change the job owners if they have not
> been set to sa.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||As long as you change references to the server (eg @.publisher, @.subscriber),
job owners if necessary, the repldata path if you are setting up the
distributor, the distribution database path also if it is to be on a
different disk etc then the rest of the script is constant. This sounds a
lot but it is pretty straightforward really. In some circumstances you might
want different agent profiles on each server and this is also something to
be aware of.
HTH,
Paul Ibison, SQL MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> I'm going to create a job that will run after the restore has completed to
> recreate the publicaion and subscriptions.
> Will the fact that the distribution database is "different" than the
> database in production, will recreating the publication on the other
> server
> still work? The articles and everything will still be thae same.
> "Paul Ibison" wrote:
|||Paul:
Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.
I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?
Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.
Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.
Have I missed anything?
Thanks!
"Paul Ibison" wrote:
> As long as you change references to the server (eg @.publisher, @.subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:
> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
This is correct - the different server names preclude a successful restore
of the replication settings. The easiest solution is to use the replication
script generated on the origional server and search and replace on the
servername. You might also have to change the job owners if they have not
been set to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm going to create a job that will run after the restore has completed to
recreate the publicaion and subscriptions.
Will the fact that the distribution database is "different" than the
database in production, will recreating the publication on the other server
still work? The articles and everything will still be thae same.
"Paul Ibison" wrote:
> This is correct - the different server names preclude a successful restore
> of the replication settings. The easiest solution is to use the replication
> script generated on the origional server and search and replace on the
> servername. You might also have to change the job owners if they have not
> been set to sa.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||As long as you change references to the server (eg @.publisher, @.subscriber),
job owners if necessary, the repldata path if you are setting up the
distributor, the distribution database path also if it is to be on a
different disk etc then the rest of the script is constant. This sounds a
lot but it is pretty straightforward really. In some circumstances you might
want different agent profiles on each server and this is also something to
be aware of.
HTH,
Paul Ibison, SQL MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> I'm going to create a job that will run after the restore has completed to
> recreate the publicaion and subscriptions.
> Will the fact that the distribution database is "different" than the
> database in production, will recreating the publication on the other
> server
> still work? The articles and everything will still be thae same.
> "Paul Ibison" wrote:
|||Paul:
Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.
I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?
Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.
Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.
Have I missed anything?
Thanks!
"Paul Ibison" wrote:
> As long as you change references to the server (eg @.publisher, @.subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:
> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
Subscribe to:
Posts (Atom)