SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 6

In this last post of the AlwaysOn series I’m going to build a simple C# console application to demonstrate how a  business app can benifit from using an AlwaysOn enabled database.

In Visual Studio I’m going to create a Console Application project:

1To be flexible in changing connection strings and SQL queries I’m going to use an external XML config file – App.config. For that I need to include few additional namespaces into my Program.cs code:

App1

Do not forget to add a reference in the Reference Manager.
Now, open App.config file and create an appSettings tag with three keys: “Query”, “Query2” and “ConnectionString”:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <appSettings>
      <add key="Query" value="SELECT @@SERVERNAME, db_name()" />
      <add key="Query2" value="SELECT Name,LastName FROM [dbo].[First]" />
      <add key="ConnectionString" value="Server=ListenerZ;Database=Test;Integrated Security=SSPI;MultiSubnetFailover=True" />
    </appSettings>
</configuration>

Here: in the first query, I’m just querying server name and a database name [this is done to know which server is being hit]; In the second query I’m querying a table. And the final key is used to store a Connection string.

Switch back to the Program.cs file and enter the following code [comments are inline]:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Collections.Specialized;

namespace ConnectToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            //Creating a SQL connection
            string ConnectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();

            //Getting query string from the config file
            string Query = ConfigurationManager.AppSettings.Get("Query");

            //Executing a SQL query to get the Servername and the Database name
            SqlCommand SQLQuery = new SqlCommand(Query, conn);

            //Now reading the SQL query results and printing them in the application console
            SqlDataReader reader = SQLQuery.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("Server name: " + reader.GetString(0));
                Console.WriteLine("Databasse Name: " + reader.GetString(1));
                Console.WriteLine("");
            }
            reader.Close();

            //Now lets repeat a similar logic but this time by querying a table from the database
            string Query2 = ConfigurationManager.AppSettings.Get("Query2");
            SqlCommand SQLQuery2 = new SqlCommand(Query2, conn);
            SqlDataReader reader2 = SQLQuery2.ExecuteReader();
            while (reader2.Read())
            {
                Console.WriteLine("{0}, {1}", reader2.GetString(0), reader2.GetString(1));
            }

            reader2.Close();
            conn.Close();

            //To keep a console window opened while debugging this app
            if (Debugger.IsAttached)
            {
                Console.ReadLine();
            }
        }
    }
}

App2After building the project we should get these files created:

App3Here “ConnectToSQL.exe” is our console app and “ConnectToSQL.exe.config” is an editable config file.

OK, now it’s time to test. I will be executing my application on the WIN2012DC. Just to remind you how the diagram looks like:

AG15And the table in the Test database is populated with just few rows:

App6First lets do some simple test by querying a particular server directly. So I set my connection string to point to WIN2012R2-1:

App4And I get my data:

App5Now lets test the AlwaysOn Listener. I’m changing the connection string to use ListenerZ:

App7And run the app again:

App8Connection is succesful. “WIN2012R2-1” was hit because it’s a Primary server at the moment:

Now make a failover to another node:

App9And run the app again:

App10uHoo 🙂 as you can see the application automatically got redirected to the other node. How cool is that 🙂

OK people, I’m finishing this sequence of posts, hope it was interesting and useful 🙂

 

Advertisements

2 thoughts on “SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 6

  1. Pingback: SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 6 | jogendra@.net

Leave a Comment here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s