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.
- 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.