Why SQL Server 2005 Database Mirror Can't Failover?

Hello All,

I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.

1. Enable encrypted outbound connections on the primary server
2. Enable encrypted outbound connections on the mirror server
3. Enable encrypted outbound connections on the witness server
4. Enable encrypted inbound connections on the primary server
5. Enable encrypted inbound connections on the mirror server
6. Enable encrypted inbound connections on the witness server
7. Set mirror''s partner to the primary
8. Set primary''s partner the mirror
9. Set principal€™s witness.
Every step run''s normally,but why can''t auto failover.

I used the following Script:

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO



ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates



/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
--On Mirror
--Remove the database
--Connect to either partner.
--Issue the following Transact-SQL statement:

ALTER DATABASE Mirrortest SET PARTNER OFF

--Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:

--RESTORE DATABASE Mirrortest WITH RECOVERY;

RESTORE DATABASE [Mirrortest] FROM
DISK = N'D:BackupCertMirrortest.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
select * from sys.endpoints

drop endpoint Mirror_Endpoint_Mirroring

select * from sys.certificates


Create certificate MirrorCertForPartner
with subject ='this is the certificate for mirror',
start_date='06/25/2006'

backup certificate MirrorCertForPartner
to file='d:ackupcertMirrorCertForPartner.cer'



CREATE ENDPOINT Mirror_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (
Authentication=Certificate MirrorCertForPartner
,ROLE=ALL)
GO

/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

--On Witness

USE [master]
GO


select * from sys.certificates

drop certificate CertForDBRole
GO


create master key
encryption by password='asdf'

Create certificate CertForDBRole
with subject='this is a certificate for pricipal role',
start_date='06/25/2006'

backup certificate CertForDBrole
to file='c:CertForDBRole.cer'


select * from sys.endpoints

drop endpoint Witness_Endpoint_Mirroring


CREATE ENDPOINT Witness_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7000)
FOR DATABASE_MIRRORING (
Authentication=Certificate CertForDBRole
,ROLE=ALL)
GO


/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO



ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates


/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
use master;
go
DROP USER MirrorLogin1
DROP LOGIN MirrorLogin1
DROP USER MirrorLogin2
DROP LOGIN MirrorLogin2
GO

Create login MirrorLogin1
with password='MirrorLogin'
go
Create user MirrorLogin1
from login MirrorLogin1
go

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1
go

--stop
create certificate PrincipalCertForPartnerPub
authorization MirrorLogin1
from file = 'd:BackupCertPrincipalCertForPartner.cer';
go

Create login MirrorLogin2
with password='MirrorLogin'
go
Create user MirrorLogin2
from login MirrorLogin2
go
create certificate WitnessCertForPartnerPub
authorization MirrorLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2
go


ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.10.116.42:5022'
GO

ALTER DATABASE Mirrortest
SET WITNESS = 'TCP://w7mis02:5024'
GO




/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

use master;
go
DROP USER WitnessLogin1
drop login WitnessLogin1
DROP USER WitnessLogin2
drop login WitnessLogin2

Create login WitnessLogin1
with password='WitnessLogi*n@'
go
create user WitnessLogin1
from login WitnessLogin1
go

create certificate PrincipalCertForPartnerPub
authorization WitnessLogin1
from file = 'c:ackupcertPrincipalCertForPartner.cer';
go
--***************************************
Create login WitnessLogin2
with password='WitnessLogi*n@'
go
create user WitnessLogin2
from login WitnessLogin2
go

create certificate MirrorCertForPartnerPub
authorization WitnessLogin2
from file = 'c:ackupcertMirrorCertForPartner.cer';
go

Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1
go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2
go

All runs normally,But how can't be auto failover?