Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirroring failover via T-SQL Expand / Collapse
Author
Message
Posted Friday, January 23, 2009 1:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 2:07 PM
Points: 174, Visits: 73
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
Post #642759
Posted Friday, January 23, 2009 5:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,748, Visits: 2,957
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
Post #642849
Posted Saturday, January 24, 2009 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 2:07 PM
Points: 174, Visits: 73
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
Post #643039
Posted Monday, January 26, 2009 10:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 2,748, Visits: 2,957
Users would also need the "ALTER" permission for each mirrored db.
Post #643550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse