Database Engine Instances (SQL Server): A Quick Refresher


SQL Server instance of the Database Engine is nothing but a copy of the sqlservr.exe executable that runs as an operating system service. This means for each running SQL Server instance you must see one sqlservr.exe in task manager. Like in screenshot.

SQL-Instance

  • An instance can manage six system databases.
    • An instance can manage one or more than one user databases.
  • Multiple instances can be hosted on a server
  • There can be only one instance as default instance. The default instance has no name. Other Instances are called Named Instances on the same server.
  • A connection request must specify both the computer name and instance name in order to connect to the instance, mentioning port is always good idea. Default instance works with (.) as well.

Instance ID – By default, instance name is used as the Instance ID. Installation directories and registry keys are identified by Instance ID. For a default instance, the instance name and instance ID will always be MSSQLSERVER.

How to start SQL Server instance using command line

Locate sqlservr.exe on your server – if you dont know, just search for it. If there isn’t any change, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. You may start one instance of SQL Server by using sqlservr.exe from another instance (in case if there are multiple instances installed), SQL Server will start the version of the incorrect instance as well. Remember, this may lead to something unexpected. To avoid, always use the correct & similar version sqlservr.exe to bring Sql server up.

I always perfer to use windows services however, you may use some commands like below, from a command prompt:

  • Run sqlservr.exe to start default instance
  • Run sqlservr.exe -s <instancename> to start named instance.

Renaming SQL Server Instance

The general recommendation is that you can change an instance name if it is the default instance, but modifying an existing named instance is not recommended. One should always uninstall and install. However in case if you need to rename – you can use below procedures:

Open Management Studio, connect to SQL server and then run

SELECT @@servername
EXEC master.dbo.sp_dropserver 'OldMACHINENAME\INSTANCENAME'
go
EXEC master.dbo.sp_addserver 'NewMACHINENAME\INSTANCENAME', 'local'
Go

Restart SQL server services and you should see the new name. I hope this refreshes your concepts about SQL Server instance.