SQL Server 2016 has been officially launched for a little more then three month, many people are feeling excited to check if they should upgrade. In fact, many DBA should already played with it for awhile as the CTP has been around for sometime before the actual launch date, there are many new features in this version, let us get into details on some of those features and take a closer look if that can fits your needs.
I am going to break it down into a few part as it will be a really long post if we try to group them all into one. Firstly, let's take a look at what has changed in setup.
In previous version of SQL, basic installation is pretty simple, just a few clicks in the GUI, pick the items you want to install, that is pretty much it. However, in this version, there are a few new things you can choose, and there is something missing from the standard install GUI.
Let take a look at what's new you can configure / choose during installation. Take a look at the below screenshot, there are more features you can choice from, apart from the standard DB engine, SSRS, SSIS, SSAS. we now got R support,
You might say, OK, that is just new features for SQL, what else that has changed compare to the old version? The answer is the next two screenshot:
The first one here shows an additional option that every DBA will enable, that is grant perform volume maintenance task rights for the startup account, if you haven't enabling that, this option gives SQL server enough permission to perform instant file initialization for data files, do note that this cannot apply to log file due to the way log file works. I won't go into details of that as that will be another post 🙂
In previous version of SQL, its pretty common for DBA to increase the number of tempdb data file due to contention that it might experience. In fact, that might one of the first thing DBA will change in the post configuration script. However, every time you modify the tempdb, you might want to restart the service just to make sure everything is starting up the way you wanted. This new version takes that away by integrating the choice of the number of tempdb data files during installation. It also show the recommended number of tempdb data files for you, this makes it one step easier for installation, and so much simpler for new DBAs. In the screenshot above, my test server has 4 cores, hence it default that to 4 tempdb data files and calculated the total size of tempdb file for your reference.
On the other hand, what is missed from the install? If you did take a detail look at the feature selection screenshot above, you might notice that SSMS feature was not there! In fact, you might notice a new link from the first screenshot about SSMS install, however, it just show a link that you can download it, not actually perform the installation. SSMS is now outside of the standard install and needs to install separately. You can find the download link here: https://msdn.microsoft.com/en-us/library/mt238290.aspx
It got its advantage making SSMS a separate install, as how many times you do need to install SSMS for developers and power users that just require read-only access to SQL, This will makes it so much simpler for other IT Pro (or service desk) to install for them, Or if your company is using package application deployment, you can simply package it and allow user to install (or even use SCCM to push it down to the client).
Even though the new install helps you to decide how many tempdb data files during installation, this is by no means can take away your post configuration steps (or scripts). There are still things like max server memory, admin connection that you should configure. For a list of things that you should look at, you can take a look at my previous post here(http://www.sqlservercentral.com/blogs/denniss-sql-blog-1/2016/03/01/installation-of-sql-server-part-2-post-installation/), where I go through the post-configuration of SQL and things that you should change.
This is only the beginning of our journey to look at SQL 2016, stay tune and there are more to come.
