Monthly Archives: March 2014

SQL Server 2012 Availability Group Demonstration

Leave a reply

I’ve created a short video post for this blog entry to demonstrate how to:

  • Add a cluster node, with single PowerShell command, to an existing Windows Server 2012 Cluster
  • Enable SQL Server AlwaysOn Availability Groups (AG) in a SQL Server 2012 instance
  • Create a sample database and add some records for testing SQL Server Async-commit replica propagation
  • Demonstrated a custom tool to return the record count from primary and secondary replicas
  • Walked through the pre-requisites for enabling SQL Server AlwaysOn AG

Enjoy!

SQL statements to create and add sample records to the CoolCars database.


USE MASTER

CREATE DATABASE COOLCARS

USE COOLCARS

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CarList](
[ID] [int] NOT NULL,
[Manufacturer] [nvarchar](50) NOT NULL,
[Model] [nvarchar](15) NOT NULL,
[ModelYear] [nvarchar](4) NOT NULL,
[ExteriorColour] [nvarchar](15) NOT NULL,
[Miles] [int] NOT NULL,
[Transmission] [nvarchar](20) NOT NULL,
[Price] [money] NOT NULL,
[InteriorColour] [nvarchar](15) NOT NULL,
[Doors] [int] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Picture] [image] NULL,
CONSTRAINT [PrimaryKey_883d618c-922d-4695-98d4-c4e79c5871d7] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON)
)

GO

USE COOLCARS
BEGIN TRANSACTION MORECARS
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('1','Ferrari','F355','1999','Red','20000','Manual','56000','Black','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('2','Ferrari','F355','1995','Blue','22000','Manual','46000','Black','2','GTB')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('3','Ferrari','Enzo','2006','Black','2000','Paddle Shift','225000','Red','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('4','Ferrari','438','1992','Yellow','26000','Manual','35000','Black','2','TB')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('5','Ferrari','F355','1997','Yellow','40000','Manual','3000','Red','2','Berlinetta')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('6','Porsche','Carerra','2008','Silver','15000','Automatic','78000','Black','2','GT4')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('7','Ferrari','308','1985','Red','60000','Manual','15000','Black','2','GTO')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('8','Lamborghini','Diablo','1994','Blue','66000','Manual','120000','Black','2','Coupe')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('9','Aston Martin','DB9','2008','Black','10000','Automatic','5544','Grey','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('10','BMW','545','2010','Black','40000','Paddle Shift','76000','Grey','4','M')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('11','Ferrari','F355','1999','Red','20000','Manual','56000','Black','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('12','Ferrari','F355','1995','Blue','22000','Manual','46000','Black','2','GTB')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('13','Ferrari','Enzo','2006','Black','2000','Paddle Shift','225000','Red','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('14','Ferrari','438','1992','Yellow','26000','Manual','35000','Black','2','TB')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('15','Ferrari','F355','1997','Yellow','40000','Manual','3000','Red','2','Berlinetta')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('16','Porsche','Carerra','2008','Silver','15000','Automatic','78000','Black','2','GT4')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('17','Ferrari','308','1985','Red','60000','Manual','15000','Black','2','GTO')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('18','Lamborghini','Diablo','1994','Blue','66000','Manual','120000','Black','2','Coupe')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('19','Aston Martin','DB9','2008','Black','10000','Automatic','5544','Grey','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('20','BMW','545','2010','Black','40000','Paddle Shift','76000','Grey','4','M')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('21','Ferrari','4+4','2012','Red','30000','Paddle Shift','16000','Black','2','coupe')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('22','Ferrari','4+4','2012','Red','30000','Paddle Shift','16000','Black','2','coupe')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('23','Ferrari','F430','2007','Red','2342','Paddle Shift','98000','Black','2','Spider')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('24','BMW','545','2010','Black','40000','Paddle Shift','76000','Grey','4','M')
INSERT INTO dbo.CarList
(ID,Manufacturer,Model,ModelYear,ExteriorColour,Miles,Transmission,Price,InteriorColour,Doors,Type)
VALUES ('25','Ferrari','4+4','2012','Red','30000','Paddle Shift','16000','Black','2','coupe')
COMMIT TRANSACTION MORECARS

C# code for the sample Windows Forms application to check the record count in the primary and secondary SQL instances.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CoolCarsSQLDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnGetPrimarySQLCount_Click(object sender, EventArgs e)
        {
            try
            {
                lblClusterCount.Text = getRecordCountfromSQLServer("<SOURCESQLSERVER>", "CoolCars", "CarList");
            }
            catch { }
        }

        private void btnGetDRSQLCount_Click(object sender, EventArgs e)
        {
            try
            {
                lblDRCount.Text = getRecordCountfromSQLServer("<DRSQLSERVER>", "CoolCars", "CarList");
            }
            catch { }
        }

        public string getRecordCountfromSQLServer(string SQLServerName, string DataBase, string TableName)
        {
            string result = "";
            SqlConnection DBConnection = new SqlConnection("Server=" + SQLServerName + ";Database=" + DataBase + ";Trusted_Connection=True;");
            try
            {
                SqlCommand myCommand = new SqlCommand("SELECT Count(*) from " + DataBase + ".dbo." + TableName, DBConnection);
                DBConnection.Open();
                var count = myCommand.ExecuteScalar().ToString();
                DBConnection.Close();
                result = count;
            }
            catch (Exception ex)
            {
                var exception = ex.ToString();
                string error = "Error Occurred";
                return error;
            }
            return result;
        }
    }
}
Advertisement