MS SQL Server Discovery and CMDB tables

[robert_w]

I'm trying to wrap my head around the different CMDB tables that seem to be related to MS SQL Servers.

Out of the box it looks like there are at least 5 different tables specifically for MS SQL:

• MS SQL Server Instance (cmdb_ci_db_mssql_instance)
• MS SQL Database (cmdb_ci_db_mssql_database)
• Database (cmdb_ci_database, type = "Microsoft SQL Server")
• MS SQL Server (cmdb_ci_db_mssql_server)
• MSFT SQL Catalog (cmdb_ci_db_mssql_catalog)

In the instance I'm working on, Discovery has populated data in the first three tables, the MS SQL Server (cmdb_ci_mssql_server) and MSFT SQL Catalog (cmdb_ci_db_mssql_catalog)  tables are empty. I don't know if these two tables have been deprecated, or if they serve another purpose?

As far as I can tell, MS SQL Server Instance represents the running instance of sqlserver.exe, and has a "Runs On" relationship to a Windows Server or Cluster CI, depending on whether the database server is clustered or not. This is the "SQL Server", in simple terms.

The MS SQL Database table looks like it contains the actual database - the logical representation of the tables, triggers, stored procedures etc. that make up the database itself. It also has a "Contained by" relationship with the MS SQL Server Instance - one SQL server could have multiple databases. What's confusing is that in my PDI at least, this tabled is labeled as "Database Catalog" in the left hand navigator, I think Catalog is more an Oracle term? I'm not sure, I'm not a DBA unfortunately.

Lastly the Database table contains all databases, so I also have Postgres and MySQL databases in there. What I can't understand is that if I filter on type = "Microsoft SQL Server", the record count is different than what I see in the MS SQL Database table - I would think they should be the same?

Any input on how these tables are intended to be used is appreciated.

Thanks!

[Daniel Borkowi1]

Hi Robert, use the table, that Discovery populates. The other ones are heritage of old implementations. Also other Database discovery is similar structured.

Hope that helps.

Greets Daniel

[SiD2]

Hi @robert_w 

The last 2 might be deprecated not sure when but currently not used by discovery at all. The 3rd class Database is used only to populate all cloud DBs hence it has type field indicating the same.

[SNUG AM]

Hi Robert, 

• MSFT SQL Catalog (cmdb_ci_db_mssql_catalog) -- This table holds MSSQL's catalogs as Master/Model/Msdb/tempdb.. depending on the configuration setup for the actual database instances.
• To discover this all information discovery should have enough rights on the respective MSSQL DB, then and then only discovery can access catalog data from MSSQL DB, 

"Service Catalogs are named collections of schemas in an SQL-environment"

[robert_w]

Thanks Anand, you referring to a credential that would be either SQL Server auth or Windows Integrated auth (depending on how the database security has been configured) - correct? That would make sense, without the proper credential Discovery should be able to detect a running instance of SQL Server, but won't be able to tell which databases are actually contained in that server.