Hello,
I want to know if the re is an easy way to modify the Primary Key on a
replicated table to be a non-clustered index instead. Looking through
queries hitting this table show a clustered index on a different field will
significantly increase performance.
Anyone run into this and find a solution?
Thanks.
~lbLonnye Blake Bower wrote:
> Hello,
> I want to know if the re is an easy way to modify the Primary Key on a
> replicated table to be a non-clustered index instead. Looking through
> queries hitting this table show a clustered index on a different
> field will significantly increase performance.
> Anyone run into this and find a solution?
> Thanks.
> ~lb
Unfortunately, I don't think there is an easy way to do this. If you
ever wonder how a tool , like SQL Enterprise Manager, does something
using its UI, you can run Profiler to watch the activity. When you
change a PK constraint from clustered to non-clustered it looks like SQL
EM is creating new tables with the desired changes (and this affects FK
tables as well).
What you can do is work from a test database until you get the script
working properly. The script will have to remove the FK constraints,
remove the PK constraint, change the PK constraint, and re-create the FK
constraints. Once you have the script working, you should start the
database is single-user mode and make your changes off-hours.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
How to calculate response time.
Hello.
I have a task to do which is as follows:
I have to do an "id query" on a primary key of my db and then calculate the response time of the query.
Task says that i'm able to take screenshots of a graphical view of response time.And as a hint says that there are sql commands that can do such thing.Allthough i've searched the books i have access to and the internet i can't find such thing.I dunno maybe someone more familiar with this stuff can understand what i'm supposed to do.I don't want to give me the whole solution but just if u can tell me where to look.
I don't understand what means "do an id query on primary key 'SMTH' " and i can't find anythin which is related on calculating system response time to execute a query.
Any help is much appreciated.
Thx a lot :)See set statistics statement, set statistics time in particular
I have a task to do which is as follows:
I have to do an "id query" on a primary key of my db and then calculate the response time of the query.
Task says that i'm able to take screenshots of a graphical view of response time.And as a hint says that there are sql commands that can do such thing.Allthough i've searched the books i have access to and the internet i can't find such thing.I dunno maybe someone more familiar with this stuff can understand what i'm supposed to do.I don't want to give me the whole solution but just if u can tell me where to look.
I don't understand what means "do an id query on primary key 'SMTH' " and i can't find anythin which is related on calculating system response time to execute a query.
Any help is much appreciated.
Thx a lot :)See set statistics statement, set statistics time in particular
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)