One: Setup Developer SQL 2016 Machine

Set Up SQL Server

In this lesson, we’ll install and configure a basic SQL 2016 developer machine. Note that this is NOT the same configuration we would use for a production system; but instead is simply a fundamental single machine developer configuration.

You can use the following screencast video to get a step by step with narration; or, you can review the bullet points that follow for the highlights.

The following are quickstart tips:

  • Run the Configuration Checker to confirm your machine is ready for SQL installation.
  • Install any critical updates before you install SQL, run Windows Update after the installation to insure your system is up to date.
  • Disregard the Windows Firewall warning in the Install Rules section.  By default, the ports are not enabled but this won’t be an issue for a developer machine where you are doing your work on the box instead of over the network.
  • In the SQL Features, install: the Database engine, Full Text and Semantic Search, Integration Services, and the Documentation Components.
  • Unless you have a compelling reason to change the defaults, accept the default file locations.
  • Likewise, accept the default Named Instance of MSSQLSERVER.
  • The only service that is set to automatically start is the Database Engine; set the Integration Services service to manual start.
  • For SQL 2016, click the “Grant Perform Volume Maintenance Task priv…”  See here for more details as why this is a good thing for a developer machine, including data that shows it takes ~2 minutes to initialize a 10GB data file with this OFF; but less than 2 seconds with it ON.

Click here to Toggle Sidebar: Enabling the Grant Perform Volume Maintenance Task priv

  • Enable SQL Authentication in Mixed Mode authentication and use  a password you can remember.  This may be the best back door you can have.
  • Add the Current User and the Local Administrators Group to the SQL Admins
  • We’ll play with Filestream later so click both “enable” boxes and the “Allow remote clients”… box.  Leave the share name the default.
  • Click the Install button.

Setup the SQL  Tools

The following is a screenshot on how to set up the tools (with no audio narration).  Following that, are the tips if you’d prefer to skip the video.

  • The management studio is now a separate setup.  Follow the links to install the studio.
  • Since we are setting up a developer machine, install the Data tools for the most currently support version of Visual Studio.  That’s VS 2015 as of the date of this writing.
  • For now, just install the integration services tools and skip the reporting services and analysis service tools.
  • Update the Visual Studio data tools and use Windows Update to update SQL if necessary.

In the next lesson, we’ll create our first database.

Leave a Reply

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

one × one =