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:


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" ?>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      <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" />

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);

            //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));

            //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));


            //To keep a console window opened while debugging this app
            if (Debugger.IsAttached)

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 🙂



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