Inventory SQL server - output file for SQL newbie

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Tony Pagliaro Tony Pagliaro 4 months, 4 weeks ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #38353
    Profile photo of Adam Hoge
    Adam Hoge
    Participant

    TL:DR – At bottom

    I'm in a new position (for the past 4 months) where I am now responsible to do remote inventory, management and software patching of multiple windows servers. That includes running MSI files, copying over a few files, uploading logs, and my current least favorite, running SQL stored procedures to update databases.

    After a few days messing around, I found powershell and started piecing things together on my own as much as possible to help my with my duties. I'm already started on the powershell month of lunches book and youtube series and have been bouncing around the net piecing things together fairly well on my own. At the same time as I'm learning powershell, I'm also learning databases and SQL Server management. Powershell has certainly been making this learning curve a bit easier for me, but I have a lot to chew through at the moment

    I'll cut to the quick of it.
    Right now I use a modified version of the following script to inventory a unknown server. I have the IT guy at each site run this against a list of servers, and he sends back all the individual machinename.html files.

    This works great, but doesn't tell me much about the SQL server side of things. What I'm looking for is the same kind of inventory script that tells me more than I need to know about the SQL server, the databases that are installed on the server, and perhaps a short summary of each of the databases. The burn on this is, that I don't yet know much about SQL so I tend to miss things the first time. I need someone out there with SQL brains to point me to a script that gets a SQL beginner enough information to be dangerous. Even better, tell me what to add to this existing script so it could get the information if the server had SQL server installed on it and only require one script.

    I've done a LOT of searching out there and have found a LOT of stuff out there on the SMO cmdlets and my head is spinning. I don't know what I need to know, which prevents me from knowing if I'm getting the needed information.

    TL:DR
    I'm a SQL newbie, looking for suggestions on a prebuilt powershell script that I can run aginst a SQL server and then get an output file that I can look at.

    #38356
    Profile photo of AK
    AK
    Participant

    Hello Adam,

    I'm afraid there is no quick script for this. I'd like to mention a few posts.
    This should tell you if MS SQL is installed:
    https://www.mssqltips.com/sqlservertip/2358/using-powershell-to-pull-sql-server-services-configuration-information/
    This one collects server-level configuration:
    https://www.simple-talk.com/sql/sql-training/how-to-document-and-configure-sql-server-instance-settings/
    To get started collecting database-specific settings:
    https://www.mssqltips.com/sqlservertip/1759/retrieve-a-list-of-sql-server-databases-and-their-properties-using-powershell/
    You probably want to know SQL agent job status as well:
    https://www.mssqltips.com/sqlservertip/1798/checking-sql-server-agent-jobs-using-windows-powershell/

    #38483
    Profile photo of Adam Hoge
    Adam Hoge
    Participant

    Thanks a lot for the feedback.

    I've been looking these links over and working on combining all of this as much as possible. I'm also wondering if there isn't a better way to do this with something other than powershell [ gasp ]. I'll post back here if I get a good way to do this.

    #38487
    Profile photo of AK
    AK
    Participant

    SCOM can do many things, but then you are looking at managing/maintaining System Center. .

    #38491
    Profile photo of Tony Pagliaro
    Tony Pagliaro
    Participant

    I have a PS SQL script I made to spit out all the table and column names for a given DB. Is that what you're looking for? I would run it once on a DB, then save as csv and apply a filter in excel. Then if I need to search for a keyword, i just type it in and see what tables and columns return. Really helpful for me while learning T-SQL and writing queries.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.