Automate SQL server version and database compatibility report using PowerShell

Posted in: Microsoft SQL Server, Technical Track

PROBLEM


What version of SQL Server are you using? Is the current version supported as per Microsoft Support Lifecycle policies? Are you running the latest Service Pack? Are you still clinging to old database compatibility levels?

Earlier this year when Microsoft Retired Support for SQL Server 2005 we were tasked to review all the Servers within the client environment and provide answers to all of the above questions. Now there are 2 ways of doing the same, or at least I could only think of the following:

 

  1. Log into all the Servers one by one, and run some SQL Scripts to collect the data. Use Excel or Word to organize the data and then verify the details online. This approach is interesting, and based on the number of servers you have in your environment you will doing this repetitive task on all those servers again and again and again before you have the final report.
  2. Automate the entire report using Powershell. All you need to do is provide a list of SQL Instances and wait for a little while Powershell does it magic to generate a HTML formatted report for you.

I am not going to discuss both options because that’s not why you guys are here. Follow along to get the answers you’re looking for.

 

SOLUTION


First of all, I wanted to share the motive behind doing this code. I was tasked to review around 200 SQL Instances for Current SQL Server Version, Current SQL Server Service Pack, and Database Compatibility Levels. I then had to share a report with a list of instances\databases to be upgraded or altered. I’m not sure about you, but 200 servers are a lot for me to check manually. I won’t repeat the same steps even on a second server, so 200 was obviously out of question. Then I decided to automate it using Powershell. When you need to do a similar task more than once you should always think about automating it.

The Powershell script shared in the following link will accept a list of parameters,which is primarily going to be list of SQL Server Instances and location to dump the report. The script will give you a complete summary of Current SQL Server Versions, Current Service Pack, Database Compatibility Level details, and will make suggestions on what to upgrade or alter.

I am not going to discuss why you should really care about the old versions of SQL Servers, not applying the latest service pack, or old database comparability levels. We both know you are not here for that kind of information. I believe you already know why Latest SQL Server versions or Service Pack and Database Compatibility levels makes sense for you or maybe not depending upon your application requirements.

Verified on following platforms

  • Powershell v2.0 and higher versions
  • Windows Server 2008 and higher versions

ACTUAL SCRIPT


Click the below link to download the Powershell Script
SQL Server Compatibility Report Code

How to Execute the Script?

I am keeping things simple by keeping everything on Desktop but you can use it by choosing the location of your liking

  • Copy the File to Desktop
  • Open PowerShell console as Administrator and set execution policy to remotesigned
  • Execute the Script with required parameters

What if I have a long list of SQL Instances to review?

If you have a long list of SQL Instances and passing Instance names as comma delimited list is going to be laborious job don’t worry.

  • Create a text file and copy the list of SQL Instances delimited by end of line between records
  • Go to console and import it into a variable using get-content and pass that variable as a parameter

SQLCompatabilityExecute2Image

 

 

email

Author

Interested in working with Sandeep? Schedule a tech call.

About the Author

Project Engineer at Pythian
This Guy has done stuff. Sandeep stands at the forefront of the fastest moving technology trend: Cloud Services & DevOps. He’s spent the past seven years evangelizing from a role in database administration to trying to automate everything using PowerShell to doing some stuff in DevOps to becoming a Solution Architect in AWS, Azure, and GCP. If he is not watching any video tutorials or helping a customer putting off fires then you can find him at the gym trying to lean out.

8 Comments. Leave new

Great job. Thank you for sharing!

Reply
Sandeep Arora
August 10, 2016 10:02 am

Hello John,
Thanks for pointing it out. I thought I addressed that in the latest bug fix before syncing it to the master branch. I have updated the code and you should be able to see the latest Patch details for SQL 2014 as well :)

Reply

The one thing I’d like to mention is it doesn’t seem to be detecting MS SQL Server 2014 SP2 as being the latest available for current SQL 2014 SP1 instances.

Reply

There’s a small bug with the RTM “LatestServicePack” label, on 2016 (no SP available) it shows “RTM (No SP)” which causes the report to show red when compared against the SERVERPROPERTY (‘productlevel’) (“CurrentServicePack” label) which is just “RTM”. Changing both occurrences of “RTM (No SP)” to “RTM” solves the issue.

Otherwise great script! Wonder if there’s a way to pull the latest SP available from Microsoft instead of hard-coding it in the script.

Reply
Sandeep Arora
August 12, 2016 4:55 am

Hello Tim, Thanks for the feedback. I will get it fixed. There is definitely a way on polling the latest SP details from internet. I am working on that script and will share that as soon as it is ready. It will still have hard coded values in case your server doesn’t have a internet connection and update itself in case it can connect to internet. I hit some road blocks with that script but will make a blog post out of it once ready :)

Reply

Hi, I executed the script as you suggested in the mail but unable to connect the other SQL servers.
can you please suggest to modify any credential to connect the other servers.

Thanks and Regards,
Karthik

Reply
Sandeep Arora
January 2, 2017 8:08 am

Hello Karthik,

I normally use the below function and process when I have more than one domain and account connecting to SQL Server. The GetRemotePSCredential can be used to get the account that works on that system\machine and then you can change the function to use invoke command and pass the creds to it.

function GetRemotePSCredential ($hostname)
{
$key = (([System.DirectoryServices.ActiveDirectory.Domain]::getcurrentdomain().GetDirectoryEntry().objectSid)[0])
$serverCredentialList = @()
$serverCredentialList += New-Object System.Management.Automation.PSCredential(“domainname1\accountname1”, (“Encrypted String using the above key” | ConvertTo-SecureString -Key $key))
$serverCredentialList += New-Object System.Management.Automation.PSCredential(“domainname2\accountname2”, (“Encrypted String using the above key” | ConvertTo-SecureString -Key $key))
$serverCredentialList += New-Object System.Management.Automation.PSCredential(“domainname3\accountname3”, (“Encrypted String using the above key” | ConvertTo-SecureString -Key $key))

#If connection successful, returns the credential object
$serverCredentialList | %{
if ((Invoke-Command -ComputerName $hostname -Credential $_ -EA 0 -ScriptBlock {hostname})){
return $_}
}
}

function WhateverNeedstobedone (parameters) {

$creds = GetRemotePSCredential -hostname ComputerName

—————————-
—————————-
Rest of the code

}

Let me know if this makes sense.

Reply

Hi Sandeep,

When i am applying service pack on my one of node , its throwing an error saying “Index (Zero size) size must be greater than or equal to zero and less than the size of arguments”

Error Code :- 0x84B10001.

your guidance will much appreciated.

Thanks,
Vipin

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *