While working on a chapter I am writing, I was going through the ALTER SERVER CONFIGURATION topic in the Books Online and noticed some really useful changes in the syntax that I hadn’t seen before. Starting in SQL Server 2008 R2, ALTER SERVER CONFIGURATION was introduced to allow for process affinity configuration and to replace the affinity mask sp_configure options for SQL Server, which were marked as deprecated at the same time. SQL Server 2012 extends the usage to also control diagnostic logging and failover clustering properties as well.
Diagnostic logging in SQL Server 2012 is accomplished through the execution of sp_server_diagnostics, which Joe blogged about last year here and again here. It can be turned ON or OFF using ALTER SERVER CONFIGURATION, and you can also specify the PATH, MAX_SIZE, and MAX_FILES options to control the logging location, file size, and maximum number of rollover logs to be created and maintained by SQL Server.
— Disable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;
— Enable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;
— Configure logging path
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG PATH=N'C:\SQLskills\Logs';
— Configure max file sizes
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_SIZE=100 MB;
— Configure max number of files to keep
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_FILES=5;
The flexible failover policy in SQL Server 2012 allows for more fine grained control of when a SQL Server Failover Cluster actually performs a failover of the instance. This is accomplished through the execution of sp_server_diagnostics and is based on the output of the health checks being performed by the procedure. The FailureConditionLevel can be set by ALTER SERVER CONFIGURATION, along with the HealthCheckTimeout value to control when a instance performs a failover or restart associated with an unhealthy condition. You can also set the logging level for the Failover Cluster, and a number of SQLDumper options as well.
— Set failover on any qualified failure condition
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY FailureConditionLevel = 5;
— Set the health check timeout to 120000 milliseconds (2 minutes)
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 120000;