Finding the SQL Server Version on all the servers

Question

Finding the SQL Server Version on all the servers

fawad.majid

Mr or Mrs. 500

Points: 551
More actions
December 14, 2015 at 9:48 am

#303288

Hi there
i am trying to find out SQL Server version of all the servers which we have on the network. i know there will a dynamic query to find out the versions of all the servers running on network on different servers. I already have all the server as linked servers.
can anyone help me with that and send me a query or powershell script to find this out .. doing it manually going to take alot of time
please help
Thank you

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

There's several ways of doing it. Set up a Central Management Server in SSMS, then register all your servers and run a query to get the server name on all of them. Or write a SQLCMD script something like this:

:connect Server1

PRINT @@Server

PRINT CAST(SERVERPROPERTY(N'ProductVersion') AS nvarchar(20))

:connect Server2

...

Or you may be able to find a DMV that returns the server version, in which case you can use your linked servers.

John

fawad.majid Mr or Mrs. 500 Points: 551 More actions · Answer 2

do you have DMV script which i can use with my linked servers

Thank you for reply John

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

If you've got them defined as linked servers, give this a whirl (needs SQLPS module loaded):

$servers = Invoke-Sqlcmd -ServerInstance serverwithlinkedserversdefined -Query "select name from sys.servers where product = 'SQL Server'"

$output =

foreach ($name in $servers)

{

new-object ('Microsoft.SqlServer.Management.Smo.Server') $name.name | select Name, Edition, Version, ProductLevel

}

$output | ft -AutoSize

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

John Mitchell-245523 (12/14/2015)
Set up a Central Management Server in SSMS, then register all your servers and run a query to get the server name on all of them

Nice, you can even do without the CMS and just register them in a Local Server Group in SSMS and run a multi-server query:

SELECT ServerProperty('ServerName') AS [Server], ServerProperty('Edition') AS Edition, ServerProperty('ProductLevel') AS [SP], ServerProperty('ProductVersion') AS [Version]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

fawad.majid (12/14/2015)
do you have DMV script which i can use with my linked servers
Thank you for reply John

No. I'm not aware of any. They may exist - you'd need to do your own research on that.

John

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I don't know if this will work for your particular situation, but if you have all your servers defined in a group in SSMS, you can run a query against all of them in that group. Right-click the group name and click New Query. SSMS opens a new window connected to all the servers. Run your query and you'll get your results back with a "Server Name" column added. This also gives you the results in a single result set.

You can use the SERVERPROPERTY that John posted or @@VERSION.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

mceventphoto Ten Centuries Points: 1299 More actions · Answer 7

I am doing something similar with the code below.

--Declare a table to hold a list of servers you want to check

declare @list table(id int identity(1,1),Instance nvarchar(50))

--Declare a table to hold all server information

declare @ServerInfo table(InstanceName nvarchar(50),SQLVersion nvarchar(30),Edition nvarchar(30), ProductVersion nvarchar(20),AuthenticationMethod int,PatchLevel nvarchar(10))

--Build your server list. In my case, I am reading from a table. Below, I am using the insert into ... union all to populate the @list table

insert into @list

select 'Instance1' UNION ALL --Replace Instance1 and Instance2 with the names of your servers

SELECT 'Instance2'

declare @count int

declare @sqlcmd nvarchar(MAX)

DECLARE @InstanceName VARCHAR(200)

set @count = 0

while 1=1

Begin

select top 1 @InstanceName=Instance, @count=ID from @list where ID > @count

if @@ROWCOUNT = 0 break;

SELECT @sqlcmd = 'select a.* from openrowset(''SQLNCLI'', ''Server='

+ @InstanceName

+ ';Trusted_Connection=yes;'', ''select cast(@@servername as varchar(50)) [ServerName],

rtrim(left(@@version,26)) [SQLVersion],

cast(serverproperty(''''Edition'''') as varchar(50)) [Edition],

cast(serverproperty(''''ProductVersion'''') as varchar(25)) [ProductVersion],

cast(serverproperty(''''IsIntegratedSecurityOnly'''') as int) [AuthenticationMode],

cast(SERVERPROPERTY(''''productlevel'''') as varchar(5)) [PatchLevel]'') as a'

insert into @ServerInfo

EXEC sp_executeSQL @sqlcmd

End

select * from @ServerInfo

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 8

PowerShell Script:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

djj SSCoach Points: 18831 More actions · Answer 9

I use a third party software (Idera Admin Toolset) that you can find by IP address.