One of the most important aspects of SQL Server administration and maintenance is the ability to review and changing the existing configuration of the server. In Part 01 of this tutorial series, I had mentioned that one of the key responsibilities of SSMS was to replace the erstwhile SQL Server Enterprise Manager – the interface that is used to manage & maintain the SQL Server instance configuration. Today, we will look at some of the windows and wizards that can be used to configure your SQL Server instance.
Reviewing & Changing Instance-level Configuration
Using the Object Explorer, you can review the instance-level configuration (provided you have sufficient privileges, of course) for all servers that you are connected to. Simply right-click on the instance name and select “Properties” from the pop-up menu to launch the SQL Server instance properties window.
The base or “general” view of the window is a read-only view that provides details on the SQL Server build, hose operating system version, number of processors and memory, SQL Server collation and high-availability configuration values.
Navigating out to any of the child nodes (“Database settings” node visible in the screenshot below), allows us to view two sets of values – the “configured” values, and the “running” values.
- Configured values: These are editable values, and display the configured values for the options on this pane. If you change these values, click Running Values to see whether the changes have taken effect. If they have not, the instance of SQL Server must be restarted first.
- Running values: View the currently running values for the options on this pane. These values are read-only
Below is a summary of the various child nodes in the Server Properties window:
Reviewing & Configuring database-level configuration
Similar to the instance-level properties page, simply right-click on the database name and select “Properties” from the pop-up menu to launch the database properties window. Please note that this window does not have the concept of “configured” and “running” values. Any changes you make are in effect for ever new connection to the database going forward.
The screenshot below shows the “General” page, which displays the following information:
- Last backup information
- Database creation date, status
- Database owner
- Space available in the database
- Current Number of concurrent users connected to the database
- Database collation
Below is a summary of the various child nodes in the Database Properties window:
Scripting changes for future reference
Whenever a SQL Server configuration change is required, it needs to be planned and scheduled for execution during an outage. There too, the team implementing the change may or may not be the same as those recommending the change. In such cases, it becomes essential to script the changes made so that the change can be supplied to the implementation team for:
- Record keeping – for future reference
Scheduled deployment – the required change has been scripted, so it can be applied whenever the outage window permits
- Login to your SQL Server instance using SSMS
- From the object explorer, right-click on the SQL server instance name to bring up the instance properties window
- Go to the “Security” tab
- Assume that we want to switch from “Windows Authentication mode” to "mixed” mode. Do NOT apply the change
- Click on the little arrow near the “script” button at the top
- Choose “Script Action to New Query Window” or press Ctrl+Shift+N
- Notice that a new query window opens up with the underlying change scripted and ready for execution
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
- The change can now be executed during the regular maintenance window for an instance
Practice Exercises…
The properties page is available for every distinct object visible within the Object Explorer.
- Explore the properties for your SQL login
- Define an idle CPU condition for the SQL Server agent using the using the SQL Server Agent properties window
Further reading…