Real World:SQL server configuration Quality check script for DBA's.

  • Comments posted to this topic are about the item Real World:SQL server configuration Quality check script for DBA's.

  • Very interesting. I had something similiar but it isn't as good as this one. Thanks for sharing.

  • Very nice little script. Thanks for sharing with the community.

    For more security-minded DBAs (which I hope are most of us!) I recommend keeping "show advanced options" turned off, as well as xp_CmdShell. I wound up putting the enabling commands at the end of the script just before step N :

    [font="Courier New"]EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'xp_cmdshell', 1

    RECONFIGURE

    [/font]

    then the "N" display, and then

    [font="Courier New"]EXEC sp_configure 'xp_cmdshell', 0

    RECONFIGURE

    EXEC sp_configure 'show advanced options', 0

    RECONFIGURE

    [/font]

    I'll then keep the entire N block commented out until I specifically want to see its data.

    Thanks again!

    Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

  • Thanks for the script.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • A script that purports to "check" settings should NOT change them, period. Even more so without a warning/statement that they've been changed!

    If a specific setting needs to be changed to run the script, that should be documented, and a properly written script would check the existing condition first, then set it to match what it was when the script started.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks for your support.

  • Thank you for your script, must have taken some time to do. Much appreciated.

    I do agree with Mike Hinds and ScottPletcher about changing the settings on xp_cmdshell, although I would warn to also be careful adding Mikes suggestion in and running this on any server as somebody might have set xp_cmdshell for specific reasons and you might just mess things up by going about setting everything back to 0 again.

    I therefore spent a bit of time to put together the following to check what the settings are before starting and then setting them back after finishing

    ---start---

    -- Check 'show advanced options' value, write value to variable and set to 1

    DECLARE @var_show_advanced_options VARCHAR(1)

    SELECT @var_show_advanced_options = CONVERT(VARCHAR(1),value) FROM master.sys.configurations WHERE name = 'show advanced options'

    IF @var_show_advanced_options != 1

    BEGIN

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    END

    -- Check xp_cmdshell value, write value to variable and set to 1

    DECLARE @var_xp_cmdshell VARCHAR(1)

    SELECT @var_xp_cmdshell = CONVERT(VARCHAR(1),value) FROM master.sys.configurations WHERE name = 'xp_cmdshell'

    IF @var_xp_cmdshell != 1

    BEGIN

    EXEC sp_configure 'xp_cmdshell', 1;

    RECONFIGURE;

    END

    /* Run your XP_CMDSHELL and entire SCRIPT after this line */

    /* Run your XP_CMDSHELL and entire SCRIPT before this line */

    /* Cleanup */

    -- Set xp_cmdshell back to what it was

    IF @var_xp_cmdshell != 1

    BEGIN

    EXEC sp_configure 'xp_cmdshell', @var_xp_cmdshell

    RECONFIGURE

    END

    IF @var_show_advanced_options != 1

    BEGIN

    EXEC sp_configure 'show advanced options', @var_show_advanced_options

    RECONFIGURE

    END

    ---end---

  • I don't think turning off "xp_cmdshell" is anyways a good idea, as without that we can't even run batch commands from sql.

    Thanks.

  • Why are we concerned about checking Lock Pages in Memory? Does that help us doing better in DBA activities anymore?

    Thanks.

  • BEGIN TRY

    exec xp_cmdshell @PScmd

    END TRY

    BEGIN CATCH

    BEGIN TRY

    exec sp_configure 'xp_cmdshell',1;RECONFIGURE

    exec xp_cmdshell @PScmd

    exec sp_configure 'xp_cmdshell',0;RECONFIGURE

    END TRY

    BEGIN CATCH

    exec sp_configure 'show advanced options',1;RECONFIGURE

    exec sp_configure 'xp_cmdshell',1;RECONFIGURE

    exec xp_cmdshell @PScmd

    exec sp_configure 'xp_cmdshell',0;RECONFIGURE

    exec sp_configure 'show advanced options',0;RECONFIGURE

    END CATCH

    END CATCH

  • Excellent script. Thanks for sharing. I did however run into an issue with Section J: where more than just TEMPDB was returned. It turns out that the logical name for a 3rd party database file was "BlankTemplate". To avoid this issue I changed this line:

    DECLARE tempfile_cursor CURSOR FOR SELECT filename from sys.sysaltfiles where name like '%temp%'

    To this:

    DECLARE tempfile_cursor CURSOR FOR SELECT filename from sys.sysaltfiles where dbid = 2

    Thanks again.

    Lee

  • Hello all,

    You can avoid xp_cmdshell to get max and min memory as follows:

    DECLARE

    @MaxMemory NVARCHAR(10) -- Max memory

    , @MinMemory NVARCHAR(10) -- Min memory

    SET @MaxMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'max server memory%'))

    SET @MinMemory = CONVERT(NVARCHAR(10), (SELECT VALUE FROM SYS.CONFIGURATIONS where Name like 'min server memory%'))

    PRINT 'Detection of Maximum Memory (Megabytes) --> '+@MaxMemory

    PRINT 'Detection of Minimum Memory (Megabytes) --> '+@MinMemory

    GO

    If your systems require any audit/security compliance then you should never use xp_cmdshell for anything.

    Thanks,

    Rudy

    Rudy

  • Good script, thank you.

  • Thanks for sharing Randy!!

    You can avoid all those cursors and simplify your code like this:

    print 'I. Name of Members in Serveradmin role.:'

    DECLARE @PrintResults NVarchar(4000) = ''

    SELECT@PrintResults = @PrintResults + ' Login - ' + name COLLATE DATABASE_DEFAULT + CHAR(13)

    FROMsys.server_principals

    WHEREIS_SRVROLEMEMBER('sysadmin', name) = 1

    PRINT@PrintResults

    I didn't bother with the second column as it is already stated in the header.

    How I deal with configuration options:

    DECLARE @AdvOpts Bit

    ,@CmdShellBit

    SELECT@AdvOpts= CAST(MAX(CASE WHEN Name = 'show advanced options' THEN CAST(value_in_use AS TinyInt) ELSE NULL END) AS Bit)

    , @Cmdshell = CAST(MAX(CASE WHEN Name = 'xp_cmdshell' THEN CAST(value_in_use AS TinyInt) ELSE NULL END) AS Bit)

    FROMsys.Configurations

    WHEREName IN ('show advanced options', 'xp_cmdshell')

    SELECT[@AdvOpts]= @AdvOpts

    , [@CmdShell] = @CmdShell

    IF @CmdShell = 0

    BEGIN

    PRINT 'Warning : server configuration change needed. These will be reverted at the end of the script.'

    IF @AdvOpts = 0

    BEGIN

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE WITH OVERRIDE

    END

    EXEC sp_configure 'xp_cmdshell', 1

    RECONFIGURE WITH OVERRIDE

    END

    /*

    Code

    */

    IF @CmdShell = 0

    BEGIN

    EXEC sp_configure 'xp_cmdshell', 0

    RECONFIGURE WITH OVERRIDE

    END

    IF @AdvOpts = 0

    BEGIN

    EXEC sp_configure 'show advanced options', 0

    RECONFIGURE WITH OVERRIDE

    END

  • Interesting, thank you.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply