Recentemente, consultei nossa ferramenta interna de inventário de banco de dados para obter uma lista de servidores, instâncias e bancos de dados e adicionei o status correspondente a cada servidor, instância e banco de dados.
Diagrama de Relacionamento
Server ˂-- 1 : n --˃ Instance ˂-- 1 : n --˃ Database
˄ ˄ ˄
| | |
| 1 : 1 |
| | |
| ˅ |
+-- 1 : 1 --˃ Status ˂-- 1 : 1 --+
Leia como:
...um servidor pode ter múltiplas instâncias
...uma instância pode ter vários bancos de dados
...um servidor, uma instância e um banco de dados podem ter um status
Configurar
Tabela de status
CREATE TABLE [Status]
(
StatusID int,
StatusName char(20),
);
Dados de status
INSERT INTO [Status] (StatusID, StatusName)
VALUES
(1,'Productive'),
(2,'Prod ACC'),
(3,'Prod APP'),
(4,'Test'),
(5,'Test ACC'),
(6,'Test APP'),
(7,'OFFLINE'),
(8,'Reserved'),
(9,'Decommisioned');
Tabela de servidores
CREATE TABLE [Server]
(
ServerID int,
ServerName char(20),
ServerStatusID int
);
Dados do servidor
INSERT INTO [Server] (ServerID, ServerName, ServerStatusID)
VALUES
(1,'FirstServer',1),
(2,'SecondServer',2),
(3,'ThirdServer',5),
(4,'FourthServer',8),
(5,'FifthServer',8);
Tabela de Instâncias
CREATE TABLE [Instance]
(
InstanceID int,
ServerID int,
InstanceName char(30),
InstanceStatusID int
);
Dados da instância
INSERT INTO [Instance]
(InstanceID, ServerID, InstanceName, InstanceStatusID)
VALUES
(1,1,'GENERAL',1),
(2,1,'TAXES',1),
(3,2,'GENERAL',9),
(4,2,'SOCIAL',2),
(5,3,'GENERAL',5),
(6,3,'FBI',8),
(7,5,'COMINGSOON',8);
Tabela de banco de dados
CREATE TABLE [Database]
(
DatabaseID int,
InstanceID int,
DatabaseName char(30),
DatabaseStatusID int
);
Dados do banco de dados
INSERT INTO [Database]
(DatabaseID, InstanceID, DatabaseName, DatabaseStatusID)
VALUES
(1,1,'master',1),
(2,1,'model',1),
(3,1,'msdb',1),
(4,1,'UserDB1',1),
(5,2,'master',1),
(6,2,'model',1),
(7,2,'msdb',1),
(8,2,'TaxesDB',1),
(9,4,'master',2),
(10,4,'model',2),
(11,4,'msdb',2),
(12,4,'HealthCareDB',2),
(13,5,'master',5),
(14,5,'model',5),
(15,5,'msdb',5),
(16,5,'GeneralUserDB',5),
(17,6,'master',8),
(18,6,'model',8),
(19,6,'msdb',8),
(20,6,'CriminalDB',8);
Instrução SELECT sem tabela de status envolvida
A instrução SELECT inicial envolvia simplesmente juntar as três tabelas: servidor, instância, banco de dados e era a seguinte:
-- Simple SELECT to get all information on Servers, Instances and Databases
-- The status of the server, instance or database is not returned
SELECT
ServerName,
InstanceName,
DatabaseName
FROM [Server] as srv
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID;
Resultados de 1. Declaração
POR FAVOR, OBSERVE ISSO...
- existe um servidor sem instância e banco de dados
- há uma instância sem banco de dados
Nome do servidor | Nome da instância | Nome do banco de dados |
---|---|---|
Primeiro Servidor | EM GERAL | mestre |
Primeiro Servidor | EM GERAL | modelo |
Primeiro Servidor | EM GERAL | msdb |
Primeiro Servidor | EM GERAL | UsuárioDB1 |
Primeiro Servidor | IMPOSTOS | mestre |
Primeiro Servidor | IMPOSTOS | modelo |
Primeiro Servidor | IMPOSTOS | msdb |
Primeiro Servidor | IMPOSTOS | ImpostosDB |
Segundo Servidor | EM GERAL | nulo |
Segundo Servidor | SOCIAL | mestre |
Segundo Servidor | SOCIAL | modelo |
Segundo Servidor | SOCIAL | msdb |
Segundo Servidor | SOCIAL | HealthCareDB |
Terceiro Servidor | EM GERAL | mestre |
Terceiro Servidor | EM GERAL | modelo |
Terceiro Servidor | EM GERAL | msdb |
Terceiro Servidor | EM GERAL | GeralUserDB |
Terceiro Servidor | FBI | mestre |
Terceiro Servidor | FBI | modelo |
Terceiro Servidor | FBI | msdb |
Terceiro Servidor | FBI | CriminalDB |
Quarto Servidor | nulo | nulo |
Quinto Servidor | EM BREVE | nulo |
Instrução SELECT envolvendo tabela de status
Na próxima instrução decido adicionar o status a cada elemento (servidor, instância, banco de dados) e JOIN
editar cada tabela com a Status
tabela da seguinte forma:
-- Advanced SELECT to get all information on Servers, Instances and Databases
-- including their status
SELECT
ServerName,
srvst.StatusName,
InstanceName,
insst.StatusName,
DatabaseName,
dbsst.StatusName
FROM [Server] as srv
JOIN [Status] as srvst
ON srv.ServerStatusID = srvst.StatusID
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
JOIN [Status] as insst
ON ins.InstanceStatusID = insst.StatusID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID
JOIN [Status] as dbsst
ON dbs.DatabaseStatusID = dbsst.StatusID
;
Resultados de 2. Declaração
Para minha surpresa o servidor sem instância e banco de dados e o servidor com instância mas sem banco de dados não estavam mais listados:
Nome do servidor | StatusNome | Nome da instância | StatusNome | Nome do banco de dados | StatusNome |
---|---|---|---|---|---|
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | mestre | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | modelo | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | msdb | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | UsuárioDB1 | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | mestre | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | modelo | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | msdb | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | ImpostosDB | Produtivo |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | mestre | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | modelo | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | msdb | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | HealthCareDB | ACC de produção |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | mestre | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | modelo | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | msdb | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | GeralUserDB | Teste ACC |
Terceiro Servidor | Teste ACC | FBI | Reservado | mestre | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | modelo | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | msdb | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | CriminalDB | Reservado |
Descobertas / Solução
Depois de verificar várias opções com uma abordagem de tentativa e erro, descobri que JOIN
on the Status
table teve que ser alterado para a LEFT JOIN
para permitir que a instrução exibisse o servidor sem uma instância ou um banco de dados e exibisse a instância sem um banco de dados :
-- Advanced SELECT to get all information on Servers, Instances and Databases
-- including their status
SELECT
ServerName,
srvst.StatusName,
InstanceName,
insst.StatusName,
DatabaseName,
dbsst.StatusName
FROM [Server] as srv
LEFT JOIN [Status] as srvst
ON srv.ServerStatusID = srvst.StatusID
LEFT JOIN [Instance] as ins
ON srv.ServerID = ins.ServerID
LEFT JOIN [Status] as insst
ON ins.InstanceStatusID = insst.StatusID
LEFT JOIN [Database] as dbs
ON ins.InstanceID = dbs.InstanceID
LEFT JOIN [Status] as dbsst
ON dbs.DatabaseStatusID = dbsst.StatusID;
Resultados de 3. Declaração
Nome do servidor | StatusNome | Nome da instância | StatusNome | Nome do banco de dados | StatusNome |
---|---|---|---|---|---|
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | mestre | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | modelo | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | msdb | Produtivo |
Primeiro Servidor | Produtivo | EM GERAL | Produtivo | UsuárioDB1 | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | mestre | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | modelo | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | msdb | Produtivo |
Primeiro Servidor | Produtivo | IMPOSTOS | Produtivo | ImpostosDB | Produtivo |
Segundo Servidor | ACC de produção | EM GERAL | Desativado | nulo | nulo |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | mestre | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | modelo | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | msdb | ACC de produção |
Segundo Servidor | ACC de produção | SOCIAL | ACC de produção | HealthCareDB | ACC de produção |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | mestre | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | modelo | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | msdb | Teste ACC |
Terceiro Servidor | Teste ACC | EM GERAL | Teste ACC | GeralUserDB | Teste ACC |
Terceiro Servidor | Teste ACC | FBI | Reservado | mestre | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | modelo | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | msdb | Reservado |
Terceiro Servidor | Teste ACC | FBI | Reservado | CriminalDB | Reservado |
Quarto Servidor | Reservado | nulo | nulo | nulo | nulo |
Quinto Servidor | Reservado | EM BREVE | Reservado | nulo | nulo |
Material de referência
Aqui está um link para o db<>fiddle para reproduzir minhas descobertas.
Pergunta
Por que o SQL Server exige um LEFT JOIN
na Status
tabela para itens filhos que não existem e para que a consulta exiba esses itens?