Archive for November, 2012

SQL Server 2012 Virtual Labs – Part I

November 12, 2012 Leave a comment

It’s probably a new concept, but I’ve only recently discovered it and got pretty excited – Virtual Labs. It allows you with no effort on your side on setting up your hardware environments, which for certain situation, when you want to practice HA (High Availability), such as failover cluster, mirroring or log shipping, could be quite effort –demanding and time-consuming task, not mentioning you actually have to have a powerful-enough hardware setup.

While very few people have multiple desktops or powerful laptops from so called “desktop replacement” segment, the total majority of us would go with setting up multiple virtual machines on the same computer, which means it has to have pretty good CPU and lots of free space on its hard disk, but what’s more important, crucially important for running multiple VMs on your local box – RAM: at least 8 but better 16 gigabytes or RAM. Not something you might have at the moment.

Here is exactly where Virtual Labs step in. You click on the link, wait for several minutes while the environment is being cooking for you and voilà – you got it. Fast, convenient and overall just niiiiiiicccccceeee!

Speaking of “fast”, – be prepared to have some patience working with Virtual Labs, because it is going to be laggy and jumpy, especially during any kind of configurations, new feature installations or, generally, any kind of activities involving system changes. Also, another important point to keep in mind: you might want to refrain from creating a table or a collection of tables of dozens of columns and populating them with dozens of thousands of records. It might, or I’d rather say almost surely would make your entire VM session hang almost instantaneously. It would be a pity to lose all your work and start all over again. Free Virtual Labs VMs are not intended, neither were supposed to be intended for any kind of scalable data-related tests, I guess. This is actually quite fare, if you think. So, stick to a table or few tables with just few columns and one – ten thousands of rows. It’ll do it for the test anyway.

Well, enough of intro. Let’s get started. In this demo I would do the AlwaysOn lab. In short, AlwaysOn is much enhanced combination of SQL Server Fail-Over Cluster (built on top of MS Cluster) with Mirroring. Very good HA (high availability) feature, IMHO. Another great advantage of this new AlwaysOn is the its configuration – very straight forward, easy and fast. It took away something like 80-90% of all the scripting and manually managing complexity, leaving with just pointing the installation and configuration wizards to the right direction. Very, very good, well-thought, well implemented, robust new SQL Server 2012 feature. Oh, and now, with this new SQL Server 2012 AlwaysOn, you can have multiple mirrored nodes, not just one, – they really combined all the HA concepts under one roof: Clustering , Mirroring and Log Shipping, taking the best traits of each one.

Here are few links to give you the detailed insight over new SQL Server 2012 AlwaysOn:

Light Intro by MS

BOL-kind of article

Great and fun to read article from Brent Ozar – “a-must-read”

And… apparently it’s not always as cloudless as it seems to be, – a “down-to-Earth” by, again, Brent Ozar:

(first you might want to just scroll down to “OUR CHALLENGES WITH AVAILABILITY GROUPS”, but after that I do encourage you to read the rest of the article – it gives a very good idea of how AlwaysOn simplifies and leverages HA compared to how it is in SQL Server 2008)

Now, that, I assume, you read all the above… Let’s move on with the process of configuring AlwaysOn Availability Group with MS SQL Server 2012 AlwaysOn Virtual Lab.

click on the second from top link (“AlwaysOn Availability Groups”) and let’s go



Maximize the windows and give it several minutes to cook



The rightmost panel is the exercise itself. You can follow it along with this post or just remove this pane by unpinning it, which will give you more screen space.

The main goal of this “exercise”, which I prefer to refer as demo is:

  • Configure Failover Cluster
  • Configure Availability Group
  • Configure secondary replica for read-only
  • Configure Availability Group Listener
  • Perform the failover

First step – connect to the first node: EMU-SQL1


And right away we’re getting


A quick, spiteful smile and moving further – Server Manager, Add Features


Check Failover Clustering


Next and Install




Repeat the above steps for the second and third nodes (EMU-SQL2 and EMU-SQL3)



Now let’s setup the windows failover cluster. First, we’ll need to run the configuration validation



Add, Next



The next screen has “Yes” option selected by default – this will run the cluster validation tests. You can leave it as is and run the tests or can skip it thus saving yourself several minutes of time. To skip the validation tests and move forward, select “No” option


Name the cluster and assign IP


Confirmation and Next




The cluster of three nodes is ready


Now, it’s part of SSMS to kick in.

Run SSMS and follow these steps:

Create database (FULL recovery mode, of course, as we’ll be mirroring it)

  1. Create sequence (new great SQL Server 2012 feature, picked up after Oracle [finally, what was taking MS so long?!])
  2. Create a test table
  3. Populate the test table with 10K records
  4. Create a second database and repeat steps 2 – 4

 1.  Create database




2. Unfortunately there is no way to copy/paste the code from into the virtual lab session, so all the typing has to be done in the terminal

Create the first database and the test table



Create the second database with the second table




This is what you should be getting in the end:



Now it’s time to reconfigure SQL Server service properties a little bit. Quit from SSMS and run SQL Server.





Contoso\administrator, password = pass@word1, hit “Apply” button (do not hit OK – not yet), Yes – to confirm restarting the instance (won’t happen right after, – you will have to restart it yourself later)




Now switch to AlwaysOn High Availability and check Enable AlwaysOn Availability Groups, and now hit OK





Now do the same for 2nd and 3rd nodes.