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:
| 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) |
http://msdn.microsoft.com/en-us/library/ms180254.aspx |
| 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:
- Record keeping – for future reference
- Scheduled deployment – the required change has been scripted, so it can be applied whenever the outage window permits
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2 GO
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
SQLTwins by Nakul Vachhrajani 
Nice series. Thanks for sharing
LikeLike