SQL Server AlwaysOn Availability Groups Feature

The above mentioned feature have been out in the market since the launch of SQL Server 2012. There is one thing that I like about this feature is that it allows me to have a group of SQL Server hooked out replicating the data continuously without having Shared Storage in the Windows Server Failover Clustering mode.

AlwaysOn-Availability-01

AlwaysOn-Availability-02

I spent just yesterday getting two of the SQL Server up and running; configured with AlwaysOn Availability Group. The whole configuration process isn’t complex at all and I felt this is much safer when the “Primary” database goes offline or it is being intentionally pulled out of the availability group for maintenance.

Notice in the second picture, I have my newly created database “NerdReservation” in the “Synchronizing” mode, similar to the Replication that we used to deploy in SQL Server 2008 without the Failover Cluster feature installed. In the AlwaysOn High Availability, we are always to see all the Replicas that is currently connected to the main database server or the parent server.

This really made things so much neater now.

For the installation process, the Canadians IT Pros did a good write-up here.

For the overview of the AlwaysOn Availability Groups, here is a good one.

Cheers.

Regards,
Milton Goh

Using SQL Data Sync to Synchronize On-Premises Database to Windows Azure

Have you ever thought of scaling your Web Application so that it can extend the usage to external parties or have you ever hit in any situation where you have some subset of your data in your on-premises SQL Server that you require it for other system that are external facing?

Let’s take for example, if you have an intranet that is hosted within your own corporate network which store membership details that you all are keeping it for internal usage all along. Then on one fine day you all decide to explore the Cloud by putting your custom developed newsletter blasting system to the Cloud as you would prefer not to choked up the bandwidth in your corporate network.

Therefore, how in this world could we have the best of the both world? How can we maintain a set of information but can be used in multiple systems in multiple locations but still maintain the integrity and consistency without any duplication within the dataset itself?

SQL Data Sync Agent is here to save the woes of people who are facing such issue. Currently, the SQL Data Sync Agent is in the Preview stage and has not reached the General Availability stage therefore things may change but I anticipate that things wouldn’t be that far off as the current tool is really designed and developed to be simple and user friendly. More information on TechNet.

Let’s take the following as an example.

01

I have this 3 tables in one of my Web Application that is hosted in my corporate data centre which is hosted within the network and no external interface to the internet. Right now, I need to surface the membership table to one of the newly created database in Windows Azure so that the other application that is hosted on Windows Azure will be able to access these information.

Creation of Windows Azure SQL Database

Let’s navigate to Windows Azure Management Portal then select SQL Databases via the left hand corner navigation bar. If this Azure account is newly created, then you probably wouldn’t have any database created. Click on Create a SQL Database to continue.

03

Key in the necessary information to continue the setup of the SQL Database on Windows Azure. If you do not have any SQL Database instance setup before, you will likely not have any SQL Server setup. Therefore, go ahead and select “New SQL Database Server”.

04

Continue to define the SQL database server settings by entering your preferred username and password. Please note that this username and password is very important because it is the username you will be using to log on to the SQL Server Management Portal on Windows Azure Management Portal. Do note that you will need to ensure that you check that checkbox if you want other of your Windows Azure Services to access this database. An example will be, if you decided to host your Web Application to host onto one of the Virtual Machine while utilizing the Database hosted on Windows Azure SQL Database rather than loading and managing your own SQL Server instance on Virtual Machine.

05

Go ahead and click on the “Tick” to proceed to create your SQL Database. The creation process should take less than one minute and you will know it is done when the database status is flag as “Online”.

06

 

Installation of SQL Data Sync Agent (Preview) and Configuration of Sync Agent

Go ahead and download the SQL Data Sync Agent if you have not done so and install on any of the computer within your corporate network which have access to the internal SQL Server.

In my example, I will just install the SQL Data Sync Agent onto the SQL Server itself but it should be the same even if the two program seats on separate client. After installation, launch Microsoft SQL Data Sync Agent Preview found in the start menu as shown.

02 

You will be welcome by the simple and neat interface. You will notice that most of the controls are being gray out. What you are left with is to enter the Agent Key so that Windows Azure can communicate with your client that host the SQL Data Sync program.

07

Upon clicking on the Submit Agent Key Configuration, a popup will appear as shown below. However, on first try, I have no idea what to key in. There after, I caught this message that I have highlighted. The key that is require can be retrieved from the SQL Data Sync web portal.

08

Navigate back to the Windows Azure Management Portal, navigate to the SQL Databases and look at the bottom. You will notice there are other navigational items available. Click on Add Sync which will show two more options available. Select New Sync Agent.

09

Give a name to the Sync Agent. Notice at the top, it actually prompt you that you will need your local client agent to be installed.

10

Click on the “Tick” to proceed and this Sync Agent will be created. Notice the “Status” is currently flag as Offline because we have not configure the local agent to communicate with the one at Windows Azure.

11

Our ultimate purpose is to get the key so that we can input into the configuration popup window at the local agent. So we click on the arrow at the Sync Agent to go into the detail page. Notice that at the bottom, there is this option “Manage Key” that is available. Click on it.

12

Instructions is available at the top. Just click on the “Generate” button to generate the access key and click on the “Copy to Clipboard” button just beside the Generate button.

13

When you are done, navigate back to the client that you install the local sync agent. Input the access key into the popup window.

14

Once the key is validated to be valid, the other controls will be enabled. Click on “Register” to register the SQL Database in your organization.

15

Enter the SQL Server details along with the targeted database that you want the Sync to take upon. Click on “Test Connection” to ensure that the connection can be established. Once verified, click “Save”.

16

Now, you will notice that the SQL Database is being added to the list.

17

In order to do a verification, click on “Ping Sync Service” to determine if the connection back to the Sync Agent at Windows Azure is reachable. Next navigate to the Windows Azure Management Portal. Right now, you should see the Sync Agent status will switch to “Online”.

18

Click the arrow beside the Sync Agent to view the details inside. You should see the database and the SQL Server hostname.

19

 

Creation of Sync Group and Objects

Go back to the SQL Databases main menu and as usual click on “Add Sync” and select “New Sync Group” this time.

09

Enter the details and select your desire region.

20

Click the arrow to proceed and select the Hub Database and key in your desire credentials. Notice that there is this “Conflict Resolution”. What it actually does is in times of conflict, whose data should take the precedent. So it depends on which is your master database. If new information is mainly populate through the database server located within your organization, then select “Client Wins” else select “Hub Wins”.

21

Click on the arrow to proceed. Now you will be prompt to select the referencing database. This time round, Windows Azure will need to know which database its needs to communicate with. In this case, it will be the database in the organization.

22

Then select the Sync Direction on which how you want data to flow. Do you want the Windows Azure SQL Database to always read from the SQL Database in the Corporate Network or you may have changes that is always made on both end and you want synchronization between the two location?

23

After you click on the “Tick”, it will go on to proceed the creation of the Sync Group. Then you will notice that the status will be shown as “Not Ready”. Fret not, it just means that you have not decide what to sync between the two SQL Database.

24

Click on the Sync Group to view the details. You will notice the following. Select Sync Rules.

25

You will be welcome with this page, by right you shouldn’t have any Sync Rules being added yet. Click on “Define Sync Rules”.

26

Select one of the database. In this case, I select my SQL Server database that is located within the organization.

27

You should see all the tables that you have that resemble the database back in the organization.

28

Select the columns that you want to be Synchronized. For my case, I want the whole “Membership” table to be synchronize between the two data sources. Therefore, I check all the columns as shown in the table. After making the selection, click “Save” to commit your selection.

29

Next, click on “Configure” on the top navigation and we will proceed with configuring the Automatic Synchronization of the data between the sources.

30

Click on “ON” and define the Sync Frequency. Make sure to click on “Save” to commit the changes.

31

Proceed to click on “Sync” to do a force synchronization between the two data sources so that we can see some results.

Head back to the SQL Database on Windows Azure and click into the Database as shown. Click on “Set up Windows Azure firewall rules for this IP address” so that you will be able to connect into the management portal to manage the database. One of the magic charm bar will appear at the bottom with your IP address as the detail, just click Yes to proceed.

32

Click on “Design your SQL database” to launch the SQL Database Management Portal. You will be prompted with the login screen to provide your credentials.

33

Upon successful login, you should see the tables in your Windows Azure SQL Database. See the “Membership” table that was synchronized over.

34

Let’s do some test by running query against this database.

35

There, I have all the data synchronized over! This tool is really very useful and in any case, I could easily synchronized data from my on-premises database and treat the Windows Azure SQL Database as a passive node.

Cheers.

Milton Goh

Installation Process and Preview of SQL Server 2014 CTP 1

I am really excited that today Microsoft has released the line of updated products such as,-

  1. SQL Server 2014 CTP 1
  2. System Center 2012 R2
  3. Windows Server 2012 R2

Times like this really kept me excited and busy trying out the new release and hopefully without much interference from my job, I will be able to meddle and understand the new features in the upcoming releases. For every product release, there bound to have installation guide that will be floating around and I hope to the first few to share with you my experience of installing this preview products. 🙂

Here goes… While downloading the media from TechNet, I have already prepare one of the virtual machine that is loaded with Windows Server 2012 Standard Edition.

1) Insert the media via Hyper-V console and the ISO will auto run (if it doesn’t, click on the disc drive at “My Computer“)

01_Starting_Installation_of_SQLServer2014_CTP1

2) This menu page looks rather similar to the one of SQL Server 2012.

02_Starting_Installation_of_SQLServer2014_CTP1

3) Click on “Installation” and in my case since this is will be a stand-alone database server, I always select the first option “New SQL Server stand-alone installation or add features to an existing installation.

03_Starting_Installation_of_SQLServer2014_CTP1

4) The “Setup Support Rules” will appear, just take things as default. Click “OK“.

04_Installation_Setup_Support_Rules

5) If you have a product key, enter the product key and the installer will determine what version of SQL Server you are entitled to, for the demo purposes, I am selecting “Evaluation” version.

05_Enter_Product_Key

6) In the “License Terms” page, you may just want to check the checkbox “I accept the license terms” and continue with the installation unless you do not comply with the agreement terms.

06_License_Terms

7) The installer will continue to install the Setup Files on the system. One feature I really like since version 2012 is that it will bundle in new product updates available during the installation rather than patching it manually later on after installation.

07_Install_Setup_Files

8) Once “Setup Support Rules” has completed, review the results of the rules to see if there is any show-stopper. For my case, Windows Firewall isn’t a show-stopper even though there is “Warning” sign because it just means that even if you installed SQL Server, users may not be able to access this server as the required ports are not opened.

08_Setup_Support_Rules

9) Select the type of role you want this database server to be configured, for all demo machine, I tends to choose “All Features with Defaults” so that I can have a better understanding of what is being changed.

09_Setup_Role

10) Leaving this as default as I wish to install all features available.

10_Feature_Selection

11) Installation rules completed to verify that the necessary files and settings are correct before the configuration starts.

11_Installation_Rules

12) Instance Configuration – Configure the Instance ID and defining the root directory. All along, I always make sure I do not have any SQL related stuffs sitting on the same drive as my OS. Therefore, I will do some modification as shown in the 2nd image below.

12_Instance_Configuration

 

13_Instance_Configuration_MyPractices

13) Review the disk summary

14_DiskSpace_Requirements

14) Defining the Service Accounts – As shown in the 2nd image, I always have a set of practices that I align with for all my deployments.

15_Server_Configuration

16_Server_Configuration_Accounts

15) Database Engine Configuration – This is the part to select whether you will need what type of Authentication Mode and specifying the SQL Server Administrator (You need not define all the Administrator at one go, you may do so later on using SQL Server Management Studio and assigning the various roles to the users)

17_Database_Engine_Configuration

18_Database_Engine_Configuration

16) Analysis Services Configuration – Leave the default settings and add the right administrator to it.

19_Analysis_Services_Configuration

17) Reporting Services Configuration – The standard menu where it gives you two options to select from,- I usually goes for the first one to let SQL Server Installer do the job for configuring the Reporting Services for me.

20_Reporting_Services_Configuration

18) Distributed Replay Controller – Assign the respective Administrator

21_DRC_Configuration

19) Distributed Replace Client – Define the name of the Controller Name

22_DRC_Configuration_Specify_ControllerName

20) Error Reporting – Somehow, I feel that this option should only be provided upon meeting any errors and this page should be left out during installation. (Just my opinion)

23_Error_Reporting

21) Installation Configuration Rules

24_Installation_Configuration_Rules

22) Summary of the configuration settings

25_Ready_To_Install

23) Installation Progress – There we go…

26_Installation_Progress

24) Installation Completed

27_Installation_Complete

28_Installation_Complete

25) Verification of the SQL Server version

29_Start_Menu

30_Start_Program

31_SSMS

32_Verification_of_Version

 

All in all, I feel that this release of the SQL Server has not changed much in terms of the ecstatic which is good. It just means that Microsoft is really accepting opinion from the grounds and putting in more useful features that people are requesting for! For more information of what’s new in SQL Server 2014, check out of one of the article that I personally like here. In my next few posts on SQL Server 2014, I will be experimenting out and write out the experience that I have with the “Cloud“! 🙂

Until then…

Regards,
Milton Goh

[Review] Red-Gate SQL Data Generator

Today when I was preparing one of my Microsoft SQL Server Virtual Machine for one of the Proof-of-Concept (POC) for one of our client, it came to my attention that I needed some test data so that it can facilitate in my POC. My whole POC is about generating beautiful graphs using tool-kits from Infragistics. Therefore, in order to make things look more realistic, I would need lots of data that will be eventually pumped into the UltraChart elements to populate my various graphs such as Bar Chart or Polar Chart.

So what are some of the quick and dirty ways to get test data loaded into the database?

I went to search online and found out solutions from Free and Not-So-User-Friendly ones and those that are Paid and Comprehensive ones and also Paid and Not-That-Comprehensive-Ones. So which would you get?

As previously I have been exposed to the whole suite of tools from Red-Gate known as the SQL Tool Belt (I may not have gotten the name correctly!). Thus, I head over to the Red-Gate website and took a look at what is their offerings! I downloaded the installer and installed not only the SQL Data Generator but also the other products along with the suite so that I can further perform testing. Here is how easy and comprehensive  Red-Gate SQL Data Generator is!

01_SQLDataGenerator_2_StartWindow

 

-> After installation, it will appear in the Start Menu (unless otherwise you have chose not to add it into the Start Menu). Launch the program.

02_Start_SQLDataGenerator_2

 

-> Notice the trial period stated, ignoring message for now since I will probably kill this Virtual Machine that I am using after 14 days. 😉

03_Configuring_Database_Connectivity

04_Configuring_Database_Connectivity_SelectDb

 

-> Select the target database server and database.

05_List_Database_Properties

 

-> Notice how much details is being shown! It’s so clear-cut and easy to use!

06_Modify_Database_Properties

 

-> Modify each database table column by simply clicking on the column that you intend to make changes and make the necessary changes using the available settings feature on the top. These settings simply are those that set the boundary of the data to be inserted. 

07_Set_ProductPrice_ToBe_1

 

-> Take for example, I am changing the minimum value for the “ProductPrice“. I am taking the assumption that my product would at least cost a dollar therefore putting 1 as the bottom-line is better in case the generator generates zero value into the field.

08_Set_ProductStock_0

 

-> Same goes here, the value that is being pre-set is the upper limit of the data type being assigned to each column, however to be realistic, we know that I probably wouldn’t have stock level of more than 2 millions quantity thus I am limiting it down the upper limit.

09_Ready_To_Populate_Data

 

-> When all the settings have been manipulated and reviewed, then hit on the “Generate Data” button located on the top menu bar to get things going.

10_Populate_Data_Report

 

-> One thing about tools that are payable, it will make life easy for you until the level it produces report on the data insertion although it is just test data.

11_Verify_Data

 

-> Lastly, we will verify the data being added via SQL Server Management Studio (SSMS). 

Of course, other than this paid tool from Red-Gate, there are others methods such as importing CSV into the database, however that way is much more manual and require more effort in manipulating the data to be inserted. So , to all my friends out there, if you need to do lots of POC or wanting to setup an environment with some pretty realistic data, please check out Red-Gate tools!