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.

[quoted text, click to view]

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;


--

Hello,

As Hugo mentioned, you could get information of database mirroring from
system management views.

Client applications shall be aware of database mirroring so that they could
work properly to SQL Server. Actually client side application shall use SQL
native client and specify failover partner in connection string. This means
that client application shall have method to users to configure their
failover partner and end server name. Please refer to the following article
for details:


http://msdn2.microsoft.com/en-us/library/ms366348(SQL.90).aspx

Usually it shall not be a work for client side application to monitor
witness server. SQL native client will try to connect to failover partner
once it detects conection fail on initial server. When witness cannnot
work, failover on server side does not work and client application cannot
connect to failover partner also. It is suggested that use a different
monitor program on server side to monitor witness server if it is
neccessary.

For those applications that is not aware of database mirroring, there shall
be a different program to monitor SQL connection and re-configure it to
differnt server when necessary.

If you'v further questions, please feel free to let's know. We look forward
to your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Is there no way to make calls to the SQL Native Client?

My problem is that there is a requirement to know the names of the machines
that makeup the database mirror, and to monitor their state from the client
application.
The number of clients is likely to be around 500.
I could write a stored procedure to extract the information I require from
the tables you have directed me to, and have the clients run that every 1-5
seconds.

But isn’t this information held and updated by the SQL Native Client anyway?

If so, would it not make more sense to get this information from the local
SQL Native Client, as opposed to having every client run a custom stored
procedure over the network?

Hello,

As for as I know, SQL native client does not monitor mirror status
consistenly, when it fails to connect to the primary server, it will try to
connect to partner so that it will be transparent for client applciation
after database is failed over.

There is no exposed interface of SQL native client to provide information
on the status of database mirroring connection.

http://msdn2.microsoft.com/en-us/library/ms366199.aspx
http://msdn2.microsoft.com/en-us/library/ms365783.aspx

If you do not want to monitor the status of the monitor witness server on
your side, you could just use SQL native client directly in your
appliation, and it shall handle client side connection issues transparently
for you. Client application shall encounter problem when both primiary and
secondary database/server fails. If you do want to monitor witness server
and the database mirroring, you shall write your own code to query server
views.

If anything is unclear or you have concerns about this, please feel free to
let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================