Category: 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.

Cheers!

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!

 

 

 

SQL Server 2012 Licensing – Myths/Gossips

This will be a pretty short blog post where I would like to share my thoughts on new Licensing policies of Microsoft SQL Sever 2012. Last year, when Microsoft announced that they are moving to Core Based Licensing from Socket based starting SQL 2012, I still remember many conversations we had within our team that “Microsoft is probably taking a wrong step by increasing the price, we might need to pay more than 2 to 3 times just for licensing on what we are paying currently”. Also there are huge ripples all over the internet regarding this change. My thoughts on this are “Nope! This doesn’t apply in all cases”. Few people just started to panic without properly understanding the new licensing terms and conditions. Actually, there is no need for creating such an attention all over the internet. Oracle has core based licensing since ages(come on guys, we are not running on SQL Server 6.5 or 7….we are taking about the best of its kind, the awesomely awesome SQL Server 2012 :D)

Why am I saying that this doesn’t apply in all cases?

Well, Core Based licensing limitation is only for High end Enterprise Edition(around 6900USD/Core). You can get BI Edition(around 8500USD/Server+around 200/CAL/user) license on Server based+CAL. Standard Edition has both the flavors – You can go with either Core Based(around 1800USD/Core) or Server Based(around 900USD+ around 200 USD/CAL/user). So If your shop is running on Standard Edition, you should be good straight away!

Note: We must buy minimum of 4 core licenses if we choose core based as per MSFT! Remember, this is just list price. Depending on your corporate agreements with Microsoft, you’ll be having special discounts in most of the cases…. 🙂

So, First we’ve to identify our needs, what exactly we really need in our environments. Do we really need Enterprise editions on each and every box? Do we really need each and every Physical server in our data center to be a 4 socket Quad Core Server? Gathering this crucial information and planning accordingly will save millions of dollars for any large SQL Server Shop.

For Virtualization – we’ve to license all the Virtual CPU’s. This might sound little harsh to few folks! How ever, if you are planning for SQL Server 2012 Enterprise, once you license all the CPU Cores in the physical Server(Host), you can have unlimited number of VM’s 🙂

So….My humble request is, stop blaming Microsoft and understand your real requirement and only pay for your real needs. Please do not just blame without really understanding the underlying nuts and bolts of licensing. I totally agree…licensing any enterprise product is always a pain and very confusing with especially with each and every release/edition. Touch base with your procurement department(if your team is not responsible for direclty dealing with MSFT) within your organization to know more about your corporate agreements you’ve with MSFT for licensing.

Visit this for more info. Have a wonderful weekend all!

SQL Server 2012 CU1 Released!!

Microsoft has released CU1 for SQL Server 2012 RTM within 3 weeks of the initial release of the product, which has more than 50 fixes! This is something really interesting to me which I’ve never seen(at least I don’t remember) where they had fixes for ~50 bugs in a Cumulative Update(not Service Pack).

You can get more details(what got fixed what are the enhancements) and download from http://support.microsoft.com/kb/2679368

Cheers!!

SQL Server 2012 RC0 on Windows 8

Today I got some leisure time and thought of Installing SQL 2012 on my Win8 machine and sharing my experiences at a very high level. Well,  I’ve Installed SQL Server 2012 RC0 on my Windows 8(Consumer Preview – Evaluation Copy, Build 8250) Machine.

To be honest, I was expecting few bugs and annoying errors. But let me tell you this, My Installation went Flawless. FYI, I’ve Installed only basic components(DB Engine – with Replication, SSIS,Complete Client Tools, SDK Connectivity).

Just a screenshot of how it looks like on Win8. Well, Technically there is no difference in how SSMS looks and how it behaves, but as most of you are already aware of…there is no START Button on my Menu bar on my Desktop! See below on the bottom Left corner.

So, where did I open SSMS from?? If you said “Metro Screen” yes, you are correct. See the below Screenshot for how Windows8 organized programs on my Metro Screen.

Hmm…this is something which really disappointed me. Basically, It created bunch of Apps for each exe! Imagine how ugly your Metro Screen gets if you Install all the components of SQL Server!! I Wish it was smart enough to create at least folders/Groups and place related exe’s accordingly.

Btw, if you want to Run SSMS as Admin, just right click on the App and you will see the available options in the bottom of your Screen as shown below. ( This applies to all the Executables)