SQL Server 2012 – Page 3

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!

SQL Server AlwaysOn Availability Groups Terminology….!

In SQL Server 2012, with AlwaysOn being introduced there are lot of new terms/words which we need to get used to as we support SQL Sever 2012. Well, Just getting used to those terms is not enough…we’ve to understand the terminology. In this short Post let me define what I’ve understood so far with these new fancy terms.

Availability Groups(AG) : – Group of Databases which move together from one Instance to other. Each Instance can have multiple AGs, where each AG contains multiple Databases.

Listener :- It’s a virtual entity which moves around to the current Primary Server for a given AG.

Replicas :- All the SQL Servers involved in your AG are considered as Replicas. Even current Primary Server is treated as a Replica, not just the secondaries…! So, replicas are not Just secondary SQL Instances. They are differentiated by using Primary Replica and Secondary Replica.

Note: AGs run on top of Windows Clustering. So is it a new clustering?? Nope! Same old Windows Clustering, but with a flavor of no Shared Storage. FYI, SQL Instances in my lab are 3 Standalones which are built on top of a 3 Node Windows Cluster with no shared Storage!

Note: your SQL Server Instances can be clustered as well, which adds more complexity, but is needed for some customers based on their own business needs. Typically, MSFT calls this scenario as AlwaysOn Failover Clustered Instance.

AlwaysOn Failover Cluster is not same as AlwaysOn Availability Groups!

SQL Server 2012 SP1 CTP4 now Available for download…

MSFT today released SQL Server Sp1 CTP4 for public to download. Looks like we are very close to RTM version of SP1 🙂 This is definitely a very good news for many of us who are already using SQL Server 2012 in our Production Environments!

My favorite enhancements are…

AlwaysOn Availability Group OS Upgrade and Full Blown SSMS with SQL Express!

For More information…visit here download and test it for yourself!

Have fun folks!

How to add a new Database to an existing AlwaysOn Availability Group?

In this post, let us see how to add a new database to an existing AlwaysOn Availability Group. Let’s assume we’ve a sales AG already in place and application team requested us(DBA) team to add one more Database to this Group as they need them to be available all together. For this Example let’s assume the new Database Name is “Sales_Q1” – Of Course this would be more realistic Name in your Real world deployments!

Please see my current Availability Grp Status Below:

At a glance, you can see 3 Databases currently Sales_1, Sales_2 and Sales_3 participating in my AG. Also you can see only 2 nodes out of 3 are Powered Up(See SreeSQLDR status as Down) and the Primary Replica is SreeSQLA.

Now, let me create a new Database named “Sales_Q1”. See below

As you can see I’ve just created a new Database(Simple Recovery Model) which is not yet added to my AG. Now, let’s try to add this Database and see how SQL behaves by default.

Step1: Right Click on Availability Databases and select Add Database.

Step2:

As you can see Wizard is smart enough to recognize your Database needs Full Recovery Model to participate in your AG.

Step3: I changed the Recovery Model to Full and refreshed the Wizard, now….See below for what it says

Cool…now it says you need a Full backup to be taken.

Step4: Now I tool full backup and placed on a share where all the nodes have access to. Now it says you are good to go as you can see below 🙂

Step 5: Now it’s time to choose how do you want to start synch. I’ve selected Full by providing the share(I placed by backup here) where all my nodes participating in my AG have access to.

Step 6: Time to Join all other Nodes by connecting. Notice I can’t proceed further(Next button is greyed out)

As you can see in the above Screenshot I’m not allowed to proceed any further. (Remember from my first screenshot? I didn’t powered UP my DR Node…) Just wanted to show you how this wizard behaves if any one of the nodes participating in AG is down. Once, I powered up my DR machine, I was able to connect to that Instance and was able to proceed Next.

Step7: It validated all my configurations and did it’s magic behind the scenes 🙂

Note: You can script out the entire process while you are at summary Screen.
Finally, see below for how it looks once I added new DB to our AG.

You can see Sales_q1 added to the AG successfully. Perfect! Hope this helps.

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”