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.

• An instance manages few system databases and one or more user databases.


• A Server/Computer can run multiple instances


• There can be only one instance as default instance. The default instance has no name. Other Instances are called Named Instances


• A connection request must specify both the computer name and instance name in order to connect to the instance. 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.

Starting SQL Server instance using command line

You must locate sqlservr.exe on your server. By default, sqlservr.exe is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn. If there are more than one instance running, another copy of sqlservr.exe is located in a directory such as 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, SQL Server will start the version of the incorrect instance as well, including service packs, and this may lead to some unexpected results. To avoid, always use the correct & similar version sqlservr.exe to bring Sql server up.

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 "MACHINENAME\OLDINSTANCENAME"

go

EXEC master.dbo.sp_addserver "MACHINENAME\newINSTANCENAME", "local"

Go

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

Database Engine Instances (SQL Server): A Quick Refresher