Sample Application to test database mirroring failover

Here's the source for a tiny C# app I wrote to test database mirroring failover at the application level, including a sample database mirroring connection string. It assumes that you have already set up a mirroring partnership. It doesn't matter whether the partnership includes a witness or has auto or manual failover.

I'll let you create your web or winform project as you see fit, and therefore have only included the actual C# code from the button click event, but basically you need a form with 3 controls:

button1 - a button

textBox1 - a multiline text box

testBox2 - a text box

In addition to this you need to create a stored procedure in your mirrored database called 'dbmtest' and get it to fetch something that can be converted to a string. The code from my procedure is also shown at the end, but it's not exactly rocket science!

Here's a quick summary of what the event does:

1. When you click the button, it attempts to connect to the server supplied in the connection string and call a stored procedure to get some data.

2. If it gets the data, it displays it. If it gets a SQL Exception error it displays this.

3. it attempts to display in a separate text box which server it is connected to, using the SqlConnection.DataSource property.

4. If you failover your mirror you will receive an error when you click the button

5. Then re-try by clicking again and you will connect to the other server automatically.

6. If any other general errors are caught it shows them in the text box.

Obviously in a real world situation you would make the re-try method somewhat more graceful, but this serves my purposes :-) It's nice sometimes to stick breakpoints in and see what is happening at what stages across the components.

Here's the code:

private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection dbmconn = new SqlConnection();
                dbmconn.ConnectionString = "server=server1;initial catalog = mymirrordb;failover partner=server2;user=domain\\user;trusted_connection = true";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = dbmconn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "dbmtest";

                try
                {
                    SqlDataReader dbreader;
                    dbmconn.Open();
                    dbreader = cmd.ExecuteReader();

                    while (dbreader.Read())
                    {
                        textBox1.Text = "Data fetched = " + dbreader[0].ToString();
                        textBox2.Text = dbmconn.DataSource.ToString();
                    }
                }
                catch (SqlException sqlex)
                {
                    textBox1.Text = sqlex.Message.ToString();
                }
                finally
                {
                    dbmconn.Close();
                }
            }
            catch (Exception ex)
            {
                textBox1.Text = ex.Message.ToString();
            }
        }

Stored Procedure Code

create procedure dbmtest

as

select top 1 blog_data
from blog

Comments

• Anonymous
  January 14, 2011
  I am not a developer, but a junior DBA trying to test mirrored SQL setup.  How do I get this app created so that I may test to see which server is actually returning the data?