SQL Server 2016 AlwaysON AG setup – Part 2

Now that I’ve created a windows cluster and installed SQL server on all the three nodes(Part 1), I am ready to configure AGs. Below is the screenshots tour.

1.JPG

You can see  a new option “Database Level Health Protection” (In CTP releases It had “Basic AG”and “Per Database DTC Support” options as well.). Now, on Enterprise/Eval/Developer editions, all we get is above option. If you are trying to setup AG on standard edition(Wait till RTM gets released), then You should be seeing an option for Basic AG(Well, only Basic AG can be configured on standard edition)

So, what’s DB level health protection? If one of your databases in your AG goes offline or becomes unavailable for some reason, entire AG doesn’t get impacted.

Next:

2

3.JPG

Here’s the bonus, Now we get 3 Synchronous(Automatic) replicas and Active secondaries are Load balanced with your routing list and also distributed transactions are now supported in 2016 AGs 🙂

Now, pick your backup preferences.

4.JPG

And finally configure Listener:

6.JPG

Below are the corresponding CNOs/VCOs/DNS records created in my AD.

From my SSMS:

9

That’s it folks! Happy weekend…

SQL Server 2016 AlwaysON AG setup – Part 1

This is a quick guide on how to configure AlwaysON AGs on SQL Server 2016. This blog post isn’t a thorough walk through, My goal in this post is to show you guys what’s different in SQL 2016 comparing with 2012 or 2014. (For detailed information on how to setup AGs in your own lab, please refer to my previous posts on SQL 2012).

First and foremost, I’ve built a windows cluster with 3 nodes(No shared Storage) for setting up AGs(See below).

a

Starting from Node SQLTrekAG1, Inserted SQL Server 2016 RC2 media, hit setup.exe…basic sanity checks…next next…blah blah.

1

One thing you might have already noticed, all our management tools and Data tools has a separate link to install. Well, This is a huge change(I would say improvement, YMMV), in earlier versions we had option to select Client tools when selecting features and IIRC data tools was a separate download all together in SQL 2014.

Next:

2.JPG

As mentioned earlier we no longer see client tools(basic and complete) in features selection.

Next:

3.JPG

One more amazing enhancement, now it always remind DBAs about IFI 🙂

Next:

4.JPG

Choose your dirs and if you go to tempdb tab, you can see that now SQL Server is smart enough to identify number of CPUs on the box and will choose number of data files for your tempdb.

Everything else should be similar to what we had in previous versions. Now after performing a standalone installation successfully, you can see SSMS hasn’t been installed.

5.JPG

Now let’s install SSMS on this machine:

All I had to do was double click setup.exe and on Installation tab, select “Install SQL Server Management Tools”and……Oops!Bummer….! Did I already mention, you need to have Internet connection to be able to do this. Well, see below screenshot carefully again.

6

It downloads an 815 MB file(SSMS-Setup-enu.exe), now that’s the media for installing SSMS and other SQL utilities! Store it in your media library, from now on we don’t need internet connection.

Note: You can directly go to https://msdn.microsoft.com/en-us/library/mt238290.aspx for downloading the same(As of 2016 RC2).

Run that executable…I got a warning about shared components already installed(I am good with that) and next to Install. That’s it!

It installs .NET framework 4.6.1(took a while), Report viewer, batch parser, SSMS, SQLCMD, BCP, SMOs, Visual studio 2015 Shell(took its sweet time), SQLPS,Profiler,DTA and all those goodies. Restart if required.

Now, I enabled AGs from SQL Config manager.

9.JPG

I will install SQL by performing the same on all the three nodes and will come up with part-2 later where I setup an actual AG. Cheers!

Installing Linux (CentOS 7) on a VM.

All these days…I’ve seen my fellow Oracle/MySQL DBAs working on NIX platforms, but I haven’t really got a chance to work Full time on these machines being a SQL Server DBA. Well, with Microsoft’s recent announcement(SQL coming to Linux), I’ve decided and committed to explore Linux in depth.  “MySQL” and “Linux” are my go to technologies for my 2016 Road Map besides VMWare! I know that’s too much…but hey, there’s nothing wrong in setting goals for ourselves 🙂

So, why did I choose CentOS? Well, It’s simple….It’s free to download and a replica of RHEL(Red Hat Enterprise Linux). From my understanding, All the commands which run on CentOS should run in RHEL. Once we get the familiarity with these Operating Systems, working on any Linux/Unix machines should not be difficult. So, if you want to get started to Linux, my recommendation is to begin with CentOS.

Screenshot Tour of installing CentOS 7 on Oracle Virtual box:

Prerequisites:

Download CentOS 7.0(ISO) from their website(Just Bing it or google it).

Download Oracle VirtualBox and install on your machine. (I prefer installing it on a VM rather than creating a dual boot machine with Windows and Linux, YMMV).

Step1:

1

Step 2:

2

Step 3:

3

Step 4:

4

Step 5:

5

Step 6:

6

Step 7:

7

Step 8:

8

Step 9:

9

Step 10:

10

Step 11:

11

In the above step, you can leave it to Minimal Install if you are sure that you absolutely don’t need a GUI. I selected KDE plasma for GUI…(We can load/start OS without GUI if needed, that’s probably what I would do after couple of days)

Step 12:

12

13

Once done, it asked me to reboot the VM. Now am ready 🙂

Note: I haven’t enabled networking and didn’t installed few things. well, if I see something wrong with my setup and if it isn’t working as expected….well, I can destroy my VM and build a new one as needed in just few minutes! That’s the beauty of a Virtual machine.

SQL Server on Linux – Too good to be True!

Microsoft just announced that they are going to support SQL Server on Linux! Yup, that’s true. They also say private preview of SQL Server is already available starting today for customers who are interested. It’s hard to believe me right…Well head over to this blog post and see it for yourself.

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

If what I am seeing is really true, well, for all the guys who says” SQL Server is not secure(We good DBA’s know how to keep it secure)…It only runs on Windows…blah blah” better stop saying that from now on 🙂

For an early look and to sign up for this head over to https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx and sign up.

I really hope this is not some prank! Any ways…it’s time to brush up my Linux Skills….Voilaaa.

Update – SQL Server 2016 very first Release Candidate(RC 0 ) has been announced as well today.  I sense RTM is coming soon 🙂 Happy SQLing!!!

Installing SQL Server 2016 – Is it any different?

Howdy…it’s been a long time I’ve written something related to SQL Server here in my blog. So here I am, I took some time out of my holidays to download and Install  SQL Server 2016 CTP 3.0 in my laptop. Let me share my thoughts on what has been changed/improved with setup experience.

One thing I’ve noticed is during “Features selection” phase, there’s a new feature called “Polybase Query Service for External Data” which requires Oracle JRE to be installed before in hand manually. See below…

3

If you choose Polybase Service without installing JRE, you get below error.

4

You can download JRE from the link shown below:

5

After I Installed JRE, I passed that screen without any errors.

7

So what is Polybase service? At a high level it’s an interface which lets us query Big data from SQL server using SQL Statements.

Moving on, in the section where we typically provide Service accounts, I noticed a wonderful enhancement. See below…

9

This takes care of Instant File Initialization(IFI) of all your data files for your databases 🙂 That’s fantastic….!

Moving on, under DB Engine configuration page, there is a new tab for tempdb. So, now SQL Server setup is smart enough to identify number of CPU cores you’ve got and will create tempdb files accordingly. You can choose initial size and growth settings for tempdb data and log files per your requirements.

10

This is an excellent enhancement IMHO. I’ve seen hundreds of servers where tempdb has been misconfigured in my experience.

Since I’ve selected Ploybase service initially, it has prompted me to configure polybase service per below screenshot.

11

After 10 mins or so….my installation completed successfully and below is what I see from my SSMS:

12

  • SQL Server version: 13.0.700 ( This is SQL Server 2016 CTP 3.0)
  • DB version is 839. But interestingly I noticed master database has been created in 836 version and upgraded to 839! Normally we see this when a database has been upgraded from older version to a newer version.
  • Every database has a new node called “External resources”
  • Three DW databases have been created by SQL Server since I’ve selected this Instance will serve as head node for my polybase configuration.
  • Each database has a new property category called “Query Store”. See below…

13

Query store is one of my favorite features microsoft announced for SQL 2016 besides temporal tables. At a very high level Temporal tables feature allows us to do time travel without any third party tools and Query store is a game changer in performance tuning area for DBAs and Developers. I will come up with blog posts on these topics once I explore these features in detail.

SQL Server 2012 SP2 CU7 – @@Version

Today I was testing CU7 for SQL Server 2012 SP2 and as usual after installing CU on our test server, as a practice I was reviewing the Error Log and interestingly it mentioned CU7 during the startup! Let me show you what am saying…See below screenshot on a server running SQL Server 2012 SP1+CU4. Till date SQL Server error log never revealed CU# in the start up entries…It just says the Service pack level which you’ve got.

1

Now with the CU7 for SQL 2012 SP2, that’s no longer the case 🙂 See below screenshot:

2

Even @@version from T-SQL is revealing the CU installed.

3

SSMS 2016 CTP 2.1 Web Setup – Installation Error.

Recently, Microsoft released SQL Server 2016 CTP for public and today I decided to Just install SQL Server 2016 CTP 2.1 Management Studio on my Windows 7 machine by using “SSMS-Web-Setup.exe”. (I tried to install full blown engine using ISO, but it says my OS is unsupported). Anyways…coming to the issue, I downloaded the file and placed it under my default downloads folder and ran the setup file as an admin. After few minutes, It failed generating huge log file, which is basically referencing to other .msi files present in my downloads folder and complaining that elements are not found. I previously downloaded SQLDOM, few Reporting services related msi’s etc which are located in my downloads folder. So, basically SSMS 2016 web Setup has conflicts with those old files which are present in the same folder.

Fix: Created a new folder and place the “SSMS-Web-Setup.exe” file in it and run the setup. That’s it guys…It worked just fine.

2016_ssms

Actually, there is a connect item as well opened for this issue.