Is there an easy way to change the server that is
monitoring log shipping, in an existing pair? I need to
remove the log shipping monitor server and put the
monitoring on another machine and would prefer not to have
to rebuild 20 maintenence plans!Hi Dave,
This is a manual process. You might want to test this before implementing
(and make backups of msdb)
1. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server
2. Run the following query on current monitor server and save the output -
select maintenance_plan_id from msdb.dbo.log_shipping_primaries
select secondary_plan_id from msdb.dbo.log_shipping_secondaries
3. Execute the scripts created in Step 1 on the new monitor server (in MSDB)
4. Execute the following stored proc on the new monitor server -
sp_add_log_shipping_primary
@.primary_server_name = '<primary server name>' ,
@.primary_database_name = '<primary database name>'
,@.maintenance_plan_id = '<maintenance Plan ID from first select command
in Step 2>'
,@.backup_threshold = <backup threshold - should be at least 3 times the
freq of backup job>
,@.threshold_alert = '14420'
,@.threshold_alert_enabled = 1
,@.planned_outage_start_time = 0
,@.planned_outage_end_time = 0
,@.planned_outage_weekday_mask = 0
5. Execute the following select statement to get the Primary_ID -
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name ='<primary server name>' and primary_database_name = '<primary database
name>'
6. Execute the following stored procedure on the new monitor server -
sp_add_log_shipping_secondary @.primary_id = <primary id retrieved in Step 5>
, @.secondary_server_name = '<Secondary Server name>'
, @.secondary_database_name = '<secondary database name>'
, @.secondary_plan_id = '<Comes from second select statement in Step 2>'
, @.copy_enabled = 1
, @.load_enabled = 1
, @.out_of_sync_threshold = <out-of-sync threshold should be at least 3
times the freq of copy/restore jobs>
, @.threshold_alert = '14421'
, @.threshold_alert_enabled = 1
, @.planned_outage_start_time = 0
, @.planned_outage_end_time = 0
, @.planned_outage_weekday_mask = 0
7. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Verify that you have 1 for logon
type before proceeding with step 8. If you do not have this field set to 1,
then for the time being we will change the authentication mode to NT and
then we can change this later on once the monitor is completely migrated -
select * from msdb.dbo.log_shipping_monitor
go
8. Execute the following command on both Primary and all secondary servers
to change the monitor information -
delete from msdb.dbo.log_shipping_monitor
go
sp_define_log_shipping_monitor '<New monitor server name>', 1, NULL, 1
go
9. This should complete the migration. Test that the monitor server icon
thing appears under Management tree (Enterprise Manager). When you click
this Log Shipping Monitor, there should be the new pair that you just
added. Once the jobs are working successfully the last copied/loaded file
field will be updated to reflect the changes.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Showing posts with label shipping. Show all posts
Showing posts with label shipping. Show all posts
Monday, March 26, 2012
Sunday, February 19, 2012
How to bring up warm stabdby server?
Hello, everyone:
I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?
Thanks
ZYTHello, everyone:
I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?
Thanks
ZYT
SQL 2000 or SQL 2005?
Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.
Regards,
Hugh|||Hi Hugh:
I am running SQL 2000.
Thanks for the advices. I read a ton of papers. Almost all papers are from BOL to recommend last restore transaction log backup from primary server with recovery. I am conderning if it is for sure the secondary server can resume general model from read-only after restoration. Does any one have real experience? If the secondary server runs online with Real-only, that is a big trouble.
ZYT
SQL 2000 or SQL 2005?
Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.
Regards,
Hugh|||Hello, everyone:
I would like share this point with you. My testing was executed by "How to: Set Up, Maintain, and Bring Online a Warm Standby Server (Transact-SQL)" in BOL step by step. After restore from last transaction log backup from primary server WITH RECOVERY. Like,
RESTORE LOG northwind1
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL$Subscription_srv\Log\Northwind_tlog_2 00407111530.TRN'
WITH RECOVERY
Refresh secondary database, it become general model from Read-Only.
Thanks
ZYT
I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?
Thanks
ZYTHello, everyone:
I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?
Thanks
ZYT
SQL 2000 or SQL 2005?
Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.
Regards,
Hugh|||Hi Hugh:
I am running SQL 2000.
Thanks for the advices. I read a ton of papers. Almost all papers are from BOL to recommend last restore transaction log backup from primary server with recovery. I am conderning if it is for sure the secondary server can resume general model from read-only after restoration. Does any one have real experience? If the secondary server runs online with Real-only, that is a big trouble.
ZYT
SQL 2000 or SQL 2005?
Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.
Regards,
Hugh|||Hello, everyone:
I would like share this point with you. My testing was executed by "How to: Set Up, Maintain, and Bring Online a Warm Standby Server (Transact-SQL)" in BOL step by step. After restore from last transaction log backup from primary server WITH RECOVERY. Like,
RESTORE LOG northwind1
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL$Subscription_srv\Log\Northwind_tlog_2 00407111530.TRN'
WITH RECOVERY
Refresh secondary database, it become general model from Read-Only.
Thanks
ZYT
How to bring Second SQL Log Ship Server Online
Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.net
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net
|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC
|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC
|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC
|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>
|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.net
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net
|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC
|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC
|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC
|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>
|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
How to bring Second SQL Log Ship Server Online
Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
How to bring Second SQL Log Ship Server Online
Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/conten...ogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/conten...ogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC
Subscribe to:
Posts (Atom)