locked
SPs over multiple mirrored databases RRS feed

  • Question

  • Hello,

    I got two servers: S1, and S2;they have two mirrored databases DataBase_A, and DataBase_B. DataBase_A has various SPs that access DataBase_B.

    Now, when both databases fails everything works fine. But if only one of the databases fails (for example DataBase_A on S1 fails and  DataBase_A on S2 becomes 'principal' while DataBase_B on S2 is still 'mirror') the SPs fail when they try to access DataBase_B.

    How can that case be controlled? I guess I could query from the SP the status of the database to check if it is mirror or principal, connect to a server or the other, but that would force me to hard-code the server inside the SP, right? (or transform the calls to dynamic queries).

    There is a less hacky way to solve that problem? In C# clients you can modify the connection string to include the 'Failover Partner' to automatically use the right server. But I do not find a similarly transparent, and elegant way to deal with the issue when the business logic is programmed in SQL Stored procedures.

    Thanks for your help.

    Friday, February 2, 2018 3:58 PM

Answers

  • Yep, that is one great limitations of the Mirroring that was addressed in Availability Group  feature introduced in SQL Server 2012

    A database mirroring session is always in one of several states that indicate the current activity in the session and the status of the connections between the server instances. When conditions change, the internal state of the session changes.

    https://technet.microsoft.com/en-us/library/cc966392.aspx?f=255&MSPPError=-2147217396


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by yukiarkh Tuesday, February 6, 2018 8:31 AM
    Sunday, February 4, 2018 8:31 AM

All replies

  • Yep, that is one great limitations of the Mirroring that was addressed in Availability Group  feature introduced in SQL Server 2012

    A database mirroring session is always in one of several states that indicate the current activity in the session and the status of the connections between the server instances. When conditions change, the internal state of the session changes.

    https://technet.microsoft.com/en-us/library/cc966392.aspx?f=255&MSPPError=-2147217396


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by yukiarkh Tuesday, February 6, 2018 8:31 AM
    Sunday, February 4, 2018 8:31 AM
  • Thanks Uri Dimant.

    Uhm. I looked into Availability Groups but it looks like they require a Domain, while the customer I am working with uses Workgroup, too bad because it seem an interesting alternative.

    So I guess the best option is to work with the Alert events you link in your answer, and see if I can manually switch the databases when one of them changes state.

    Tuesday, February 6, 2018 8:31 AM