Script to Determine SQL Server Version and if the patch level is still in support for large estate

Question

Script to Determine SQL Server Version and if the patch level is still in support for large estate

juniorDBA13

Ten Centuries

Points: 1052
More actions
May 29, 2017 at 3:16 am

#326207

Hi,
I am wondering does anybody have a script to determine the SQL Server Version and whether or not it is in support e.g. what patch is it at and if this is still in support?
Thanks,
Fiona

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

juniorDBA13 - Monday, May 29, 2017 3:16 AM
Hi,
I am wondering does anybody have a script to determine the SQL Server Version and whether or not it is in support e.g. what patch is it at and if this is still in support?
Thanks,
Fiona

Select @@version, copy output and google
😎

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 2

Yes, I know select @@version works but I need to determine for it for number of servers so i would prefer to have a script that checks the version and lets me know if patching is required.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The thing is, such a script would have to be updated frequently. Probably best if you check the MS sites for what the current supported patches are, then write a script, using SERVERPROPERTYEX most likely that looks for anything below that value.
The script will be out of date as soon as the supported versions change though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

juniorDBA13 - Monday, May 29, 2017 3:23 AM
Yes, I know select @@version works but I need to determine for it for number of servers so i would prefer to have a script that checks the version and lets me know if patching is required.

Patching is one thing, checking if the servers are still in support is another, you definitely do not want to depend on some script for determine the latter!. Suggest you change you're approach to monitor the releases of new patches, keeping a register of which have already been applied to each instance.
😎

Brandie Tarvin SSC Guru Points: 173056 More actions · Answer 5

The only thing I could suggest is maybe creating a process that does a screen scrape of Microsoft's version page which then loads the data into a table. You then use the data in the table (getting the latest version) to compare against your servers. It's somewhat complicated and will break the instant that the version page goes down for any reason, but if you really have your heart set on automating this, it's your best bet.

On the other hand, having this automated is no excuse for not regularly (and manually) checking for patches. I never put in an automated job without building manual checks around it so I can be sure things are going as planned.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Something like what you want would probably end up being too much manual work to be worthwhile.
I'd probably start with just finding all of your current versions, editions, patch levels for your instances and go from there.
One quick way to get all of your version information for all your instances is to use central management server and then just do a multiserver query. If nothing else, setting up a central management server will help with other things so it's not a bad idea to set it up in the first place (if you haven't yet). Refer to this doc for more information:
Create a Central Management Server and Server Group

If you really need to, you can export a list of the SQL Server support product lifecycle. There is a link on the right of the page to export the information to a csv file. It's not a good idea going forward though. Maybe just for a first time cursory check. The product lifecycle is available here:
Search product lifecycle

Sue

Lowell SSC Guru Points: 323518 More actions · Answer 7

one day back on 07/28/2016 i was inspired to create a procedure to do this;
as everyone has already identified, the problem is keeping up with releases. how many patches, CU's and service packs have been done in 10 or so months?
I thought of doing what Brandie suggested: a Powershell script or a CLR to screen scrape a known web site, but meh, i ran out of interest in automating it.
here's a linky to what I wrote; i'd run this on Central Managment servers to see where I stood for patches; it's just plain old comparing to a internal table full of what I scraped.

sp_help_patches

*Edit* it has a dependency to master.dbo.DelimitedSplit8K!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

There's a free tool from Idera Software which I use that does this kind of work:
https://www.idera.com/productssolutions/freetools/sql-instance-check
and they keep track of the patches and updates available for all the different versions.