Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to make log-shipped database to be available for queries Expand / Collapse
Author
Message
Posted Wednesday, December 31, 2008 3:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:38 AM
Points: 330, Visits: 964
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
Post #627919
Posted Wednesday, December 31, 2008 4:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 2,107, Visits: 5,405
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/
Post #627941
Posted Wednesday, December 31, 2008 4:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:38 AM
Points: 330, Visits: 964
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
Post #627945
Posted Wednesday, December 31, 2008 4:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 2,107, Visits: 3,582
This appears to be a duplicate post. Also refer to post - http://www.sqlservercentral.com/Forums/Topic616876-146-1.aspx

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #627946
Posted Wednesday, December 31, 2008 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,291, Visits: 13,552
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"
Post #628090
Posted Wednesday, December 31, 2008 10:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 2,107, Visits: 5,405
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/
Post #628176
Posted Thursday, January 1, 2009 12:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
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/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #628371
Posted Thursday, January 1, 2009 12:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:38 AM
Points: 330, Visits: 964
Thanks a Lot for the timely needed information.

With Regards
Dakshin
Post #628376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse