From the course: SQL Server 2014 Essential Training
Using SQL Server Configuration Manager - SQL Server Tutorial
From the course: SQL Server 2014 Essential Training
Using SQL Server Configuration Manager
Now that we have successfully installed Microsoft SQL Server 2014, we're going to look at some of the tools used to administer. Developing some skill with these tools is very important. Just like a carpenter can't really call himself a good carpenter unless he knows his way around a saw and a hammer. Likewise a SQL DBA needs to know how to use the tools available to them. But also, just knowing a lot about saws and hammers doesn't make you a good carpenter, and just knowing a lot about these tools doesn't make you a good DBA. It's one of the things you need to learn, not the only thing. The first tool we'll talk about is SQL Server Configuration Manager. So, I'll just type in SQL configuration. And it'll come up there for me, SQL Server 2014 Configuration Manager. When I first open that, we'll see the top item in the tree to the left of SQL Server Services. If I click on that, we'll see all of the running services. And right now I have three, SQL Server, SQL Server Agent, and SQL Server Browser. All of them are currently running. All of them set to start automatically. And we can also see what account they run as. If we right click on any of these, and go to Properties, we'll have the option to change the account they use, the option to stop or restart the service. On the Service tab, we have a little bit of additional information. And then on the Advanced tab, again some more information. But most of the things we would be looking to change would be on the Log On tab. And I use this tool most commonly to restart the services. So, let's go ahead and do that. I'll click restart, and it'll give me a dialog saying stopping service and then shortly after a dialog saying starting service. And there we go, we have successfully restarted. Now click OK on that to close that dialog. The other items in the tree, you notice some of them talk about a client. Remember the machine we are on right now is a SQL server, but it also has the tools installed to act as a client to other SQL servers. Right now I'm not real interested in configuring the client. I am instead interested in configuring the SQL server. So, we have two of those, SQL Server Network Configuration (32bit), which for me when I click on it is blank, because I do not have any 32bit SQL Server software installed. So, I'll have to go to the other one, SQL Server Network Configuration and then that'll tell me about the protocols for ENTERPRISE2014. ENTERPRISE2014 is the name of the SQL server instance installed on this server. It is a 64bit installation. And that is why it did not come up under the 32bit tab. I'll go ahead and double-click on that, Protocols for ENTERPRISE2014. And it shows me three. Shared Memory, Named Pipes and TCP/IP. So these are the three possible ways to connect to my SQL server. Shared Memory only works when the client and the server are on the same physical box. And they'll just read and write the same location in memory. That's good for administration, but obviously doesn't make the server available to the outside world. For that we'll either need to use Named Pipes or TCP/IP. In most situations TCP/IP is preferred, but if you need to run Named Pipes for a specific application, you'll see that it's disabled now. We'll double-click on it, and it'll bring up a very simple dialog box. We have one thing to choose Enabled and its just Yes or No are the options, I'll leave it on No. And then also the Pipe Name. So this is the pipe I would probably need to give to my application that'll want it to talk to SQL Server using Named Pipes. So, I'll hit Cancel there. And then I'll double-click on the TCP/IP protocol. The first part of the dialog is again, pretty simple. Just asking if TCP/IP enabled? Yes or No? I'm going to leave it on Yes. The next tab for IP Addresses is more complex. It's going to give us information for every IP address the machine can find on this server. And some of the properties it allows us to configure is Active and Enabled. Now those kinda sound similar, the difference is Enabled is usually the one that the administrator will configure manually. So, I'll go and set this to either Yes or No manually. Active will typically be set by the machine automatically. So as long as the operating system can communicate with this IP address, it's going to set this to Yes. If the operating system would lose communication with this IP address, it would change the Active field to No. Although you can come in here and override this. Typically we don't change this, we let the machine change this one. As administrators, we change the one that says Enabled. I can also choose a port. The default port for SQL Server is 1433. If I would like to listen on a different port, I can do that here. But typically, I don't. So I'll hit Cancel. You notice with all of this, I'm working on SQL Server Configuration Manager on the local machine. And we see that by the very top line in the tree view saying local at the end of it. If I'd like to, I could connect to a remote machine I could use the same utility to configure a remote machine.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.