What is Developer Edition?

• SQL Server Developer edition lets developers build any kind of application on top of SQL Server
• It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server

Express vs Developer Edition

• Express Edition: Less features but free to use for production
• Developer Edition: Same features as enterprise edition but can not be used for production

Details about SQL Server Editions: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?#-editions

Installation

Download and install: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Install using chocolatey

choco install sql-server-2019

Enable SQL Server Authentication

• Install SQL Server Management Studio (SSMS):
  • Download https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15#download-ssms
  • Chocolatey: choco install sql-server-management-studio (https://community.chocolatey.org/packages/sql-server-management-studio#install)
• Open SSMS > use windows authentication to connect to locally running SQL Server Developer Edition
• Follow the screenshots below

Server vs Instance

• A server is a host machine (physical machine or virtual machine) hosting SQL Server software
  • Since SQL Server is software like any other, you can have multiple installations
  • Installations can be running on the same box at the same time
• An instance is a collection of SQL Server databases run by a single “SQL Server Service”
  • You’re meant to use instances to partition data and policies
  • Each instance has completely separate databases, connection configuration, and security credentials
  • You can also consider ‘instance’ as ‘SQL Server Service Instance’ or ‘Service Instance’
• A server (host machine) can have multiple instances (running side by side) independently
  • If there are multiple server installations on single machine they are identified by instance name
  • The first instance is the default instance
  • In your connection string use format {server-name}\{instance-name} for non-default instances
• When specifying a local server always use ‘(local)’

Important Points About Instance Name

• Instance name must be set during installation
• It is not possible to change a instance name after it has been installed. The only way to accomplish that is to uninstall SQL Server and then re-install, it specifying an instance name during installation
• Default is “no-name” instance (if name was not given to instance during installation)
• It is not possible to connect using ‘hostname\instancename’ when no named instance was installed. This is by design
• For default (“no-name” instance): just use hostname only for SQL connections (‘Server=(local)’ or ‘Server=MyServer’)
• MSSQLSERVER is just the name of the SQL service after installing default instance, not an instance name. So, “OUR-SERVER\MSSQLSERVER” is not the true instance name and that’s why connections attempts will fail

Get Server Name and Instance Name

T-SQL to get server name and instance name

SELECT @@SERVERNAME
SELECT @@SERVICENAME

--For connection string
SELECT @@SERVERNAME + '\' + @@SERVICENAME