Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List sql servers, versions, SP installed in all the servers of environment. Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 12:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:09 PM
Points: 106, Visits: 438
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
Posted Wednesday, February 13, 2013 1:41 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:00 AM
Points: 70, Visits: 60
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
Posted Wednesday, February 13, 2013 1:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:09 PM
Points: 106, Visits: 438
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
Posted Wednesday, February 13, 2013 2:19 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:00 AM
Points: 70, Visits: 60
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
Posted Wednesday, February 13, 2013 3:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:09 PM
Points: 106, Visits: 438
Kleeeek...

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
Posted Wednesday, February 13, 2013 5:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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


__________________________________________________________________________________________________
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
Posted Wednesday, February 13, 2013 7:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:09 PM
Points: 106, Visits: 438
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
Posted Wednesday, February 13, 2013 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Wednesday, February 13, 2013 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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
Posted Wednesday, February 13, 2013 10:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:09 PM
Points: 106, Visits: 438
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 »

Add to briefcase

Permissions Expand / Collapse