Scribd
Upload
214 views28 pages

PowerShell for SQL Server Setup

The document provides information on automating SQL Server 2017 installation and configuration using PowerShell scripts. It discusses prerequisites like .NET Framework and firewall configuration. It then covers using PowerShell to install SQL Server via specifying a configuration file, and for post-installation configuration like adding users and alerts. The document also discusses PowerShell basics like syntax, commandlets, and creating scripts. It provides examples of PowerShell scripts for installing SQL Server, importing SQL modules, setting parameters, executing SQL queries and scripts, and using SQL Server Management Objects.

Uploaded by

Vineet Sharma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
214 views28 pages

PowerShell for SQL Server Setup

The document provides information on automating SQL Server 2017 installation and configuration using PowerShell scripts. It discusses prerequisites like .NET Framework and firewall configuration. It then covers using PowerShell to install SQL Server via specifying a configuration file, and for post-installation configuration like adding users and alerts. The document also discusses PowerShell basics like syntax, commandlets, and creating scripts. It provides examples of PowerShell scripts for installing SQL Server, importing SQL modules, setting parameters, executing SQL queries and scripts, and using SQL Server Management Objects.

Uploaded by

Vineet Sharma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 28

Automated SQL Server 2017

Installation and Configuration


Using PowerShell
http://sqlsaturday.com/705/
Richmond SQL Server Users Group

http://rva.sqlpass.org/

Second Thursday at Markel Plaza (Glen Allen)


Everything you may (or may not) want to know about me.

Systems & Database Engineer

Masters of Science in Computer Information Systems

Sys Admin in Brisbane, Australia

SQL Community
Agenda
• SQL Server (setup)
• PowerShell Basics
Syntax
Commandlets
• Creating PowerShell Scripts
• Scripts 101
Prepare OS & install SQL
Post install configurations
Installation
&
Configuration
of
SQL Server
(what we’re trying to do…)
SQL Server Setup

Question:

What should go into all of your SQL installs?

Answer:
Umm….
Let me go find that Checklist.
SQL Setup Checklist
Requirements for SQL installs
• .NET 4.6 feature Automatic install for Win2016/SQL2016+
• SQL configuration file (for unattended installs)
• C:\Program Files\Microsoft SQL Server\...\ConfigurationFile.ini
• Service Accouts (MSA SQL2012)  gMSA supported in SQL2016
• Firewall exceptions
Port Use
TCP 1433 Database Engine default install
UDP 1434 SQL Server Browser Service
TCP 1434 Dedicated Admin Connection (DAC)
TCP 80 & 443 HTTP/HTTPS for SSRS
TCP 139 & 445 SQL Filestream & Filetables
TCP 2383 Analysis Services
TCP 135 MS Distributed Transaction Coordinator
SQL Setup Checklist cont.
Post Install Configurations
• Add Admin Users/Groups  • Alerts
• Rename the SA account • Severity 19-25
• Service Provider Name (SPN) • ID 18456 - Login Error
• Operators • ID 15247 - Permission Error
• Enable the SQL Agent service  • ID 9002 - Log Full
• DBMail • ID 825 - Disk I/O Error
• Instant File Initialization (IFI)  • Low disk space
• Enable CLR • Maintenance Jobs
• Import Custom SQL procedures • Backups
• Create Linked Servers • Reindex
• Audit to Server Security Log • CheckDB
• Add TempDB files  FREE Maintenance Solutions
• Management Policies (PBM) http://minionware.net
• Encryption Certificates http://ola.hallengren.com
 SQL 2016 ConfigurationFile.ini
SQL Server Setup Times

• Manual Installation (1-6 hours)

• Scripted Installation (20 min)


Prerequisites & SQL installation (~15 min)
Post-install configuration (~5 min)
about PowerShell…

• aka PoSh or PS
• Shell and Integrated Scripting Environment
(ISE)

• SQL Server module for PowerShell


SQLPS
SQL Server (2008-2014)
SQLSERVER
SQL Server 2016+ & SSMS July 2016 release
PowerShell Versions
PS Windows SQL PS SQL SSMS SQL PS
v1 2008 SQLPS 2008 2008 SQLPS
mini-shell
2008 R2 2008 R2 SQLPS
v2 2008 R2 SQLPS
7 2012 2012 SQLPS
v3 2012 SQLPS 2012 R2 2012 R2 SQLPS
8 2014 2014 SQLPS
v4 2012 R2 SQLPS 2016 16.3 – SqlServer 20
8.1 SqlServer 20 16.5
v5 2016 SqlServer 21 2017 17.0 – SqlServer 21
PS Gallery
10 PS Gallery 17.3
Powershell Basics:
Syntax:
$xxxxxxx – variable name
[string]$xxxx - variable with string data type
“ (double quote) - string expands/replaces variables
‘ (single quote) - literal string (does not expand variables)
@” - here string (multi-line string w/ variable replacement)
# - comment out remainder of line
` (back tick) - escape character
{} - script block
$_ - current object (iterative loops, ForEach)
Powershell Basics: (cont.)
Commandlets: (Verb-noun naming convention)
Set-ExecutionPolicy [Restricted, AllSigned, RemoteSigned, Unrestricted]
Enable-PSRemoting –force
Import-Module [ServerManager, SQLPS, SQLSERVER]
Invoke-Command -Computername -ScriptBlock {}
Invoke-Sqlcmd -ServerInstance [-Query or -InputFile]
Invoke-Expression [$variable]
Start-Process
Write-Output
Powershell Comparators:
SQL PowerShell SQL
= or <> -eq or -ne IF (@variable = 15)
BEGIN
> or < -gt or -lt Do this
>= or <= -ge or -le END
LIKE -like
NOT LIKE -notlike PowerShell
IF ($variable -eq 15) {
IS NULL !$variable
Do this
-contains }
Powershell Scripts:
• .ps1 (script)
• .psm1 (script module), .psd1 (manifest file), .ps1xml (formatting file)
• Double-click <> Execute
.\script.ps1 -ServerName “SQL01” -Path “C:\temp”
• Set-ExecutionPolicy to allow unsigned scripts to be run
• Parameters
Param ([string]$ServerName = “.”,
[string]$Path = “C:\”)
Creating PowerShell Scripts:
Design Considerations
Execute script(s) locally or remote?
• Locally – copy script(s) to server
• Remote – run script(s) from a single repository
Single Script or Script Set?
• Single - 1 big script containing all configuration items
• Set - 1 primary script that calls sub scripts
Script Users
• Internal use – doesn’t have to be PERFECT, document for usability
• External use – better documentation, allow for env variablility
Script Examples

12 PowerShell constructs to
automate your SQL Server
Installation & Configuration
Installing SQL Server:
Install .NET Feature
Import-Module ServerManager;
# Get Windows Server Version
$WindowsVersion = [environment]::OSVersion.Version

# Add .NET 3.5 to Windows Server 2008R2 and earlier


If (($WindowsVersion.Major -eq 6) -And ($WindowsVersion.Minor -lt 2)) {
Add-WindowsFeature AS-Net-Framework;}
# Add .NET 3.5 to Windows Server 2012 and 2012R2
If (($WindowsVersion.Major -eq 6) -And ($WindowsVersion.Minor -ge 2)) {
Add-WindowsFeature Net-Framework-Core;}
# Add .NET 4.6 to Windows Server 2016
If ($WindowsVersion.Major -eq 10) {
Add-WindowsFeature Net-Framework-45-Core;}
Note: Install media location option -Source D:\Sources\SxS\
Installing SQL Server:
Create Directories
New-Item -ItemType directory -Path E:\SQL
New-Item -ItemType directory -Path F:\SQL_Data
New-Item -ItemType directory -Path G:\SQL_Logs

Open Firewall Ports


netsh advfirewall firewall add rule name="SQL Instances"
dir=in action=allow protocol=TCP localport=1433;

Run SQL install as administrator, specifying config file


$install = 'Start-Process -verb runas -FilePath "' +
$SQLInstaller + '" -ArgumentList /ConfigurationFile="' +
$ScriptPath + '\Config_Scripts\ConfigurationFile.ini" -
Wait';

Invoke-Expression $install;
Configuring SQL Server:
Import SQL PowerShell Module
SQL Server 2012-2014
Import-Module SQLPS -DisableNameChecking;

SQL Server 2016 & 2017*


Import-Module Sqlserver -DisableNameChecking;

Set Script Parameters


Param ([string]$SQLServer = ".",
[string]$SQLInstaller = "D:\setup.exe",
[switch]$CreateMSA = $True,
[string]$ScriptPath = $PSScriptRoot
)
* SSMS & PowerShell modules separated as of 2017.
Configuring SQL Server: (cont.)
Execute SQL Query (single line)
Invoke-Sqlcmd -ServerInstance $SQLServer
-Query "CREATE DATABASE [Mgmt];";

Execute SQL Query (multi-line ‘here’ string)


$query = @"
EXEC sys.sp_configure N'contained database
authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE;
"@;

Invoke-Sqlcmd -ServerInstance $SQLServer


-Query $query;
Configuring SQL Server: (cont.)
Execute SQL Script File
Invoke-Sqlcmd -ServerInstance $SQLServer -InputFile
"$ScriptPath\Alerts_Triggers.sql";

Execute Multiple SQL Script Files In Order


$ScriptList = Get-Content InstallOrder.txt;
$ScriptList | %{
$currScript = $_;
Invoke-Sqlcmd -ServerInstance $SQLServer
-InputFile $currScript;
}

Execute PowerShell Script File


./PowerShellScript.ps1 -Parameter1 "Value"
-Parameter2 $variable;
Configuring SQL Server: (cont.)
SQL Server Management Objects (SMO)
A collection of objects that are designed for programming all
aspects of Microsoft SQL Server.
$SQLMem = new-object
('Microsoft.SqlServer.Management.Smo.Server') $SQLServer

$SQLMem.Configuration.MaxServerMemory.ConfigValue = 20480;
$SQLMem.Configuration.MinServerMemory.ConfigValue = 10240;
$SQLMem.Configuration.NetworkPacketSize.ConfigValue = 1500;
$SQLMem.Configuration.OptimizeAdhocWorkloads.ConfigValue = 1;

$SQLMem.Alter();

Microsoft SMO Programming Guide


http://technet.microsoft.com/en-us/library/ms162169.aspx
Additional Resources:

SQL Server Management Studio (SSMS)


https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-
management-studio-ssms

SQL Server PowerShell Module


https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-
ps-module

PowerShell Gallery
https://www.powershellgallery.com/
Additional Resources:

http://powershell.sqlpass.org/

SQL Server 2012 with PowerShell V3 Cookbook


by Donabel Santos
Demo Scripts:
Questions?

You might also like