It’s June 2016 and we now have what is the most impressive version of Microsoft SQL Server to date. Enhancements in encryption, high availability, integration to Microsoft Azure though Stretch Database functionality, Live Query, and Query Store (among other features) are all integrated into the new release but the first experience we have with the release is the installation experience.
I just installed the released version of SQL Server 2016 after going through multiple iterations of Community Technology Previews (CTPs) and I wanted to bring a few notes about that experience to you so you’re appraised of what you’ll be encountering when it’s your turn. Oh, and your turn could be after the last period in this article (or sooner) because with the release of SQL Server 2016 Developer Edition is now free.
Item 1: Granting Volume Maintenance Tasks Rights is No Longer an Afterthought
Granting this right to the SQL Server service account was always one of those post-install items that you either:
- Knew about and implemented without question.
- Wasn’t aware of.
- Was aware of, but forgot to grant.
Most installs I ran across in my time as a consultant or team lead reviewing existing installs fell into the “Oops Bucket” composed of those last two list items. It’s understandable. Many, if not most operational database administrators don’t tend to jump into the Local Policies area of the Windows Server O/S. This is where you’d need to go once you’ve finished with the base SQL installation to grant this right to the SQL Service account. What does it do? It skips the zero-out process when there is a need to grow a SQL Server data file (log files require the zero-out process regardless.)
By avoiding the zero-out process the growth of a data file is the closest you’ll get to instantaneous and that’s important because if a data file fills and triggers an autogrowth event everything waits until that process completes. This leads to long transactions, higher chances of blocking, and so forth. Add bad practices of auto-growing based upon a percent or growing at large fixed-mb increments and you could be in for a mess.
Now this is all solved with a check box.
Item Two: Configuring tempdb During the Install Process
Another one of those post-install processes are now integrated into the installation.
It used to be that all databases were treated fairly whether they were or not. In the install process you could set the data directory and transaction log directory. Then we were given the ability to separate out the system data and log directories. We still couldn’t do the “necessaries” of dictating additional tempdb data files that had been a part of Microsoft’s best practices for SQL Server performance for versions starting with the proliferation of multi-core cpus. This was (once again) one of those post-install items we either knew about, remembered, or were oblivious of. Removal of SGAM contention is the win when we do provision multiple data files for tempdb in multi-core environments (hopefully all environments fit this bill now) and the SQL Server 2016 install process affords the ability to setup multiple data files and sizing right up front. There goes one of those post-install templates I had to run through each install. Good riddance!
Item Three: Who Put their “R” in My SQL Server?
When Microsoft acquired Revolution Analytics we knew it meant that we were going to see likely integration of the R statistical language in some degree into the Microsoft Data Platform. It’s there all right – both as integrated and as a stand-alone option in the installation process for SQL Server 2016.
With R Open you will be consenting to download (and subsequent updates of associated files according to your SQL Server update preferences.) This is one of a few new features that require internet connectivity from your SQL Server for updating (and as we’ll see soon installation.)
Item Four: Additional Installation Options
Before you even progress into the Standalone SQL Server install you’re greeted with additional options:
The big one is that second item in the list: Install SQL Server Management Tools. If you step through the install process for a new instance you’ll miss the elephant that is no longer standing in the room: there is no option to select Management Tools for installation. Rather you need to do a separate install to get SQL Server Management Studio (SSMS) installed. Not just that – but taking a cue from the monthly cadence for online SSMS updates for SQL Server 2016 pre-dating the release of the core database engine itself you need to go online to download and install SSMS. That’s right – a separate online download if you want SSMS local to your SQL Sever, furthermore (are you ready for this?) It requires a reboot!
I’ve never been a fan of installing SSMS locally to the SQL Servers. It consumes RAM and CPU and there is rarely any good reason to remote to a SQL Server and launch SSMS. By making this an online install with a reboot there are some headaches that are put between you and the install of Management Tools on a SQL Server. As an aside I do find it interesting that the core installation of the database engine and all it entails doesn’t require a reboot yet installing SSMS does.
Then there is the last item in the list: Installing R in standalone mode primarily to be used for purposes as an Analysis Server or a an R services client. This is not something you’d typically install side-by-side on the same host as the core database services/engine.
There you have it: my initial takes on the items that differ on the installation experience in SQL Server 2016 from previous versions. I don’t know about you, but I’m really excited to dig into the new features and improvements in this ambitious release and I’m thrilled to be taking you along for the ride.
