Hi,

I've been tasked with investigating different methods of achieving database
redundancy.

In doing so, I'm currently in the process of writing a test application to
prove that Database Mirroring in SQL Server 2005 is a suitable solution.

Of the established requirements I'm finding difficulties with the following:

    1.    Identifying the name/instance of SQL Server my application is
        currently connected to.
    2.    Identifying the name/instance of SQL Server cached as the
        failover partner, and confirming it state at any time.
    3.    Identifying the name/instance of SQL Server cached as the
        witness, and confirming it state at any time.
    4.    Forcing a failover via my test application.

I've already got some of this functionality via sp_dbmmonitorresults, but
this relies on the Server Agent running and also adds to the network traffic.
I was hoping for a solution using calls to the SQL Native Client.

I’m using SQL Server 2005 Enterprise Edition SP1.

My application is written in Visual C++ 6 as is the product this is intended
for.


Also, if anyone knows of any good links to web pages or forum topics with
regards to methods of implementing database retry/reconnect client-side when
SQL Server fails over, I would be grateful. I’ve implemented this
functionality already by deriving my own version of CDatabase, but I’m open
to new suggestions.

Thanks!

On Thu, 27 Jul 2006 05:43:03 -0700, GMouzourou wrote:

Show quoteHide quoteHi GMouzourou,

For the first three questions, have you looked at the system management
views? More precisely, the column mirroring_role_desc in sys.databases,
and all of the columns in sys.database_mirroring and
sys.database_mirroring_witness.

For the last question, you can manually force a failover with the
command
    ALTER DATABASE dbname SET PARTNER FAILOVER;
If you want to test automatic failover, try shutting down the server:
    SHUTDOWN WITH NOWAIT;

too long...
datetime between problem
SQL Join question - getting most recent value in a history table
Select problem Msg. 156
NEED HElP with Properly applying DISTINCT function to Count Aggregate.