In its default configuration, SQL Server is always running in the background of the system. In order to manage the settings or stop and restart the server, use SQL Server Configuration Manager.
- [Instructor] In it's default configuration, SQL Server is always running in the background of your system, ready for users to connect to it and retrieve, or write data to the databases that it contains. In order to manage the settings, or start and stop the server, we'll use a program called SQL Server Configuration Manager. Now I have Configuration Manager pinned to my Start menu, so I'll go down here, and find it there. Let's go ahead and allow it to make changes to my device. Then, on the left side of my screen, I have this tree view underneath SQL Server Configuration Manager for my local machine.
At the to is SQL Server Services. And when you click on it, you'll see the three services that are currently installed on my local computer here on the right. Let's go ahead and expand these columns open a little bit by clicking and dragging on the bars between them. The three services that we have are the server itself, the agent, and a browser. In the State column, we can currently see that my server itself is currently running. The other two are stopped. And that the server is currently set to automatically start when the computer starts up. On the toolbar at the top, we have buttons that will affect the currently selected service.
For instance, I can start a service, pause a service, stop the service, or restart a service. Occasionally, changing a setting will require the server to be restarted. And this is one of the place that you can go to do that. To get more information about the server, go ahead and right click on it, and then come down to Properties. In the Properties window, we have lots of options that we can configure. But at this point, it's probably best just to leave the default options in place. But you can go ahead and page through the tabs to get a feel for some of the things that are available to you. On the Log On tab, we can control the state of the server.
At the bottom of the tab, we can see that it's current server status is running, and we also have options down here at the bottom to start, stop, pause, and restart, as well. On the Service tab, we have a property called Start Mode, here. We can see that it's currently set to Automatic, but our other options are Disabled or Manual. When it's set to Automatic, this mean that SQL Server will automatically start running when the computer turns on, so that users can begin logging into the server to access the data. If we don't want SQL Server to automatically start, simply change that property to Manual. In this configuration, we'll need to remember to start the server each time we want to access the data within.
I'll leave mine set to Automatic. Go ahead and come down to the bottom, and press the OK button to dismiss that window. The SQL Server agent is currently stopped. We can go ahead and take a look at it's properties the same way. Just select it, right click on it, and choose Properties. The agent is used to automatically run administrative jobs, such as performing scheduled backups, or optimizations on the database without us having to initiate the process each time. I don't have any jobs yet defined yet for my server, so let's go ahead and just leave the agent stopped for now.
Go ahead and press the OK button. The SQL Server browser is another service that essentially listens to network traffic for incoming requests and returns information about the resources available on this instance of SQL Server. This allows remote users to log into the server to gain access to the data within. Now at this point, I'm not expecting any remote users to need to log into my instance. So, I'll go ahead and leave the browser stopped, as well. Back in the tree, we have two options for configuring client options. We have SQL dative client 11.0 configuration for 32 bit systems.
And the same one for 64 bit systems. This will help me connect to remote servers on other computers. We also have an option to configure 32 bit instances of SQL Server on my machine under here. SQL network configuration for 32 bit machines. Now I don't have any, so it's collection is empty. But finally, we have the SQL Server network configuration for 64 bit options. Inside of here, we have some protocols for the current server that we installed, MSSQL Server. Let's go ahead and expand this open. Once again, we can right click to get the properties for our instance.
I'll right click on it, and go down to Properties. And inside of here, we have a few options that we can alter, if we desire. Such as, the ability to enable encryption. Now let's go ahead and press the OK button to back out without making any changes. So that's a quick tour of the SQL Server Configuration Manager. For our purposes, the default configuration will work out fine. But as you develop your databases, your usage requirements and hardware configuration might require some adjustments. Most of the time though, the configuration manager is simply used for starting and stopping the server, and to define whether the server will automatically start when the computer boots up.
- Reviewing the different editions of SQL Server
- Configuring and starting the server
- Touring the SQL Server Management Studio (SSMS) interface
- Creating a new database on the server
- Creating a data table
- Joining tables together with keys
- Writing Transact-SQL commands