locked
Database Mirroring automatic failover RRS feed

  • Question

  • Hi,

    I am considering implementing Database Mirroring on SQL 2019 standard edition.

    I need automatic failover, so will need a witness instance I assume? The application currently connects using a config file
    with the Servername\InstanceName specified.

    Can anyone tell me how I can get the application to point to the Mirror instance in the event the Principal fails?

    Does the witness instance need a licence?

    Thanks,

    Zoe


    • Edited by Zoe.Ohara Tuesday, August 4, 2020 9:43 AM
    Tuesday, August 4, 2020 9:43 AM

All replies

  • As you probably know. db mirroring is an old technology, it has been deprecated for a long time now. Anyhow, you can get automatic client re-direct by having an attribute in the connection string. Basically, both SQL Server names in the connection string, and it will connect to whichever is available.

    There is no "listener" or "virtual machine name" for the mirroring technology, so the client need to do the work, as described above.

    More info here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/database-mirroring-in-sql-server

    As for licensing, I refer to the licensing web site - the forums are for technical discussions. If that doesn't answer your question, we recommend that you contact an MS representative. https://www.microsoft.com/en-us/licensing/product-licensing/sql-server?activetab=sql-server-pivot%3aprimaryr2


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, August 4, 2020 10:02 AM
  • Tuesday, August 4, 2020 3:17 PM
  • Note that on standard edition only Basic availability groups are available. But, yes, AG is the successor of mirroring. Limitation for basic AGs here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, August 4, 2020 4:34 PM
  • Hi Zoe.Ohara,

    > I need automatic failover, so will need a witness instance I assume?

    Yes. If you want to automatic failover, you need to add one witness instance in your database mirrioring.

    The database mirroring with the witness database has both high availability and disaster recovery functions. If database mirroring is not configured with a witness server, automatic failover cannot be achieved. Once the principal database is abnormal, the database administrator needs to manually failover. Please reference: database-mirroring_OperatingModes

    Note:

    Database mirroring is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead.

    > Can anyone tell me how I can get the application to point to the Mirror instance in the event the Principal fails?

    You can specify the preferred server in the connection string, and you can also specify the alternate database after failover. Those applications that use ADO.NET or SQL Native Client to connect to SQL Server can get the ability to automatically redirect connections through this feature. The following is a sample connection string, the Data Source property specifies the preferred service. The Failover Partner attribute specifies the candidate server: "DataSource=SeverA;FailoverPartner=ServerB;InitialCatalog=AdventureWorks;Integrated Security=True;" Generally, we assign the server that plays the main role most of the time to the DataSource parameter. Assign the server that plays the mirror role most of the time to the Failover Partner parameter.  Please reference: connect-clients-to-a-database-mirroring-session-sql-server

    > Does the witness instance need a licence?

    It does not need one license. However, this is just my personal opinion, for the license issue, you can search “sqlserver 2019 license guide” and download the PDF to have a look firstly and then consult the license team for details.

    License Team: For detailed information about the license issue, please call 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist. For international customers, please use the licensing_worldwide to find contact information in your locations. Or you can go to Volume Licensing Service Center supportPlease choose region/language and choose to call or have web. People there will help you more effectively.

    If the response helped, do "Accept Answer" and upvote it.

     

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 8:48 AM
  • BTW, last time I used DB Mirroring, we could use Express Edition as witness. That is free. This is from memory, so do your own research. :-)

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 5, 2020 8:52 AM
  • Hi Zoe.Ohara,

    Is the reply helpful?

    If the response helped, do "Accept Answer" and upvote it.

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 1:23 AM
  • Zoe like others have said do not go for mirroring its deprecated feature with unknown support. Basic Availability group is just the feature that you would like. Has automatic failover and you do not need to worry about client redirection. OTOH achieving the same with mirroring is not less than hassle.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, August 6, 2020 12:06 PM
  • Hey, 

    As mentioned, you can use Basic Availbility Groups in order to achieve your goal. Another alternative is to use Failover Cluster Instances, which requires shared storage. StarWind VSAN free can be used as a shared storage, which will replicate data between the nodes, while Failover Cluster will handle the failover. The following guide will walk you through the configuration process: https://www.starwindsoftware.com/resource-library/starwind-virtual-san-installing-and-configuring-sql-server-2019-tp-failover-cluster-instance-on-windows-server-2016/

    Cheers,

    Alex Bykvoskyi

    StarWind Software

    Blog:   Twitter:   LinkedIn:  

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Sunday, August 9, 2020 11:15 AM
  • Hi Zoe.Ohara,

    Is the reply helpful?

    BR,

    Mia


    If the response helped, do "Accept Answer" and upvote it.--Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 10, 2020 3:07 AM