Introduction
SQL Server Configuration Manager is a part of SQL Server Suite, allowing us to configure the options external to the database engine, and options that control the aspects of SQL Server even when that SQL Server is down. The current article reviews some of these configurations.
SQL Server Startup and Shutdown
You can start/stop SQL Server with the services.msc command in the command-line interface or with SQL Server Configuration Manager.
To launch services.msc, type this expression in the Run menu (or Start Menu) and click OK or ENTER. It will display all services that are running on your machine, including SQL Server.
Services.msc is visual. Therefore, you can control startup and shutdown as simply as clicking START, STOP, or RESTART as desired:
By exploring properties of the SQL Server Service, we see important details, such as the service description, the path to the executable (Figure 3), the startup credentials, and service dependencies. As we shall see later in the article, these aspects are also exposed in SQL Server Configuration Manager.
Using the Windows Command Line Interface, one can control SQL server startup with the NET command or by using the SQL Server, that is executable itself (sqlservr.exe) as shown in Figures 3 and 4:
Important: When you apply the NET command, the SQL Server stop will also stop the SQL Server Agent. The same behavior takes place when we use Services.msc.
The cause of this is the dependency relationships between SQL Server and SQL Server Agent (Figure 5):
Starting SQL Server, we must also start the SQL Server Agent. Otherwise, we can check the tasks the next day only to find out that Agent jobs have not been run.
Also, you can control your SQL Server by SQL Server Configuration Manager.
To start, stop or restart SQL Server or any of the other services associated with an installation, simply right-click the service to display a drop-down menu showing the list of commands and then click the command.
SQL Server Service Properties
SQL Server Configuration Manager allows doing much more than starting and stopping the service.
To view SQL Server services properties, right-click the service, and select Properties from the drop-down menu. You’ll see the credentials used to start the service at once (Figure 9). Note that this option also shows up in services.msc.
If you ever need to change these credentials, you should do it in the SQL Server Configuration Manager.
SQL Server Service and Startup Information
The Service tab informs us about the service state and the path of the sqlservr.exe binaries. It also allows us to configure the Start Mode:
More importantly, by using the Startup Parameters tab (Figure 11), we can do such sophisticated things as changing the master data file location and introducing Trace Flags.
Also, you can set the startup parameters, such as -m for single-user mode. Notice the three entries in the Startup Parameter tab coming by default:
- The location of the master data file is (labeled “-d”).
- The error log file (labeled “-e”) location.
- The master database transaction log file (labeled “-l”) location.
Advanced Options
The Advanced tab provides a lot more information about how SQL Server is configured. We can change a few important settings, such as Instant File Initialization and Lock Pages in Memory. Also, we get critical information on whether the instance is clustered or not.
The essential thing is that everything we see we can change even when the SQL Server instance is down.
Network Settings in SQL Configuration Manager
There are many network-related settings we can configure for SQL Server using the SQL Server Configuration Manager. They include Client Protocols, Aliases, Server protocols, connection encryption, and so forth.
One more option is worth mentioning: we can change the port number a SQL Server instance listens on. to do this, we navigate to
SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP > Properties > IP Addresses
Also, we can determine which order SQL Server will apply when accepting connections.
The default order is to try Shared Memory first, then TCP/IP the Named Pipes. Giving priority to TCP/IP may help performance in terms of connections in certain scenarios.
Enabling Force Encryption is not the only action required to configure encryption. We’ll cover the description of the full steps in another article. Important: Most changes made on SQL Server network settings on Configuration Manager require a restart of the instance. When one thinks about it, it does make sense.
Conclusion
This article explored the most important options available in SQL Server Configuration Manager and related utilities. We highlighted the common tasks’ performance, like starting and stopping SQL Server and enabling features like encryption and trace flags. Detailed discussions on these topics are reserved for further articles; however, our references would be a good starting point.
