Using SQL Server Configuration Manager

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 on the Run menu
Figure 1: Services.msc on the Run menu

Services.msc is visual. Therefore, you can control startup and shutdown as simply as clicking START, STOP, or RESTART as desired:

Using Services.msc to Control SQL Server Startup
Figure 2: Using Services.msc to Control SQL Server Startup

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:

MSSQLSERVER Properties
Figure 3: MSSQLSERVER Properties
Logon Details for SQL Server Services
Figure 4: Logon Details for SQL Server Services

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):

SQL Server Dependencies
Figure 5: SQL Server Dependencies
Using NET START/STOP to Control SQL Server Startup
Figure 6: Using NET START/STOP to Control SQL Server Startup
Using sqlservr.exe to Control SQL Server Startup
Figure 7: Using sqlservr.exe to Control SQL Server Startup

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.

Using SSCM to Control SQL Server Startup
Figure 8: Using SSCM to Control SQL Server Startup

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 Startup Credentials
Figure 9: SQL Server Startup Credentials

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:

SQL Server Service Information
Figure 10: SQL Server Service Information

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:

  1. The location of the master data file is (labeled “-d”).
  2. The error log file (labeled “-e”) location.
  3. The master database transaction log file (labeled “-l”) location.
SQL Server Startup Parameters
Figure 11: SQL Server Startup Parameters

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.

“Clustered” status in the Advanced section
Figure 12. “Clustered” status in the Advanced section

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

TCP/IP Properties
Figure 13: TCP/IP Properties

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.

SQL Server Client Protocols
Figure 14: SQL Server Client Protocols
SQL Server Client Protocols
SQL Server Client Protocols
Figure 15: SQL Server Protocols

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.

References

  1. An Introduction to SQL Server
  2. Enable Lock Pages in Memory Option
  3. Database Instant File Initialization
Kenneth Igiri

Kenneth Igiri

Kenneth Igiri is an Enterprise Architect with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over fifteen years' experience in Information Technology with nine of those years focused on SQL Server and Oracle databases. His interests include Database Performance, HADR, Cloud Computing, Data Architecture and Enterprise Architecture. Asides from work, Kenneth teaches at Children's Church, writes faith-based fiction and helps small businesses grow. You can connect with Kenneth via his blog https://kennethigiri.com, LinkedIn, or on Amazon.com.

Leave a Reply

Your email address will not be published. Required fields are marked *