Log Shipping via DMZ

Hi MVPs,
I have a situation where I have a DB server that is in a VLAN that is only
accessable from a server in the DMZ, and I have a site operated by a service
provider that will be offering a DR facility. I want to switch on log
shipping, but how can I get the data to the service provider data centre via
the DMZ.
What options do I have?
I'd be very grateful for your help.
Mac
Dude, I'm not an MVP, but I play one at work... anyway, your options here are
extremely limited. If you can't get direct access to the server to ship the
files, then your only choice is to roll your own. You can either write a
process to ship the logs to an ftp and then another process to pull them down
and restore them, or you can create a common share and drop the logs there
and pick them up on the other side. Either way, you're gonna have to do some
coding. And it's a pain. It's best to get them to punch a hole if you can.
"Mac" wrote:

> Hi MVPs,
> I have a situation where I have a DB server that is in a VLAN that is only
> accessable from a server in the DMZ, and I have a site operated by a service
> provider that will be offering a DR facility. I want to switch on log
> shipping, but how can I get the data to the service provider data centre via
> the DMZ.
> What options do I have?
> I'd be very grateful for your help.
> Mac

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 Failover Situation

Hi All,

I am little confused in the way how log shipping has been chnaged in SQL Server 2005. I read the whole of articles in BOL to find that microsoft has not given any procedures to handle the failover situation and the role change job. From my understanding i shall ask some quick questions.

1. For the failover MS says that restore any unrestored log files from the primary database to the secondary database and bring the secondary database up. My Question here is. What if my last log file is restored or the server where the primary database resides is unavailable due to some hardware or OS failure?

2. I have brought my secondary up restoring the last log file from the primary server. Now MS says that to again change the primary server to what it was it says to backup the database in secondary and restore it in primary and reconfigure the logshipping. Is this true are there no other alternatives to this?

3. What happened to the old procedures that were available with SQL Server 2000 that we used to do the role change? I find they are not available in 2005.

looking for answers to enrich myself thanks for all.

Cheers,
Sugesh Kumar R. MCDBA

Theory is when you know something, but it doesn't work.
Practice is when something works, but you don't know why.
Programmers combine theory and practice: Nothing works and they don't know why.

Hi All,

Even I am interested to know the solution to this. Tried to find the good old role change procedures but to no avail.

Thanks in anticipation.

Amit

|||Sugesh has rightly pointed out ....it seems there is no specific role change procedure available in sql 2005 as in sql 2000...any valuable suggestion in this regard from the MVPs or any1 will be greatly appreciated...we are fed up searching.|||

well here's what I thought of...It is not comparable to what the Stored Procedures used to do in 2000 but I guess will server to some extent...

1. Backup the log of the primary database giving the file name a specific name and with no recovery.

2. This will put the original primary in Recovering mode which will take the t-log backups from the would be primary.

3. Create a job to copy this file over to secondary server (xp_cmdshell will have to be enabled for this)

4. Restore this log on the secondary database with recovery.

This will convert the secondary database into primary and primary into secondary and of course log shipping will have to be set up again in the reverse way.

Suggestions on this are most welcome and awaited.

|||yes what you said is possible but how can you apply tran logs from the new primary server to old primary? you can only do it by reconfiguring log shipping and its jobs.......but in sql 2000 you had those sps i.e sp_change_primary_role something like that....if your primary is down you can bring your secondary up and just use those sps to perform role change.....so that ur secondary has become primary and once ur primary server is up and running you can once again rerun those sps to make ur original primary as primary and original secondary as secondary.....but those sps seem to be depriciated......|||

Deepak,

I completely agree with what you have said and that's why in my post i said that it is not comparable to what the original procedures used to do.

But since they have been deprecated now, we have to find some work around.

-Amit

|||anyways Amit thanks for the alternative you provided...it was indeed useful...as sugesh has pointed out no sps are present sounds bizzare.......friends come out with your suggestions it will greatly appreciated.........|||I have contradicting views to what Amit and Deepak has said. Let me post the exact brief reply sooner. But my question stills remians to be unanswered?