DB-유

Installing SQL Server 2016 Developer Edition Step by Step

· mssql, sql server 2016, installation, windows

I've rebuilt my test VM three times this month, and every time I forget one of the setup checkboxes and have to fix it afterwards. So here are my notes. Developer Edition is a full Enterprise build that Microsoft gives away for non-production use, so I put it on my workstation and every test VM: Always On, partitioning, In-Memory OLTP and Query Store, no licence bill. You just can't use it in production.

What I use: Windows Server 2012 R2 / 2016 or Windows 10, 64-bit, 4 GB RAM at minimum (I'd give it 8), about 6 GB of disk, .NET 4.6 (already there on Windows 10 and Server 2016), and a local Administrator. Get the Developer ISO (SP1 slipstreamed if you can find it) plus the SSMS installer, which is now a separate download and no longer on the SQL Server media.

Mount the ISO and start the Installation Center

Mount the ISO (right-click, Mount) and run setup.exe. The SQL Server Installation Center opens. Go to the Installation tab and pick New SQL Server stand-alone installation or add features to an existing installation.

On the Product Key page choose Specify a free edition and select Developer. Accept the license terms and let the setup rules run. The only warning I typically see is the Windows Firewall one, which just reminds you that port 1433 is not open yet.

If you prefer an unattended install, the equivalent command line is:

D:\setup.exe /Q /ACTION=Install /FEATURES=SQLENGINE,FULLTEXT /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT Service\MSSQLSERVER" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /AGTSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS

Feature selection

Only install what you'll use. My usual pick for a test box:

  • Database Engine Services
  • Full-Text and Semantic Extractions for Search
  • SQL Server Replication (only if I plan to test it)

I leave out R Services (In-Database), PolyBase Query Service (it needs a JRE 7 Update 51 or later) and Reporting Services unless there is a specific need. Every extra service eats memory at boot, and on a laptop VM I notice.

Set the Instance root directory to a drive other than C: if you have one; the shared feature directories can stay on C:.

Instance name and service accounts

Choose Default instance unless you already have one, in which case give the named instance a short name like DEV2016.

On the Server Configuration page:

  • Leave the SQL Server Database Engine and Agent on the virtual accounts (NT Service\MSSQLSERVER, NT Service\SQLSERVERAGENT).
  • Set SQL Server Agent startup type to Automatic. The default is Manual, which means your jobs silently never run. I lost a weekend of backups to that once.
  • Tick Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service. This is new in the 2016 setup and enables instant file initialization, so data files no longer need to be zeroed out on growth.

On the Collation tab keep SQL_Latin1_General_CP1_CI_AS unless the application vendor demands otherwise. Changing collation after the fact is painful.

Database Engine configuration

On Server Configuration, pick Mixed Mode if any application needs a SQL login, set a strong sa password, and click Add Current User so your Windows account becomes sysadmin.

On Data Directories, split the paths:

User database directory:   D:\SQLData
User database log directory: L:\SQLLogs
Backup directory:          B:\SQLBackup

The TempDB tab is also new in 2016. Setup now proposes one data file per logical core, up to eight. I set the initial size to 1024 MB per file with 512 MB autogrowth and place them on the fastest disk available. This replaces the old routine of adding tempdb files by hand after installation.

Click through to Ready to Install, note the path of ConfigurationFile.ini (useful for repeating the install elsewhere) and click Install.

SSMS

Run the separate installer (SSMS-Setup-ENU.exe). It takes a few minutes and may want a reboot. SSMS 16.x talks to everything from SQL Server 2008 up, so one copy covers all my instances.

Did it work?

Open SSMS, connect to localhost (or localhost\DEV2016) and run:

SELECT @@VERSION;
SELECT SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductLevel');
SELECT name, physical_name FROM tempdb.sys.database_files;

You should see Developer Edition (64-bit), the service pack level, and the number of tempdb files you configured. Also check that both the engine and the Agent service are running:

Get-Service MSSQLSERVER, SQLSERVERAGENT

Things that bit me

  • "Rule 'Microsoft .NET Framework 3.5 Service Pack 1 is required' failed." This appears on Windows Server 2012 R2 if you selected features that still depend on .NET 3.5, such as the legacy Database Engine tools. Enable it through Server Manager or Install-WindowsFeature NET-Framework-Core -Source D:\sources\sxs.
  • "A network-related or instance-specific error occurred while establishing a connection to SQL Server" from another machine. Setup does not open the firewall. Run New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow and make sure TCP/IP is enabled in SQL Server Configuration Manager.
  • "Wait on the Database Engine recovery handle failed." Usually a service account or permission problem on the data directories. Check the Summary log under C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\.
  • Setup insists on a restart because of a pending reboot from Windows Update. Reboot first; trying to bypass the rule causes trouble later.

Patch to the latest Service Pack and Cumulative Update before creating any databases. After that I only set max server memory, cost threshold for parallelism and a backup schedule.