Setting up environment for PowerShell scripts

Being a dba I need to (and wish to) automate everything possible when it’s about SQL Server. And for doing that I use PowerShell alot. Working for different clients I need to setup the environment for the PS scripts to work, which involves installing different modules and changing the execution policy. Below are the things that I generally need to have.

  1. The SQLServer module, this is the module created by Microsoft and imports SQL Server provider and cmdlets for SQL Server.
  2. dbatools module, this is a community created set of cmdlets to help dbas in day-today activities.
  3. SQLServerDsc, the desired state configuration is my new found love 🙂
  4. Execution policy, we need to set the proper execution policy to run scripts.

I generally have all the scripts in my local machine, but have decided to upload them for public.

Below is the script that I run once I get my workstation for a client. It’s link to Github.

#This script is created to make your environment SQL Server + DSC + dbatools ready


#Check if the current session is admin or not, as we require admin account

$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if(!($currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)))
    {
        Write-Host "You are not currently running under admin, right click PowerShell icon and click run as administrator"
        return
        }

#We will install SQL Server module if not already installed
if(Get-Module -ListAvailable -Name SQLSERVER)
    {
        Write-Host "SQL Server module already installed, way to go!"
        }
else
    {
        Write-Host "Installing SQL Server module"
        Install-Module Sqlserver -Force -AllowClobber
        Write-Host "SQLServer module installed"
        }


#We will install dbatools if not already installed
if(Get-Module -ListAvailable -Name dbatools)
    {
        Write-Host "Dbatools module is already installed moving to next.."
        }
else
    {
        Write-Host "Installing dbatools"
        Install-Module dbatools -Force -AllowClobber
        Write-Host "dbatools modules installed"
        }

#We will install SQL Server DSC if not already installed
if(Get-Module -ListAvailable -Name SqlServerDsc)
    {
        Write-Host "SqlServerDsc module is already installed.."
        }
else
    {
        Write-Host "Installing SqlServerDsc"
        Install-Module SqlServerDsc -Force -AllowClobber
        Write-Host "SQLServerDsc modules installed"
        }

#We will require to set execution policy changed to either Remotesigned ,if running self developed scripts, or Unrestricted to allow anything to run

$currentexecpol = Get-ExecutionPolicy
if($currentexecpol -eq 'Unrestricted' -or $currentexecpol -eq "RemoteSigned")
    {
        Write-Host "Policy is already set to $currentexecpol"
        }
else
    {
        Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Force
        }

I hope it helps!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s