List sql servers, versions, SP installed in all the servers of environment.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » List sql servers, versions, SP installed in...

List sql servers, versions, SP installed in all the servers of environment.

Rate Topic

Display Mode

Topic Options

Author

Message

- Win

Posted Wednesday, February 13, 2013 12:22 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 PM
Points: 86, Visits: 321

Help required,

How to gather / list a report of all the sql servers and versions in my environment
Prod / Dev / Test / UAT / SYST using TSQL Scripts.

I would like to provide a sql server installed, version report, SP updated for all the SQL Servers in each environment (prod / dev / uat).
I just want to know which ones have SQL Server need, SP1 and which ones need it.

Please let me know if the question makes any sense.

Chills
- Win

Cheers,
- Win
"Dont Judge a Book by its Cover"

Post #1419318

DBArrr!

Posted Wednesday, February 13, 2013 1:41 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 4:49 AM
Points: 70, Visits: 55

Hey
I am wondering why you want to do this with t-sql?

Assuming you have somehow discovered your servers with sql server services, og just have a list of instances you want to check, I would use powershell and smo to run through the instances and collect instance properties for product, productlevel, edition, and maybe versionstring (look through the properties and see which you're after).

Getting them from a single instance would go something like this:

[reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")
$inst=new-object microsoft.sqlserver.management.smo.server "insert your instancename here"
#assumint integrated security can be used, else youll have to make a connection object
$inst | format-table Name, Product, ProductLevel, Edition, VersionString -AutoSize

For a list of instances, you'd have to put them in an array or write them to somewhere for review... youll have to look into some more powershell for that :)

Hope the idea helps you - this is how i've collected my instance information.

Post #1419338

- Win

Posted Wednesday, February 13, 2013 1:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 PM
Points: 86, Visits: 321

Thanks for your prompt response.

I used a powershell script, which resulted local machines report.
I am not that much familier with PowerShell. If there is something that I can fetch the required details, then I am good and thankful.

I would like to pull details in the form of below format for all the environments. we have more than 300+ servers with multiple instances.

Host|| IPAddress || MSSQL Instance || Version || ServicePack || OS || OS Version

Please let me know if this make some sense now.

Cheers,
- Win
"Dont Judge a Book by its Cover"

Post #1419346

DBArrr!

Posted Wednesday, February 13, 2013 2:19 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 4:49 AM
Points: 70, Visits: 55

Wow... you're so lucky! that is a perfect opportunity to start learning some powershell :)

If you want the easy solution, try asking uncle google if he knows an application called "discovery wizard for sql server" - pretty sure that would cover your needs on this.

Post #1419351

- Win

Posted Wednesday, February 13, 2013 3:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 PM
Points: 86, Visits: 321

Kleeeek... w00t

I am good to learn new techies.. But, its a mandatory thing to report today.
Trying to get it done.

Thanks.

Cheers,
- Win
"Dont Judge a Book by its Cover"

Post #1419367

opc.three

Posted Wednesday, February 13, 2013 5:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

The MAP Tool from Microsoft has done well by me in the past. It gave me an inventory of all SQL Servers on my network and some decent reports including the info you mentioned. I haven't used it in a couple years and there have been new versions but unless it took a huge leap backward it should be able to get you what you need pretty quickly.

Microsoft Assessment and Planning (MAP) Toolkit for SQL Server 2012

PS I get that you have a deadline but down the line know that learning PowerShell is well worth your effort Wink

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419411

- Win

Posted Wednesday, February 13, 2013 7:11 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 PM
Points: 86, Visits: 321

Thank You very much for your efforts and help towards my issue.

I tried with the tool 'MAP' you specified, but it is not deteting the Domain group.
Trying many options, but none worked for me.

Cheers,
- Win
"Dont Judge a Book by its Cover"

Post #1419500

opc.three

Posted Wednesday, February 13, 2013 7:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

Have you checked the MAP FAQ and MAP Forum pages available from the page I linked to?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419503

Lowell

Posted Wednesday, February 13, 2013 7:22 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 11,792, Visits: 28,078

I'd go with the powershell as well, but here's one more alternative:
I've used SQLRecon in the past, which is free and did the job for me, but was last created in 2005;

http://www.specialopssecurity.com/labs/sqlrecon/

I just ran it on my network again with default settings, and noticed it did not find any of my local instances like \SQL2005 and \SQL2012 , only my default instance, which is running 2008R2.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1419505

- Win

Posted Wednesday, February 13, 2013 10:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 PM
Points: 86, Visits: 321

Thanks much Lowell.

I will try and update. I am worried if I run this tool in my environment, may be in a centralised server, it will send alerts to network teams.
Since it also checks the port numbers and versions. It is a banking domai, so dont wanna create alarm.

I am trying to work on Powershell, but it takes time for me to understand and implement.
After all I cannot get that done in one night.

Appreciate all your efforts.

Cheers,
- Win
"Dont Judge a Book by its Cover"

Post #1419816

« Prev Topic | Next Topic »

Permissions