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…


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


You can download JRE from the link shown below:


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


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…


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.


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.


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


  • 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…


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.


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


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


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.


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

SQL Server 2016 announced :)

Not sure if you guys have got a chance to grab updates from Microsoft Ignite event(Previously called TechEd), So here is a news for you. Microsoft CEO Satya Nadella announced SQL Server 2016 day before yesterday. It’s not yet available to download for public, but they are saying it(public preview) will be available soon :)

Looks like they have lot of enhancements in encryption(Always Encrypted…sounds very similar to AlwaysON) area and cloud integration/BI/Polybase areas.

For data sheet and other details please check

Am all set for getting my hands dirty playing with Public preview release…


How to connect to Azure VM SQL Server from SSMS?

In one of my previous posts, I’ve shown how to connect to Azure SQL DB(DaaS) from your local SSMS. In this post, let’s see how you can connect to SQL Server Instance which is running inside your Azure Virtual machine.

I’ve created a Azure VM( with SQL Server 2014. I RDP’d onto the server and see below for how it looks.


But at this point I wasn’t able to connect to this SQL Server Instance remotely from my local SSMS on my laptop. So, what should we do to be able to connect to this instance?

1. Create an End Point from your VM dashboard to allow TCP port 1433.


2. Enable Mixed mode authentication on your SQL Server Instance and make sure TCP/IP protocol is enabled from SQL Server Config manager.

3. Create a firewall rule(Inbound) to allow port 1433 on your VM.


I’ve created a SQL login named “sreeremote” on this instance and voilaaa…now am able to connect to this Instance remotely from my SSMS :)


Hope this helps…

Azure SQL DB using SSMS – Things you should know.

Hey folks…In this post let’s see few things which you should be aware of, to avoid any surprises when connecting to your Microsoft Azure SQL DB from your SSMS.

First thing you might notice is there are no msdb,model and tempdb databases under system databases category, No Agent and pretty much nothing what you would see on your On-Premise SQL Server.

. 1

Now, let’s try opening a new query and see how database context works with Azure SQL DB. As you can see I got connected to master database which is my default database.


Nothing strange right? Let’s see what happens when I try to change my database context.


Ahaaaa….so, What if I change my database context from GUI?


Am able to change the database context for the first time. Well, but you can’t switch it back from GUI. It will be greyed out(See below).


So…Basically you have to establish a new connection to desired database as needed. In other words, you are directly connecting to your Database(literally). After all…this is Azure SQL DB, not Azure SQL Server :D

Getting started with Microsoft Azure – Creating Azure SQL Database(DaaS)

In this blog post, Let’s see how to create a Microsoft Azure SQL database and how to connect to it from your SSMS client tool remotely.

Pre-Req: you need to have an Azure subscription. You can sign up for free (I guess, you will get 30 days for trail).

Once you login to your Azure management portal, it looks similar to this…


As you can see I’ve one Virtual machine and a storage account and “Zero” SQL Databases at this point. So, do you need a virtual machine or a storage account to create Azure SQL DB? The answer is “No”. I created them for exploring Azure IaaS(Infrastructure as a Service). Anyways…let’s start creating Azure SQL DB.

Step1: select SQL Databases and select “create a SQL Database”


Step 2: Give your database a name and choose your tier and DTUs (Data throughput Unit). I will explain what they are in another short blog post. Now…focus on the server in the below screenshot. Remember, I already have a VM but it’s not listed here and I’ve selected “create new SQL database server”. If you are wondering why, here am trying to create a Azure SQL Database as DaaS(Database as a Service), In which I’ve no control over the underlying server and hardware. All I get is to provide DTUs and a location to pick, I can’t use Virtual machines which I’ve created under IaaS for this service.


Step 3: Provide a SQL Server authenticated admin account to connect to this database(Think this as “sa” account on your traditional SQL Server Instance) and pick the server location where you want the server to be created.


Click on the “Check” button and you should have your server and SQL database up and running in matter of few seconds.

You can see I’ve got a new SQL Server database and when I go to servers tab, see the new server as well which got created.



So, how would you connect to this SQL Server from your SSMS?

You should go to servers tab and click on your server and click on dashboard. On the bottom right you will see an URL. That’s your server name which you should be using to connect from SSMS without https://.


Let’s give it a shot and see how it goes from my SSMS.


Oops! The fix? Simple, I have to go to azure management portal and select the server and go to configure and create a rule to allow my IP address where I’m trying to connect from using SSMS as shown below and click save.


Once I did that, I was able to successfully connect to my Azure SQL DB :)


I hope this will help setting up your first Azure SQL DB as DaaS. In an upcoming post, I will show few “GOTCHAS and limitations” you should be aware of while connecting to your Azure SQL DB from your SSMS.