Post reply

Mirroring failover via T-SQL

  • January 23, 2009 at 1:47 pm

    #129824

    I have a mirrored database and I would like to create (or use, if they already exist) a set of stored procedures that "Front" the tasks in the Mirror properties dialog - (Pause, resume, failover).

    Several questions - do such procs already exist somewhere that I can tap into or will I have to create them myself, assuming that there are T-SQL statements that can be called to perform those actions? Where would they be and/or where should I create them? MSDB? Elsewhere?

    Can I create a database user that is NOT the SA user (or dbowner at all for my database) but has authority to perform those operations?

    I would like to build into my existing front-end "dashboard" application, the ability to call these functions and perform those operations, but I do not want to have to use SA to get those done.

    Any thoughts? Did I pose the question clearly enough?

    Thanks in advance for any help!

    Jim

  • January 23, 2009 at 5:22 pm

    #932100

    Pause:

    ALTER DATABASE your_db_name SET PARTNER SUSPEND

    GO

    Resume:

    ALTER DATABASE your_db_name SET PARTNER RESUME

    GO

    Failover:

    ALTER DATABASE your_db_name SET PARTNER FAILOVER

    GO

  • January 24, 2009 at 12:54 pm

    #932273

    Thanks for the syntax on those statements. If I built stored procedures for each of those statements, would I put them in MSDB?

    What permissions/role would a user have to be to be able to execute those statements? Anything special, or would they just need execute permission on those procs?

    Jim

  • January 26, 2009 at 10:08 am

    #932690

    Users would also need the "ALTER" permission for each mirrored db.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply