SQL Server Database Mirroring without witness

Question

I have setup Database mirroring for SQL Server 2005 for my database on two different SQL Server machines. There is no witness server used here. I also see the status "Principal/Synchronized" and "Mirror,Synchronized/Restoring" respectively on Primary and Mirror servers for my database.

Now I wanted to test whether the failover to mirror server works fine or not. So I wrote a small console app like below.

try
{
    SqlConnection cn = new SqlConnection("data source=PRIMARYSQL1;Failover Partner=MIRRORSQL1;initial catalog=TESTDB;Integrated Security=SSPI;");
    SqlCommand cm = new SqlCommand("insert into department(name) values('Dept10')", cn);
    cn.Open();
    cm.ExecuteNonQuery();
}

I then turned off thehSQL Server and SQL Agent services on the primary server and I expected my console app to still work fine but I get below error.

"A network-related or instance-specific error occurred while establishing a conne ction to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a conne ction to SQL Server)"

Also, once the primary server goes down I have no way to make the mirror database primary as it doesn't allow me to do that from the mirror server.

I am sure I am doing something wrong. Can anyone help me?

Answer 1

I think you're looking for something like this (run this on the mirror):

ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS GO

Answer 2

You are expecting automatic failover to occur, but automatic failover cannot occur w/o a witness. Given that any SQL Express instance running from any cheap/aging hardware can act as a witness, I would strongly suggest that you add a witness and have automatic failover, rather than have to do manual failover with potential data loss in case of a primary loss.