How to make log-shipped database to be available for queries

Question

How to make log-shipped database to be available for queries

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
December 31, 2008 at 3:04 am

#204574

Hi,
How to make log-shipped database to be available for queries.
I have MyDB in one Server which is used for Log-shipping and in the other Server MyDB is getting Logshipped. Now how do i cross check the DML Operations done in MyServer for MYDB is also been made in the other Server which is getting log-shipped.
In the sence can i execute a Select Statement to the logshipped DB in the other Server, even still my DB in My Server is active.
With Regards
Dakshin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

When you create the log shipping you have 2 choices. You can configure restore log operation to be done with norecovery or with standby option. If you use the norecovery option, you can not issue select statements on it. If instead of norecovery you use the standby option, you can run select queries on the database. Notice that in that case you also have to make the decision what to do when it is time to restore the log, but there are users that are using the database. You’ll have to wait with the restore or to kill the users’ processes before issuing the restore log statement. All of those options can be configured with the wizard.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 2

Thanks for the information, i will try the same and will get back to you if i have any queries. I Wish You A Very Happy New Year 2009

With Regards

Dakshin

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

This appears to be a duplicate post. Also refer to post - http://www.sqlservercentral.com/Forums/Topic616876-146-1.aspx

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Perry Whittle SSC Guru Points: 233846 More actions · Answer 4

Daksin

as Adi pointed out you cannot query the database in the norecovery state. Bear in mind with the standy option when log file restores occur users will be kicked out without warning by the restore process. If you're needing to query the database that much then Log shipping probably isn't your best option.

If you're asking how can you bring the database back online when in the norecovery state then simply issue

RESTORE DATABASE MYDATA WITH RECOVERY

Bear in mind that the log shipping config will be broken

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Perry Whittle (12/31/2008)
Daksin
as Adi pointed out you cannot query the database in the norecovery state. Bear in mind with the standy option when log file restores occur users will be kicked out without warning by the restore process. If you're needing to query the database that much then Log shipping probably isn't your best option.
If you're asking how can you bring the database back online when in the norecovery state then simply issue
RESTORE DATABASE MYDATA WITH RECOVERY
Bear in mind that the log shipping config will be broken

Acutely when you configure the log shipping with standby option, you can also select between 2 choices – kill all processes in the secondary database and perform log restore or don’t perform log restore if the database is being used. Of course if you select the second option, the restore operation might never run if someone opens a connection to the database and doesn’t close it, so it is better to use the first option.

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 6

dakshinamurthy (12/31/2008)
Hi,
How to make log-shipped database to be available for queries.
I have MyDB in one Server which is used for Log-shipping and in the other Server MyDB is getting Logshipped. Now how do i cross check the DML Operations done in MyServer for MYDB is also been made in the other Server which is getting log-shipped.
In the sence can i execute a Select Statement to the logshipped DB in the other Server, even still my DB in My Server is active.
With Regards
Dakshin

When u sertup log shipping , then make secondary database on stand by mode

or

restore database dbname with recovery

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 7

Thanks a Lot for the timely needed information.

With Regards

Dakshin