SQL Server instance

Question

I am trying to create a local instance. When I issue a command ssms.exe from the below directory, it always starts SQL Server 2008 which is also installed on my computer. It never starts SQL Server 2012 Management Studio. SQL Server 2012 is installed on my computer and we are using this version, not SQL Server 2008.

The directory path is

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

When I do this

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\> sqlcmd -S            (localdb)\MyInstance
 1> SELECT @@VERSION;
 2> GO

I get the below information

Microsoft SQL Server 2012 - 11.0.2318.0 (X64)
Apr 19 2012 11:53:44
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Is there any way, I can start SQL Server 2012 instead of 2008?

Answer 1

SQL Server - when installed as a server - is most likely already running. But if you have two (or more) instances side by side, only one of them is the default instance - any other needs an explicit instance name that you also need to use when connecting.

Go to SQL Server Configuration Manager (from your start menu) and have a look at what "SQL Server" services are installed - something like this:

On my machine, I have two instances - one is MSSQLSERVER which is the default instance to which I connect using just the machine name (or . or (local) for the local machine) - this would be your SQL Server 2008 installation.

The other installation is SQLEXPRESS which is also installed and running, as you can see in SQL Server Configuration Manager. To connect to this version (that would be the SQL Server 2012 Express on your server), you need to specify the instance name:

Data Source=.\SQLEXPRESS
Data Source=(local)\SQLEXPRESS
Data Source=YourServer\SQLEXPRESS

With this, you're now connecting to and using SQL Server 2012 Express - and this works for SSMS as well as your own code.

Answer 2

The SQL Server database engine and the SQL Server Management Studio (SSMS) are two completely different programs (executable files).

The SQL Server database engine runs as a Windows service, and if you have multiple SQL Server instances installed, even if they are different versions, each instance is implemented as a Windows executable named "sqlservr.exe" running as a Windows service.

You may or may not have installed SSMS for both versions of the SQL Servers you've installed. Additionally, either of the versions of SSMS you may have installed can connect to either instance of the SQL Server database engines you've installed (SSMS 2008 can connect to SQL Server 2012 database engines and SSMS 2012 can connect to SQL Server 2008 database engines).

All that said, if you have multiple instances of the SQL Server database engine installed, at least one of these instances must be a "named instance". A named SQL Server instance is an instance that's not running under the default name, "MSSQLSERVER".

If you open the Windows Services control panel applet, and look in the list of services on your local computer, you should see "SQL Server ([instance name])" in the list, where [instance name] represents the name of that instance. If [instance name] = "MSSQLSERVER", then this is known as the default instance. There can be only one default instance of SQL Server running on a particular host operating system.

You can also see this, and more, in the Windows task manager. And there you can probably figure out what each instance's database version is. Just go to the "Processes" tab and display the "Command Line" column. It will show you exactly where on disk the process is running from, and that will help you determine what's running.

You can also run the SQL Server Configuration Manager, and it will show you the names and instances of all the various SQL Server components running on your computer, but not necessarily it's version.

To run a particular version of SSMS, specify the full path to SSMS when you start it, such as C:\Program Files(x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe.