SQL Server 2016 CTP 3 Setup – UI Improvements

There are some improvements in the setup UI for SQL Server 2016 CTP3, and its great to see Microsoft making these changes as they receive feedback from multiple customers and early adopters.

My earlier post on setup UI changes for CTP 2 is available here.

The 1st change is around granting the SQL Engine Service account necessary privileges to enable INF(Instant File Initialization) during the setup itself.

This was a surprise when I saw it for the 1st time :

New Setup

You can notice the check box for Grant Perform Volume Maintenance Task privilege.

There is an interesting post by Nic Cain(B/T) on this topic and you can read it here.

The 2nd change is around Tempdb configuration and here are the new options :

New Setup2

The wizard will now check the no of cores on the server and will come with a pre-populated value for the no of temp db data files.

It also provides options to define the initial size and the auto growth size.

This might not be something which the power users care,(When I said power users,I meant people who automate lot of SQL server deployments and are already doing this with custom scripts) however this is really a value add for people who do manual SQL installation.

Going forward we also don’t need to turn on Trace Flags 1117 and 1118 as they are taken care by default.


I’m really glad that Microsoft is giving options for users to follow some of the long-standing best practices during the install process itself and this is a welcoming change.

Thanks for reading and keep watching this space for more!

Azure Virtual Machine Blog Series – Changing the size of a VM

I’m starting a new blog series on Microsoft Azure Virtual Machines, and my goal is to write blog posts exclusively on Azure VM/SQL Server related topics.Here is the 1st post –

“Changing the size of a VM”

When creating an Azure VM to host your SQL Server, you have an option to select the pricing tier. This pricing tier states what will be your VM configuration in terms with CPU cores,memory,No of disks, IOPS etc.

Azure provides plenty of pricing tier options,and you have the power to create a VM with the least amount of configuration or pick a monster like below –


Gone are those days where you had to think twice to move your SQL Server workload to the cloud because of the lack of compute power. 32 cores, 448 GB, 64 disks with 80,000 ! Can’t get better than this !

What if I created a VM with a standard pricing tier like DS1 Standard, and after running it for a while realize that it’s not meeting my needs, and my SQL instance starts freaking out.


Hmm, bad planning isn’t? Yeah, it’s definitely bad planning, however Azure will cover you pretty well.

If I check the VM configuration, I can see its current configuration as 1 Core and 3.5 GB of memory. AzureVM3

If I go to the portal and choose the settings option for the VM and the select size, I have multiple selection options for pricing tier-


If I scroll down on the selection options, I can see that the G series are not available. This means that you cannot scale up from a D series to a G series directly. Again, not planning ahead can give you trouble.



What if I select DS3-Standard. Will it change my VM configuration on the fly?  Lets check it out –

On the portal I’m selecting the DS3 option which is 4 cores and 14 GB memory.

Note – This will restart your VM behind the scenes.If you are following this article and trying to change the pricing tier of a critical production server, then stop and plan to do this activity during a planned downtime.


Within few seconds, I got an alert which says –



Neat ! Lets check the configuration –


Indeed the new configuration is applied after a quick reboot.

Conclusion – 

Flexibility to change the VM size pretty easy can come handy sometimes,however carefully analyzing the workload compute demands and choosing the correct sized VM is always the recommended approach.

Thanks for reading and keep watching this space for more Azure VM related posts.

SQL Server Data Tools is now a separate installer – Things to know!

Recently I had to develop some SSRS report, and my desktop machine had SQL Server 2014 installed on it. I had previously used SQL Server Data Tools, and I tried to search for it under Programs. To my surprise, it was missing.

I thought I might have missed adding it while installing SQL 2014, and re-ran the setup to add it again. There comes my next surprise –

There is no option to add SQL Server Data Tools!


After some research got to know that Microsoft moved away from bundling data tools with SQL 2014(For obvious reasons!) and this link provides the required bits.

Picking the right installer was again little confusing after browsing the above URL and eventually I was able to download the right product after carefully checking the description.


Installing the tool was pretty straightforward and here are the instructions –

  1. Run the setup.exe(After extracting the installer).
  2. Click on New SQL Server stand-alone installationSSDT3
  3. Perform a new installationSSDT4
  4. Choose SQL Server Data Tools – BI for Visual Studio 2013SSDT5
  5. Install.
  6. Eventually you will have it on the Program list SSDT6

Conclusion –

Microsoft’s decision to un-bundle SSDT with SQL 2014 installer is acceptable as this gives them the opportunity to update this tool on a fast pace.

Thanks for reading,and keep watching this space for more!


Why I won’t be running SQL Server Standard Edition on Business Critical Servers!

I keep getting questions around what core features are missing in Standard Edition of SQL Server,and why there is a need to install Enterprise edition for business critical systems.Here are my top features which are missing in standard edition –

Scale limits

Memory – Standard only supports 128 GB memory.

Memory is relatively cheap now,and I can’t think of any reason why someone can’t have more memory on business critical servers.

CPU cores Limited to lesser of 4 Sockets or 16 cores.

High Availability

AlwaysOn Failover Cluster Instances – Possible but only 2 nodes supported.

AlwaysOn Availability Groups*.

*AG is the future of HA/DR strategy within SQL Server and because of great demand, Microsoft will be allowing AG in standard edition of SQL 2016, however it will only support 2 nodes. Support of just 2 nodes is not sufficient for a solid HA/DR architecture.

Online page and file restore.

Page restores can come handy if you have to deal with corruption of a very large database and corruption is only for very few pages.

Online indexing.

You don’t really want to lock down the table while doing index maintenance right? Online index operations are key to maintain the uptime of the database.

Online schema change.

Hot Add Memory and CPU.

Database Snapshots.

Scalability and performance

Table and index partitioning.

Database size is increasing like never before and 3-5 TB DBs are very common these days. Maintaining these DBs without a partitioning strategy can be a very bad idea.

Data compression.

One of the most ignored features in SQL Server. Compression can be really helpful to save some space without much overhead.

Resource Governor.

IO Resource Governance.

In-Memory OLTP.

In Memory OLTP might be having lot of restrictions today, however things are going to change soon. I’m pretty sure that there will be multiple use cases when this feature is stable.

Memory optimized columnstore indexes.

Column store indexes dramatically improves DW workload performance and this feature will get more and more enhancements in the future.


Transparent database encryption.

Conclusion – 

Business critical systems needs to be treated as critical as there is a potential risk of losing revenue if these systems are not highly available and uptime requirements are not met.Performance is also a key factor, and SQL Server standard edition is not the right edition for these requirements.

Installing SQL Server 2016 CTP2 – UI Changes

SQL Server 2016 CTP2 public build was released by Microsoft yesterday, and if you haven’t downloaded it yet, then drop everything(No, not a Production DB !) and go and get it from here.

This post is based on the UI changes/additions which I observed while I installed this specific build. Its going to a very short post as there are no major changes.

First addition or change was around the Feature Selection page –



You can notice a new instance feature called PolyBase Query Service for External Data. 

This feature enables truly integrated querying across Hadoop data and SQL Server data using standard T-SQL statements.

Second change or addition was around the Database Engine Configuration page.


This one is beautiful, I was pretty much excited when I saw this for the first time. As the description says the default is 8 or the number of cores, whichever is lower.

My test machine had 2 cores and it configured 2 files for me.


However, the growth is still 10% for these files ! You may want to change that.

2016CTP24Conclusion –

Those are the quick UI changes which I could notice during the install process.

Thanks for reading and keep watching this space for more ! (Hint : Lot of good SQL Server 2016 stuff coming your way)

Full Domain Trust and Natively Compiled Stored Procedures – In Memory OLTP

Recently one of my developers brought this specific problem to my attention. He was trying to create a natively compiled stored procedure(With Execute as Owner), and was getting an error –


Could not obtain information about Windows NT group/user ‘Domain\UserName’, error code 0x5.

My first reaction was to check the builds and to ensure that the user is running latest CU.(Not yet SP1 !) . The user was indeed running the latest SQL 2014 build.

I decided to repro this issue at my end, and I was able to create/compile the procedure without any errors.

My next troubleshooting direction was to check user permissions, and understand more about the environment where the user is running into this error.

The user in this particular scenario was using his domain credentials to create the SPROC. The account was something like  HQ\Developer. HQ is the domain and Developer is the user name.

SQL Engine Services was running under account LAB\SQL.LAB is the domain and SQL is the SQL Service Account.

I read more about Natively Complied Stored Procedures, and especially focused well on this MSDN article, Creating Natively Compiled Stored Procedures.

This portion of the article was my focus area(Underlying the main point) –

” Regarding EXECUTE AS and Windows logins, an error can occur because of the impersonation done through EXECUTE AS. If a user account uses Windows Authentication, there must be full trust between the service account used for the SQL Server instance and the domain of the Windows login. If there is not full trust, the following error message is returned when creating a natively compiled stored procedure: Msg 15404, Could not obtain information about Windows NT group/user ‘username’, error code 0x5.”

In my case there was one way trust between HQ and LAB, however LAB didn’t trust HQ. The error message which the user got was obvious.

There are multiple workarounds to solve this problem and its listed in the article too –

  • Use an account from the same domain as the Windows user for the SQL Server service.
  • If SQL Server is using a machine account such as Network Service or Local System, the machine must be trusted by the domain containing the Windows user.
  • Use SQL Server Authentication.

I decided to leverage a SQL account and the user was able to create the procedure with that specific account.

Conclusion – 

In memory OLTP is a very exciting feature and there are a lot of dependencies which you should be aware before deciding to deploy it on Production.

Thanks for reading and keep watching this space for more !

SQL Server 2016 Announced, and it stretches to the Cloud!

Last week during Ignite, Microsoft CEO Satya Nadella announced the next version(Till then it was called as SQL vNext) of SQL Server, and its SQL Server 2016.

Satya started the Ignite keynote, and he looked really pumped when he announced SQL 2016.

“SQL 2016 perhaps is the biggest breakthrough you have ever seen in database technology”



He was focusing on Stretch Database capabilities, and he called it can infinite database where you have the capability to pick a single table and stretch it to Azure without any code changes.

The keyword here is “without any code changes”.

SQL Server 2016 is packed with some real exciting features, and below is my top favorite list(Yes, ranked from 1 to 3) –

  1. Operational Analytics – In Microsoft SQL Server 2016, users can run analytic queries on in-memory and disk-basedOLTP tables with minimal impact on business criticalOLTP workloads, requiring no application changes.The concept is based on leveraging In Memory OLTP + In Memory Column Store.This indeed is game changing. The idea of running analytic queries on OLTP tables might be a strict ‘No’ now, however as SQL Server evolve we can see changes in thought process.
  2. Always Encrypted –  

We had Transparent Data Encryption(TDE) all these years,and its been doing a great job. Then, why there is a need for something like Always Encrypted ?

TDE encrypts data at rest. As soon as the data is in motion, its vulnerable. Classic example is man-in-the-middle-attacks.

Always Encrypted protects data in rest and in motion. This feature lets application encrypt and decrypt critical data automatically, and it happens seamlessly in ADO.NET.

 The master-key resides in the application and there are no application changes which are required.

3.   Stretch Database – 

We all have hot and cold data in our databases, and in majority of the cases this data will be in the same database, same table without any specific partitions.

Years old data which might come useful only during audit purposes will be utilizing costly storage space and will add extra maintenance overhead.

SQL Server 2016 will bring in a new concept called stretch database, where in you have the ability to pick a specific table and stretch it to Azure.

If an application queries for the historical data, it will be retrieved from Azure.

Storage in Azure is cheap and the price will always go down as long as the “Cloud War” is happening between Microsoft, Amazon and Google.

With concepts like Always Encrypted, the data which resides or in motion to Azure is secure.

Apart from these 3 features, I’m really looking forward for Query Store and the Round Robin Load Balancing capabilities for secondaries in AlwaysOn Availability Groups.

There will be plenty of In Memory OLTP enhancements and it will cover larger T-SQL surface area. More on this will follow shortly.

Conclusion – 

Yes, SQL Server 2016 is a good release with lot of nice features and enhancements.

I’m pretty happy with the vision of the Data Platform Group within Microsoft, and I’m really looking forward to write more about these features in the upcoming months.

Thanks for reading and keep watching this space for more !