Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping v Database Mirroring (again!)

Hi

I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?

On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?

Does anyone have any experience of mirroring and is using it in prefence to log shipping?

Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring

For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?

Thanks in advance for any feedback

Robert

I also was greatly disappointed to learn that MS recommends only 10 mirrors per server instance. Considering that I have around 1300 databases in a federated model spread out over 12 servers, it threw a major monkey-wrench into our plans. So, now we are considering ranking clients on a scale and putting top level clients on DB Mirrors and 2nd level clients on log shipping. Demo db's for the various inernal departments get nothing (no soup for you!!).

Currently, I am still trying to figure out how many mirrors I can actually do on a server. I have 19 mirrors running on a 64 bit server with a 64 bit mirror with no noted performance degradation. Thread consumption did double (approximately) when I added the mirrors, but I'm still way below what's available (average of 100 threads consumed out of an available 512). However, I have to keep in mind that this is the slow part of the year for our clients and we have to leave enough threads avaialble to handle the increased load that will come at the end of the year.

I've suggested offering DB miroring as an upgrade that clients can purchase due to the increased hardware requirements. Log shipping = free, mirroring = $$$.

Yes, handling of the automatic failover does require some specific coding. The SQL Native Client and ADO.Net 2.0 have the ability to specify a failover partner in the connection string, and it automatically tries the failover partner if the principal server is not available. Earlier versions of ADO or other db access interfaces that do not support failover partners must be coded to manually try a second connection string if the first fails (which generally invovles a long timeout period). Another option is to build a middle tier that handles the transport of client db access so that the client isn't even aware of which server it is using.

If indeed you do not need the data to be up to the minute, then perhaps log shipping is the answer for you.

|||

Yes, I'm rapidly coming to the conclusion that 15 minute log shipping will be sufficient for our needs.

The majority of our database requirements are from 3rd party software. There are often very slow to update - many don't support SQL 2005 yet anyway!

|||

Robert,

I would be interested in any real world information you would like to share about mirroring. It would be good fodder for future articles at SSWUG.ORG as well as support to my company for implementing mirroring. They are concerned about the newness of this feature.

Cheers,

Ben

btaylor@.sswug.org

|||

As was stated above, we currently recommend no more than ten databases be configured for mirroring on a single instance. This is not a fixed limit, but due to resource consumption is a good general guideline. Some customers are perfectly happy with larger number of mirrored databases, however your mileage may vary depending on hardware, application, etc. Mirroring was not designed for large numbers of database on the same instance. If you are facing this scenario, MSCS is a more natural choice (with optional addition of storage level replication for disaster recovery).

Regards,

Matt Hollingsworth

Microsoft SQL Server

Log Shipping v Database Mirroring (again!)

Hi

I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?

On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?

Does anyone have any experience of mirroring and is using it in prefence to log shipping?

Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring

For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?

Thanks in advance for any feedback

Robert

I also was greatly disappointed to learn that MS recommends only 10 mirrors per server instance. Considering that I have around 1300 databases in a federated model spread out over 12 servers, it threw a major monkey-wrench into our plans. So, now we are considering ranking clients on a scale and putting top level clients on DB Mirrors and 2nd level clients on log shipping. Demo db's for the various inernal departments get nothing (no soup for you!!).

Currently, I am still trying to figure out how many mirrors I can actually do on a server. I have 19 mirrors running on a 64 bit server with a 64 bit mirror with no noted performance degradation. Thread consumption did double (approximately) when I added the mirrors, but I'm still way below what's available (average of 100 threads consumed out of an available 512). However, I have to keep in mind that this is the slow part of the year for our clients and we have to leave enough threads avaialble to handle the increased load that will come at the end of the year.

I've suggested offering DB miroring as an upgrade that clients can purchase due to the increased hardware requirements. Log shipping = free, mirroring = $$$.

Yes, handling of the automatic failover does require some specific coding. The SQL Native Client and ADO.Net 2.0 have the ability to specify a failover partner in the connection string, and it automatically tries the failover partner if the principal server is not available. Earlier versions of ADO or other db access interfaces that do not support failover partners must be coded to manually try a second connection string if the first fails (which generally invovles a long timeout period). Another option is to build a middle tier that handles the transport of client db access so that the client isn't even aware of which server it is using.

If indeed you do not need the data to be up to the minute, then perhaps log shipping is the answer for you.

|||

Yes, I'm rapidly coming to the conclusion that 15 minute log shipping will be sufficient for our needs.

The majority of our database requirements are from 3rd party software. There are often very slow to update - many don't support SQL 2005 yet anyway!

|||

Robert,

I would be interested in any real world information you would like to share about mirroring. It would be good fodder for future articles at SSWUG.ORG as well as support to my company for implementing mirroring. They are concerned about the newness of this feature.

Cheers,

Ben

btaylor@.sswug.org

|||

As was stated above, we currently recommend no more than ten databases be configured for mirroring on a single instance. This is not a fixed limit, but due to resource consumption is a good general guideline. Some customers are perfectly happy with larger number of mirrored databases, however your mileage may vary depending on hardware, application, etc. Mirroring was not designed for large numbers of database on the same instance. If you are facing this scenario, MSCS is a more natural choice (with optional addition of storage level replication for disaster recovery).

Regards,

Matt Hollingsworth

Microsoft SQL Server

Log Shipping to old box?

My primary SQL Server box is 2005 Standard x64 running on 2 quad-core processors. I'm licensed for 2 processors.

I'd like to set up some kind of redundancy. The hardware I have available is about 4 years old and is currently running SQL Server 2000. Let's assume I upgrade the old box to SQL Server 2005 Standard. A couple of questions:

1) It seems the hardware gap between the 2 will make little difference in a log shipping. Please confirm this. Any other things that might be an issue?

2) Do I need lots of CALs on the backup box for Log Shipping? Or can I get by with 5?

3) I'd like to schedule log shipping pretty often--maybe every 5 or 10 minutes. Will this be an issue?

Thanks!

1. No

2. No, Yes, as long as it is not accessed by users that are not covered with CALs. A SQL Server CAL is good for all SQL Servers in the domain.

3. 5-10 minutes 'should' not be an issue. As long as the performance/load differential is not so great that the log cannot be applied in the time alloted.

Another option with SQL 2005 is Database Mirroring. Have you explored the benefits of Database Mirroring?

|||>Another option with SQL 2005 is Database Mirroring. Have you explored the benefits of Database Mirroring?

I've read a little about Database Mirroring. How will my old hardware affect the mirroring process? Will I have trouble with the slower server keeping up with the transactions?

Brian

|||

The same issue occurs with Log Shipping.

Will, in fact, the old box have enough 'power' to keep up with the data flow. That is something you will have to test.

|||

I guess I made this assumption (that could be very wrong):

Log Shipping moves data in batches. Database mirroring moves data by record or transaction. (Not sure what method is used). A batch append should be less stressful on a backup server than a bunch of individual appends.

Even if these are true, maybe the difference in stress is much less than I think.

Brian

|||

It could go either way.

I would set up a test environment and test both if they seemed equally valid solution candidates. You're trying to balance the trade-offs of cost of solution vs. speed of recovery.

sql

log shipping restore & recovery test

I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following

RESTORE DATABASE database_name WITH RECOVERY

but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?

Try as below,

Code Snippet

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then restore the db as,

Restore database dbname with recovery

just disable the jobs in primary server no need to delete the maintenance plan it should work fine !

you can refer the articles related to log shipping in,

http://sql-articles.com/articles/lship/lship.htm

http://sql-articles.com/articles.php

For failover in SQL 2005, refer

http://sql-articles.com/articles/lship/fail-lship.htm

refer the link for

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

Thanxx

Deepak

|||

the alter database command was successful HOWEVER, the restore still did not work because of the same reason...

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

|||

you need to ensure that you dont have any connections to the db accidentally ! !

just go to SSMS or enterprise manager and type sp_who2 and see the dbname column and see the corresponding spid column if the value is less than greater than 50 type as follows,

kill spid value and then perform the restore it will work ! the problem is someone is accessing the db

Thanxx

Deepak

|||

I did the sp_who2 and saw two connections, one was the EM and one was the query analyzer... I killed the EM but when I tried the restore on query analyzer, it still gave me the same error about not being able to get exclusive use...

|||

ah... I changed the db in the drop down to point to the master and then did the recovery... it worked! thanks....

log shipping restore & recovery test

I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following

RESTORE DATABASE database_name WITH RECOVERY

but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?

Try as below,

Code Snippet

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then restore the db as,

Restore database dbname with recovery

just disable the jobs in primary server no need to delete the maintenance plan it should work fine !

you can refer the articles related to log shipping in,

http://sql-articles.com/articles/lship/lship.htm

http://sql-articles.com/articles.php

For failover in SQL 2005, refer

http://sql-articles.com/articles/lship/fail-lship.htm

refer the link for

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

Thanxx

Deepak

|||

the alter database command was successful HOWEVER, the restore still did not work because of the same reason...

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

|||

you need to ensure that you dont have any connections to the db accidentally ! !

just go to SSMS or enterprise manager and type sp_who2 and see the dbname column and see the corresponding spid column if the value is less than greater than 50 type as follows,

kill spid value and then perform the restore it will work ! the problem is someone is accessing the db

Thanxx

Deepak

|||

I did the sp_who2 and saw two connections, one was the EM and one was the query analyzer... I killed the EM but when I tried the restore on query analyzer, it still gave me the same error about not being able to get exclusive use...

|||

ah... I changed the db in the drop down to point to the master and then did the recovery... it worked! thanks....

log shipping restore & recovery test

I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following

RESTORE DATABASE database_name WITH RECOVERY

but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?

Try as below,

Code Snippet

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then restore the db as,

Restore database dbname with recovery

just disable the jobs in primary server no need to delete the maintenance plan it should work fine !

you can refer the articles related to log shipping in,

http://sql-articles.com/articles/lship/lship.htm

http://sql-articles.com/articles.php

For failover in SQL 2005, refer

http://sql-articles.com/articles/lship/fail-lship.htm

refer the link for

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

Thanxx

Deepak

|||

the alter database command was successful HOWEVER, the restore still did not work because of the same reason...

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

|||

you need to ensure that you dont have any connections to the db accidentally ! !

just go to SSMS or enterprise manager and type sp_who2 and see the dbname column and see the corresponding spid column if the value is less than greater than 50 type as follows,

kill spid value and then perform the restore it will work ! the problem is someone is accessing the db

Thanxx

Deepak

|||

I did the sp_who2 and saw two connections, one was the EM and one was the query analyzer... I killed the EM but when I tried the restore on query analyzer, it still gave me the same error about not being able to get exclusive use...

|||

ah... I changed the db in the drop down to point to the master and then did the recovery... it worked! thanks....

sql

Log shipping recover

I have changed the roles of primary and secondary servers due to server failure.
Can anyone advise how to revert back to previous main server? Are these any links available?See BOL topic:
How to set up and perform a log shipping role change (Transact-SQL)

In couple words - you have to set up log shipping again (move last backup from secondary to primary with stopping any activity of course on first one).|||If I set up log shipping with the old server, it becomes secondary. But I want to make my old server primary again.

Would I need to copy the db from current primary to old primary server, switch off the current server and switch on the old one?

Log Shipping Question: reset copy delta

SQL Server 2000 on both servers. Log shipping between them.
We had to reboot the primary server and now the trn files are not
being copied to the secondary server.
In the past when this happened, we manually copied the files from
primary to secondary and then ran the upload job on the secondary
server. The copy delta would then reset at some point (usually
overnight).
This time after I have copied the files and uploaded them, the copy
delta has not reset and now stands at 5415 minutes. The Load delta is
at 0 minutes currently.
Is there a way to force the copy delta to update itself because all
the trn files now have been loaded?
If there isn't I will have to run the wizard to create the log
shipping plans again for all four of our databases.
Regards
Nicko
Hi
What does mean 'copy delta'?
http://www.sql-server-performance.com/sql_server_log_shipping.asp
<nickostle@.hotmail.com> wrote in message
news:1179713332.252623.51410@.z28g2000prd.googlegro ups.com...
> SQL Server 2000 on both servers. Log shipping between them.
> We had to reboot the primary server and now the trn files are not
> being copied to the secondary server.
> In the past when this happened, we manually copied the files from
> primary to secondary and then ran the upload job on the secondary
> server. The copy delta would then reset at some point (usually
> overnight).
> This time after I have copied the files and uploaded them, the copy
> delta has not reset and now stands at 5415 minutes. The Load delta is
> at 0 minutes currently.
> Is there a way to force the copy delta to update itself because all
> the trn files now have been loaded?
> If there isn't I will have to run the wizard to create the log
> shipping plans again for all four of our databases.
> Regards
> Nicko
>
|||Copy Delta is the time dfference between when it says the last file
was copied to the present time on the server.
sql

Log Shipping Question: reset copy delta

SQL Server 2000 on both servers. Log shipping between them.
We had to reboot the primary server and now the trn files are not
being copied to the secondary server.
In the past when this happened, we manually copied the files from
primary to secondary and then ran the upload job on the secondary
server. The copy delta would then reset at some point (usually
overnight).
This time after I have copied the files and uploaded them, the copy
delta has not reset and now stands at 5415 minutes. The Load delta is
at 0 minutes currently.
Is there a way to force the copy delta to update itself because all
the trn files now have been loaded?
If there isn't I will have to run the wizard to create the log
shipping plans again for all four of our databases.
Regards
NickoHi
What does mean 'copy delta'?
http://www.sql-server-performance.c...og_shipping.asp
<nickostle@.hotmail.com> wrote in message
news:1179713332.252623.51410@.z28g2000prd.googlegroups.com...
> SQL Server 2000 on both servers. Log shipping between them.
> We had to reboot the primary server and now the trn files are not
> being copied to the secondary server.
> In the past when this happened, we manually copied the files from
> primary to secondary and then ran the upload job on the secondary
> server. The copy delta would then reset at some point (usually
> overnight).
> This time after I have copied the files and uploaded them, the copy
> delta has not reset and now stands at 5415 minutes. The Load delta is
> at 0 minutes currently.
> Is there a way to force the copy delta to update itself because all
> the trn files now have been loaded?
> If there isn't I will have to run the wizard to create the log
> shipping plans again for all four of our databases.
> Regards
> Nicko
>|||Copy Delta is the time dfference between when it says the last file
was copied to the present time on the server.

Log Shipping Question: reset copy delta

SQL Server 2000 on both servers. Log shipping between them.
We had to reboot the primary server and now the trn files are not
being copied to the secondary server.
In the past when this happened, we manually copied the files from
primary to secondary and then ran the upload job on the secondary
server. The copy delta would then reset at some point (usually
overnight).
This time after I have copied the files and uploaded them, the copy
delta has not reset and now stands at 5415 minutes. The Load delta is
at 0 minutes currently.
Is there a way to force the copy delta to update itself because all
the trn files now have been loaded?
If there isn't I will have to run the wizard to create the log
shipping plans again for all four of our databases.
Regards
NickoHi
What does mean 'copy delta'?
http://www.sql-server-performance.com/sql_server_log_shipping.asp
<nickostle@.hotmail.com> wrote in message
news:1179713332.252623.51410@.z28g2000prd.googlegroups.com...
> SQL Server 2000 on both servers. Log shipping between them.
> We had to reboot the primary server and now the trn files are not
> being copied to the secondary server.
> In the past when this happened, we manually copied the files from
> primary to secondary and then ran the upload job on the secondary
> server. The copy delta would then reset at some point (usually
> overnight).
> This time after I have copied the files and uploaded them, the copy
> delta has not reset and now stands at 5415 minutes. The Load delta is
> at 0 minutes currently.
> Is there a way to force the copy delta to update itself because all
> the trn files now have been loaded?
> If there isn't I will have to run the wizard to create the log
> shipping plans again for all four of our databases.
> Regards
> Nicko
>|||Copy Delta is the time dfference between when it says the last file
was copied to the present time on the server.

Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only '
(Is that what the "No Recovery" mode is for?)
Thanks
JonoHi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only '
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono

Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only ?
(Is that what the "No Recovery" mode is for?)
Thanks
Jono
Hi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only ?
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono
sql

Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only '
(Is that what the "No Recovery" mode is for?)
Thanks
JonoHi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only '
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono

log shipping question

Hi,
I've established the log shipping between my Primary database server and the
Stand by database server.
In the Stand by server, the database is gray, and in the BACKUP folder,
there're MyDB_init.bak and many .TRN files.
1.How do I make the MyDB's color not gray in that Stand by server? I wanna
test the SELECT and see if the record between the Primary and Stand by
server are consistant.
2. What will happen if I delete some .TRN files?
Thanks for help.
Jason
Jason
A standby database must be read-only. You can run SELECT statements againts
it. If you want the db READ-WRITE apply last log file and run
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:OGKoEINJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I've established the log shipping between my Primary database server and
> the
> Stand by database server.
> In the Stand by server, the database is gray, and in the BACKUP folder,
> there're MyDB_init.bak and many .TRN files.
> 1.How do I make the MyDB's color not gray in that Stand by server? I
> wanna
> test the SELECT and see if the record between the Primary and Stand by
> server are consistant.
> 2. What will happen if I delete some .TRN files?
> Thanks for help.
>
> Jason
>

log shipping question

What happens when you check the " Allow database to assume primary role"
option during log shipping setup. What happens if you dont check it ?
Also, does anyone have any links as far as recovery procedures for a log
shipping implementation used for disaster recovery..i.e. if Server A fails,
what are the exact steps to follow to recover the application onto Server B
which would also include changing the servername from Server B to Server A
...
Allow database to assume primary role - When this option is enabled, this secondary server becomes the primary server if there are any problems on the original primary server. If you select this option, you must provide a share where the transaction logs
are stored when the server assumes the role of a primary server (for example, (\\secondary_computername\sharename).
KBA http://support.microsoft.com/default...314515&sd=tech about complete FAQ sheet on Log shipping.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Performance topic.
"Hassan" wrote:

> What happens when you check the " Allow database to assume primary role"
> option during log shipping setup. What happens if you dont check it ?
> Also, does anyone have any links as far as recovery procedures for a log
> shipping implementation used for disaster recovery..i.e. if Server A fails,
> what are the exact steps to follow to recover the application onto Server B
> which would also include changing the servername from Server B to Server A
> ...
>
>

log shipping question

Hi,
I've established the log shipping between my Primary database server and the
Stand by database server.
In the Stand by server, the database is gray, and in the BACKUP folder,
there're MyDB_init.bak and many .TRN files.
1.How do I make the MyDB's color not gray in that Stand by server? I wanna
test the SELECT and see if the record between the Primary and Stand by
server are consistant.
2. What will happen if I delete some .TRN files?
Thanks for help.
JasonJason
A standby database must be read-only. You can run SELECT statements againts
it. If you want the db READ-WRITE apply last log file and run
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:OGKoEINJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I've established the log shipping between my Primary database server and
> the
> Stand by database server.
> In the Stand by server, the database is gray, and in the BACKUP folder,
> there're MyDB_init.bak and many .TRN files.
> 1.How do I make the MyDB's color not gray in that Stand by server? I
> wanna
> test the SELECT and see if the record between the Primary and Stand by
> server are consistant.
> 2. What will happen if I delete some .TRN files?
> Thanks for help.
>
> Jason
>

log shipping question

What happens when you check the " Allow database to assume primary role"
option during log shipping setup. What happens if you dont check it ?
Also, does anyone have any links as far as recovery procedures for a log
shipping implementation used for disaster recovery..i.e. if Server A fails,
what are the exact steps to follow to recover the application onto Server B
which would also include changing the servername from Server B to Server A
...Allow database to assume primary role - When this option is enabled, this se
condary server becomes the primary server if there are any problems on the o
riginal primary server. If you select this option, you must provide a share
where the transaction logs
are stored when the server assumes the role of a primary server (for example
, (\\secondary_computername\sharename).
KBA http://support.microsoft.com/defaul...;314515&sd=tech a
bout complete FAQ sheet on Log shipping.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on Perform
ance topic.
"Hassan" wrote:

> What happens when you check the " Allow database to assume primary role"
> option during log shipping setup. What happens if you dont check it ?
> Also, does anyone have any links as far as recovery procedures for a log
> shipping implementation used for disaster recovery..i.e. if Server A fails
,
> what are the exact steps to follow to recover the application onto Server
B
> which would also include changing the servername from Server B to Server A
> ...
>
>

Log Shipping Question

Would it an issue in setting up Log Shipping if the database files and the
Log files of the primary SQL Server 2000 reside on a RAID5 and Raid0+1 arrays?No. SQL Server doesn't care about the underlying disk subsystem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:A0521D7C-8E7F-4780-885B-B6D3BBE536F0@.microsoft.com...
Would it an issue in setting up Log Shipping if the database files and the
Log files of the primary SQL Server 2000 reside on a RAID5 and Raid0+1
arrays?|||Not at all
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:A0521D7C-8E7F-4780-885B-B6D3BBE536F0@.microsoft.com...
> Would it an issue in setting up Log Shipping if the database files and the
> Log files of the primary SQL Server 2000 reside on a RAID5 and Raid0+1
> arrays?sql

log shipping question

Hi,
I've established the log shipping between my Primary database server and the
Stand by database server.
In the Stand by server, the database is gray, and in the BACKUP folder,
there're MyDB_init.bak and many .TRN files.
1.How do I make the MyDB's color not gray in that Stand by server? I wanna
test the SELECT and see if the record between the Primary and Stand by
server are consistant.
2. What will happen if I delete some .TRN files?
Thanks for help.
JasonJason
A standby database must be read-only. You can run SELECT statements againts
it. If you want the db READ-WRITE apply last log file and run
RESTORE DATABASE database_name WITH RECOVERY
EXEC SP_DBOPTION 'database_name', 'read only', 'false'
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:OGKoEINJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I've established the log shipping between my Primary database server and
> the
> Stand by database server.
> In the Stand by server, the database is gray, and in the BACKUP folder,
> there're MyDB_init.bak and many .TRN files.
> 1.How do I make the MyDB's color not gray in that Stand by server? I
> wanna
> test the SELECT and see if the record between the Primary and Stand by
> server are consistant.
> 2. What will happen if I delete some .TRN files?
> Thanks for help.
>
> Jason
>