Disconnected Mirror / In Recovery

Hello,

I'm having a problem with two mirrored databases, using SQL Server 2005 SP2, that autmatically failed over last night for unknown reasons. I was trying to fail them back over to the primary SQL server because it says the mirror is disconnected and out of sync. Other databases failed over too, but I was able to fail those back over without a problem. It's just these two. I removed the mirror from the secondary server thinking that this would allow me to restore the database back on the primary server, but that didn't help.

These two databases show a status of (Principal, Disconnected / In Recovery) and I still cannot do anything with the two databases on the primary server. When I try to pull up the properties for them, I get the following error:

"Database <Name> is enabled for Database Mirroring, but neither the partner nor the witness server instances are available: the database cannot be opened.(Microsoft SQL Server, Error: 955)"

I cannot delete, detach, Alter or do anything with the databases. If I could just delete them that would be fine so I can just do a restore, but I can't seem to do anything.

Does anyone know what I can do to resolve this problem.

Thanks in advance!

-Jay

I was finally able to get the two databases to come back up online. I was trying so many different things, I 'm not sure which one, or combination, fixed it. But I checked the processes and there was one process running on one of the databases so I killed it. It seemed to come back right away, but after that, I was able to detach the DB. I reattached it and both of the databases came up with an online status.

I hope this helps.

-Jay

|||

This sounds similar to problems I have had (see my post below on split brain). I too had to kill connections to the principal in order to do anything to fix it, it went into recovery when I tried to remove mirroring and all users were disconnected.

I think this is very undesirable. There is no way both nodes should ever be the principal. If failover happens, it should either complete or not, there should be no half way, whatever the mode of failure.

|||

Sound like it, also mention what kind of configuration mode you are using for DB Mirroring.

As suggested referring to witness server log will helps a bit to see what is going on.

Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disaster recovery

How to protect the transaction logs from disaster considering the loss
of hardware mirror? Can we use remote mirror for these files?
tram,
Back them up frequently, say every 5 mins? You can also deploy log shipping:
http://msdn.microsoft.com/library/de...erver_8elj.asp
Do you have redundancy at the disk level? Have you implemented a raid array?
RAID-1 is commonly used for transaction log protection - this cannot be used
remotely though, it is local to the server.
See:
http://www.acnc.com/04_01_01_flash.html
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404211702.2fbb4462@.posting.google.c om...
> How to protect the transaction logs from disaster considering the loss
> of hardware mirror? Can we use remote mirror for these files?
|||What if RAID 1 crashes or on fire? Oracle has got redo groups where
you can place on REDO group in local and other in SAN. Sql
serverdoesn't have this functionality and we lose the data in active
redo . Log shipping doesn't meet our requirement.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message news:<e41kfnEKEHA.2716@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> tram,
> Back them up frequently, say every 5 mins? You can also deploy log shipping:
> http://msdn.microsoft.com/library/de...erver_8elj.asp
> Do you have redundancy at the disk level? Have you implemented a raid array?
> RAID-1 is commonly used for transaction log protection - this cannot be used
> remotely though, it is local to the server.
> See:
> http://www.acnc.com/04_01_01_flash.html
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0404211702.2fbb4462@.posting.google.c om...
|||tram,
I'm not familiar with REDO groups. If you are using a SAN, then you could
use the SAN replication functionality to replicate the SQL Server data.
Which SAN vendor are you using? I know this is possible with EMC SRDF and
also Hitachi Data Systems TrueCopy.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404230248.78fefeb1@.posting.google.c om...
> What if RAID 1 crashes or on fire? Oracle has got redo groups where
> you can place on REDO group in local and other in SAN. Sql
> serverdoesn't have this functionality and we lose the data in active
> redo . Log shipping doesn't meet our requirement.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:<e41kfnEKEHA.2716@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
shipping:[vbcol=seagreen]
http://msdn.microsoft.com/library/de...erver_8elj.asp[vbcol=seagreen]
array?[vbcol=seagreen]
used[vbcol=seagreen]

Disaster recovery

How to protect the transaction logs from disaster considering the loss
of hardware mirror? Can we use remote mirror for these files?tram,
Back them up frequently, say every 5 mins? You can also deploy log shipping:
http://msdn.microsoft.com/library/d...>
ver_8elj.asp
Do you have redundancy at the disk level? Have you implemented a raid array?
RAID-1 is commonly used for transaction log protection - this cannot be used
remotely though, it is local to the server.
See:
http://www.acnc.com/04_01_01_flash.html
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404211702.2fbb4462@.posting.google.com...
> How to protect the transaction logs from disaster considering the loss
> of hardware mirror? Can we use remote mirror for these files?|||What if RAID 1 crashes or on fire? Oracle has got redo groups where
you can place on REDO group in local and other in SAN. Sql
serverdoesn't have this functionality and we lose the data in active
redo . Log shipping doesn't meet our requirement.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message news:<e41kfnEKEHA.2716@.tk2ms
ftngp13.phx.gbl>...[vbcol=seagreen]
> tram,
> Back them up frequently, say every 5 mins? You can also deploy log shippin
g:
> http://msdn.microsoft.com/library/d...
erver_8elj.asp
> Do you have redundancy at the disk level? Have you implemented a raid arra
y?
> RAID-1 is commonly used for transaction log protection - this cannot be us
ed
> remotely though, it is local to the server.
> See:
> http://www.acnc.com/04_01_01_flash.html
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0404211702.2fbb4462@.posting.google.com...|||tram,
I'm not familiar with REDO groups. If you are using a SAN, then you could
use the SAN replication functionality to replicate the SQL Server data.
Which SAN vendor are you using? I know this is possible with EMC SRDF and
also Hitachi Data Systems TrueCopy.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404230248.78fefeb1@.posting.google.com...
> What if RAID 1 crashes or on fire? Oracle has got redo groups where
> you can place on REDO group in local and other in SAN. Sql
> serverdoesn't have this functionality and we lose the data in active
> redo . Log shipping doesn't meet our requirement.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:<e41kfnEKEHA.2716@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
shipping:[vbcol=seagreen]
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8elj.asp[/ur
l][vbcol=seagreen]
array?[vbcol=seagreen]
used[vbcol=seagreen]

Disaster recovery

How to protect the transaction logs from disaster considering the loss
of hardware mirror? Can we use remote mirror for these files?My server stuff is a bit rusty but I don't believe you can
have a remote mirror, mainly due to network traffic.
Have you though of backing up your database (files and
logs) onto a different server.
J
>--Original Message--
>How to protect the transaction logs from disaster
considering the loss
>of hardware mirror? Can we use remote mirror for these
files?
>.
>|||tram,
Back them up frequently, say every 5 mins? You can also deploy log shipping:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8elj.asp
Do you have redundancy at the disk level? Have you implemented a raid array?
RAID-1 is commonly used for transaction log protection - this cannot be used
remotely though, it is local to the server.
See:
http://www.acnc.com/04_01_01_flash.html
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404211702.2fbb4462@.posting.google.com...
> How to protect the transaction logs from disaster considering the loss
> of hardware mirror? Can we use remote mirror for these files?|||What if RAID 1 crashes or on fire? Oracle has got redo groups where
you can place on REDO group in local and other in SAN. Sql
serverdoesn't have this functionality and we lose the data in active
redo . Log shipping doesn't meet our requirement.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message news:<e41kfnEKEHA.2716@.tk2msftngp13.phx.gbl>...
> tram,
> Back them up frequently, say every 5 mins? You can also deploy log shipping:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8elj.asp
> Do you have redundancy at the disk level? Have you implemented a raid array?
> RAID-1 is commonly used for transaction log protection - this cannot be used
> remotely though, it is local to the server.
> See:
> http://www.acnc.com/04_01_01_flash.html
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0404211702.2fbb4462@.posting.google.com...
> > How to protect the transaction logs from disaster considering the loss
> > of hardware mirror? Can we use remote mirror for these files?|||tram,
I'm not familiar with REDO groups. If you are using a SAN, then you could
use the SAN replication functionality to replicate the SQL Server data.
Which SAN vendor are you using? I know this is possible with EMC SRDF and
also Hitachi Data Systems TrueCopy.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0404230248.78fefeb1@.posting.google.com...
> What if RAID 1 crashes or on fire? Oracle has got redo groups where
> you can place on REDO group in local and other in SAN. Sql
> serverdoesn't have this functionality and we lose the data in active
> redo . Log shipping doesn't meet our requirement.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:<e41kfnEKEHA.2716@.tk2msftngp13.phx.gbl>...
> > tram,
> >
> > Back them up frequently, say every 5 mins? You can also deploy log
shipping:
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_8elj.asp
> >
> > Do you have redundancy at the disk level? Have you implemented a raid
array?
> > RAID-1 is commonly used for transaction log protection - this cannot be
used
> > remotely though, it is local to the server.
> >
> > See:
> > http://www.acnc.com/04_01_01_flash.html
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "tram" <tram_e@.hotmail.com> wrote in message
> > news:26ee1067.0404211702.2fbb4462@.posting.google.com...
> > > How to protect the transaction logs from disaster considering the loss
> > > of hardware mirror? Can we use remote mirror for these files?