From the course: Microsoft SQL Server 2016 Essential Training

Using SQL Server Configuration Manager - SQL Server Tutorial

From the course: Microsoft SQL Server 2016 Essential Training

Start my 1-month free trial

Using SQL Server Configuration Manager

“

- [Instructor] In this section, we're going to work with SQL server configuration manager. So off the start menu, I'll just type in configuration and one of my options is SQL server 2016 configuration manager. I'll click on that and if you get the user account control, click yes. Inside of configuration manager, the first thing we see is every SQL related service running on our machine and we have the option to stop, start, or restart any of these. Notice they are named along with the name we created for our SQL server instance. So in the installation, I called mine SQL 2016 ENT. And we see services related to that. I can right click on this and stop, pause, restart. In SQL server, when you make a major configuration change, you will sometimes be given a message saying the configuration change will not take effect until you restart your SQL server service. And here is one of the tools you could use for that. So I'll hit restart, usually doesn't take very long. When we right click one of these services, we can also go to properties and see quite a number of things we could edit related to the account, the service type. These are things you very rarely change. You probably wouldn't change these things unless you were in a troubleshooting scenario. So as long as your SQL server is working properly, I would advise don't change any of this. In the left-hand menu, we see some additional options. We have two options for SQL server network configuration and SQL server client configuration. For each of these, one of the options is 32-bit and the other option is 64-bit. Being I'm on a 64-bit operating system, I'm going to work with the 64-bit section of these tools. The first one I'll look at is SQL native client 11 configuration. If I expand out this menu item, I see client protocols. And there are three client protocols, shared memory, TCPIP, and named pipes, they are all enabled. So these are the protocols that my client will use to connect to a SQL server. So when I open up something like SQL management tool, that is a client tool and it is connecting to a SQL server either locally or on another physical machine. How is it going to make that connection? Again, we have three options. The top one, shared memory only works when the client is installed on the same machine as the database engine. And rather than use a network protocol, they just both access the same part of memory and that's how they can communicate. If the client is not installed on the same machine as the database engine, then we will have to use a network protocol. The preferred network protocol is TCPIP, it will work in almost all situations. There are a few tools that don't recognize TCPIP and those would need to use named pipes, which is another network protocol. Typically, if you have the option to use either, TCPIP is going to be your more reliable protocol. So again, that is the protocols for how my client will attempt to contact my server. I can also look at the SQL server network configuration and see the protocols for SQL 2016 ENT. And this will tell me what protocols the database engine is listening for. So in other words, the database engine will respond to these protocols. The default for SQL server is to have shared memory enabled for local access, TCPIP enabled for network access, and named pipes disabled. Again, if you have a specific tool that has to use named pipes, you could come right here, right click and go to enable. But the default status is for that to be disabled so I usually leave it disabled. So again, this is SQL server configuration manager. It's one of the best ways to stop and start our SQL server services. And it allows us to set up the protocols used for both the client and the server.

Contents