Installing SQL server 2016 – standalone instance – new features

Posted in: Microsoft SQL Server, Technical Track

In this article I am going to go through a typical install of SQL Server 2016, and explain some of the best practices to follow when setting up a production server. I will also take a look at the new features when installing SQL Server 2016 compared to older versions.

The version of SQL Server I am using in SQL Server 2016 RC2. That means that some of the features may change between the time of writing and the retail release of SQL Server 2016.

 

Let’s Get Started!

The first screen you come to after opening the installation media:

Initial Screen

  1. Click the Installation button highlighted, and then the first option in the list New SQL Server Standalone Installation.

 

New for SQL Server 2016
For the Seasoned SQL Server DBA you will notice a few additions to this screen.

  • SQL Server Management Tools can now be installed from this screen. The files and binaries will be downloaded when you click this link, as they are no longer bundled with the installation media.
  • SQL Server Data Tools can also be installed from this screen.
  • Additionally, a standalone instance of R can be installed. R is a statistical programming language embedded into SQL server 2016, making it easy for data scientists and BI professionals to get a good level of analysis without leaving the SQL Server environment.

 

  1. Select the edition of SQL SERVER you would like you would like to install, I chose developer as it’s a full featured installation of SQL Server that can be used for development only, and not in a production environment. You could also enter a key in here instead of selecting a version.

Edition

  1. Read and accept the licence.
  2. SQL Server will then check a few rules to make sure you can install SQL server on your hardware.
  3. Select whether or not to allow Microsoft to check for updates and click next.
  4. SQL Server Installation will then install some setup files needed for installation and perform a few more checks. As you can see below, a warning has appeared asking me to make sure the correct ports are open on the firewall. Click Ok and then Next to proceed with the installation.

Rule Check

  1. The next screen is where we want to select all the features we want to install. As my machine is being set up as a dev machine to test SQL 2016 features, I am installing all the features. In a production environment only install the features that are needed. You can also select where to install the binaries and the root instance from this screen. Click next once you have selected the settings needed.

Features

New for SQL Server 2016

  • The ability to install a standalone instance of R is now available in the Shared Features Section.
  • The ability to install R services in the database engine is now available.
  • The ability to install Polybase Query Service for external sources is now available to install. Polybase query service allows users to query big data sources such as Hadoop using common T-SQL statements. If you are planning on installing the Polybase feature, then the Java Runtime SRE needs to be installed first.

 

  1. The next screen is where you will need to name the instance, if this is not the only SQL server installation on this hardware. It also confirms the installation directory as per the previous screens. As this is the only installation of SQL Server on this machine, I am going to leave these settings as default. Click next when ready to proceed.

Default Instance

  1. The Next Screen is the Server Configuration screen. You should run each service under a domain account with a strong password. Whether you use managed accounts or do not enforce password expiration is up to you. However, these accounts are going to be running your services and should always be available. As this is a test machine not connected to a domain, I will leave the defaults. You can also select your default server collation from this window by clicking on the tab at the top highlighted in yellow. It is important also to set the start-up type parameters to allow services to start automatically on reboot if needed.

The ability to allow Perform Volume Maintenance tasks to be checked from this screen is a new feature. This is a best practice among SQL server DBA to allow instant file initialization. Previously this had to be done outside of the installation window.

Server Config

 

New for SQL Server 2016

  • The ability to allow Perform Volume Maintenance tasks to be checked from the Server Configuration Screen.

 

  1. Next up is the database configuration screen, and we are going to step through the tables.

In the first tab you will want to add all the users that require sysadmin access. To administer the server, click on the Add Current user and Add buttons. I always use mixed mode authentication so I can still get to the server if Active Directory plays up, and I add a complex password for security. When your done, click on the Data Directories tab at the top.

Database Config1

This is the screen where you set up all of your default directories for your databases.

Best practice states that we should put Log files on separate disks to Data Files, as there are two different access patterns for these, so they would be more performant on separate disks. Backups should also be on their own disks where possible. Additionally, the OS should also have its own drive separate from all SQL server files. As this is a test server and I only have a c drive, I will leave them as default. Click TempDB tab when ready.

Database Config 2

 

New For SQL 2016

  • This is new in SQL 2016, and previously had to be configured after install. This screen allows us to create files for temp db. Best practice stated there should be 1 file per logical core up to a maximum of 8 as I have 4 cores in my machine I have created 4 files. You can also spread the files over more than one disk if needed. Once you’re happy with your selections click next.

Database Config 3

 

  1. The next screen is to configure Analysis Services. I have configured mine in Multidimensional mode adding myself as sysadmin and setting directories using same best practice as database engine. Look out for a further blog article on SQL server Analysis Services.

SSAS

 

  1. Leave the default options for Reporting Services. Again keep an eye out for another article on Reporting Services.
  2. In the next screen you have to configure the users capable of using the Distributed Replay Controller.
  3. Give a name to the DRC.

DRC_Name

  1. On the next Screen you will need to accept the terms of the features being installed by clicking accept, and then next.
  2. Finally, you can click install and that’s it! SQL Server 2016 and all its new features are installed.

Final

 

Here is a great link on some of the new features available in SQL Server 2016

email

Author

Interested in working with Daniel? Schedule a tech call.

About the Author

Project Architect
Highly experienced and skilled Solution Architect with extensive systems expertise. My recent career has allowed me to develop an outstanding track record in assisting with the implementation of new business solutions and demonstrate a strong trouble shooting ability meaning fewer and less frequent issues for a large-scale network. I have excellent experience in end to end solutions from setting up infrastructure to designing Relational Databases and Datawarehouse Databases with the ability to report write across a wide range of software/technologies.

13 Comments. Leave new

Hi Dan,
Very helpful and thorough post. However in the Server Configuration section, you do not cover Polybase Domain Account assignment. I am currently installing SS 2016 Developer on a PC and the domain account fields are left blank next to the Polybase Engine & Polybase Data Management fields. Any thoughts/suggestions on what to input here?

Reply
Daniel Holt
July 19, 2016 1:55 pm

Hi John

These need to be Domain accounts simular to the SQL server Engine account. When i set up SQL and polybase needs to be installed i make sure that the polybase service account is the same as the SQL service account. The account has to have permissions on all applications that it needs to retrieve data from

Here is a great article that explains this a little better

https://www.mssqltips.com/sqlservertip/4080/introduction-to-polybase-in-sql-server-2016–part-1/

Any questions let me know

Regards
Daniel

Reply

Hi Daniel
I installed SQL Server 2016 on my server but ,after installation finished, there isn’t any SQLserver engine for create a database,
What is wrong? Please help me.

Reply
Daniel Holt
June 11, 2017 6:34 am

Hi Atie

You will need to download and install the latest SSMS which can be found here https://go.microsoft.com/fwlink/?linkid=849819

After this you can connect to the database engine and create the databases you need to.

Dan

Reply

Hi Daniel,

Where can I find sql server developer edition for download?

Thank you!

Reply

Hi Tanya

You can download SQL Server developer edition directly from Microsoft

Here is the link

https://www.microsoft.com/en-us/sql-server/application-development

Regards
Daniel

Reply

Hi Daniel,
Is it free to download Sql server 2016 on my pc from Microsoft website?

Reply

HI Farrah

Developer edition is free to install as part of Visual studio dev essentials.

https://www.visualstudio.com/dev-essentials/

Regards
Daniel

Reply
Balaji Veeraraghavan
April 10, 2018 3:51 am

All the componenets installed except for R Services. I guess it couldnt download that installation package from destination. When i connect to this instance via SSMS, i get the following error.

Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.PolybaseManagement)

How can i rectify this? Please advise.

Reply

Balaji,
The installation media for SQL Server contains all the components with the exception of SSRS. If you selected R Service to be installed but it is not found I would recommend reviewing the installation logs.

You will likely need to do a repair on your SQL Server installation to resolve that error, or a complete uninstall/reinstall. Again, you would need to check your logs on the server/device to troubleshoot it further.

Reply
Balaji Veeraraghavan
April 11, 2018 1:13 am

Hi Shawn,

Repair didnt work. Please find the logs below.

First Installation Log:

Extract from RSetup Log file.

2018-04-10T06:30:24 INFO Command invoked: E:\Dev Tools\en_sql_server_2016_express_with_advanced_services_with_service_pack_1_x64_9540756\x64\RSetup.exe /install /component SRS /version 8.0.3.15000 /language 1033 /destdir C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\R_SERVICES /logfile C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20180410_114432\RSetup.log /instance SQLEXPRESS
2018-04-10T06:30:24 INFO Searching for cache dir: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\\R_SERV_CACHE
2018-04-10T06:30:24 INFO Using default cache dir: C:\Users\balaji.v\AppData\Local\Temp\
2018-04-10T06:30:24 INFO Backing up source dir: C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\R_SERVICES\ => C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\R_SERVICES_4d040c58-b12e-414b-82d1-5a11dde969b7
2018-04-10T06:30:24 ERROR Error renaming source dir: Access to the path ‘C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\R_SERVICES\’ is denied.
2018-04-10T06:30:24 INFO Exiting with code 5

Repair Log

Feature: R Services (In-Database)
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, and then try the setup process again.
Component error code: 0x84B40002
Error description: The SQL Server feature ‘sql_inst_mr’ is not in a supported state for repair, as it was never successfully configured. Only features from successful installations can be repaired. To continue, remove the specified SQL Server feature.
Error help link: https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.4001.0&EvtType=0x2841E06E%401204%402&EvtType=0x2841E06E%401204%402

Feature: SQL Server Replication

During both these processes, the end result is that R-Services install failed.

Reply

Hi Balaji

As Shawn mentioned there seems to be an issue with the initial installation of SQL and R services.

I have reviewed your log files you sent to Shawn and can see that there is a permissions issue on the path.

The problem is even after doing a clean uninstall of sql it does not seem to clear up the registy keys associated with R for SQL Server.

The steps i would take are.

1. Backup your Registry and create a restore point for your computer
2. Uninstall SQL Server
3. Restart
4. locate and remove the Registry keys and its children – an example is HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\36832937063F44041B69EA54696C5005
5. Restart Machine
6. Disable Anti Virus
7. Run SQL Server installation again as Administrator

Please note registry changes should be completed at your own risk and always backup before making any changes

Regards
Daniel

Reply
Balaji Veeraraghavan
April 11, 2018 11:20 pm

Thanks for the info. Let me look into this.

Leave a Reply

Your email address will not be published. Required fields are marked *