SQL Server 2014 with PowerShell v5 Cookbook

Changing SQL Server Instance configurations

This recipe discusses how to change instance configuration settings using PowerShell.

Getting ready

For this recipe, we will perform the following tasks:

• Change fill factor to 60 percent
• Enable SQL Server Agent
• Set Minimum Server Memory to 500 MB
• Change authentication method to Mixed

How to do it...

Let's change some SQL Server settings using PowerShell:

1. Open PowerShell ISE as administrator.
2. Import the SQLPS module and create a new SMO Server Object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

3. Add the following script and run: ...