7 Inventorying your SQL estate
In the previous chapter, you learned how to find all of the SQL Server instances on your network. Now it’s time to gather essential information about each of those servers and create an inventory of it.
Creating inventories lets you provide access to reports for members of the organization without having to grant access to your SQL instances. Keeping your inventory up to date will help to speed up the planning of migrations and upgrades in particular, because knowing what features are in use can keep upgrades properly planned and on track.
DBAs are often expected to just know the configuration of every host, instance, and database in their estate. If you have only a handful of instances, this may be possible, but for hundreds or thousands, it is unlikely.
In this chapter, we will show you how to use dbatools to build an inventory of the things we’re often expected to know, such as the following:
Armed with this information, you will be able to answer ad hoc questions about a host, an instance, or a database. Combine this knowledge with the skills you learned in chapters 5 and 6, and you will be able to document your entire estate in a SQL database.
Inventorying in depth Years ago, Microsoft’s Kendal Van Dyke created a PowerShell-based SQL Server inventorying tool called SQL Power Doc. SQL Power Doc is, according to Van Dyke, a “collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations.” To explore this toolset, check out the repository at sqlps.io/sqlpowerdoc, and visit SQL Shack’s article “Using SQL Power Doc to Discover, Diagnose and Document SQL Server” at sqlps.io/sqlpowerdocddd.
You’ll also have the beginnings of a baseline (see sqlps.io/baseline): basically, a starting point for comparison, which can help with capacity planning, determining trends, and troubleshooting (because it’s easy to see what’s changed recently).
7.1 SQL features
We’ll begin with finding and documenting your SQL Server features. As a DBA, you may be asked which SQL Server features have been installed on a host. This is especially true during audits, because unused features can needlessly expand your attack surface and increase the amount of time it takes to patch your instances.
One way to find feature usage is to use SQL Server’s built-in feature report generator. In figure 7.1, you can see an example of such a report, called the SQL Server Discovery Report. HTML output is automatically generated when setup.exe is executed and stored in %ProgramFiles%MicrosoftSQL Server nnSetup BootstrapLog<last Setup Session>.
You can also create the Discovery Report manually using the same setup.exe with the /Action=RunDiscovery flag (dbatools.io/validatesqlinstall), as shown in the next code listing.
Listing 7.1 Running the SQL Server Discovery Report from the command line
PS> cd "C:Program FilesMicrosoft SQL Server140Setup BootstrapSQL2017" PS> .setup.exe /Action=RunDiscovery
Those reports are pretty and useful, but generating them took a number of steps. First, we had to log in to the machine, then we had to find the setup file, and then finally, we ran the commands. Imagine if you needed to do this, not just for one server, but for 10 or 10,000 hosts.
Generating feature reports across a vast SQL Server estate provides an exceptional use case for remoting and automation, and we’ve created a command, Get-DbaFeature, which does just that. Get-DbaFeature makes it easy to log in to hundreds of Windows servers and gather all of the features they are using—and it takes just one line of code!
Community inspiration As with many dbatools commands, Get-DbaFeature was based on a blog post. In this case, community member Dave Mason created an excellent tutorial with sample code at sqlps.io/discover, and we couldn’t help but wrap the blog’s code into a dbatools command.
You learned in chapter 4 that you can pass multiple values to the -ComputerName parameter. To gather the SQL feature information about your hosts in your estate, you can run the command in the next code snippet. Note that, as of this writing, Get-DbaFeature works only on Windows hosts.
Listing 7.2 Gathering SQL feature information about Windows hosts
The screenshot in figure 7.2 shows that the command returns the information about every instance on the host (MIRROR is a named instance).
Now that you’ve got your features collected, let’s gather more detailed SQL Server build information.
7.2 Build
As a DBA, it is important for you to know the specific build version of an instance. Knowing precisely what build your instance is using is vital when an application is supported on only a particular version of SQL. Understanding whether your instances are supported by Microsoft and compliant with your organization’s stipulations is an expected requirement for all DBAs. When you are asked this question about an instance, you can use Get-DbaBuildReference to get that information.
Let’s say a project manager walks up to your desk and asks you what version of SQL is running on SQLDEV01. You can easily switch to your PowerShell session and run the following single command to find out.
Listing 7.3 Getting the build information about an instance
PS> Get-DbaBuildReference -SqlInstance SQLDEV01 SqlInstance : SQLDEV01 Build : 11.0.6607 NameLevel : 2012 SPLevel : SP3 CULevel : CU10 KBLevel : {4025925, 4019090} BuildLevel : 11.0.6607 SupportedUntil : 10/9/2018 12:00:00 AM MatchType : Exact Warning :
Less than a quarter of a second after pressing Enter, you can tell them all the information they need. You will save so much time doing this, and with practice it will soon become muscle memory.
The build reference uses a JSON database that is included in the dbatools module. Considering Microsoft’s aggressive release cycle for patches, be sure to keep this index updated by keeping dbatools updated, or using the -Update parameter, as shown next.
Listing 7.4 Updating the build information file
This build database is used in a variety of ways within dbatools. Not only does it help power Get-DbaBuildReference, it is also the data source for our ultra speedy, JavaScript-based SQL build reference website at dbatools.io/builds. We even used it to create a compliance-checker command called Test-DbaBuild and shown in the next code sample. Test-DbaBuild makes it easy for you to ensure that your SQL Servers are at a specific patch level, including the latest.
Listing 7.5 Checking build compliance
Test-DbaBuild provides detailed build information, the most interesting being Compliant. This result makes it easy to determine whether a server is noncompliant and needs to be updated.
Listing 7.6 Results showing noncompliance
Build : 14.0.3192 BuildLevel : 14.0.3192 BuildTarget : 14.0.3208 Compliant : False CULevel : CU15 CUTarget : KBLevel : 4505225 MatchType : Exact MaxBehind : NameLevel : 2017 SPLevel : RTM SPTarget : SqlInstance : SQLDEV01 SupportedUntil : 10/12/2027 12:00:00 AM Warning :
You have gathered the information about the SQL features and versions that are installed across your estate, but you want more. Let’s take a closer look at the SQL Server’s host operating system.
7.3 Host information
It is important to be able to identify the version of the operating system that your instances are running for compliance reasons and also during an incident. Imagine that you are part of the team responding to a significant incident affecting an important system for your company. You are on a conference call with many other colleagues or in the special Slack chat room, and someone asks, “How many logical processors does this host have?” You can use Get-DbaComputerSystem, as shown in the following listing.
Listing 7.7 Getting information about the host operating system
PS> Get-DbaComputerSystem -ComputerName SQLDEV01 ComputerName : SQLDEV01.ad.local Domain : ad.local DomainRole : Member Server IsHyperThreading : True IsSystemManagedPageFile : False Manufacturer : Microsoft Corporation Model : Virtual Machine NumberLogicalProcessors : 4 NumberProcessors : 1 PendingReboot : False ProcessorCaption : Intel64 Family 6 Model 63 Stepping 2 ProcessorMaxClockSpeed : 2397 ProcessorName : Intel(R) Xeon(R) CPU E5-2673 v3 @ 2.40GHz SystemFamily : SystemType : x64-based PC TotalPhysicalMemory : 16.00 GB
Now you’ve got information that will help assist with troubleshooting by allowing you to quickly understand the resource profile of your host server. Get-DbaComputerSystem once helped us discover that a host, which was expected to have 16 GB of RAM, had only 6 GB, which was ultimately determined to be the source of resource contention.
Oftentimes, we also need to know details about the operating system running on a remote host. This knowledge can help us determine a number of things, including which of our servers still need to be updated to the latest version or how many licenses are required for a specific edition of Windows. To gather detailed operating system information from a host, use the Get-DbaOperatingSystem command, as shown here.
Listing 7.8 Getting information about another host operating system
PS> Get-DbaOperatingSystem -ComputerName SQL2016N1.ad.local ComputerName : SQL2016N1.ad.local Manufacturer : Microsoft Corporation Organization : Architecture : 64-bit Version : 10.0.14393 OSVersion : Microsoft Windows Server 2016 Datacenter LastBootTime : 2019-06-23 06:51:07.794 LocalDateTime : 2019-06-27 22:35:27.836 PowerShellVersion : 5.1 TimeZone : (UTC+00:00) Dublin, Edinburgh, Lisbon, London TotalVisibleMemory : 2.56 GB ActivePowerPlan : Balanced LanguageNative : English (United States)
Getting used to running dbatools commands at the command line will enable you to quickly and easily provide information when speed is of the essence. You also want to gather that information about your entire estate and probably, because you are a DBA, store it in a database.
7.4 Databases
Of course, databases are important, too! When you are required to gather information about your databases, you can use Get-DbaDatabase, as shown next.
Listing 7.9 Listing the databases on an instance
You will get more information than just the names of the databases. Take a look at the default results for a single database shown in the following code listing.
Listing 7.10 Output of listing the databases on an instance
ComputerName : SQLDEV01 ① InstanceName : MSSQLSERVER ② SqlInstance : SQLDEV01 ③ Name : dbatoolsBestPractices_SQL1 ④ Status : Normal ⑤ IsAccessible : True ⑥ RecoveryModel : Full ⑦ LogReuseWaitStatus : Nothing ⑧ SizeMB : 16 ⑨ Compatibility : Version130 ⑩ Collation : Latin1_General_CI_AS ⑪ Owner : adspservice ⑫ Encrypted : False ⑬ LastFullBackup : 10/05/2018 07:15:50 ⑭ LastDiffBackup : 08/06/2018 00:01:25 ⑮ LastLogBackup : 08/06/2018 08:11:03 ⑯
① The Windows hostname of the server
③ The SQL Server name (SELECT @@SERVERNAME)
⑤ The database status: Emergency, Normal, Offline, Recovering, Restoring, Standby, and Suspect
⑥ Specifies whether the database is accessible
⑦ The recovery model of the database
⑧ The type of operation on which the reuse of transaction log space is waiting
⑨ The size of the database in megabytes
⑩ The compatibility level of the database
⑪ The collation of the database
⑫ The user account that is the database owner
⑬ The encryption state of the database
⑭ The time of the last full backup of the database
⑮ The time of the last differential backup of the database
⑯ The time of the last log backup of the database
There is much more that you can do with this command than simply list all of databases on an instance and get the information about them. You can return the information for a single database using the -Database parameter with the name of the database. This parameter also accepts a comma-delimited list of databases to return information for multiple databases, as shown next.
Listing 7.11 Getting information about multiple databases
PS> $splatDatabase = @{ SqlInstance = "SQLDEV01" Database = "WideWorldImporters", "AdventureWorks" } PS> Get-DbaDatabase @splatDatabase
As with all dbatools (and, indeed, PowerShell) commands, look at the examples in Get-Help to expand your learning.
7.4.1 Filtering databases returned from Get-DbaDatabase
Some of your instances may hold dozens or even hundreds of databases, but most of the time, you’ll need information from only a small subset. When you are asked for information about only a fraction of your databases, you want to filter the information returned. You can use a number of parameters to filter the results returned from Get-DbaDatabase.
For example, when you need information only about the system databases, use the -ExcludeUser switch parameter. When user databases are all that you require, use the -ExcludeSystem switch parameter. We use these switches on a regular basis, especially when we’re working with backups or conducting audits because different rules usually apply to user and system databases.
7.4.2 Filtering databases returned from Get-DbaDatabase by last backup time
The information returned from the Get-DbaDatabase command includes the times of the last backups. This data can help quickly identify which backups are out of date. You can even use it in the dreaded scenario of having to find a database that has never been backed up.
Full backups are required to recover data in the event of a disaster, and you can use dbatools to quickly list all of the databases without a full backup using the -NoFullBackup parameter, as seen in the next listing.
Listing 7.12 Getting databases without a full backup
If your organizational policy requires that all databases have a full backup within the last 30 days, see the following code snippet to see how to easily find this information.
Listing 7.13 Getting databases without a full backup in the last 30 days
PS> $date = (Get-Date).AddDays(-30) PS> Get-DbaDatabase -SqlInstance SQLDEV01 -NoFullBackupSince $date
You can also use -NoLogBackup and -NoLogBackupSince to filter databases by the time of their last log backup in the same way. These parameters not only check for no log backups, they also filter out any databases that use the SIMPLE recovery model, because log backups are not required for those databases. This helps reduce the noise in your returned data. Knowing which databases have not had log backups in a specific period can also help you better manage your storage capacity requirements—transaction logs grow pretty much until a disk is out of space.
When a user leaves the organization, you may need to identify the databases that the user owns and alter that user. Get-DbaDatabase enables you to filter the databases by the user account that owns the database. As you learned in previous chapters, you can run dbatools commands against multiple instances. The next example shows gathering instance names using a SQL query and passing them to Get-DbaDatabase.
Listing 7.14 Returning databases that are owned by a particular user
# Gather all of the instances from the estate PS> $splatInvokeQuery = @{ SqlInstance = "ConfigInstance" Database = "Instances" Query = "SELECT InstanceName FROM Config" } PS> $SqlInstances = (Invoke-DbaQuery @splatInvokeQuery).InstanceName # Find databases owned by the user PS> Get-DbaDatabase -SqlInstance $instances -Owner adg.sartori
You can even inventory your entire estate for object ownership, as shown in the next listing! This is an essential command to run as part of your outprocessing procedures. Once an employee or organizational member has left the company, it is important to reassign the ownership of their objects.
Listing 7.15 Returning all object owners
PS> Find-DbaUserObject -SqlInstance sql2017, sql2005 ComputerName : SQL2017 InstanceName : MSSQLSERVER SqlInstance : SQL2017 Type : Database Owner : adclaudio.silva Name : agroupdb Parent : sql2017 ComputerName : SQL2017 InstanceName : MSSQLSERVER SqlInstance : SQL2017 Type : Proxy Owner : adsmoore Name : CopyBackupProxy Parent : sql2017 ComputerName : SQL2017 InstanceName : MSSQLSERVER SqlInstance : SQL2017 Type : Credential Owner : ads.bizzotto Name : PowerShell Proxy Account Parent : sql2017 ComputerName : SQL2017 InstanceName : MSSQLSERVER SqlInstance : SQL2017 Type : Credential Owner : adw.s.melton Name : PowerShell Proxy Account Parent : sql2017 ComputerName : SQL2005 InstanceName : MSSQLSERVER SqlInstance : SQL2005 Type : Endpoint Owner : adsander.stad Name : Mirroring Parent : sql2005
If you’d like to return the objects assigned to a specific user, pipe the results to Where-Object and search on Owner.
7.5 Putting it all together into a database
Taking each of the commands that you learned earlier in this chapter, you can finally gather information and store it in a database. In chapter 5, you learned about Write-DbaDataTable, so to gather the information about all of the instances that you found with Find-DbaInstance in chapter 6, you can run the script shown next on a regular basis.
Listing 7.16 Bringing it all together
# Our central instance PS> $sqlconfiginstance = "ConfigInstance" # A comma delimited list of Host names PS> $sqlhosts = "SQLDEV01", "sql1" # A comma-delimted list of SQL instances PS> $sqlinstances = "SQLDEV01","sql1","SQLDEV01SHAREPOINT", "sql1DW" # Create DBAEstate database if not existing PS> New-DbaDatabase -SqlInstance $sqlconfiginstance -Name DBAEstate # Put information about the SQL hosts into the DBAEstate database PS> $splatWriteDataTable = @{ SqlInstance = $sqlconfiginstance Database = "DBAEstate" AutoCreateTable = $true } PS> Get-DbaFeature -ComputerName $sqlhosts | Write-DbaDataTable @splatWriteDataTable -Table Features PS> Get-DbaBuildReference -SqlInstance $sqlinstances | Write-DbaDataTable @splatWriteDataTable -Table SQLBuilds PS> Get-DbaComputerSystem -ComputerName $sqlhosts | Write-DbaDataTable @splatWriteDataTable -Table ComputerSystem PS> Get-DbaOperatingSystem -ComputerName $sqlhosts | Write-DbaDataTable @splatWriteDataTable -Table OperatingSystem PS> Get-DbaDatabase -SqlInstance $sqlinstances | Write-DbaDataTable @splatWriteDataTable -Table Database PS> Find-DbaUserObject -SqlInstance $sqlinstances | Write-DbaDataTable @splatWriteDataTable -Table UserObject
In this chapter, you have learned how to use dbatools to gather information about one or many hosts or SQL instances at the command line. This practice will be useful in your daily life for answering “walk-up” questions and during incident resolution. You have also seen how to load the data about your entire estate into database tables.
As DBAs ourselves, we will end this chapter with a statement about using the right tool for the right job. PowerShell is excellent at gathering information from multiple hosts around your estate, but it’s not the best tool for processing large amounts of data. SQL is much better at doing that, so you will probably want to use the database tables created in the earlier example to create a database that will be better suited to relational querying rather than the flat tables that we have created here. These tables would be perfect for staging tables, with a follow-up process in SQL to transform and load the data as required. This is outside of the scope of this book, but we feel it is an excellent learning opportunity.
7.6 Hands-on lab
Let’s practice what you just read about in this chapter. See if you can complete the following tasks:
Unsure of the answers? You can check your work at dbatools.io/answers.