locked
SQL Server mirroring with SQL Browser service disabled RRS feed

  • Question

  • We have two servers (Server 2008 R2 x64 on ESX4) running SQL Server (2008 R2 64 Bit Enterprise) in a mirroring session.
    We have the SQL Browser service stopped due to our firewall not letting UDP 1434 through.

    When we configured mirroring we specified servername,port for both sides.  this is fine except SQL server resolves this to be servername\instance.
    This can be seen be selecting mirroring_partner_instance from sys.database_mirroring.

    When a client tries to connect it reads this value and not the value specified in the connection string using the port number

    As the Browser service is stopped no clients can connect using the instance name and so fail, defeating the point of having the high availability through DB Mirroring

    We can not update sys.database_mirroring due to "Ad hoc updates to system catalogs are not allowed."

    Reproduce

    • Install two instances of SQL Server as a SQL instance on SERVER\INSTANCEA and SERVER\INSTANCEB
    • Disable the SQL Browser service on completion or close the firewall for UDP 1434
    • Create a new database on InstanceA and create a mirrored copy on InstanceB specifying Server,Port number in the configuration
    • Once mirroring has been established run SELECT mirroring_partner_instance FROM sys.database_mirroring
    • Create an ODBC connection to the database specifying the failover partner again using Server,Port for both the primary and failover

    Results

    • SELECT mirroring_partner_instance FROM sys.database_mirroring returns SERVER\INSTANCE
    • ODBC connection is configured but gives the following warning
      WARNING: The 'mirror server' in the DSN does not match the server configuration. 'SERVER\INSTANCEB' will be used upon failover.
    • On failover no clients can connect to the mirror automatically.  A manual process to reverse the primary/failover in the connections string must be undertaken.

    MCDBA, MCAD, MCITP, MCTS

    Monday, September 24, 2012 1:47 AM

All replies

  • Hi,

    I saw the connect item which it looks like you posted. It will be good to see what MS come back with however I was under the impression that the SQL Browser was required for mirroring to function correctly.

    You could have a look at this post and try the alias workaround - 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


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, September 24, 2012 3:22 AM
  • yup that is my connect issue.

    I have been through that article you linked which has the same issues, but we have 130+ client machines making connections back in...an Alias on each is a PITA.

    The reason i am questioning it is in the mirroring documentation it states you can specify the connection string as server,port for both the primary and failover, but SQL then doesn't use this ODBC certainly doesn't, it looks at what is stored in the system tables, which is the resolved name...Really I just want to change value in the system table.

    We have also hit this problem with an MSX server and the utility server where SQL resolves the server name instead of using the port number as you specify...you can fix both of these though by running an update on the relevant MSDB system tables to set the server_name to be server,port.


    MCDBA, MCAD, MCITP, MCTS


    • Edited by graz.smith Monday, September 24, 2012 4:19 AM
    Monday, September 24, 2012 3:33 AM
  • Just thought I would add

    Is the information in this Microsoft SQL Server 2005 Security Best Practices - Operational and Administrative Tasks still pertinent on the newer version of SQL?

    From Page 6 - For a more secure configuration, always use static TCP/IP port assignments and disable the SQL Server Browser service.

    but looking at the SQL 2008 R2 Mirroring  configuration page it states DNS and SQL Browser must be running for failover...

    If needs must i will get the firewall opened...but it seems daft to me


    MCDBA, MCAD, MCITP, MCTS

    Monday, September 24, 2012 5:10 AM
  • Agree. Would be good to have MS chime in on this one however I have always had to have firewall rules configured to allow the SQL browser for mirroring but that does not mean you must do it this way.

    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, September 24, 2012 5:14 AM
  • Can you try configuring a ODBC on Client machine to use port number and when Client gets the mirror Server name it gets mapped to mirror Server with port number.

    For Example:(I have not done this myself but it may work)

    ServerA\InstanceA(port 51000) is primary and ServerB\InstanceB(port 51001) is Mirror.

    On Client use the mirror server name and primary server name in connection string.

    But create a ODBC ServerB\InstanceB on client machine with a port number inside it. If you have port number it should not rely on Browser Service.

    Hope this helps ..............Let me know if this works.


    Vamshi SQL DBA(MCITP)
    My Blogs:
    SQL Server Administration: SQL Adminstration
    SQL Development: SQL Development




    • Edited by Vamshi.rb Tuesday, September 25, 2012 4:39 PM
    Tuesday, September 25, 2012 4:30 PM
  • What you say is correct...

    But i don't want to crete an alias on every client...there are over 130.

    I want the configuration I have specified in the connection string (of primary and failover) to be used and not for SQL Server to ignore that and return the Failover Partner as it has found it(i.e. with the resolved instance name)...It seems daft to me that you can configure it using ports, and specify ports in the connection string for SQL to ignore that.


    MCDBA, MCAD, MCITP, MCTS

    Tuesday, September 25, 2012 11:19 PM