CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-mirroring
MjeOsX
MjeOsX

Reputation: 405

Database Mirroring doesn't work (SQL Server 2008)

I have a problem with the database connection. There are two database server: DBS1, DBS2

DBS1 (primary server) DBS2 (mirrored server)

I connect with the following Connection String:

Data Source=DBS1;Failover Partner=DBS2;Persist Security Info=True;Initial Catalog=database;User ID=xxx;Password=xxx

When I run my program on my PC it works, but when i try the program on the webserver in the DMZ the failover doesn't work.

Can you tell me some configurations which can be the reason for this?

Upvotes: 1

Views: 232

Answers (1)

bennie j
bennie j

Reputation: 739

Run this on your principal db

SELECT DB_NAME(database_id) AS 'DatabaseName'
, mirroring_role_desc 
, mirroring_safety_level_desc 
, mirroring_state_desc 
, mirroring_partner_instance
FROM
sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

The value returned in mirroring_partner_instance is the server name that will be used by your connection for failover, not DBS2. DBS2 will be used when it first tries to get a connection but cannot contact DBS1. If DBS1 is available the failover partner will be set in cache from the sql server value mirroring_partner_instance.

I imagine your PC can see this server using the name in mirroring_partner_instance, where as when running in the DMZ it cannot.

See here for more info: http://blogs.msdn.com/b/spike/archive/2010/12/15/running-a-database-mirror-setup-with-the-sqlbrowser-service-off-may-produce-unexpected-results.aspx

Upvotes: 1

Related Questions