0
Shares
0
0
0

Este post tiene por objeto definir que es el análisis de dependencias en SQL Server así como enumerar que herramientas tenemos, sus limitaciones y como hace la herramienta Health Check de SOLIDQ para superarlas.

¿Qué es el Análisis de Dependencias?

El análisis de dependencias es el estudio que se realiza para conocer las relaciones que existen entre los objetos de SQL Server. Las relaciones se pueden realizar a varios niveles:

  • Columnas
  • Tablas
  • Bases de datos
  • Instancias

Este análisis es fundamental a la hora de migrar bases de datos, ya que hay veces que por ejemplo procedimientos, vistas o funciones utilizan otros objetos de otras bases de datos o incluso de otras instancias mediante linked servers, queries dinámicas o Rowset Functions (OPENDATASOURCE, OPENQUERY, OPENXML, OPENROWSET) etc… .Sin descubrir estas dependencias lo más seguro es que una vez migrada la instancia hubiera funcionalidades que dejarían de funcionar debido a que ya no se tiene acceso a estos objetos.

¿Qué herramientas nos proporciona SQL Server?

SQL Server nos proporciona una DMV y dos DMFs que son:

  • DMV
  • sys.sql_expression_dependencies
  • DMFs
  • sys.dm_sql_referenced_entities(‘schema_name.referenced_entity_name’, ‘<referenced_class> ‘)
  • sys.dm_sql_referencing_entities(‘schema_name.referenced_entity_name’, ‘<referenced_class> ‘)

La definición que nos da Microsoft para estos objetos es la siguiente:

sys.sql_expression_dependencies

Contiene una fila para cada dependencia por nombre en una entidad definida por el usuario en la base de datos actual. Se crea una dependencia entre dos entidades cuando una entidad, llamada entidad a la que se hace referencia, aparece por nombre en una expresión de SQL persistente de otra entidad, llamada entidad que hace la referencia.
Por ejemplo, si en la definición de una vista se hace referencia a una tabla, la vista,
como entidad que hace la referencia, depende de la tabla, la entidad a la que se hace referencia.
Si desapareciera la tabla, la vista sería inservible.

sys.dm_sql_referencing_entities

Devuelve una fila para cada entidad en la base de datos actual que hace referencia por nombre a otra entidad definida por el usuario. Se crea una dependencia entre dos entidades cuando una entidad, llamada entidad a la que se hace referencia, aparece por nombre en una expresión de SQL persistente de otra entidad, llamada entidad que hace la referencia.
Por ejemplo, si un tipo definido por el usuario (UDT) se especifica como la entidad a la que se hace referencia, esta función devuelve cada entidad definida por el usuario que hace referencia a ese tipo por nombre en su definición.
La función no devuelve las entidades en otras bases de datos que pueden hacer referencia a la entidad especificada. Se puede ejecutar esta función en el contexto de la base de datos master para devolver un desencadenador DDL de nivel de servidor como una entidad que hace la referencia.

sys.dm_sql_referenced_entities

Devuelve una fila para cada entidad definida por el usuario a la que se hace referencia por nombre en la definición de la entidad de referencia especificada. Se crea una dependencia entre dos entidades cuando una entidad definida por el usuario, llamada entidad a la que se hace referencia, aparece por nombre en una expresión SQL persistente de otra entidad definida por el usuario, llamada entidad de referencia.
Por ejemplo, si un procedimiento almacenado es la entidad especificada de referencia, esta función devuelve todas las entidades definidas por el usuario a las que se hace referencia en el procedimiento almacenado, como tablas, vistas, tipos definidos por el usuario (UDT) u otros procedimientos almacenados.

Como se puede ver en las definiciones anteriores las diferencias entre ellas son meros matices y no permiten distinguir a primera vista la diferencia entre ellas. Vamos a verlas con un ejemplo tomando de referencia el siguiente procedimiento:

Code Snippet
  1. ALTER PROCEDURE [dbo].[uspGetManagerEmployees]
  2.     @BusinessEntityID [int]
  3. AS
  4. BEGIN
  5.     SET NOCOUNT ON;
  6.     — Use recursive query to list out all Employees required for a particular Manager
  7.     WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) — CTE name and columns
  8.     AS (
  9.         SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 — Get the initial list of Employees for Manager n
  10.         FROM [HumanResources].[Employee] e
  11.             INNER JOIN [Person].[Person] p
  12.             ON p.[BusinessEntityID] = e.[BusinessEntityID]
  13.         WHERE e.[BusinessEntityID] = @BusinessEntityID
  14.         UNION ALL
  15.         SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 — Join recursive member to anchor
  16.         FROM [HumanResources].[Employee] e
  17.             INNER JOIN [EMP_cte]
  18.             ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
  19.             INNER JOIN [Person].[Person] p
  20.             ON p.[BusinessEntityID] = e.[BusinessEntityID]
  21.         )
  22.     — Join back to Employee to return the manager name
  23.     SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS ‘ManagerFirstName’, p.[LastName] AS ‘ManagerLastName’,
  24.         [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] — Outer select from the CTE
  25.     FROM [EMP_cte]
  26.         INNER JOIN [HumanResources].[Employee] e
  27.         ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
  28.             INNER JOIN [Person].[Person] p
  29.             ON p.[BusinessEntityID] = e.[BusinessEntityID]
  30.     ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
  31.     OPTION (MAXRECURSION 25)
  32. END;

Vamos a lanzar tres consultas (una con cada objeto de SQL Server):

Code Snippet
  1. SELECT g.* FROM sys.objects o JOIN sys.schemas s on o.schema_id = s.schema_id CROSS APPLY sys.dm_sql_referenced_entities(s.name + ‘.’ + o.name, ‘OBJECT’) g where o.name = ‘uspGetManagerEmployees’ order by referenced_schema_name
  3. SELECT g.*,o.* FROM sys.objects o JOIN sys.schemas s on o.schema_id = s.schema_id CROSS APPLY sys.dm_sql_referencing_entities(s.name + ‘.’ + o.name, ‘OBJECT’) g  where g.referencing_entity_name = ‘uspGetManagerEmployees’
  5. SELECT d.* FROM sys.sql_expression_dependencies d join sys.objects o on d.referencing_id = o.object_id where o.name = ‘uspGetManagerEmployees’ order by referenced_schema_name

El resultado es el siguiente:

Análisis de Dependencias en SQL Server

La primera query usa la DMF sys.dm_sql_referenced_entities y muestra las dependencias a nivel de columna, ver la columna referenced_minor_name.

La segunda query usa la DMF sys.dm_sql_referenced_entities muestra las tablas de las que depende el procedimiento “uspGetManagerEmployees”, (solo tablas).

La tercera query usa la DMV sys.sql_expression_dependencies muestra las dependencias a nivel de tablas y otros objetos del mismo nivel (CTES o alias) de las que depende el procedimiento “uspGetManagerEmployees”.

Como se puede ver existen similitudes entre ellas:

sys.dm_sql_referenced_entities y sys.sql_expression_dependencies tienen columnas comunes como nombre del servidor (rectángulo rojo), nombre de base de datos, esquema, objeto (rectángulo negro) y entidad menor (rectángulo naranja). Como se puede ver la diferencia es que la primera query en la columna entidad menor pone los nombres de las columnas y en la tercera no.

En este ejemplo el objetivo era ver de que objetos depende el procedimiento “uspGetManagerEmployees”, pero en nuestro caso y si realizáramos una migración nos interesarían el servidor o la base de datos.

Un ejemplo podría ser el siguiente, supongamos que tenemos una bbdd y en ella existe un procedimiento que contiene una consulta que devuelve los datos de otra instancia de base de datos:

Code Snippet
  1. CREATE DATABASE LUIS
  2. GO
  3. USE LUIS
  5. CREATE PROCEDURE PROC_DEPENDIENTE AS
  7. SELECT [CustomerID]
  8.       ,[PersonID]
  9.       ,[StoreID]
  10.       ,[TerritoryID]
  11.       ,[AccountNumber]
  12.       ,[rowguid]
  13.       ,[ModifiedDate]
  14.   FROM [SOLIDQPCSQL2008R2].[AdventureWorks2008].[Sales].[Customer]
  15. GO
  16. EXEC PROC_DEPENDIENTE

El resultado con la siguiente consulta sería:

Code Snippet
  1. SELECT d.* FROM sys.sql_expression_dependencies d join sys.objects o on d.referencing_id = o.object_id

Análisis de Dependencias en SQL Server

Esto quiere decir que si migramos la bbdd Luis a otra instancia de SQL Server deberíamos crear un linked server a “SOLIDQPCSQL2008R2” para que esta query funcione.

Limitaciones

El ejemplo anterior demuestra que las DMFs y DMV anteriores funcionan, pero claro no vivimos en un mundo perfecto y como siempre la realidad supera a la ficción, veamos que pasa si el procedimiento cambia un poco:

Code Snippet
  1. CREATE PROCEDURE PROC_DEPENDIENTE1 (@linkedServer VARCHAR(MAX))
  2. AS
  4. DECLARE @SQL VARCHAR(MAX)
  6. SET @SQL = N’SELECT [CustomerID]
  7.       ,[PersonID]
  8.       ,[StoreID]
  9.       ,[TerritoryID]
  10.       ,[AccountNumber]
  11.       ,[rowguid]
  12.       ,[ModifiedDate]
  13.   FROM ‘ + @linkedServer
  15. EXEC(@SQL)
  17. GO
  18. EXEC PROC_DEPENDIENTE1 ‘[SOLIDQPCSQL2008R2].[AdventureWorks2008].[Sales].[Customer]’
  19. GO
Code Snippet
  1. CREATE PROCEDURE PROC_DEPENDIENTE2
  2. AS
  4. DECLARE @SQL VARCHAR(MAX)
  6. SET @SQL = N’SELECT [CustomerID]
  7.       ,[PersonID]
  8.       ,[StoreID]
  9.       ,[TerritoryID]
  10.       ,[AccountNumber]
  11.       ,[rowguid]
  12.       ,[ModifiedDate]
  13.   FROM [SOLIDQPCSQL2008R2].[AdventureWorks2008].[Sales].[Customer]’
  15. EXEC(@SQL)
  17. GO
  18. EXEC PROC_DEPENDIENTE2

Estos dos nuevos procedimientos realizan la misma consulta que el primero pero si ejecutamos la query de las dependencias veros que la DMV no es capaz de detectar la dependencia. En el procedimiento PROC_DEPENDIENTE1 lo que hicimos fue indicarle por parámetro el linked server y concatenarlo al resto de la consulta. En el procedimiento PROC_DEPENDIENTE2 metimos toda la consulta en una variable.

Pues bien:

Code Snippet
  1. SELECT
  2.     o.name,d.referenced_server_name,d.referenced_database_name,
  3.     d.referenced_schema_name,d.referenced_entity_name
  4. FROM
  5.     sys.sql_expression_dependencies d join sys.objects o on
  6.         d.referencing_id = o.object_id

Análisis de Dependencias en SQL Server

Y sin embargo existen 3 procedimientos no uno:

Análisis de Dependencias en SQL Server

Como se puede apreciar estas limitaciones son importantes de cara a una migración, es frecuente ver código dinámico. ¿Ante esto que podemos hacer? lo vemos en siguiente punto :).

Health Check

Health Check es una herramienta de SOLIDQ que entre otras muchas cosas y remarco muchas 🙂 nos permite solucionar el problema anterior. Abordamos el problema en dos ámbitos:

  1. Revisamos la definición de las vistas, procedimientos y funciones buscando nombres de tres y cuatro partes
  2. Revisamos las trazas de SQL Profiler buscando sentencias con nombres de tres y cuatro partes. Si os estáis preguntando el motivo por el que miramos también en las trazas es que hay aplicaciones que tienen el código Transact SQL embebido en el código de la aplicación (viejas aplicaciones de visual basic, Navision, Maximo, Aplicaciones de informes etc…)

Un nombre de tres partes nos indicaría dependencia de una base de datos que existe en una misma instancia, ej. BaseDeDatos.Esquema.Tabla .

El nombre de cuatro partes nos indicaría dependencia de una base de datos que existe en otra instancia, ej. Instancia.BaseDeDatos.Esquema.Tabla .

Como veis el separador utilizado es el “.”.

Resultados

Los resultados que proporciona Health Check son dos:

  • Una Excel con la siguiente información organizada en hojas:
  • Patrones problemáticos (Queries que podrían tener algún error si se migraran y que hay que corregir)
  • Objetos de usuario creados en la base de datos master
  • Objetos de usuario creados en la base de datos msdb
  • Lista de servidores remotos y linked servers
  • Lista de objetos de tres partes
  • Lista de objetos de cuatro partes

Análisis de Dependencias en SQL Server

En esta imagen se puede ver la base de datos, el objeto con el nombre de tres partes, tipo y la base de datos referencia dentro de las sentencias del objeto.

  • Un archivo Visio con la siguiente información organizada en pestañas:
  • Esquema visual de los linked servers

Análisis de Dependencias en SQL Server

  • Esquema de las bases de datos que contienen nombres de tres partes

Análisis de Dependencias en SQL Server

Conclusión

El análisis de dependencias es imprescindible a la hora de abordar una migración con varias bases de datos implicadas. Las DMVs y DMFs que nos proporciona SQL Server tienen limitaciones que nos pueden complicar la migración.

SOLIDQ dispone de una herramienta (Health Check) que permite el análisis completo para eliminar estas limitaciones reportando tanto en Excel como en Visio las dependencias y objetos a revisar.

 

0 Shares:
Share 0
Tweet 0
Share 0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

¿Desarrollamos paquetes de SSIS o proceso de ETL?

Next article —

Añadir miembros en roles de bases de datos en Analysis Services con Powershell

You May Also Like