Obtention de dependencies de database SQL Server croisées

Version SQL Server – 2008 R2

Je travaille sur l'évaluation d'une solution DMS, avec pour objective de prendre en charge la maintenance. La solution d'origine a une database centrale, qui a des données relatives au fabricant. Il a également une database pour chaque revendeur, ce qui signifie qu'il y a beaucoup de dependencies entre bases de données.

Les problèmes:

Pas de documentation DB
Aucun commentaire de code
Beaucoup de tas
Aucune convention de dénomination d'object standard
La database centrale comporte 460 tables et plus de 900 SProcs, en plus d'autres objects
Chaque database de revendeur a plus de 370 tables et 2350 SProcs, en plus d'autres objects

Dans un premier time, je recommand un nettoyage complet de la database, pour lequel il est essentiel de comprendre les dependencies d'objects, y compris les dependencies entre bases de données. J'ai essayé d'utiliser la solution de Red Gate, mais la sortie est beaucoup trop volumineuse. Tout ce que je veux, c'est une list d'objects dans les bases de données qui n'ont pas de dependencies – ils ne dépendent pas d'autres objects, ni d'objects qui en dépendent.

Voici le script que j'ai utilisé pour get une list de dependencies:

SELECT DB_NAME() referencing_database_name, OBJECT_NAME (referencing_id) referencing_entity_name, ISNULL(referenced_schema_name,'dbo') referenced_schema_name, referenced_entity_name, ao.type_desc referenced_entity_type, ISNULL(referenced_database_name,DB_NAME()) referenced_database_name FROM sys.sql_expression_dependencies sed JOIN sys.all_objects ao ON sed.referenced_entity_name = ao.name

Je vais créer une table – Dépendances – dans laquelle je vais insert cet set de résultats de chaque DB. À l'étape suivante, je vais également créer une autre table – AllObjects – qui contiendra une list de tous les objects dans les bases de données. Voici le script pour faire ceci:

 SELECT DB_NAME() DBName, name, type_desc FROM sys.all_objects WHERE type_desc IN ( 'VIEW', 'SQL_TABLE_VALUED_FUNCTION', 'SQL_STORED_PROCEDURE', 'SQL_INLINE_TABLE_VALUED_FUNCTION', 'USER_TABLE', 'SQL_SCALAR_FUNCTION' )

Maintenant, une list de noms de cette table, qui n'apparaissent pas dans la colonne referenced_entity_name dans la table des dependencies, devrait donner une list d'objects que je cherche.

 SELECT AO.DBName, AO.name, AO.type_desc FROM AllObjects AO LEFT OUTER JOIN Dependencies D ON D.referenced_database_name = AO.DBName AND D.referenced_entity_name = AO.name AND D.referenced_entity_type = AO.type_desc WHERE D.referenced_database_name IS NULL AND D.referenced_entity_name IS NULL AND D.referenced_entity_type IS NULL

Maintenant les questions:

Certaines dependencies d'object semblent être manquantes dans la sortie. Qu'est-ce que je rate?
Comment puis-je valider que mes résultats sont corrects?
Je veux dire, est-ce qu'il y a une façon différente de faire cela, donc je peux comparer les résultats et vérifier?

Merci d'avance,

Raj

Vous pouvez comparer vos résultats à ceux trouvés par le script suivant. Voici l' article complet

 CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS SET NOCOUNT ON; CREATE TABLE #databases( database_id int, database_name sysname ); INSERT INTO #databases(database_id, database_name) SELECT database_id, [name] FROM sys.databases WHERE 1 = 1 AND [state] <> 6 /* ignore offline DBs */ AND database_id > 4; /* ignore system DBs */ DECLARE @database_id int, @database_name sysname, @sql varchar(max); CREATE TABLE #dependencies( referencing_database varchar(max), referencing_schema varchar(max), referencing_object_name varchar(max), referenced_server varchar(max), referenced_database varchar(max), referenced_schema varchar(max), referenced_object_name varchar(max) ); WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN SELECT TOP 1 @database_id = database_id, @database_name = database_name FROM #databases; SET @sql = 'INSERT INTO #dependencies select DB_NAME(' + convert(varchar,@database_id) + '), OBJECT_SCHEMA_NAME(referencing_id,' + convert(varchar,@database_id) +'), OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), referenced_server_name, ISNULL(referenced_database_name, db_name(' + convert(varchar,@database_id) + ')), referenced_schema_name, referenced_entity_name FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies'; EXEC(@sql); DELETE FROM #databases WHERE database_id = @database_id; END; SET NOCOUNT OFF; SELECT * FROM #dependencies;

Oh, MS a fait un bon effort pour détecter les dependencies entre les bases de données avec sys.sql_expression_dependencies, mais j'ai vu qu'il manquait des choses auparavant. Dans votre cas, je findais un exemple d'une dépendance manquante, et commencer à revenir en arrière: l'avez-vous laissé tomber de votre requête comment? Si c'est le cas, corrigez votre requête. Est-ce que sys.sql_expression_dependencies omet une certaine class de dependencies? Sous quelles conditions? Le SQL dynamic est-il à blâmer? etc.

Vous devez également exécuter sp_refreshsqlmodule pour chaque object dans sys.sql_modules, puis réexécuter votre code. Il oblige SQL Server à actualiser les informations de dépendance (au mieux de ses capacités).

À présent, pour la validation, configurez une trace et écoutez l'événement 114, «Evénement d'access aux objects du schéma d'audit», plus les events de début et de fin pour les appels de procédure stockée et / ou RPC. Inclure les colonnes DatabaseName , ParentName , ParentName , ServerName , SPID et RequestID (pour les connections compatibles MARS). Peut-être d'autres aussi. "Evénement d'access aux objects du schéma d'audit" se produit chaque fois qu'un object est accédé, donc exercez l'application pendant que cette trace est en cours d'exécution, puis rassemblez datatables en utilisant SPID + RequestId et comparez-les à vos résultats en utilisant sys.sql_expression_dependencies. Si quelque chose est dans datatables de trace qui n'apparaissent pas dans vos données de dependencies, alors vous avez manqué quelque chose.