#0133 – SQL Server – SSMS – Tutorial – 08 – How SSMS helps in reviewing and configuring your SQL Server Instance (L200)

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.

image image

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:

Page Brief description Further help from BOL
Memory Adjust the memory allocated to the given SQL Server instance, index creation and query execution operations http://msdn.microsoft.com/en-us/library/ms181453.aspx
Processors Adjust processor/IO affinity, NUMA configuration, lightweight pooling, max. worker threads and sql server process priority http://msdn.microsoft.com/en-us/library/ms189435.aspx
Security Choose the authentication mechanism, audit logging, cross-database chaining, C2 auditing and other security configurations. http://msdn.microsoft.com/en-us/library/ms188470.aspx
Connections Adjust default connection options, query governor, remote connections, distributed transactions and max. concurrent connections http://msdn.microsoft.com/en-us/library/ms180124.aspx
Database Settings Adjust database options – index fill factor, backup set options, compress backups, recovery intervals and default data & log file paths http://msdn.microsoft.com/en-us/library/ms178521.aspx
Advanced Enable Containment & FILESTREAM, allow cascading triggers, adjust 2-digit year cutoff, full-text settings, parallelism and network options http://msdn.microsoft.com/en-us/library/ms189357.aspx
Permissions Assign permissions to instance level logins and roles. (Not found on BOL)

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:

Page Brief Description Further help in BOL
Files Database file paths, change database owner, enable use of full-text indexes
(NOTE: Once enabled, full-text indexing cannot be turned off)
Filegroups Adjust row filegroups and FILESTREAM data filegroups http://msdn.microsoft.com/en-us/library/ms187567.aspx
Options Manage collation, recovery model, compatibility level, containment, database state, AUTO options, Cursor behaviour, FILESTREAM, Service Broker, recovery, ANSI and other miscellaneous options http://msdn.microsoft.com/en-us/library/ms188124.aspx
Change Tracking Manage change tracking for the database http://msdn.microsoft.com/en-us/library/bb895205.aspx
Permissions Manage database permissions to users or roles (Not found on BOL)
Extended Properties Manage database extended properties, typically used for documentation purposes (Not found on BOL)
Mirroring Configure database mirroring and associated security options http://msdn.microsoft.com/en-us/library/ms183684.aspx
Transaction Log Shipping Configure log shipping – define secondary instances and databases, enable current database a primary database in the log shipping configuration, establish a monitor instance and finally, script all changes for future reference! http://msdn.microsoft.com/en-us/library/ms186910.aspx

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:

  1. Record keeping – for future reference
  2. 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
  • image
  • Click on the little arrow near the “script” button at the top
  • image
  • 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
  • image
  • USE [master]
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2
  • 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…

    Until we meet next time,

    Be courteous. Drive responsibly.

    Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql


    1 thought on “#0133 – SQL Server – SSMS – Tutorial – 08 – How SSMS helps in reviewing and configuring your SQL Server Instance (L200)

    Let me know what you think about this post by leaving your feedback here!

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.