SQL Server 13/11 ODBC installation using PowerShell - Automation

This document explains the usage of automated SQL Server ODBC client installation using PowerShell

Linkedin: linkedin.com/in/vinod-prasanth-peter-a05302ab

Why automate ODBC Client Installs?

Are you installing SQL Server ODBC 13/11 in the application servers manually.? Its fine if it is one/two server to install manually. what if you get many servers.? Try this out. No manual intervention.

 

How does the script works?

1)    Feed server name(s) on server.txt file

2)    Functionality to checks if ODBC driver is installed

a.     If installed, exit the process

b.     Else initiate the installation process

3)    Functionality to copy ODBC driver from DBA Share to E:

a.     Location:  \\Location of the ODBC Setup file

4)    Looks for prerequisite prior installation

5)    Install the ODBC Client and Outputs the Status

6)    Delete local copy from E:

How to use the script?

Open the ODBCInstall.PS1 file and run with PowerShell.

Output:

Powershell Script:

#Title: ODBC Installation for SQL SERVER using Powershell.

#Author: Vinod Prasanth.

cls

#Get the Server List from the below location:

$Serverlist = Get-Content D:\ODBC\servers.txt

$Version= "Microsoft ODBC Driver 13 for SQL Server"

#Check ODBC Is Installed or not, If not reboot the server before installation:

Foreach ($ServerName in $Serverlist)

{

$session = New-PSSession -ComputerName $servername

$ODBC_Version=Invoke-Command -Session $session -ScriptBlock {Get-WmiObject "win32_product" | select-object -property Name,PackageName,Version | Where {$_.name -match "Microsoft ODBC Driver 13 for SQL Server"}} 

#Invoke-Command -server $servername -ScriptBlock {$ODBC_Version} 

if ($ODBC_Version.Name -eq $Version)

{

Write-Host "ODBC SQL Server 13 is already installed on $ServerName"

exit

}

else

{

$pending_reboot = invoke-command -computerName $servername -scriptblock{Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"}

if ($pending_reboot.PendingFileRenameOperations.count -ge 1)

  {

 $ask_confirmation = Read-host -prompt 'There is a pending reboot on this server do you want to proceed with the server reboot? YES/NO: '

 if ($ask_confirmation -eq 'YES')

 {

   Restart-Computer -ComputerName $servername -Force -Wait -For PowerShell -Timeout 300 -Delay 2

 start-sleep 120

 }

 else

 {

 exit

 }

 }

}

}

#Install ODBC:

Foreach ($ServerName in $Serverlist)

{

if ($ODBC_Version.Version -eq $Version)

{

Write-Host "ODBC $ODBC_Version is already installed on $ServerName"

}

else

{

#Copy the ODBC Setup

Try

{

$binarypath = "\\" + $servername +"\E$\msodbcsql.msi"

if (test-path $binarypath)

    {

      Write-Host -Foregroundcolor Green "ODBC driver Exists on $servername" 

    }

  else

  {

    Write-Host -Foregroundcolor Green "Copying ODBC Driver for $servername"

$copy_cmd = "################Provide the ODBC SETUP FILE LOCATION #####################"

    Copy-Item $copy_cmd \\$servername\E$\ -recurse -force;

Write-Host -Foregroundcolor Green "ODBC Driver Successfully copied on $servername"

  }

 }

 Catch

{

  Write-Host "Media copy failed" -Foregroundcolour Red 

  exit

}

$session = New-PSSession -ComputerName $servername

Invoke-Command -Session $session -ScriptBlock {cd e:\; msiexec /i msodbcsql.msi IACCEPTMSODBCSQLLICENSETERMS=YES /passive ADDLOCAL=ALL;} 

Write-Host -Foregroundcolor Green "ODBC $ODBC_Version is installed on $ServerName"

#Delete the Setup

Start-Sleep -Seconds 30

if(test-path \\$servername\E$\)

{

  remove-item \\$servername\E$\msodbcsql.msi -recurse -force

  Write-Host -Foregroundcolor Green "msodbcsql Removed"

} 

else

{

Write-Host -Foregroundcolor Red "msodbcsql Not available"

}

}

}