SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
- Win
- Win
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 625
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"
DBArrr!
DBArrr!
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 78
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 Smile

Hope the idea helps you - this is how i've collected my instance information.
- Win
- Win
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 625
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"
DBArrr!
DBArrr!
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 78
Wow... you're so lucky! that is a perfect opportunity to start learning some powershell Smile

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.
- Win
- Win
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 625
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"
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14927 Visits: 14396
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
- Win
- Win
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 625
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"
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14927 Visits: 14396
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28131 Visits: 39939
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

--
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!

- Win
- Win
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 625
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search