Learn how to configure the database engine services for operation and network access.
- [Narrator] With a SQL Server instance installed, we have the ability to work with some of the configuration aspects of the different services and components that make up the instance of SQL Server. So in this video we'll discover what those are and how we can access and configure those for your specific instance of SQL Server. We do so through the use of a tool called the SQL Server Configuration Manager, and there's a couple of different ways for you to gain access to that tool. We can click on the Start button, go to All Apps, and with Windows 10 we can select any letter, which brings up a little alphabet list.
Select M for Microsoft, and find the SQL Server Configuration Manager option here, or in our Cortana search box, we can simply type in SQL Server Manager 13.MSC, which is the SQL Server Manager Microsoft Configuration console. This one presents the user account control and our SQL Server Configuration Manager snap-in opens up. In our left nav tree, we have our SQL Server Services and then we have Network and Client Configurations.
So we'll take a look at each of these in turn, but let's focus on the Services first. Now, when we talked about Link Servers, you saw an example of us coming in here and working with the SQL Server Browser Service in the SQL Engine Service and restarting those services so that we could get the appropriate functionality for the Link Server in place. So let's talk a little bit about some of the other options available. You'll notice that we have some columns that list the name of the service, the current state, whether it's running or stopped.
It could be paused or it could be disabled. And so, as an example if we look at our SQL Server Agent Service, we can see that our start mode is currently set to manual. We can disable that and choose OK, and in doing so, now our start mode shows that we are Other, Boot System Disabled or Unknown and the service is currently stopped. So we're just changing the start mode for that specific service. You'll also see the Log On As column, which tells us what account that specific services is using.
And if you like to see the process IDs and you're a command line junkie and you want to turn them off via the of the command line, this is what the process ID would be if you wanted to issue those commands. And then finally, the Service Type column is listed over here. You also have the option to click on the view and choose Add/Remove Columns, and if you want to clean up the screen, you can actually get rid of some of the columns that are displayed. Currently this is displaying all of the columns. We can also double click each service in turn and look at the dialog box that focuses on the properties for that.
Now the Log On tab is the first one that is displayed, and in here we can see that our SQL Server Browser Service is using a local service account. Again, this is a typical default from the install and we haven't modified anything on this system. You can also choose This Account and then select a different account for this service to run under, so it gives it a different security context. Clicking on the browse button, brings up the Select User or Group dialog box, in which case you can select Local Users, or if this machine was connected to a domain, you can actually provide a domain account in here as well.
For the time being, we'll leave it at the built in local account. Also, you'll see the status of the service down here and you can stop, pause and restart from this dialog box. You can look at specific information around the service itself and again manipulate the start mode, and in the Advanced tab, you can tell whether the service is active, if it's part of a clustered environment, where the directory is for error reporting and dumps. Each one of the services will look exactly the same, so we won't go through each and every one of them. But let's focus now a little bit on our SQL Client and Network Configuration.
You'll notice that there's multiple entries. One for 32-bit, for each of these, and the one that is not listed for 32-bit is basically your 64-bit install. The protocols that we have available for the network configuration are Shared Memory, Named Pipes and TCP/IP. You can change the status of either one of these by double-clicking and either enabling it or disabling it. Some of them have a little more configuration options such as a Pipe Name. You should always leave these at the default. Otherwise if you have linked servers or if you have other servers trying to connect to it, this will change what it is they're using for a connection name.
And TCP/IP actually has a Keep Alive and a Listen All. So if you select each one of these options, you'll get an explanation in the lower part of the dialogue that tells you what those are used for. Again, keep in mind that Named Pipes are disabled by default and you would have to enable those for that Link Server functionality that we focused on, on the previous video. And finally, for the Client Configuration, we have the three different protocols available, here again as well. And these are for your SQL Native Client, so there are similar options for the shared memory.
You'll see the default port, which SQL Server requires to be open on firewalls. You'll see whether the service is enabled, and again your Keep Alive and Keep Alive Intervals. And finally Aliases. There are currently no aliases, but you can create a new Alias for this specific native client configuration, specifying a new port, a protocol and a specific server. So again, just a quick rundown of how you can work with and manage the services and a client configuration for your instance of SQL Server through the SQL Server Configuration Manager.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options