Client-Side Database Mirroring Details

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 ope
n
to new suggestions.
Thanks!On Thu, 27 Jul 2006 05:43:03 -0700, GMouzourou wrote:

>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 traffi
c.
>I was hoping for a solution using calls to the SQL Native Client.
Hi 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;
Hugo Kornelis, SQL Server MVP