About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Thursday, April 7, 2011

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



No comments:

Post a Comment