Finding server and instance name

Question

I want to know how to find the server and instance name on my computer to connect to the server.

I am using the SQLCMD utility, and I am currently able to connect to the server with the line

>SQLCMD -S "(localdb)\mssqllocaldb"

I want to know if the server and instance name, "(localdb)\mssqllocaldb", is information that is listed anywhere I can access on my computer.

I'm trying to configure another computer to connect its own local server in the same way I am able to connect to (localdb)\mssqllocaldb, and I want to know what "(localdb)\mssqllocaldb" should be on their computer so that they can connect to it with SQLCMD.

Answer 1

If you're really using LocalDB and haven't just named a regular SQL Server instance as if you were, there are definitely some extra hurdles in establishing connectivity from remote machines. This feature is targeted primarily at local, isolated development.

I wrote a pretty lengthy article on LocalDB, including how to get over some of the connectivity hurdles, in this tip (and there is likely some valuable information in some of the comments, too):

• Getting Started with SQL Server 2012 Express LocalDB

More recently, I added a tip to handle a more modern version, which I highly recommend if for no other reason that most of the connectivity hurdles have been reduced or eliminated:

• Getting Started with SQL Server 2017 Express LocalDB

I would strongly recommend installing a proper instance of SQL Server (even if only the Express Edition), which can be set to always be running (no matter who is or isn't logged into the machine), does not have any of these additional connectivity hurdles, etc.

Answer 2

For the server Name:

SELECT @@SERVERNAME;

For the instance Name (Service Name):

SELECT @@SERVICENAME;

• jmk22: I can type that command into SQLCMD, but I want to find the server name on a separate computer. Is there any way to find the servers on a computer if you're not already connected to the server?
• agpoweredmg: @jmk22 you can try nslookup <IP address>

Answer 3

If your computer is known as localdb (though I doubt it) then the other computer should be able to connect to it across your LAN (depending on firewall rules and other network configurations). Otherwise, you will need to specify the IP address or your computer name (however it is recognized over the network) and then the instance name (mssqllocaldb) or the port number.

Examples

SQLCMD -S 127.0.1.12\mssqllocaldb
SQLCMD -S Hostname,PORT

Answer 4

I usually write my scripts for this stuff in Powershell and pass it in as a variable. In this Powershell script we do not use SMO or load any extra assemblies. This is compatible with Powershell 2+.

This script will:

-get your service account. If you have multiple services you will have to add a loop but that wasn't mentioned.

-Remove the MSSQL$ from the service acct name

-Save that as a variable.

-Passes it into the OSQL cmd. I found OSQL more used by MS than SQLCMD fro mmy experience, so I started using it too.

For example:

$SQLServer = '.' ##Set Server Name
$SQLService = Get-WmiObject -ComputerName $SQLServer win32_service | where {$_.name -like "MSSQL*"} #Find instance info from services.
$SQLInstance = $($SQLService.name) #Set name
$SQLInstanceName = $SQLInstance.substring(6) ##Remove MSSQL$ from service name. This is a ghetto method and the best I could do in 5 mins.
write-output "Powershell output- SQL Instance Name is: $($SQLInstanceName)"
##Query information## 
$Query = "set nocount on; select 'SQL SVC: ' + @@servicename; select 'SQL Server: ' + @@SERVERNAME"
Write-Verbose "SQL Query to be executed: '$($Query)'"
osql -E -S "$Server\$SQLInstanceName" -Q $Query -m 10 -h-1 ##Your command.

Answer 5

I have localdb installed as it came with Visual Studio, and I was able to find the instance name (MSSQLLocalDB) with the command > SQLLOCALDB INFO