access mirrored database from procedures RRS feed

  • Question

  • I have created a mirrored db and it fails over properly. I have database procedures on the server on other databases that need to access objects within the mirrored database.

    When the database is on the same server, the access works fine.  When the database fails over
    the procedures crash with a "can't open database as it is acting as a mirror" message. This makes perfect sense. Obviously I need to define access to the mirror server and I would like the procedure to access the mirror seamlessly regardless of which server is the primary.

    Assume the procedure contains statements like "exec mirrordb.dbo.proc" or "select field from mirrordb.dbo.table"

    I have created a function which returns the server name of the active mirror server for a specified database. I do not wish to use dynamic sqls if possible. Duplicating the code via
    "if server_name=X then server1.mirrordb else server2.mirrordb" is a not desired solution.

    how do I cause it to check and access the correct server? what security issues are invovled as well

    Wednesday, February 4, 2009 3:20 PM

All replies

  • PS I have datqabases with compatability level 80 (sql2000) and 100 (sql2008). I would like to know my options for both levels of database
    Wednesday, February 4, 2009 3:51 PM
  • You can use synonyms to accomplish this.  You create a synonym, and when the database fails over, you have a script ready to drop the synonyms and recreate them, pointing to the new server.  You can read more about them here:

    Aaron Alton | thehobt.blogspot.com
    Friday, February 6, 2009 1:17 AM
  • I was hoping for something complete automatic and invisible to the users.
    this will also not work for sql2000 which doesn't have synonyms

    any other possibilities?
    Friday, February 6, 2009 10:04 AM