ConfigNewServer

Here's a proc I like to keep handy in the Admin database for when I set up a new server.  I may not set all options, but it serves as a checklist to which ones I might want to set.

USE Admin

GO

IF object_id('dbo.ConfigureNewServer') Is Null

      DROP PROC dbo.ConfigNewServer

go   

CREATE PROC [dbo].[ConfigNewServer]

AS

/*    DATE        AUTHOR            REMARKS

      4/7/11            PPaiva            Initial creation.

      DESCRIPTION

            Code snippets for some server-wide settings that may need to be set

                  for a new server.

*/

RETURN      -- contains code snippets, not meant to be executed

Exec sp_configure

Exec sp_configure 'show advanced options', 1

RECONFIGURE

Exec sp_configure xp_cmdshell, 1

RECONFIGURE

EXEC sp_configure 'backup compression default', 1

RECONFIGURE

EXEC sp_configure 'max server memory (MB)', 2000

RECONFIGURE

Exec sp_configure  'clr enabled', 1

RECONFIGURE

-- This should always be off

Exec sp_configure 'allow updates', 0

-- Set Windows and SQL authentication (requires service restart)

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

-- Set Windows only authentication (requires service restart)

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1