SQL Server Configuration Manager
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console snap-in that is available from the Start menu, or can be added to any other Microsoft Management Console display. Microsoft Management Console (mmc.exe) uses the SQLServerManager<version>.msc file (such as SQLServerManager13.msc for SQL Server 2016 (13.x)) to open Configuration Manager.
Here are the paths to the last four versions when Windows in installed on the C drive:
SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 (11.x) C:\Windows\SysWOW64\SQLServerManager11.msc
1. Managing Services
Use SQL Server Configuration Manager to start, pause, resume, or stop the services, to view service properties, or to change service properties.
2. To Create an Alias
- In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.
- In the Alias Name box, type the name of the alias. Client applications use this name when they connect.
- In the Server box, type the name or IP address of a server. For a named instance append the instance name.
- In the Protocol box, select the protocol used for this alias. Selecting a protocol, changes the title of the optional properties box to Port No, Pipe Name, or Connection String.
Don't forget to create the alias for 32bit + 64bit if running a 64bit machine otherwise you'll receive a "network error" when opening a connection.
3. Configure a Server to Listen on a Specific TCP Port
The section Configure a server alias to use TCP/IP sockets pointed out that I had to look up the specific port number used by the TCP/IP protocol:
Here's how you find the port number that's being used by TCP/IP on your machine:
- Open the SQL Server Configuration Manager.
- Expand SQL Server Network Configuration and select Protocols for <INSTANCE_NAME>.
- Double-click on TCP/IP and make sure Enabled is set to Yes.
- Remember whether Listen All is set to Yes or No and switch to the IP Addresses tab.
- Now, if Listen All was set to Yes (which it was for me), scroll down to the IPAll section at the very bottom of the window and find the value that's displayed for TCP Dynamic Ports.
- If Listen All was set to No, locate the value of TCP Dynamic Ports for the specific IP address you're looking for.
You'll have to copy this port number into the Port No field when you're configuring your alias:
4. SQL Services
SQL Server 2008 and SQL Server 2008 R2:
Common Name | Service Display Name | Service Name | Executable Name & binary path |
Distributed Transaction Coordinator (DTC) | Distributed Transaction Coordinator | MSDTC | msdtc.exe |
SQL Server | SQL Server (MSSQLSERVER) SQL Server (Anurag) | MSSQLSERVER MSSQL$Anurag | C:\Program Files\Microsoft SQL Server\MSSQL11.AN2012\MSSQL\Binn\sqlservr.exe |
SQL Server Active Directory Helper | SQL Active Directory Helper Service | MSSQLServerADHelper100 | C:\Program Files\Microsoft SQL Server\100\Shared\SQLADHLP.EXE |
SQL Server Agent | SQL Server Agent (MSSQLSERVER) SQL Server Agent (Anurag) | SQLSERVERAGENT SQLAgent$Anurag | C:\Program Files\Microsoft SQL Server\MSSQL11.AN2012\MSSQL\Binn\SQLAGENT.EXE |
SQL Server Analysis Services | SQL Server Analysis Services (MSSQLSERVER) SQL Server Analysis Services (Anurag) | MSSQLServerOLAPService MSOLAP$Anurag | C:\Program Files\Microsoft SQL Server\MSAS10_50.AN2008\OLAP\bin\msmdsrv.exe |
SQL Server Browser | SQL Server Browser | SQLBrowser | C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe |
SQL Server Full Text Search | SQL Full-text Filter Daemon Launcher (MSSQLSERVER) SQL Full-text Filter Daemon Launcher (Anurag) | MSSQLFDLauncher MSSQLFDLauncher$Anurag | Server\MSSQL10_50.AN2008\MSSQL\Binn\fdlauncher.exe |
SQL Server Integration Services | SQL Server Integration Services 10.0 | MsDtsServer100 | C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ MsDtsSrvr.exe |
SQL Server Reporting Services | SQL Server Reporting Services (MSSQLSERVER) SQL Server Reporting Services (Anurag) | ReportServer ReportServer$Anurag | C:\Program Files\Microsoft SQL Server\MSRS10_50.AN2008\Reporting Services\ReportServer\bin \ReportingServicesService.exe |
SQL Server VSS Writer | SQL Server VSS Writer | SQLWriter | sql |
Integration Services not have dump dir, only has binary path:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ MsDtsSrvr.exe
SQL Server & SQL Server Agent : Dump directory:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS\MSSQL\LOG\
SQL Server Analysis Services: Dump directory:
C:\Program Files\Microsoft SQL Server\MSAS10_50.SS\OLAP\Log
Reporting services, dump dir:
C:\Program Files\Microsoft SQL Server\MSRS10_50.SS\Reporting Services\LogFiles\
SQL Server Browser: Dump directory
C:\Program Files\Microsoft SQL Server\MSAS10_50.AN2008\OLAP\Log
5. Available Network Protocols
SQL Server supports Shared Memory, TCP/IP, and Named Pipes protocols.
Protocols
When an application communicates with the SQL Server Database Engine, using communication protocol, such as TCP/IP or Named Pipes etc.
When an application communicates with the SQL Server Database Engine, using communication protocol, such as TCP/IP or Named Pipes etc.
SQL Server can be configured to support multiple protocols simultaneously, coming from different clients. Each client connects to SQL Server with a single protocol.
The following protocols are available:
• Shared Memory
The simplest protocol to use, with no configurable settings. Clients using the Shared Memory protocol can connect only to a SQL Server instance running on the same computer, so this protocol is not useful for most database activity.
• Shared Memory
The simplest protocol to use, with no configurable settings. Clients using the Shared Memory protocol can connect only to a SQL Server instance running on the same computer, so this protocol is not useful for most database activity.
• Named Pipes
A protocol developed for local area networks (LANs). A portion of memory is used by one process to pass information to another process, so that the output of one is the input of the other.
A protocol developed for local area networks (LANs). A portion of memory is used by one process to pass information to another process, so that the output of one is the input of the other.
• TCP/IP
The most widely used protocol over the Internet. TCP/IP can communicate across interconnected networks of computers with diverse hardware architectures and operating systems.
• Virtual Interface Adapter (VIA)
A protocol that works with VIA hardware. This is a specialized protocol; configuration details are available from your hardware vendor.
6. Changing the Accounts Used by the Services
As an additional benefit, passwords changed using SQL Server Configuration Manager, SMO, or WMI take affect immediately without restarting the service.
