"Failover Partner" keyword in connection string - Q&A

1. How to connect client to a Database Mirroring Session with failover aware

A: add FailoverPartner keyword in the connection string, for instance:

"Server=sq01;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"

2. why I get timeout error more frequently when I connect to mirrored database with keywords "Failover Partner"?

A: You probably encounter a .Net bug which happens on mirrored database, please refer to the link below

http://support.microsoft.com/kb/2605597

the workaround would be 

•Set the time-out connection string to the 150 

3. If the "failover partner" parameter is incorrectly setup in the connection string, is the client still aware of the failover, and connect to the mirror(new primary) after failover?

A: The Answer is YES.

when client connect to the primary server first time, it will download the mirror server name, and cache it in client process as failover partner name, it means, even if you set the wrong "Failover Partner" value in the connection string, client still can connect to the correct mirror server when failover happen. for instance, 

we have database mirror setup on SQ01 and SQ02, SQ01 is primary, in your connections string, we use SQ03 as failover partner as below, actually sq03 is not existed. 

"Server=sq01;Failover Partner=sq03; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"

when you failover db from sq01 to sq02, your connection will be failed, but after you reconnect it without changing any connection string,  you can still get the data which is from sq02. 

so even without specifying the failover partner name in the connection string, client can still access primary or mirror when failover, just like below

"Server=sq01;Failover Partner=; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"

Now you know, the words "Failover Partner" is the key in the connection string

4. how about the "Server" keywords in the connection string? if it is wrong, can client still support failover?

A: "Server" is most important in the connection string, here has several situation. let's use the previous sample

primary is sq01, mirror is sq02, sq03 is not existed.

1. "Server=sq03;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes" 

Failed, client can not connect to the server since 02 is mirror and not online.

2. "Server=sq03;Failover Partner=sq01; Database=mydb; Network=dbmssocn;Trusted_Connection=yes" 

successful, client connected 03 failed, then tried to connect failover partner which is sq01

but it doesn't support failover database from sq01 to sq02.  in other words, after client get data from sq01 successfully, if we failover db from sq01 to sq02, client will lost connection, and can not failover to sq02 automatically.

5. last question, just for interesting, if 2 db have same name on sq01 and sq02, both online, does it supports failover

A: No, it will not happen. let's say you have database mydb on sq01 and sq02, both are online without mirroring, you use the connection string below

"Server=sq01;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes" 

then , if the db on sq01 is online, your client can get data from sq01, but if the db on sq01 is not accessible, you will get error below:

Exception calling "Open" with "0" argument(s): "Server sq02, database mydb is not configured for database mirroring."

so it tells us the client provider is aware of mirror :) don't want to cheat it. 

database mirror is phasing out from new sql server version, alwayson is new technology of HA now.

One Database Mirror disconnected case

Today I got a database mirror case, I was told the all databases were in disconnected\in recovery status on both primary and mirror server, it is a dev environment.

Here just share my troubleshooting steps:

1. First, I login primary and mirror server. I got the same database status on both 2 servers

so it showed to me:

       a) since all databases had problem, it should be a system level error, not single database issue

       b) database in "disconnected/In Recovery" status, it indicts the server not only lost communication with the partner server(primary or mirror), but also lost communication with witness. so sql server can not bring database online on either servers, or else it will cause split-brain problem.

the question now is : why the 3 servers lost communication with each other?

2. Check connection among 3 servers with telnet command

telnet [sqlservername] [port]

all 3 servers can connect each other, so the network should be fine, and endpoing port is opened, firewall should be fine.

3. Check SQL Server Errorlog

I checked the sql server errorlog on all 3 servers(primary, mirror, and witness), I found same error message below:

2013-11-06 23:07:47.70 Logon       Database Mirroring login attempt by user 'xxx\xxxxx-db.' failed with error: 'Connection handshake failed. The login 'xxx\xxxxx-db' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: xx.xx.xx.xx]

so it should be permission account of account 'xxx\xxxxx-db'

4. Check Account 'xxx\xxxxx-db' in sql server

By checking account permission, it shows the account is sys admin on every sql servers. so there should be no permission issue.

in your case, if you find account is not sys admin, make sure you grant connect endpoint permission to that account.

so what's wrong here? 

Although the account is sys admin in sql server, but it doesn't mean the account is 100% ok, because it is window domain account, when using window authentication mode, sql server will ask window to authenticate the user account first(by NTLM or Kerberos), if windows authentication is failed, sql server will reject that login request.

so I tried to login sql server with the account 'xxx\xxxxx-db', this time I got error message below:

Good. we are closed to the answer. normally, you can find the detail information of login failed error in errorlog just like below:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]

the most important information is "State" code:

ERROR STATE	ERROR DESCRIPTION
2 and 5	Invalid userid
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid password
11 and 12	Valid login but server access failure
13	SQL Server service paused
18	Change password required

you can read more detailed from http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

But unfortunately, this time I didn't find anything from errorlog! then how to capture the detail information? you are right, Profiler trace!

I run the Profiler trace on sql server, and enabled the "Audit login failed" event only. finally I got the detail error message below

by searching the error message, I found the link below

http://sblackler.net/2012/05/30/sql-server-fix-token-based-server-access-validation/

It prove my guess that the window authentication has problem, in other words, the Account should have problem. 

what is the next step?

5. Check if there was any changes made before the error occurred

I am not a AD expert, troubleshooting the window account authentication is little bit hard for me, I can do it, but it might take several hours or days to troubleshooting it. so the easiest way is checking with the guy who report this issue to me, or ask AD expert to look into it. 

I asked the engineer who report this issue : is there any change they made on the account 'xxx\xxxxx-db' before the error occurred? suddenly they seems to remember something, and told me wait a minute, then they come back to me and said the issue has been solved, the mirror is recovered  now. I went back to the server, it is true, all database are in mirror synchronized status.

I asked them what happen, they told they made some changes on that account(like pull the account out of some domain groups), if I didn't mention the account to them, they would never think about the problem was caused by their change. so problem is solved. 

Despite no root cause here, we have some steps to narrow down the problem, and find a fastest way to solve it. 

Change file logical name for mirror database

Today I encountered a interesting issue when rename logical name for mirror database.

We have a database which has been setup mirror on it, and we need to change the logical file name for data and log file. As usually, I rename the logical name by SSMS on the primary server(SQL01), but after failover to the mirror(SQL02) server, I found only the data file has correct logical name, but the log file still has the original name, it didn't change!

so I tried to change the logical name for log file by SSMS again on SQL02 which is the current primary server, but failed and got error message:

Although the logical name of log file is old name, but the error indicted the old logical name didn't exist!

so I run the query blow
 select * from sysfiles

it showed the new logical name for the log file, however, when I run the query below:
select * from sys.master_files

it showed the old logical name! That's the difference~!

Table Sysfiles is in the user database, while sys.master_files is in the master database
http://msdn.microsoft.com/en-us/library/ms186782.aspx

When I changed the logical name for data and log file on Primary,  the transaction of the mirrored database is transferred to mirror, however master is not sync. so in the sys.master_files, it still has the old logical name. unfortunately, SSMS get the file logical name info from sys.master_files, so we saw the old logical name in SSMS on mirror(SQL02), and we can not change the name in SSMS since it still use the old name which does not exist!

 In order to correct the file logical name on mirror(SQL02), we need to update the file logical name in the master db. we rename the logical name to a temp name by "ALTER DATABASE" command, then change it back new name.

USE [DatabaseName]
GO
ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'New_logical_name', NEWNAME=N'New_logical_name_Temp')
GO
ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'New_logical_name_Temp', NEWNAME=N'New_logical_name')
GO

By this way, we update the logical file name in master database with new logical file name. Now primary and mirror server have the same logical file name.

There is another question:
Why the data logical name is correct in SSMS on mirror server(SQL02). I made another test
1. change the file logical name on Primary(SQL01)
2. check the logical name in table sys.master_files on mirror(SQL02), it is still the old name
3. failover to mirror(SQL02), then check the logical name in sys.master_files. this time the data file logical name has been changed to new name, while the log file logical name is still the old one.

so when mirror failover, sql server will bring the database online on mirror , and update the logical file name of data file, but there is issue, it not update the logical file name in master db.

Setup database mirroring and log shipping together

SQL Server has multiple high availability solutions. Comparing with cluster, database mirroring and logshipping are easier to setup, and they don't need special hardware.

here is a great article about how to setup the database mirroring and log shipping together.
http://technet.microsoft.com/en-us/library/ms187016.aspx

In our system, we setup data mirroring with high-safety mode in a fast network domain, and setup log shipping with the secondary server in remote data center, which provide off-site disaster-tolerant for our system.

All system diagram and setup steps are in the artive upper, but there is less comments about log shipping monitor setup. Based on my setup, if you want to keep log shipping monitoring as seamless as possible, you'd better use a standalone server as log shipping monitor server. By this way, after database mirroring failover from primary server to mirror server, log shipping monitor can still work.

However in my current system, when I setup log shipping after database mirroring setup complete, I use the current primary server as monitor server, which will cause problem after database mirroring failover.

because the secondary server only look current primary server as monitor server, so after restore job complete, it only update the log_shipping_monitor_secondary on primary, not on mirror. if primary server is down, and failover happen. the original mirror server will be primary server, however, it will not be the monitor server, and there is no log shipping monitor server any more.


When setup database mirroring and log shipping together, I recommend using a standalone server as witness server for database mirroring and monitor server for log shipping. This server can also be the file share server for transaction log backup file of log shipping. Or you can setup log shipping alert job on all primary, mirror and seconary server, it is also a simple way to solve the monitor problem.