What is SDIM?
All DBAs should keep track of their Servers/ Instances/ etc not only for their own edification, but for Management and security reasons as well. If you’re not, then you need to, as it comes in incredibly handy even if it isn’t a requirement of the job.
Most of the time, this information is compiled into a spreadsheet of some kind or possibly in a word processing document somewhere. Keeping this data up-to-date and accurate is a pain, especially when you have to break it out into multiple tabs and/or over multiple documents.
You could get a full-blown inventory manager that collects and compiles all the data and organizes it for you. But there’s a definite cost to that solution and not one that all companies will find useful (Read: “It’s not in the budget this year”).
What if you can’t get someone to shell out the money for a product like that? Then you have to either keep with the spreadsheets (yuck) or you need to find another solution with the tools you have.
This is an attempt to do this and make it portable from one system to another.
Repository Server - SQL Server 2016 (2012 if removing temporal tables functionality) or better. PowerShell 3 or better installed.
Clients - Powershell 4 or better.
CMS Server - SQL Server 2012 or better. PowerShell 3 or better installed.
Active Directory Environment
Note SDIM v2.3 and earlier: SDIM will be able to access 2005 - 2022 SQL Server Instances, however, it needs to be installed on 2012 or better. You might be able to get it to work on 2008R2, but that is not a supported setup.
Note SDIM v2.4: SDIM 2.4 can only access from 2012-2022 editions because of its use of the SERVERPROPERTY('ProductUpdateLevel') to capture CU since this is 'new' way that SQL Server updates.
Note SDIM v2.7.1: SDIM 2.7.1 by default has temporal tables for the Srv/DB permissions. This limits the CMS to 2016+. If you would like to install this on 2012/2014 then simply remove these from the table definition and do not deploy the history tables.
Is not a long process, but it does require certain things in a certain order. This will guide you through setting these up. The current version of this guide is for SDIM 2.7.1
The repository server is where you are going to store the data after you have pulled it from your instances. It can be the same server as your CMS or something else entirely. This guide assumes that you are using a CMS and the DBAdmin database. If not, then you'll need to use the correct switches and change the scripts to point to your Server\Instance.Database.
On your Repository Server, run the following script:
DBAdmin.sql
On your Repository Server in the DBAdmin database run the following scripts in this order:
Utility.Schema.sql
Reports.Schema.sql
stage.Schema.sql
dbo.DatabaseList.Table.sql
dbo.InstanceList.Table.sql
dbo.JobList.Table.sql
dbo.ServerList.Table.sql
dbo.ServiceList.Table.sql
dbo.TableList.Table.sql
dbo.tDBPermission.Table.sql
dbo.tDBPermissionHist.Table.sql
dbo.tSrvPermission.Table.sql
dbo.tSrvPermissionHist.Table.sql
stage.tDBPermission.Table.sql
stage.tSrvPermission.Table.sql
Utility.SDIMInfo.Table.sql
Reports.vwGetInstancesGroup_SQLVer.View.sql
Reports.vwGetServers.View.sql
Reports.vwGetServers_Instances_SQLVersion_Instance_FullList.View.sql
On your Repository Server in the DBAdmin database run the following scripts in this order:
dbo.prGetConnectionInformation.StoredProcedure.sql
Utility.prInsertNewServerAndInstance.StoredProcedure.sql
dbo.prUpdateServerList.StoredProcedure.sql
dbo.prUpdateInstanceList.StoredProcedure.sql
dbo.prStageSrvPermissions.StoredProcedure.sql
dbo.prStageDBPermissions.StoredProcedure.sql
dbo.prMergeSrvPermissions.StoredProcedure.sql
dbo.prMergeDBPermissions.StoredProcedure.sql
dbo.prInsertTableList.StoredProcedure.sql
dbo.prInsertServiceList.StoredProcedure.sql
dbo.prInsertNewServerAndInstanceCMS.StoredProcedure.sql
dbo.prInsertJobList.StoredProcedure.sql
dbo.prInsertDatabaseList.StoredProcedure.sql
dbo.prGetTables.StoredProcedure.sql
dbo.prGetSrvPermissions.StoredProcedure.sql
dbo.prGetServersGroup_OS_SP.StoredProcedure.sql
dbo.prGetServerServices.StoredProcedure.sql
dbo.prGetServersAndInstances.StoredProcedure.sql
dbo.prGetServers.StoredProcedure.sql
dbo.prGetServerNames.StoredProcedure.sql
dbo.prGetJobsExt.StoredProcedure.sql
dbo.prGetJobs.StoredProcedure.sql
dbo.prGetInventory.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer_SQLEd_SQLSP.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer_SQLEd.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer.StoredProcedure.sql
dbo.prGetInstancesAndDatabases.StoredProcedure.sql
dbo.prGetInstances.StoredProcedure.sql
dbo.prGetDBPermissions.StoredProcedure.sql
dbo.prGetDatabasesAndSize.StoredProcedure.sql
Utility.prSDIMInfo.StoredProcedure.sql
Utility.prSetLastRun.StoredProcedure.sql
On your Client Servers in the master database run the following scripts in this order. If you currently have these scripts on your servers it will replace the version you have with this one. You can try to use a different version but it may break the process since DB_DataPull will need the "Report" funcionality to return data in a specific format to ingest.
dbo.sp_SrvPermissions.sql
dbo.sp_DBPermissions.sql
Note: If you are using a CMS you can run the script across all servers simultaneously.
On your Repository Server in the DBAdmin database run the following script. This will populate a field or fields that need to be set for your version.
2.7.1_RunOnce.sql
Put DB_DataPull.ps1 on your repository server, somewhere easily accessible. You'll want to pass the parameters into this to pull your data. I would recommend setting up a windows task or SQL job to run this by passing in the necessary parameters. You could also create a batch file that calls it and add parameters in there, your call.
There are defaults for all of these already in the script, the only one you HAVE to change is the CMS Server location with -CMSServer and toggle -UseCMS. If you're not providing a CMS server just ignore this parameter (you will need to provide a list of servers and instances manually using Utility.prInsertNewServerAndInstance).
DB_DataPull.ps1 Parameters
-RepositoryInstance "SomeInstance"
The location of the repository instance relative to the server running the script. Assumed to be the same server "(local)" as this is probably the best practice
-RepositoryDB "SomeDB"
The location of the Repository DB relative to the instance. If you set it up like above, then that should be DBAdmin.
-CMSServer "SomeServer"
The location of the CMS Server. You can pass in an instance name as well "SOMESERVER\SOMEINSTANCE"
-LogDir "C:\SomeDir"
Where to place the log. Log file names will be generated automatically. Default is "C:\Logs" and stores all runs in a single day in one file.
-LogFile "DB_DataPull_Log"
Name of the Log. _$DATE will be appended to the name. Default (even if blank) is DB_DataPull_Log for code compatibility reasons.
-LocalDir "C:\SDIM"
Directory where the DB_DataPull.ps1 file is located. This allows the use of functions to make the code more reusable and clean.
-UseCMS
Toggle on if you're using a CMS Server. If not, it will pull from the ServerList\InstanceList and not truncate the tables.
-Verbose
Gives you lots of feedback.
-Debug
Writes Verbose to log silently.
The servers that will be queried for data should be done so via a service account.
Permssions will need to be done via two steps:
-
Run Permissions.sql on all of the servers you will be collecting on. IF you are using a CMS run the script via that method on all servers.
You can comment out the DBAdmin section for servers that are not the CMS as they only access the CMS DBadmin. -
Give wmi permissions to the service account.
The Hard Way (more secure):
- Open Component Services (dcomcnfg.exe)
- Navigate to DCOM Config (Component Services > Computers > My Computer > DCOM Config)
- In the details pane find "Windows Management and Instrumentation"
- Right click and select 'Properties'
- Go to the 'Security' tab and note the 'Launch And Activation Permissions', and 'Access Permissions'
- Select 'Customize' if not already selected
- Click Edit
- In the Security properties page, click Add
- In the Select Users or Groups popup, add the guest account (for local machine it's just type 'guest' and click 'Check Names' then 'OK', not sure about server in a domain)
- Back in the Security properties page, note that Guest has less permissions by default than 'Everyone'. Give 'Remote Launch' and 'Remote Activation' and 'Local Activation'.
The Easy Way (less secure):
Add the service account to the local Administrators group in lusrmgr.msc
The clients can run these through powershell, or you can create a batch file that passes the parameters in and then create shortcuts and whatnot to the batch (I like to pass PowerShell the -WindowStyle Hidden option). It's fairly simple.
DB_DataPull_FrontEnd.ps1 Parameters
-RepositoryInstance
The location of the repository instance relative to the server running the script. Assumed to be the same server "(local)", but probably won't be if clients are accessing it from their local machines.
-RepositoryDB
The location of the Repository DB relative to the instance. If you set it up like above, then that should be DBAdmin.
This (Simple Database Inventory Manager™) is of course provided free of charge, use-at-your-own-risk. There is no warranty either expressed or implied. If SDIM™ burns down your data center, uninstalls all your favorite toolbars and ruins your best pair of dress socks, I’m not at fault. Remember to back up your databases!
As always, feel free to contact me if you have comments, suggestions or questions.
- Works on 2005-2022.
- Pushed to 2.3 release for those with older environments.
- isProduction column now does not display in Instances list. That was a feature that got removed from everywhere (before 1.0). Finally removed the column.
- HIPAA level feature is now completely removed.
- Never got this one working right and I decided that in the future I’ll go with something a bit more general, like maybe just ‘priority’ or something.
- Never got this one working right and I decided that in the future I’ll go with something a bit more general, like maybe just ‘priority’ or something.
- Fixed a few typos. Me spel gud now.
- Collects CU info via SERVERPROPERTY('ProductUpdateLevel'). This limits SDIM 2.4+ to 2012 or later instances.
- Reports in frontend have been updated to show CU
- Will also show SP for version where that applies, otherwise RTM.
- Will also show SP for version where that applies, otherwise RTM.
- Added parameters instead of hard-coded variables for DB_DataPull_Frontend.ps1
- Cleaned up some code
- CMS will now report on itself as well.
- Expanded Database collection to include more than just size. Now includes:
- Recovery Model
- State
- Compatibility Level
- Encryption Status
- Page Verify
- Much more!
- Recovery Model
- Now collects Table information.
- Note: This process of collection can take some time depending on the number of tables you have.
- Note: This process of collection can take some time depending on the number of tables you have.
- Full Inventory Updated to display all Table information.
- Server and Database Permissions now collected with Kenneth Fisher's scripts.
- sp_DBPermissions V6.2 in master is needed on all Client servers
- sp_SrvPermissions V6.1 in master is needed on all Client servers
- Versioning added to SrvPermissions/DBPermissions so that they can be queried for changes (no UI functionality)
- LastRun added as well as Version number pulled from a table rather than manually entered.
- Fixed a few bugs and cleaned up some column names to make them more clear what they represent.