Post reply

Finding the SQL Server Version on all the servers

  • 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

  • December 14, 2015 at 9:58 am

    #1845862

    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

  • December 14, 2015 at 10:33 am

    #1845873

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

    Thank you for reply John

  • December 14, 2015 at 10:39 am

    #1845876

    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

  • December 14, 2015 at 10:49 am

    #1845879

    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]

  • December 15, 2015 at 2:10 am

    #1846015

    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

  • December 15, 2015 at 6:23 am

    #1846057

    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

  • December 16, 2015 at 9:58 am

    #1846359

    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

  • December 16, 2015 at 10:15 pm

    #1846472

    PowerShell Script:

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

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

  • December 17, 2015 at 6:31 am

    #1846546

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

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

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