New SQL Server 2016 Installation Features Walk through
SQL Server 2016 came up with some exciting new features, which could sometimes be overwhelming for data professionals. On those lines, several new features came up in the SQL Server 2016 setup program. Features like R Studio, tempdb configuration right from the setup are indeed promising. In this post, I am going to talk about new SQL server 2016 installation features walk through.
The first thing that you would notice is that SQL Server Management is not included in the software package. You will need to click on the “Install SQL Server Management Studio” link to go to the Microsoft site to download the latest version.
New Standalone R Server is also included in the software package. This allows you to install R Server for advanced analytics purposes.
The maintenance menu is mostly unchanged from the previous editions. You get the regular upgrade and repair options as you did before.
On the Tools menu, you have a lot of standalone tools included in the package. For example, MAP toolkit, PowerPivot configuration tools for SharePoint 2016 on-premises. SharePoint 2013 is not compatible with SQL Server 2016 at the time of writing this post.
Finally, under the “options” menu, you have additional options to select x86 or x64 edition of SQL Server to be installed.
SQL 2016: Feature Selection Menu
As you can see from the above screenshot, apart from the R-Services (In-Database) and Standalone, you have the Polybase connector. It allows you to connect to unstructured data like NOSQL etc. This feature already existed on the SQL Server Database appliance, a.k.a PDW (a.k.a APS).
In-Database R services provide you with essential libraries that allows you to execute R scripts on SQL Server. If you want to use a fully featured R server, you would need to install the standalone edition.
There is a sleek new feature incorporated on the server configuration menu, called “Grant Perform Volume Maintenance Task Privilege.” This feature allows “Instant File Initialization” With this option checked, you get the following performance benefits:
- Create a new database.
- Adding additional Data files, log files to an already existing database.
- If you increase the size of an existing file which includes the “Auto-grow” operations.
- While restoring a database or a file-group.
Database Engine Configuration: TempDB Tab
A lot has been said and written about tempdb and its benefits of splitting up into multiple dbs. You can now spawn multiple tempdb files from the setup menu along with the auto-growth settings. You will no longer have to change these settings post installation like in earlier releases.
Analysis Services Configuration
There is an additional “PowerPivot Mode” option. It is not a new feature but rather an old one that existed way back since SQL Server 2008 R2. It was the instance dedicated to rendering SharePoint On-premises Power-Pivot excel data models. It used to be an option under “PowerPivot” for SharePoint 2013 shared feature. With the new standalone option, it makes more sense and less confusion.
Although this is not an exhaustive list, this covers most of the features added (and removed) in the SQL Server 2016 setup installer.