Configuring Network Protocols from SQL Server configuration manager
SQL Server 2008 is a client-server application designed to efficiently exchange data and instructions over one or more network connections.
SQL Server 2008 Network Protocols
SQL Server 2008 provides support for four protocols:
· Shared Memory
· TCP/IP
· Named Pipes
· Virtual Interface Adapter (VIA)
By default, the only network protocols enabled for most editions of SQL Server are TCP/IP and Shared Memory. The Developer and Enterprise Evaluation editions are configured with all protocols except Shared Memory disabled during installation, but the remaining protocols can be enabled if required. If a protocol is not enabled, SQL Server will not listen on an endpoint that is configured to utilize that protocol.
The SQL Server Configuration Manager is used to configure server protocols.
Shared Memory
The Shared Memory protocol can only be used by local connections, because it is a shared memory and process space used for inter-server communication. It has only one configurable property: Enabled. The Enabled property can be set to Yes or No, resulting in a status of Enabled or Disabled.
Named Pipes
Named Pipes uses Inter-Process Communication (IPC) channels for efficient inter-server communication, as well as local area network (LAN) communication. The Named Pipes protocol has some enhancements in SQL Server 2008 including support for encrypted traffic, but because of the excessive overhead of Named Pipes when connecting across networks or firewalls, and the additional port that Named Pipes requires to be opened (445), it is generally a good idea to leave the Named Pipes protocol disabled. However, there are many applications that take advantage of the Named Pipes protocol because they were designed for local network implementations. Named Pipes provides easy access to Remote Procedure Calls (RPC) within a single security domain, and so is advantageous to these applications. If you need to support one of these applications, and the SQL Server is not exposed to external traffic, the risk of enabling the Named Pipes protocol and corresponding endpoint is minimal.
Named Pipes has two configurable properties: Enabled and Pipe Name. The Enabled property works the same as the Shared Memory protocol. The Pipe Name specifies the inter-process pipe that SQL Server will listen on. The default pipe is \\.\pipe\sql\query.
TCP/IP
The TCP/IP protocol is the primary and preferred protocol for most SQL Server installations. It is configured on two separate tabs on the TCP/IP Properties window: the Protocol tab and the IP Addresses tab, as shown
The Protocol tab has the following four configurable properties:
· Enabled — This works the same as the other protocols.
· Keep Alive — This specifies how many milliseconds SQL Server waits to verify an idle connection is still valid by sending a KEEPALIVE packet. The default is 30,000 milliseconds.
· Listen All — This specifies whether SQL Server will listen on all IP addresses configured on the server.
· No Delay — This option specifies whether the TCP protocol queues small packets to send out larger packets. This queuing is typically undesirable in transaction-based systems, and so it should be left in its default configuration of No.
Surface Area Configuration for services and Connections.
There are two ways to view the various services and connections on a given server. The first is by instance, and this is usually the easiest method.
However, if you have multiple components on the same server, such as multiple instances of the database engine, it may be easier to group by component, which is what the second tab (View by Component) is for:
Once you've settled on your view, the next step is to actually configure each component. Every component listed will have a Service option where you can configure the startup status for the service as well as control the current state of the service itself. For instance, the following image is of the MSSQLSERVER database engine service, which is set to Automatic, meaning it will start when the computer starts up.
You have the ability to set any of the following startup types:
· Automatic - The service will start when the computer starts up.
· Manual - The service is capable of being started, but must be started manually by an authorized user (Power Users or Administrators local group membership).
· Disabled - The service cannot be started unless the startup type is changed.
In addition to the service configurations, the Database Engine and Analysis Services have additional options. For the database engine, there is the Remote Connections configuration.
As the highlighted section shows, you can configure the database engine either to listen only for local connections (originating from the same computer as SQL Server) or to listen for both local and remote connections. In addition, if you choose for SQL Server to listen for remote connections as well, you'll have the choice whether to use TCP/IP, Named Pipes, or both. In general, for network connections, you'll want to use TCP/IP. In my experience, Named Pipes sometimes suffers from timeout issues that you don't get with TCP/IP.
The Dedicated Administrator Connection
In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.
The DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions.
Enable Dedicated Administrator Connection in SQL Server 2008 Using TSQL
Execute the below T-SQL to enable remote clients to utilize the Dedicated Administrator Connection.
Use master
GO
sp_configure 'show advanced options' , 1
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO
|
Enable Dedicated Administrator Connection in SQL Server 2008 Using SQL Server 2008 Management Studio
Database Administrators can also enable Dedicated Administrator Connection Feature using SQL Server 2008 Management Studio. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below.
This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.
Finally, click OK to save the configuration changes in the View Facets window.
You can also enable other database engine features like AsHocRemoteQueriesEnabled, ClrIntegrationEnabled, DatabaseMailEnabled, OleAutomationEnabled, ServiceBrokerEndpointActive, SoapEndpointsEnabled, SQLMailEnabled, WebAssistanceEnabled, XPCmdShellEnabled etc when required using the Surface Server Configuration Facet which is available in SQL Server 2008 Management Studio.
Once the Dedicated Administrator Connection is enabled you can connect to SQL Server 2008 using either SQL Server Management Studio or using SQLCMD.
Using DAC with SQL Server Management Studio
You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.
Once you are connected to SQL Server Instance using DAC, then you can execute code such as the code below to check the SQL Server health.
-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO  |
