March 10, 2015 at 10:48 am
Greetings from a DBA newbie. I am tasked with correcting 600 SQL Server instances default settings. I would like to use powershell, if possible to loop thorough each named instances (I have a list) and apply the following settings. I also need to query the server for the installed memory and apply * 80% logic for maxmem, and look at the VCPU count and adjust the MAXDOP to that number.
Does anyone have such a script?
Thanks.
USE MASTER
GO
EXEC sp_configure 'recovery interval (min)', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'allow updates', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'user connections', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'locks', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'open objects', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'disallow results from triggers', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'nested triggers', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'server trigger recursion', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'remote access', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default language', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'cross db ownership chaining', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max worker threads', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'network packet size (B)', '4096';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'remote proc trans', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'c2 audit mode', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default full-text language', '1033';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'two digit year cutoff', '2049';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'index create memory (KB)', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'priority boost', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'remote login timeout (s)', '10';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'remote query timeout (s)', '600';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'cursor threshold', '-1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'set working set size', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'user options', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'affinity mask', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max text repl size (B)', '65536';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'media retention', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'cost threshold for parallelism', '5';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', '4';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'min memory per query (KB)', '1024';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'query wait (s)', '-1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'min server memory (MB)', '16';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)', '12576';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'query governor cost limit', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'lightweight pooling', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'scan for startup procs', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'affinity64 mask', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'affinity I/O mask', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'affinity64 I/O mask', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'transform noise words', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'precompute rank', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'PH timeout (s)', '60';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'clr enabled', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max full-text crawl range', '4';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'ft notify bandwidth (min)', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'ft notify bandwidth (max)', '100';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'ft crawl bandwidth (min)', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'ft crawl bandwidth (max)', '100';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default trace enabled', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'blocked process threshold (s)', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'in-doubt xact resolution', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'remote admin connections', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'common criteria compliance enabled', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'EKM provider enabled', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'backup compression default', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'filestream access level', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'optimize for ad hoc workloads', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'access check cache bucket count', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'access check cache quota', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Agent XPs', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'SMO and DMO XPs', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Ole Automation Procedures', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Ad Hoc Distributed Queries', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Replication XPs', '0';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'contained database authentication', '0';
RECONFIGURE WITH OVERRIDE;
March 11, 2015 at 7:52 am
Hi,
I would register all of the servers and instances in a central management server rather than use powershell. Once that is done you can run a single script against a server group.
It takes time to prepare but it is very efficient for this type of job.
PS: Reconfigure with override is a bit of an overkill!
Regards,
Kev
March 11, 2015 at 8:21 am
I do have them in a central management server. Thanks for the tip.
March 11, 2015 at 3:11 pm
Hi,
import your registerd Servers by servers.txt file exmaple.
https://msdn.microsoft.com/en-us/library/ms190711.aspx
After the import, run your changes to your 600 Instances from one Window 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply