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!

[SNUG AM]

Thats, correct it depends how the database login is configured on those servers.  It will fetch the details of DB. you can refer the SQL patterns. 

[Jason82]

The credential of type "Windows" that is used by Discovery for the Windows OS will be able to detect that MS SQL is running. In Phase 4 when Application Dependency Mapping kicks off the gets the running processes and open ports, the Process Classification will trigger the "MSSql DB On Windows" pattern when it see sqlservr.exe is running. The "Windows" credential will have sufficient permissions to create the MSFT SQL Instance CI, but may not have access to the catalog.

You configure the Windows Authentication on the SQL Server to grant the viewer role to the account used by the "Windows" credential so you only have to manage one account. Alternatively, you can create an Applicative Credential for MSFT SQL Instance if the SQL Server Authentication is being used. 

[ChristianE]

Hi!

Unfortunately almost the whole IT world have got the terminology wrong regarding servers and databases in my view. And because of that we suffer from this endless confusion and misunderstanding that costs us billions in productivity losses year after year.

As I see it and also in accordance with the classic client server concept the SQL Server instance is the server and nothing else, hence the software product that is the executable is called SQL Server. The SQL Server is not a database and it's also not a computer system like Windows. It's wrong and misleading to call SQL Server a database, the databases are contained within SQL Server. SQL Server is the interface to control and use your databases. It's also wrong to say SQL Server and mean the host operating system or the computer that the server runs on. SQL Server is the running executable that serves client connections on a tcp port or via shared memory to manipulate databases, if we only could stick to that definition and no to deviate.

I've looked into the ServiceNow discovery in our company and I really dislike what I see, to the point that I almost find it useless. Under "Database Servers" I actually don't find the usual misunderstanding in the form of computers but an unusual variant of <database>@<SQL Server> and to top it all under the heading "Databases". Yes, it actually shows databases in the respective Database Server but why does a link "Database Servers" take you to a list of databases. Terrible! Then we have the other menu item "Database Instances" that really is showing you "Database Servers" in the sence that I've presented above. It's tragic that the terminology "Database Instances" is used for what is really "Database Servers"! PLEASE STOP CALLING SQL SERVERS DATABASES! The third menu item is called database catalogs but that is not populated at all and from what I've read in the posts above it's meant to hold the SQL Server system databases master, model and msdb.

I've spoken to our ServiceNow manager and complained about the discovery and the missnaming, in my view, of the items and all the confusing and wrongly named menu items. When it looks like this in the ServiceNow CMDB it's hard to see how we can use this for something meaningful.

rgds Christian