Post reply

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

  • February 13, 2013 at 12:22 am

    #268498

    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"

  • February 13, 2013 at 1:41 am

    #1587315

    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.

  • February 13, 2013 at 1:59 am

    #1587322

    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"

  • February 13, 2013 at 2:19 am

    #1587327

    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.

  • February 13, 2013 at 3:20 am

    #1587339

    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"

  • February 13, 2013 at 5:03 am

    #1587372

    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

  • February 13, 2013 at 7:11 am

    #1587444

    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"

  • February 13, 2013 at 7:20 am

    #1587447

    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

  • February 13, 2013 at 7:22 am

    #1587449

    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!

  • February 13, 2013 at 10:51 pm

    #1587716

    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"

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

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