One of my pet peeves is the use of RECONFIGURE WITH OVERRIDE in scripts to change server options. Am I being unreasonable or is this justified? All you need to do is look up the definition of the WITH OVERRIDE option for the RECONFIGURE command in Books Online to see where this particular peeve began.
WITH OVERRIDE: Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recovery interval advanced configuration options.
Let’s look at this definition in two parts:
1) Disables the configuration value checking (for values that are not valid or for nonrecommended values)
Does this mean that using WITH OVERRIDE will allow me to set any server configuration to an invalid value? No. Even with the override option, you can’t set a configuration option to a value outside of the defined minimum and maximum values. It also won’t override certain checks that prevent you from making configuration changes that would be dangerous to SQL Server like setting Max Server Memory to a value less than Min Server Memory. If you attempt this, the change will fail with an error stating that the defined value is not valid.
2) for the recovery interval advanced configuration options
Currently, the only option that is defined as being settable to a non-recommended or dangerous value is the Recovery Interval option that controls how frequently automatic checkpoints should occur. There are cases where this setting needs to be configured to a non-recommended value and the WITH OVERRIDE option is required to make that change. In fact, if you have this value already set to a non-recommended value, then you have to use the WITH OVERRIDE option on all future calls to RECONFIGURE even if that’s not the option being modified.
So if it’s just the one configuration, what’s so dangerous? The reason I think it is dangerous to just automatically use for everything is because it gives you no warnings at all that you are making a non-recommended change to a configuration. You could be trying to set it to a valid value and accidentally type in the wrong value, and you may never notice until you experience the downside caused by the change.
Additionally, what if they decide to add a new configuration option that can have value checking disabled and they forget to document it?
They wouldn’t do that, would they?
Surely Microsoft wouldn’t do something like add a new configuration option that allows disabling value checks and not document it or warn us. Would they? Well, in fact, they have done that very thing in SQL Server 2012. The contained database authentication setting in SQL Server 2012 can be disabled when there are contained databases present on the instance by using the WITH OVERRIDE setting. If you are using WITH OVERRIDE for all changes, you could make this change and not realize there are contained databases on the server because it makes the change without warning. Doing so will cause all contained users to be denied access to their databases.
Please help address this potential issue by voting for the following Connect bug/feature request. The Connect item requests that the behavior be changed if it was not intentional or to document the change if it was.
Connect item: RECONFIGURE WITH OVERRIDE allows disabling of containment when contained databases exist
The above Connect item includes codes to reproduce the behavior. You may have to click on “Expand Details” to see it. Check it out for yourself.