SQL Server with Cluster Shared volumes (CSV) – Part 2

In the previous part of this series, we have seen what a cluster shared volume is and what are the advantages and other considerations to keep in mind when deploying CSVs for SQL Server workloads. In this article, I will walk though actual installation of a failover cluster Instance leveraging CSVs.

To begin with, I will walk you through my cluster setup from 20,000 foot view. I created two brand new VMs running windows server 2012 R2 and renamed them accordingly. Nothing special w.r.t disk drives at this point, Just basic VMs with a system drive(C$).

I also created 2 virtual networks in my VMWare workstation which I will be using for configuring my Public and private NICs on my nodes.

On my first node: is the IPv4 address of my DNS server and below are my NIC settings.

Once IP address has been configured, below is how I joined my node to domain (sqltrek.local in my case).

Further reading: Deploy SQL with CSVs – Part 2

Awesome Task Manager of Windows Server 2012.

Let me ask you this, how many of you as Server Admins/Database Admins used Task Manager of Windows in the past? I bet, all of you! Well, how many of you thought “Aah, It gives some useful information, but not everything”. I fall in the second category. It’s a very great tool for understanding about your server(but, I would say from 20,000 foot distance). Wouldn’t it be great if it really shows you how many Physical Sockets your server have, how many CPU’s(not logical), hyperthreading,what is the Rated Speed and what’s the actual current Speed your CPU’s are currently running? Yes, that’s what I’m talking about. You can get all this information at a single glance now on Windows Server 2012/Windows 8 :). Let me show you Windows Server 2008R2 Task Manager first.

Don’t get surprised looking at my Single CPU 🙂 It’s my DC, a Virtual Machine in my lab.

Anyways, are you able to see anything which I mentioned earlier? Nope. Basically we have to rely on some third party tool(I prefer CPU-Z for a high level internal details).

As you can see below, CPU-Z tool is giving all the cool information which I’m looking for. It’s pretty awesome tool to have in your toolkit if you are not already aware of.

Now see below for Windows Server 2012 Task Manager. I logged onto another Virtual Machine which has Windows Server 2012 Eval Edition.

As you can see It’s giving Rated Speed by manufacturer, current Speed, maximum Speed, Number of sockets, Processors, Type of machine and even CPU Cache details(You’ll see L1,L2 and L3 cache details on real Servers).

Isn’t it Nifty? I would say yes, It’s pretty useful in many cases!

Well, Let me ask you one Interesting Question. Do you think Task Manager on Win Server 2012 is a good tool to Understand your hardware of underlying Host for your Virtual machines? If you think “Yes”, It’s a Bummer!! See below Screenshot for the Task manager from the actual Host hosting those 2 above shown VM’s.

  • Rated CPU Speed by Intel – 2.5 GHz, but the actual Speed right at that moment when I took screenshot was 3.03 GHz, which Implies my CPU(s) have TurboBoost Enabled 🙂
  • One Physical Socket, 2 Cores but 4 Logical Processors, which Implies I’ve Hyper Threading Enabled.
  • Virtualization Enabled, implies Intel VTx technology is enabled at BIOS.
  • You can also see all the three CPU caches available.

So, point to be noted is Task Manager and few other standard tools are not enough to dig into hidden details of VM Host. Do not rely on Task Manager for VM’s! Virtualization is a beast on it’s own which acts as a perfect Abstraction Layer. But for Physical Boxes, Task manager 2012 works like a champ and does it’s job pretty good compared to previous Versions of Windows as you can see…….

Error While enabling Windows Feature : NteFx3, Error Code : -2146498298″ while Enabling OS Feature ‘NetFx3

In this Blog Post, let us see how to fix this Weird error message you might get while Installing SQL Server 2012 on top of Windows Server 2012. I already mentioned about this in the post where I showed how to Install SQL Server 2012 on Win Server 2012, but wanted to come up with a separate post just for this Error because it’s so annoying and frustrating when you see this. Trust me guys…This Error needs special Attention 🙂

Okay, Let’s say you are all ready for deploying SQL Server 2012 on top of Windows Server 2012 as your boss is pushing you towards SQL Server 2012(Which is awesome :)). You started your Installation and somewhere in the middle of your Installation Process, you get this annoying message that Installation Failed. Well, you are on Windows server 2012 which comes with brand new .Net Framework and you are not expecting any framework Issues(Initial setup Checklist gives no Errors on this). So what is it missing? The Answer is  .NET 3.51 Payload“. Yes, we must manually enable .NET 3.51 Payload in Windows Server 2012.

Look at the below Screenshot carefully at the Bottom left where it is asking for .Net Framework 3.5 to be enabled.

So, now you know why we need to pay attention while Installing SQL Server 😀

Anyways…..For this you need to have Windows media handy or should be connected to Internet, OR you can enable this automatically by enabling Remote Management which you might not do on your Production Boxes.

So, How to Fix this Annoying thing? Yes, you are correct if you said PowerShell (Few of you might thought about DISM or Just GUI for adding this feature as you used to do in Win Server 2008R2….YMMV!)

Note: Even the Error Message says, go and enable this feature using Server Manager GUI, it might fail!!!….Avoid GUI for fixing this.  Instead use awesome PowerShell guys. I’ll show you powershell way of doing this…

1. Insert your Windows Media in your DVD Drive(in My case it’s D:)

2. Open Powershell as Admin and type  “Install-WindowsFeature Net-Framework-Core -Source D:\Sources\sxs” without double Quotes and hit enter.

Look for Exit Code “Success”! That’s it Guys…The Rest should be Flawless!

Setting up SQL Server 2012 AlwaysOn Availability Groups – Part3

In the previous Post(part-2) we have seen prerequisites for Installing SQL Server Instances participating in an AG. In this post, let’s see how to configure an actual AG…Am alllllllll excited 🙂

Before proceeding any further, Few points to be noted:

1.I’ve created a share on Node “SreeSQLA” which all the 3 Instances need visibility to.(Hence added SQL Service account(s) with Read/Write access).

2.All the Databases participating in AG should be in Full Recovery Model and a Full backup should be taken(Same as DB Mirroring Requirements we had). Well, the whole concept of AG’s was built on top of DB Mirroring. Same EndPoints concept is used here, In fact it uses the same TCP Ports for endpoints which Mirroring uses by default.

3.We have to enable “AlwaysOn Avaiability” option on all the Instances in SQL Server configuration Manager(Service Restart is required).

Here is the Snapshot of my Cluster manager before I enable AlwaysOn on SQL Instances…(Just keep this in mind that there are no Roles for now)

Now, Where can I enable AlwaysOn Availability?

Open your SQL Server Config Manager and go to Services and select SQL Server Properties and you can see a new tab as shown below.

As you can see it automatically detected my Windows Cluster name! All you’ve to do is Enable, Apply, Okay and Restart SQL Services.(Also make sure you enabled TCP/IP  under your network Protocols)

We are all set to begin our Database(s) part now. For my lab am creating 3 User Databases as “Sales1,2 and 3”. (As I mentioned earlier make sure to use Full Recovery Model and Take a Full backup for the first time and I will place it in my share which I created, Note- You can manually sync databases Initially as you did for DB Mirroring)

In my Lab, SreeSQLA is my primary(prod) Instance and SreeSQLB is sitting in the same datacenter(for HA), where I’ll be using Synchronous Mode and assuming SreeSQLB is my DR Site and I’ll be using Asynchronous Mode. (So, I have 3 Replicas in total…Primary replica at my Production Site and 2 Secondary Replicas at SreeSQLB and SreeSQLDR).

Step1: I created 3 Databases on my Production Server(SreeSQLA) and placed full backups on my share.

Step2: Navigate to AlwaysOn High Availability Node in your Object Explorer and select “New Availability Group Wizard” as shown below.

Step3: Name your AG.

Step4: Select your Databases you want in your AG. (You can see below where it says all the Prerequisites are met 🙂 )

Step5:  This is Interesting, where you’ve to select all your replicas, Backup preferences, Create End Points and Create/Assign an IP for your Listener.

Adding Replicas – I Clicked on Add Replica Button and connected to SreeSQLB and SreeSQLDR and chosen Primary and Secondary roles as per my requirement as shown below.

EndPoints: I left it to defaults. ( Make sure your Firewall is not blocking this port)

Backup Preferences: I selected to allow Backups only on My Secondary replicas and I avoided SreeSQLB as shown below. ( You can select as you wish depending on your requirement)

Listener: This is the one which clients connect to and which floats across machines. As you can see I specified a Name, Port and IP for this Listener. Note – Each AG can have only one Listener. Recommended not to use DHCP for IP in your Prod Environments. We’ll be using different subnet for our DR site in real world, for that all you have to do is, add another Static IP from your DR Server Subnet. – Starting SQL Server 2012 we’ve OR Dependency for your IP’s! ( Well, you may need to work with your Network Team for this for all your Static IPs mumbo jumbo)

Now..It’s time to select how you want to initiate Data Synchronization. I’ll provide my Share and let wizard to take care of it as needed, since my database are tiny!

Click Next and it validates our configuration and I got all Green Check Marks as you can see below..Hurray 🙂

All you are left with now is clicking Next and Finish and keep your fingers crossed as I did for this setup(This is my very First AG I ever setup…Very exciting stuff! )

After few seconds I got success message and now Object Explorer on my Production Instance Looks like this.

Huhuuuuuuuuuu….I Did it! See, it’s not that difficult. Now it’s time to understand all the nuts and bolts of this awesome technology.

Before concluding, let me tell you this…From now on, your clients/Applications should connect to Listener Name(not the SQL Instance Name). See below for what it looked like when I connected using Listener Name.

Also, take a look now how my Failover Cluster manager looks like.

It created a Role for the Listener which we created via SSMS and NodeA is the current owner(Principal) of it. Perfect…Everything looks as expected 🙂

Have fun exploring AG guys..Make your self very familiar with this technology, you never know when your boss might say “Okay, Now it’s time to move on to SQL Server 2012”

Setting up SQL Server 2012 AlwaysOn Availability Groups – Part2

In the Previous Post, I explained how I created my 3 node Windows Cluster. Now, In this post let me show you Installing SQL Server 2012 on Windows Server 2012 for our AlwaysOn Setup.

As I already mentioned in previous post, we will be Installing 3 individual standalone SQL Instances(I’ll go with Just Default Instances to keep it simple) on three nodes participating in our Windows Cluster. FYI, I’ll be using the same Service account for all the three Instances.

Note: For Availability Groups, you should use the same service account on all the Instances participating, if you are using Kerberos Authentication. Use Separate Service accounts on your Production deployment only if you are 100% sure that your applications are not using Kerberos. 

I’ve already setup my service account in my AD and added as a user on all the 3 nodes locally and granted “Lock pages in memory” and granted “Perform Volume Maintenance Tasks” in local security policy.

Okay, now let’s begin the actual Installation.

Step1: I inserted my Media in my DVD drive and started setup.exe as you would do normally. Now I selected “New SQL Server Standalone Installation or add features to an existing Installation”. Got No Validation warnings as you can see below…

Step2: I proceeded further and Unchecked “Include SQL Server product Updates” – My Server is not connected to Internet/It can’t search Updates through WSUS in my case. Well, that’s fine! Once I clicked Okay, It started Installing base/Setup files as you can see below.

Everything worked fine as expected and got the below screen in few seconds…

Step3: Select your required features. I selected below shown features and Services.

Note: As you can see BIDS is gone. It has been replaced by SQL Server  Data Tools!

Step4: I selected Default Instance and my Root Installation is on E$ as you can see below(Screenshot Error, my bad).

Step5: Time to provide Service accounts. See below for my selection…

Step6: Time to choose Administrators. I’ll always add myself and the DBA group as Admins.

Very Important: Now, don’t just proceed with Next, go to Data Directories Tab and select your data and Log Drive paths. In my lab I’m having the same Drive letters and folder structure across all the 3 nodes and I recommend you to have a similar strategy for your real world deployments(Don’t leave it defaults…especially when dealing with named Instances, because SQL Server will create Folder specific to Instance name). This is required for successful Automatic Failover of your AG’s. If not you’ll end up with Database(s) not coming Online as expected and manual intervention is required(Similar behavior as DB Mirroring), which is not something we want in our real world deployments.

As you can see, I just created Folders named “Data” and “log” under my Data and Log Drives and will be doing the same on all other SQL Server Instances participating in this AG.

TempDB location is up to you and System Databases are also up to you. ( System Databases can’t participate in AG, same as Mirroring)

Everything is pretty simple/straightforward and went smooth right…..! Wait…..at this point my Installation had some issue and I got this annoying error Message.Erkkkkkkkkkkkkk…!@#$#

“Error While enabling Windows Feature : NteFx3, Error Code : -2146498298” while Enabling OS Feature ‘NetFx3’

Hmm this is something which we never expected at this stage, especially after we passed all the validations and checks at the final stage…! I Thought we will get this interruption only on Windows Server Core. Okay, Now we know we can expect this on Full Blown Windows as well(If we don’t take care of this prior to our SQL Server Installation)

Anyways, what is this all about? What is it talking about…What feature it is referring to? The Answer is “.NET 3.51 Payload“. Yes, we must manually enable .NET 3.51 Payload in Windows Server 2012. (Scroll a little but up and see the screenshot for Step3. you can see under the Prerequisites for selected features, it is saying that “Microsoft .NET framework 4.0 is already Installed, but it’s asking us to manually enable .NET framework 3.5 from GUI) For this you need to have Windows media handy or should be connected to Internet, OR you can enable this automatically by enabling Remote Management which you might not do on your Production Boxes.

So, How to Fix this Annoying thing? Yes, you are correct if you said PowerShell (Few of you might thought about DISM or Just GUI for adding this feature as you used to do in Win Server 2008R2….YMMV!)

Note: Even the Error Message says, go and enable this feature using Server Manager GUI, it might fail!!!….Avoid GUI for fixing this.  Instead use awesome PowerShell guys. I’ll show you powershell way of doing this…

1. Insert your Windows Media in your DVD Drive(in My case it’s D:)

2. Open Powershell as Admin and type  “Install-WindowsFeature Net-Framework-Core -Source D:\Sources\sxs” without double Quotes and hit enter.

After a minute or two, I got success message as shown below 😀

Before fixing this, my Installation was only able to Install few components and my Core DB Engine failed Installing as you can see below.

Once I enabled .NET 3.5 via Powershell,  I was able to re-Install the required components which we had issues earlier without any further Issues. Btw, I enabled this on rest of my nodes at this point to avoid this issue… 🙂

(May be I’ll come up with a short Blog post later just on this Error message)

Strange things right?? Anyways besides this error,  understanding Service Accounts Requirements and Data,Log Files placements for your SQL Instances is very crucial.

I’ll proceed further and Install standalone Instances on the remaining 2 nodes as well for now….Let’s see enabling AlwaysOn and setting up in upcoming post.

Hope this helps if you get into same situation as me while Installing SQL server 2012 on top of Windows Sever 2012.


Setting up SQL Server 2012 AlwaysOn Availability Groups – Part1

I am in the process of setting up my lab for SQL Server AlwaysOn Availability Groups on top of Windows Server 2012 DataCenter Edition and would like to share my experiences . In this post, let me show you setting up your Windows Cluster which is the back bone for SQL Server AlwaysOn….

My Basic Lab setup:

4 VM’s (1 for DC/DNS and 3 other machines for SQL Server Instances). Before proceeding any further please make sure you understand the difference between AlwaysOn Failover Cluster and AlwaysOn Availability Groups(AG). They are not the same! Am talking about AG’s where I’ll be installing three individual Standalone SQL Instances on three different Windows Servers(These 3 windows servers will be acting as Nodes in a windows Failover Cluster, but with no shared Storage-No SAN required, can go with DASD’s or SSD’s or JBODS etc). In other words your Windows Servers participating in AG should be members of the same Cluster, but your SQL Server Instances can be Standalones which are completely isolated from each other! Hope am not confusing you…(Will explore more on SQL Server AG terminology in upcoming posts).

Note: All the Nodes participating in your Cluster should belong to same domain. Cluster validation may result in storage warnings as we are not using Shared Storage.

Step1: Build VM’s as needed.(For building VM’s and setting up networking Please see my previous posts on SQL Server 2008 Failover Clustering)

Step2: Build Windows Cluster

Am on Server Manager of NodeA as Domain Admin-Add Features/Roles and select Failover clustering Feature.

After few seconds I got success msg as shown below.

Perform the same on all the nodes you plan for creating an AG.(In my case I’ll install FC Feature on remaining 2 nodes as well).

Once done Installing this feature on all the nodes, I started Failover Cluster Manager on my machine SreeSQLA and started to create Failover Cluster. Added my 3 machines as you can see below.

Now, it’s time to validating my Cluster(You can do this even before starting creating your cluster). See below warnings I got on my cluster.

Network Warnings I got:

Basically complaining about single point of failures, I am ignoring as this is my lab.(In our Prod environments, we should take care of these warnings before building cluster).

Storage Warnings I got:

Ignore them as we are intentionally doing this…
Now I assigned a name and IP for my windows Cluster as you can see below.

You can see “Add all eligible Storage to the cluster” button which is new to Windows Server 2012 – I unchecked it and clicked Next. After few seconds…I got the below screen 🙂

Now, for verification, see SREEWINCLUST being added in my AD.

Perfect!! Now Let’s open Failover Cluster Manager from Administrative tools and see how it looks with this strange configuration we did without storage.

As you can see it selected Node Majority(Remember, we’ve No Quorum Disk – FYI, we can change as needed)

It’s listing 3 Nodes and Networks as expected, Interestingly you can see “Disks” and “Pools” under storage(This is new with Windows Server 2012). If you select Disks or Pools you should see nothing in the right side pane as seen below.

You will get 0 pools as well under storage Node. Interesting Huh???

You can double check your cluster ip by just pinging your Cluster Name as shown below and double check everything looks as expected.

I’ll stop right here for setting up the foundation(Windows Server Failover Cluster) for SQL Server AlwaysOn AG!- Let’s see some actual SQL DBA stuff in upcoming posts…Stay Tuned!

Hope you learned something new and also hoping this helps in setting up your own lab. Cheers!




Logout/Log off in Windows Server 2012

Let me show you where you can find  log off Option in Windows Server 2012. I know this might sound stupid, but let me tell you this..I struggled for a while to find out where this is 😀

Most of us are already aware of this, where to get power off option right…? (Shown in the below screenshot for whoever is completely new to Win 8 World).

I got this Vertical Window by hovering my mouse at Bottom Right Corner of my screen, Once you get this click on Settings and you see something as shown below..

Well, I can only see Shutdown and Restart Options!! Where is this Log off hiding at??

you’ve to Click on the annoying Start Button( you will get this by hovering your mouse at the bottom left most corner of your screen, shown below)

Now, you will get this Metro Style Screen, where you’ve to click on your User account Icon at the Top Right Corner of your Screen and you get options as shown below.

There you go…Sign out is our new Log Off Button 😀