I've never heard anyone say they want to be a Database Administrator when they grow up. We just end up here and many of us love it enough to stick with it for the bulk of our career. Coming into SQL Server administration usually doesn't come with an instruction manual - more often than not we're left to our own devices when it comes to training. That's precisely how I had my start and I made a lot of mistakes in the process. That's why I created this series and the companion presentation I giving at IT/Dev Connections in 2016. I've already covered backup and restores as topics. Now I want to discuss the primary tool to configure your SQL Server installations: SQL Server Configuration Manager.
Overview
The SQL Server Configuration Manager is a interactive application for managing all of your SQL Server-based services, networking protocols, listener ports, and creating server aliases. You access SQL Server Configuration Manager (SSCM) through Start \ Microsoft SQL Server 20xx \ Configuration Tools \ SQL Server Configuration Manager in versions of Microsoft Windows prior to Windows 8 or Windows Server 2012. From those versions of the OS simply search on SQL Server and select SQL Server Configuration Manager from the list of returned applications. When first viewing SSCM it will look similar to this (using SQL Server 2014 as an example for this article).
Configuration Actions
Let's look a bit closer at the options for actions afforded us in SSCM. Each entry on the left panel represents and provides for one or more tasks you can accomplish through use of SSCM. In some cases you'll see options for both 64-bit and 32-bit. For sake of this article we'll focus on the 64-bit option. It's now 2016 (or later depending upon when you're reading this) and if you're still hosting editions of Microsoft SQL Server on 32-bit servers then you're:
A. Using an older version of SQL Server
B. Starving your SQL Server instance of valuable RAM most likely.
These are the actions available through SSCM:
SQL Server Services
Allows for starting, stopping, restarting all services associated with Microsoft SQL Server. Also allows you to change service accounts, startup behavior, and additional advanced features and startup parameters depending on the service.
SQL Server Network Configuration
This action provides for enabling or disabling specific networking protocols: Shared Memory, Named Pipes, and TCP/IP as well as configuring advanced settings for each of those protocols.
SQL Server Native Client Configuration (Currently v. 11.0)
This action presents the ability to set the order in which clients will use the specifically-enabled protocols to connect to the instance of SQL Server you're configuring. It also allows for you to create aliases for the SQL Server instance for benefit of allowing different end user applications to connect with different server names than the actual server name should you not be able to change connection strings when migrating applications' databases but yet you want to maintain continuity or if you want to obfuscate the actual server name from end users.
Now we're going to go deeper into each of these actions.
SQL Server Services
The SQL Server related services can (and should based upon best practice) be controlled and configured all from within SSCM and not the services.msc API. As previously stated we are able to control not just the startup behavior and service account for these services but also advanced options for each service.
SQL Server Integration Services: No advanced options configurable.
SQL Server Analysis Services: No advanced options configurable.
SQL Server Service:
FILESTREAM: enable or disable FILESTREAM T-SQL Access, File I/O Access, set FILESTREAM share name, and remote client FILESTREAM access.
AlwaysOn High Availability: enable and disable AlwaysOn Availability Groups as well as set the Windows Failover Cluster (WFCS) the AG is built upon.
Startup Parameters: SSCM allows you to set specific startup parameters that take effect globally for the instance. You'll always have at least three startup parameters configured for any SQL Server instance to allow for the proper startup of Microsoft SQL Server:
- -d: specifies the location of the master database's data file (.mdf)
- -l: specifies the location of the master database's transaction log file (.ldf)
- -e: sets the location of the instance's error log
Why these three parameters? Because master serves as the front-facing "brains" of the SQL instance working in conjunction with the hidden Resource database to provide all necessary metadata needed to start up the SQL Server instance. The error log needs to be available to log each step in the startup process.
Additional parameters are available to be set for such things as starting the server in single-user mode for troubleshooting (-m), starting the instance with minimal configuration settings set (-f) for such cases as needing to bypass a poor setting that may cause the instance to not be responsive, and setting Trace Flags that alter the base behavior of Microsoft SQL Server (-T). I can speak to having to use -f to bypass poor settings thinking back to when I was attempting to present memory constraint issues in a demo and setting the max server memory setting so low that I was unable to run SQL Server. A good lesson learned! Of course most DBAs will set a handful of Trace Flags over the course of their careers on each of their SQL Servers via the -T parameter but I'll save that discussion for another article on another day.
For a complete list of startup parameters I'll point you to the official Microsoft documentation as the list is extensive.
Advanced: the Advanced tab for the SQL Server service presents the ability to change the Dump Directory for the instance as well as to set feedback reporting capabilities back to Microsoft for future product enhancement. It also provides you with read-only view into a variety of additional settings as shown below:
SQL Server Reporting Services: While you're able to control a few base service settings for SQL Server Reporting Services (SSRS) there is a completely separate (and dedicated) interface for that service. I don't advise using SSCM for any configuration aspects of SSRS even though some are exposed.
SQL Server Browser: outside of the startup behavior and service account there are not many additional configuration settings you can control for this service other than the dump directory and whether error logging is enabled. It's advisable that this service is disabled unless you have multiple instances of SQL Server on the same host.
SQL Server Agent Service: Similar to many of these other services you're only able to configure dump directory, error logging, and feedback reporting in addition to startup behavior and the service account.
SQL Server Network Configuration
The SQL Server Network Configuration action allows you to enable any or all of the three networking protocols available to Microsoft SQL Server: Shared Memory, Named Pipes, and TCP/IP. Shared Memory and Named Pipes are going to allow for local machine access to SQL Server while the later controls how networked devices communicate to the SQL Server instance. Yes, Named Pipes can be used in a Windows environment but you do lose any benefit of bypassing the network stack in using Named Pipes between remote servers. The Shared Memory configuration options start and end with enabled status. Named Pipes, in addition to enabled/disabled status also allows you to set the pipe name for SQL Server. Lastly, TCP/IP allows the ability to enable or disable this protocol in addition to changing the port upon which SQL Server listens for requests.
SQL Server Native Client Configuration
This action set allows you to set the order of precedence in which SQL accepts requests. It also provides the ability to disable these protocols for the client as well as set pipe name and port number.
Conclusion
The SQL Server Configuration Manager interface is a critical component for setting how your SQL Server instances behave and interact with those machines and clients that send requests to the databases hosted on the instance. It controls how the instance starts and the security for the instance at its very base level through controlling the service accounts that own the various components of Microsoft SQL Server. It's the first line of defense from a security standpoint for SQL Server and understanding how to properly use SSCM and what possibilities exist for configuration through its use is vital for accidental and estabilished Database Administrators alike.
