How To Create An Inventory Database In Sql

By: | Updated: 2018-07-06 | Comments (5) | Related: More > Database Administration

Problem

I have 140+ SQL Server instances to manage, I want to set up an inventory system for all the 2000+ databases in my environment, so I can log the various information of each database, such as the data / log file size, last full backup date, number of users, number of user tables / views in the database, etc.

So how can I build an efficient database inventory system?

Solution

A well-crafted database inventory system is very useful to DBAs in various ways, for example, a long-term inventory system may assist accurate capacity planning, it can also report any abnormal behaviors inside databases.

We first decide what database info we want to collect, then we will consider how to collect these data.

A database has many different properties and in theory, each property can be used as an inventory collection point.

We can find a database property list from SMO database object. For different SQL Server version, there may be different properties. Usually a later version has more properties than early version. For example, in SQL Server 2017, there are 20 new properties compared to SQL Server 2012. This can be demonstrated via the following code:

# Compare the database property difference between sql server 2017 and sql server 2012            import-module            sqlserver            -DisableNameChecking;  #localhost\sql2017 is sql server 2017 instance            $svr17            =            new-object            microsoft.sqlserver.management.smo.server            "localhost\sql2017";            $db17            =            $svr            .databases[            'mssqltips'            ];  #localhost is sql server 2012            $svr12            =            new-object            microsoft.sqlserver.management.smo.server            "localhost";            $db12            =            $svr2012            .databases[            'mssqltips'            ];                        $diff            =            $db17            .Properties.name            |            where            {$db12            .Properties.name            -notcontains            $_};            $diff            .count            $diff

We get the following result:

sql server attributes

We can see there are 20 new properties in SQL Server 2017 database object. It means, in a mixed environment, we may need to pick different sets of collection points for different versions of SQL Server instances.

But in real life, we usually do not need to get every database property for our inventory purpose, I prefer to choose a set of properties that are common in all SQL Server versions above SQL Server 2005. So, I choose the following set as shown in a table structure, about 25 properties in total. Each column name is self-evident in meaning, so I will not explain any further.

            USE            MSSQLTips            if            object_id            (            'dbo.DBInventory'            ,            'U'            )            is            not            null            drop            table            dbo.DBInventory;            create            table            dbo.DBInventory            (            ServerName            varchar            (128)            default            @@servername            ,            DBName            varchar            (128)            default            db_name            ()            ,            DBOwner            varchar            (128)            ,            CreateDate            datetime2            ,            RecoveryModel            varchar            (12)            ,            Status            varchar            (60)            -- online, recovering, offline etc..            ,            CompatibilityLevel            int            ,            DataFileSizeMB            int            ,            LogFileSizeMB            int            ,            DataUsageMB            int            ,            IndexUsageMB            int            ,            SizeMB            decimal(17,2)            ,            Collation            varchar            (60)            ,            UserCount            int            ,            RoleCount            int            ,            TableCount            int            ,            SPCount            int            ,            UDFCount            int            ,            ViewCount            int            ,            DMLTriggerCount            int            ,            IsCaseSensitive            bit            ,            IsTrustWorthy            bit            ,            LastFullBackupDate            datetime2            ,            LastDiffBackupDate            datetime2            ,            LastLogBackupDate            datetime2            );

To create a database inventory using PowerShell is actually not that difficult. Let's first look at a workable solution for getting all database info on one SQL instance. In the following code, I am using my default SQL instance on my computer. The collected database inventory information will be exported to a csv file c:\temp\dbinventory.csv.

            import-module            sqlserver            -DisableNameChecking            ;            $svr_name            =            'localhost'            ;            $csv_file            =            'c:\temp\dbinventory.csv'            $svr            =            New-Object            microsoft.sqlserver.management.smo.server            $svr_name            ;            $svr            .            Databases            |            where            name            -ne            'tempdb'            |            %                          {            $_            |            select            -property              @{l            =            'Server'            ; e            =            {            $_            .            parent            .            name}} `            ,            Name                          `            ,            Owner                          `            ,            CreateDate                          `            ,            RecoveryModel                          `            ,            Status                          `            ,            CompatibilityLevel                          `            ,                          @{l            =            'DataFileSizeMB'            ; e            =                          {            $size            =            0            ;            $_            .            FileGroups            .            foreach( {            $_            .            files            .            foreach({            $size            +=            $_            .            size})});            $size            /            1024            }} `            ,                          @{l            =            'LogFileSizeMB'            ; e            =                          {            $size            =            0            ;            $_            .            Logfiles            .            foreach({            $size            +=            $_            .            size});            $size            /            1024            }} `            ,                          @{l            =            'DataUsageMB'            ; e            =                          {            $_            .            DataSpaceUsage            /            1024            } } `            ,                          @{l            =            'IndexUsageMB'            ; e            =                          {            $_            .            IndexSpaceUsage            /            1024            } } `            ,            Size                          `            ,            Collation              `            ,                          @{l            =            'UserCount'            ; e            =            {            $_            .            users            .            count}} `            ,                          @{l            =            'RoleCount'            ; e            =            {            $_            .            Roles            .            count}}  `            ,                          @{l            =            'TableCount'            ; e            =            {            $_            .            Tables            .            count}}  `            ,                          @{l            =            'SPCount'            ; e            =            {            $_            .            StoredProcedures            .            count}} `            ,                          @{l            =            'UDFCount'            ; e            =            {            $_            .            UserDefinedFunctions            .            count}}  `            ,                          @{l            =            'ViewCount'            ; e            =            {            $_            .            Views            .            count}}  `            ,                          @{l            =            'DMLTriggerCount'            ; e            =            {            $_            .            Triggers            .            count}}  `            ,            CaseSensitive                          `            ,            TrustWorthy                          `            ,            LastBackupDate                          `            ,            LastDifferentialBackupDate                          `            ,            LastLogBackupDate            ;            }            |            Export-Csv            -Path            $csv_file            -force            -NoTypeInformation            ;

My databases in the default SQL instance are like the following:

ssms database list

After running the script in a PowerShell ISE window, we can open the $csv_file with Excel and see it like this:

sql server inventory data

If we want to upload the data into a SQL Server table instead of exporting to a CSV file, we can do so easily by using Write-SQLTableData, which is a cmdlet inside PowerShell SQLServer module.

This code is really simple in logic, but if we want to inventory databases across 100+ SQL Server instances and assume some instances may have 100+ databases (like those SharePoint databases), the code performance is far from satisfactory, it can take 3+ minutes when a SQL instance has more than 200 databases.

In my environment, for about 140+ instances with 2000+ databases, it took about 1 hr 37 min to do the database inventory collection using the code above.

To quicken the process, we need to rely on T-SQL to do the collection. The majority of the collection points can be fetched directly from sys.databases. The rest can be done via other queries and then get linked with the collected data. The detailed algorithm is as follows:

Create a temp table (#T) with the same structure as the database inventory table
Populate #T with sys.databases for those available properties, such as name, owner, CreateDate, Status etc.
Use sp_msforeachdb to dynamically update #T for those missing columns (i.e. not available via sys.databases) per each database.

The whole T-SQL code is as follows:

With the T-SQL script as the core, we will create a PowerShell function, Get-SQLDBInventory, this function takes one parameter, -ServerInstance, which may contain names of one or more SQL Server instances, and the function will return the inventory data of all databases on this or these instances.

Once the script is run and thus Get-SQLDBInventory, we can run the following:

            Get-SQLDBinventory            -serverinstance            'sql_instance_1',            'sql_instance_2',            'sql_instance_3'

And get the inventory info of all databases on sql_instance_1/2/3.

Now we will look at an example to dump the collected data into a central repository table.

First I assume, the central repository table (dbo.DBInventory) is created on my default SQL Server instance inside database [MSSQLTips].

On my computer, I have 4 SQL Server instances, they are default instance and three named instances, i.e. SQL2014, SQL2016, SQL2017. So, to inventory the databases on these four instances, I use the following two lines of codes:

            $dt            =            get-sqldbinventory            -ServerInstance            'localhost',            '.\sql2014',            '.\sql2016',            '.\sql2017'            Write-SqlTableData            -ServerInstance            localhost            -DatabaseName            mssqltips            `            -SchemaName            dbo            -TableName            DBInventory            -InputData            $dt;

Summary

In this tip, we looked at how to quickly export database inventory data into a CSV file with very simple PowerShell script and we then further discussed an efficient way to inventory databases in SQL Server environment and deposit the data into a central repository table.

Once we have the solution, we can schedule the task to run regularly (i.e. daily or weekly), and after we get enough data, we may use the inventory table to do some interesting findings. For example, to check whether there is any database that are in full recovery mode, yet never have its log backup done, or how the database grows in the past 3 months, whether any database owner is of a particular account, etc.

Next Steps

A central database inventory table is very handy for DBA work, and it can boost both DBA's work efficiency and work quality.

Using this tip, you can design your own inventory collection points, for example, you may want to add database replication state or database snapshot isolation state, etc. in your inventory data.

Here are a few similar SQL Server component inventories tips: