database mirroring error? -- Scenario HASL2.3

HASL2.3 refers to the scenario within this article of a Mirror Server Loss
followed by Witness Loss:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
Win2k3 R2 servers.
We were planning on upgrading some hardware (adding a disk array) to our
secondary site (datacenter B) and I thought that it would be a good time to
test a couple of database mirroring scenarios.
I set up database mirroring on a test database. The primary was on a server
in datacenter A. I set up a witness on a box located in the test lab at our
office . With this setup I was running database mirroring in a high safety
with automatic failover (synchronous) mode.
Mirroring was working. I manually failed over and back. No problem there.
They shut down the server within datacenter B. The server in datacenter A
could still talk to my watcher box in the lab in our office. The primary
database on the server within datacenter A stayed available but it showed
that the mirrored server was disconnected (as expected).
I stopped SQL Server on the watch box in the lab. At that point the primary
database on the server within datacenter A became unavailable (Principal,
Disconnected / In Recovery). The database was unavailable (as expected).
The fly in the ointment:
They finished working on the server within datacenter B. The box came back
up without any issues. The other databases on that server are intact and
everything looks good. SQL Server is running normally...BUT the database
mirroring did not resume. The article indicates that "If Server B is
restored first, mirroring resumes, although no automatic failover is
possible without a witness." I did NOT experience this. My "Server B" has
been back online for 45 minutes and its status still shows as "Principal,
Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
Disconnected / In Recovery" on Server B (datacenter B).
Has anyone else experienced this scenario? I could try turning on the watch
server again, but I would like to see the two servers (principal and mirror)
synchronize on their own. I realize that automatic failover will not be
possible until I bring the watch back online, but the mirrored database(s)
should be available now that both the principal and mirror are back online.
Right?
--
Keith Kratochvilmy understanding of the situation is that when both or even one of the
datacenters comes online, it cannot automatically failover because both
are unable to deciede which of them is the actual principal(beacuse of
the link between them is not there) unless there is a witness as u
said. if both come online both try to actively become the principal and
hence the same problem
if u add a witness it should solve your problem or u can manually make
the mirror as your principal
Keith Kratochvil wrote:
> HASL2.3 refers to the scenario within this article of a Mirror Server Loss
> followed by Witness Loss:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
> Win2k3 R2 servers.
> We were planning on upgrading some hardware (adding a disk array) to our
> secondary site (datacenter B) and I thought that it would be a good time to
> test a couple of database mirroring scenarios.
> I set up database mirroring on a test database. The primary was on a server
> in datacenter A. I set up a witness on a box located in the test lab at our
> office . With this setup I was running database mirroring in a high safety
> with automatic failover (synchronous) mode.
> Mirroring was working. I manually failed over and back. No problem there.
> They shut down the server within datacenter B. The server in datacenter A
> could still talk to my watcher box in the lab in our office. The primary
> database on the server within datacenter A stayed available but it showed
> that the mirrored server was disconnected (as expected).
>
> I stopped SQL Server on the watch box in the lab. At that point the primary
> database on the server within datacenter A became unavailable (Principal,
> Disconnected / In Recovery). The database was unavailable (as expected).
>
> The fly in the ointment:
> They finished working on the server within datacenter B. The box came back
> up without any issues. The other databases on that server are intact and
> everything looks good. SQL Server is running normally...BUT the database
> mirroring did not resume. The article indicates that "If Server B is
> restored first, mirroring resumes, although no automatic failover is
> possible without a witness." I did NOT experience this. My "Server B" has
> been back online for 45 minutes and its status still shows as "Principal,
> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
> Disconnected / In Recovery" on Server B (datacenter B).
>
> Has anyone else experienced this scenario? I could try turning on the watch
> server again, but I would like to see the two servers (principal and mirror)
> synchronize on their own. I realize that automatic failover will not be
> possible until I bring the watch back online, but the mirrored database(s)
> should be available now that both the principal and mirror are back online.
> Right?
> --
> Keith Kratochvil|||Please re-read my post.
I had an automatic failover setup configured between data center A
(Primary), data center B (Mirror), and our office (Witness).
I wanted to test what would happen if we lost the Mirror and then the
Witness.
When we lost the Mirror (because it was down for hardware maintenance)
everything kept running because the Witness was still available.
I shut down the Witness and the Primary stopped working (as expected).
The hardware upgrade on the Mirror was complete and we brought that machine
back online. At this point we had
Primary server: on
Mirror server: on
Witness server: off
Mirroring: broken
Expected Result: Primary and Mirror would start "talking" to each other,
synchronize any changes , and come back online.
Actual Result: the database that I mirrored (MirroredDemo) is unavailable.
When I try to connect to or use that database on the primary I receive the
following message:
"Database MirroredDemo is enabled for Database Mirroring, but neither the
partner nor witness server instances are available: the database cannot be
opened.
"
Current situation:
(Via SSMS) The database is showing that it is "Principal, Disconnected / In
Recovery" within the database list. If I look at the database mirroring
properties the status text is: "No connection: cannot connect to the mirror
or witness server instances." In this state the database is UNAVAILABLE
If I bring the witness back online the databae shows that it is "Principal,
Disconnected." Database Mirroring is not functioning. The good news is
that I can access the database. The bad news is that I cannot fail over. I
receive this message when I try to fail over: "Msg 1422, Level 16, State 2,
Line 1
The mirror server instance is not caught up to the recent changes to
database "MirroredDemo". Unable to fail over."
I thought that I might need to resume the mirroring session. With the
witness server still offline, I tried to execute this command to resume
mirroring: "ALTER DATABASE mirroreddemo SET PARTNER RESUME"
No luck.
I brought the witness online. The status (as shown within the database list
of SSMS) is still "Principal/Disconnected."
I tried resuming mirroring (again, with the command "ALTER DATABASE
mirroreddemo SET PARTNER RESUME") but that did not work either.
At this point the P,M, and W are all online.
Still, the status within the database mirroring properties on the principal
shows "No connection: cannot connect to the mirror or witness server
instances"
I am stumped. Why didn't it (mirroring) come back online and start working
after all the servers are back online?
In researching this issue I have discovered the following articles:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
We are in a similar situation as Muhammad. We have a few servers with more
than 100 databases on them. We are hoping to use mirroring as a high
availability solution to keep servers in different data centers in synch.
After reading those posts I am concerned that database mirroring might not
be able to meet our needs.
When we were running on SQL2k we used Replication. That is a path I would
rather not revisit.
Can anyone offer a solution to these problems
--mirroring not resuming properly on this one database
--mirroring not working (playing nice) on a server with many databases
Keith Kratochvil
"Udit Ghai" <ghai.udit@.gmail.com> wrote in message
news:1160458659.575183.108970@.i3g2000cwc.googlegroups.com...
> my understanding of the situation is that when both or even one of the
> datacenters comes online, it cannot automatically failover because both
> are unable to deciede which of them is the actual principal(beacuse of
> the link between them is not there) unless there is a witness as u
> said. if both come online both try to actively become the principal and
> hence the same problem
> if u add a witness it should solve your problem or u can manually make
> the mirror as your principal
> Keith Kratochvil wrote:
>> HASL2.3 refers to the scenario within this article of a Mirror Server
>> Loss
>> followed by Witness Loss:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>>
>> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
>> Win2k3 R2 servers.
>> We were planning on upgrading some hardware (adding a disk array) to our
>> secondary site (datacenter B) and I thought that it would be a good time
>> to
>> test a couple of database mirroring scenarios.
>> I set up database mirroring on a test database. The primary was on a
>> server
>> in datacenter A. I set up a witness on a box located in the test lab at
>> our
>> office . With this setup I was running database mirroring in a high
>> safety
>> with automatic failover (synchronous) mode.
>> Mirroring was working. I manually failed over and back. No problem
>> there.
>> They shut down the server within datacenter B. The server in datacenter
>> A
>> could still talk to my watcher box in the lab in our office. The primary
>> database on the server within datacenter A stayed available but it showed
>> that the mirrored server was disconnected (as expected).
>>
>> I stopped SQL Server on the watch box in the lab. At that point the
>> primary
>> database on the server within datacenter A became unavailable (Principal,
>> Disconnected / In Recovery). The database was unavailable (as expected).
>>
>> The fly in the ointment:
>> They finished working on the server within datacenter B. The box came
>> back
>> up without any issues. The other databases on that server are intact and
>> everything looks good. SQL Server is running normally...BUT the
>> database
>> mirroring did not resume. The article indicates that "If Server B is
>> restored first, mirroring resumes, although no automatic failover is
>> possible without a witness." I did NOT experience this. My "Server B"
>> has
>> been back online for 45 minutes and its status still shows as "Principal,
>> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
>> Disconnected / In Recovery" on Server B (datacenter B).
>>
>> Has anyone else experienced this scenario? I could try turning on the
>> watch
>> server again, but I would like to see the two servers (principal and
>> mirror)
>> synchronize on their own. I realize that automatic failover will not be
>> possible until I bring the watch back online, but the mirrored
>> database(s)
>> should be available now that both the principal and mirror are back
>> online.
>> Right?
>> --
>> Keith Kratochvil
>|||Hello?
Is anyone out there?
More importantly, does anyone have any ideas, suggestions, or helpful
comments?
--
Keith Kratochvil