locked
Database Mirroring and .net connection RRS feed

  • Question

  • Hi,

    I'm looking for some guidance please regarding how .net connection works with database mirroring. The following Database Mirroring in SQL Server MSDN article "https://msdn.microsoft.com/en-us/library/5h52hef8(v=vs.110).aspx" says in part

    "... The .NET Data Provider for SQL Server provides implicit support for database mirroring, so that the developer does not need to take any action or write any code once it has been configured for a SQL Server database...."

    My question is, is there a way to disable the implicit support for database mirroring?

    In our environment we don't use a witness server.  We manually fail over to the partner server if there is a need. Our environment is too complicated for auto fail over. 

    We have one database server where it's applications floods the partner server whenever we are failing over. This overwhelms the partner server as it's trying to become the principle partner and greatly slows the process.  Which in turn mean expensive downtime for the business. 

    Thanks for any assistance you can give. 

    Friday, February 17, 2017 7:29 PM

Answers

  • Hi Joe,

    Adding to my pervious post,  you could use SQL Agent Alert to automatically disable/enable logins on failover. Here’s the steps:
    1. Create a SQL Agent job that enables login(ENABLELOGIN).
    2. Create a SQL Agent job that disable login(DISABLELOGIN).
    3. Create a SQL Agent alert like this and responses with DISABLELOGIN job.
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'DBMPrincipal', 
    		@message_id=0, 
    		@severity=10, 
    		@enabled=1, 
    		@delay_between_responses=60, 
    		@include_event_description_in=0, 
    		@event_description_keyword=N'Database mirroring is active with database ''DBNAME'' as the principal copy.', 
    		@category_name=N'[Uncategorized]', 
    		@job_id=N'00000000-0000-0000-0000-000000000000' --JOBID of ENABLELOGIN job
    GO


    4. Create a SQL Agent alert like this and responses with DISABLELOGIN job.
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'DBMMirror', 
    		@message_id=0, 
    		@severity=10, 
    		@enabled=1, 
    		@delay_between_responses=60, 
    		@include_event_description_in=0, 
    		@event_description_keyword=N'Database mirroring is active with database ''DBNAME'' as mirror copy.', 
    		@category_name=N'[Uncategorized]', 
    		@job_id=N'00000000-0000-0000-0000-000000000000' --JOBID of DISABLELOGIN job
    GO

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JoeDriscoll Tuesday, February 21, 2017 4:26 PM
    Monday, February 20, 2017 5:21 AM

All replies

  • Basically if you do not use the Failover Partner=PartnerServerName parameter in your connection string, this means that you will need to go to your connection strings and edit them for the mirror server name (now principal) on failover.

    It also means that you will need to manual failover of your mirror (ALTER DATABASE database_name SET PARTNER FAILOVER) on your principal. Or a force failover (ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS).

    You also want to set your connection timeout to a large value - perhaps 300 - which is 5 minutes.

    This means that on failover your applications will crash until you edit the connection string for the mirror server (now principal).

    Perhaps your best option is to use the failoverpartner setting and set your connection timeout for a long time period - 5 min. Then have retry logic in your code where if it can't connect it will keep trying for 5 min, and then try the failover partner.

    Friday, February 17, 2017 7:51 PM
  • Thank Hilary for the quick reply.

    The connection string does not use the "failover Parnter=PartnerServerName" parameter. (see below). That MSDN article says that sqlconnection implicitly supplies that mirror partner information to the client (If I understand that correctly).  That's why I'm asking if that "implicit" parameter/option can be disabled. 

    thanks

    Connection String properties:

    ${server}: PFISTER

    ${username}: GL$MIS_DCTASKSRV

    ${database}: DCS

    ${timeout}: 600

     

    Friday, February 17, 2017 8:13 PM
  • If and only if the failoverpartner parameter is specified. Otherwise the connection attempt will keep trying to connect to the original primary/principal on failover.

    Your approach requires some logic to reconnect to the new primary on failover, or you must edit the connection strings.

    Friday, February 17, 2017 8:34 PM
  • My question is, is there a way to disable the implicit support for database mirroring?

    In our environment we don't use a witness server.  We manually fail over to the partner server if there is a need. Our environment is too complicated for auto fail over. 

    So your plan is to fail over to the mirror, change the client connection strings, and restart the client apps in an orderly manner? 

    I have not tried this but I think you could omit the Initial Catalog database specification from the connection string and rely on the login's default database context. The doc suggests the partner information is not cached in that case.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 17, 2017 8:45 PM
  • Thank you both for you help.

    First all of our (or nearly all of them) use a DNS CNAME to connect to SQL Server.  For the mirror switch we actually have a fairly automated batch process that we run manually.  

    That Ibatis example I posted is the general rule of thumb for our developers.  

    I'm still baffled than how these apps try to connect to the mirror server when A: the connection string doesn't specify a mirror server. And B: the implicit option of sql connection string is only valid if it specifies the mirror server. 

    Thanks

    Joe

    Friday, February 17, 2017 8:57 PM
  • The problem with updating your DNS entry is that the dns cache on the clients must expire before they pick up the new entry.

    I am equally as baffled as you.

    Friday, February 17, 2017 9:11 PM
  • I'm still baffled than how these apps try to connect to the mirror server when A: the connection string doesn't specify a mirror server. And B: the implicit option of sql connection string is only valid if it specifies the mirror server. 

    My take is that when a client successfully connects to the primary, the failover partner name is returned and cached regardless of the connection string partner specification. That cached information is used later if the primary connection fails.  The value of the explicit failover partner specification in database mirroring is that clients can connect initially to the mirror when the primary is down during the initial connection. 

    I think you would get the behavior you want by omitting the initial catalog and failover specifications.  After a manual failover, change the DSN and flush the client cache like Hilary suggested and clients ought to be able to connect.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 17, 2017 9:28 PM
  • Our TTL for the CNAMES is set to 2 minutes.  So it's not too bad.  Having said that, we are in the process of migrating to Load Balancers where the refresh rate is quicker, or so I am told. 

    and for this year we are supposed to be looking at some other HA options.  

    when I started working here 14 years ago, we had 2 production instances of SQL Server. Today we better than 350 production instances + the various development & beta servers.

    thank you for your help.

    Friday, February 17, 2017 9:29 PM
  • Hi Joe,

    >>"... The .NET Data Provider for SQL Server provides implicit support for database mirroring, so that the developer does not need to take any action or write any code once it has been configured for a SQL Server database...."

    I agree with Dan on this. And this is explained in BOL as well ‘If you omit the name of the failover partner server and the principal database is unavailable when the client application first connects then a SqlException is raised. When a SqlConnection is successfully opened, the failover partner name is returned by the server and supersedes any values supplied in the connection string.’.

    >>We have one database server where it's applications floods the partner server whenever we are failing over. This overwhelms the partner server as it's trying to become the principle partner and greatly slows the process.

    Am I safe to assume you are seeing a lot error 18456, state 38(Login failed for user ‘USERNAME’. Reason: Failed to open the explicitly specified database 'MIRROREDDATABASE'. [CLIENT: <xxx.xxx.xxx.xxx >]) during the failover and it prevents mirror database from coming online? 

    If so, since you mentioned you are doing manual failover, I would suggest you try disable the application logins(on mirror server) and enable them only after the failover is complete. That way, the application would receive a quicker response(error 18470 ‘Login failed for user '%.*ls'. Reason: The account is disabled.%.*ls’) and it shouldn’t slow down the recovery process.


    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 20, 2017 4:44 AM
  • Hi Joe,

    Adding to my pervious post,  you could use SQL Agent Alert to automatically disable/enable logins on failover. Here’s the steps:
    1. Create a SQL Agent job that enables login(ENABLELOGIN).
    2. Create a SQL Agent job that disable login(DISABLELOGIN).
    3. Create a SQL Agent alert like this and responses with DISABLELOGIN job.
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'DBMPrincipal', 
    		@message_id=0, 
    		@severity=10, 
    		@enabled=1, 
    		@delay_between_responses=60, 
    		@include_event_description_in=0, 
    		@event_description_keyword=N'Database mirroring is active with database ''DBNAME'' as the principal copy.', 
    		@category_name=N'[Uncategorized]', 
    		@job_id=N'00000000-0000-0000-0000-000000000000' --JOBID of ENABLELOGIN job
    GO


    4. Create a SQL Agent alert like this and responses with DISABLELOGIN job.
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'DBMMirror', 
    		@message_id=0, 
    		@severity=10, 
    		@enabled=1, 
    		@delay_between_responses=60, 
    		@include_event_description_in=0, 
    		@event_description_keyword=N'Database mirroring is active with database ''DBNAME'' as mirror copy.', 
    		@category_name=N'[Uncategorized]', 
    		@job_id=N'00000000-0000-0000-0000-000000000000' --JOBID of DISABLELOGIN job
    GO

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JoeDriscoll Tuesday, February 21, 2017 4:26 PM
    Monday, February 20, 2017 5:21 AM
  • Lin,

    Thank you for your suggestions.  You are correct in assuming the 18456 errors. We will seriously look at the options that you suggested.

    -Joe

    Tuesday, February 21, 2017 4:25 PM