sp_configure OR sys.configurations? and do you back these up?


I was checking out some of our configuration options today using sys.configurations and I started to wonder how many people actually use this table, or whether most people simply use the more common system stored procedure sp_configure.

As sys.configurations is simply a table, we can use an ORDER BY clause as in

SELECT * FROM sys.configurations C
ORDER BY C.name;

Which lists the configuration options in exactly the same order as the following call to sp_configure.(depending on the value of “show advanced options” the amount of options returned may vary.)

/*Just run the
Stored proc.*/
EXEC sp_configure

However, obviously with the SELECT statement we can also use a WHERE clause (or any other valid syntax)

SELECT * FROM sys.configurations C
WHERE C.is_advanced = 1
ORDER BY C.is_dynamic , C.name;

Of course it’s probably a matter of personal choice whether to write a query or just go for the system stored procedure. But one of the reasons that I like the query option is that I can see the advanced options straight away – even if I have “show advanced options” set to 0. If I use the stored procedure then I have to first set “show advanced options” to 1, then run a RECONFIGURE (as “show advanced options” is a dynamic option). Next the system stored procedure is run and finally “show advanced options” is set back to 0, followed by another RECONFIGURE. Note also that the code below ends by setting “show advanced options” to 0, when it may have already been at 1 in the first place.

so the full TSQL actually looks like

EXEC sp_configure 'show advanced options' , 1;
EXEC sp_configure;
EXEC sp_configure 'show advanced options' , 0;

instead of

SELECT * FROM sys.configurations C

which seems like less typing and also simply just leaves any setting for “show advanced options” alone.

But there is another good use for the table option, rather than the stored procedure. And that is to pull all of your setting out and back them up. Maybe pushing them to a text file on the server using Powershell or SSIS.
You can format the query anyway you like (perhaps in the form of runnable SQL statements) and then in a DR situation you have a full list of all your configuration setting without having to restore master to get them.

anyway, have a nice day.



Tags: , ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.