Detectar consultas con referencias a bases de datos distintas a la actual

Un problema habitual en todo proyecto de migración, escalabilidad o rearquitectura de aplicaciones y/o servidores de bases de datos es la necesidad de conocer la interdependencia de bases de datos.

Los que me conocen, saben que yo apuesto siempre por molestar al cliente al mínimo y eso implica que aunque suelo pedir información relativa a la arquitectura actual, dependencias, etc, etc,…siempre acabo verificando por mi cuenta la realidad (parafraseando a house: “El paciente siempre miente, aunque no lo sepa” )

Bien, si tenemos que pelearnos con un problema como el que comento (que por ejemplo hayan aplicaciones conectadas a la BBDD X, que lancen queries del estilo “Select * from Y.dbo.tabla_en_bbdd_y”), hay una solución bien sencilla, pero que obviamente tarda su tiempecito

Consiste en usar trazas de profiler para capturar la actividad contra la BBDD. Una vez tenemos esta actividad, nos crearemos una BBDD o tabla en la que incorporaremos información relativa a database_id, databaseName y servername (se sobreentiende que esto se hace porque además del ejemplo que estoy exponiendo, hay que hacer algo parecido para referencias entre instancias, pero se sale del ámbito del post).

Una vez tenemos dicha tabla creada y rellenada (a la que llamaremos dbo.Databases), lo siguiente que tenemos que hacer por tanto es, a grandes rasgos:

1. Obtener los SMTP: BatchCompleted events
2. Obtener el databaseid de los mismos, junto a su TextData NOT NULL e información relevante como hostname, loginname,…y lo que queramos para posteriormente buscar datos en ellos
3. Filtrar conexiones a bases de datos tipo pubs, adventureworks y northwind
4. Por último, obtener aquel texto, en el que se haga referencia a cualquier databasename seguido por “.” y que además no sea el mismo databasename al que estaba la conexión atacando (referencia externa por tanto)

Obviamente pueden haber falsos positivos (igual, tenemos mala suerte y nos sale algun comentario donde se haga referencia,…), pero habrá positivos de haberlos.

No olvideis crear los índices adecuados en la tabla dbo.Databases para que la cosa no vaya lentísima.

Aqui os dejo el código para explorar los resultados:

--
-- ECB: Meta query to detect queries that references databases
--
-- 
declare @ServerName sysname = 'servidor_sql'
declare @trcFile nvarchar(max) = 'path_to_trc_file.trc'
   
      
      
;with subselect as(            
select 
       dbs.dbid,
       dbs.DBName,
       textdata,
       applicationName,
       hostname,
       loginname,
       starttime              
from ::fn_trace_gettable(@trcFile,default) trc
 left join dbo.DataBases dbs on trc.DatabaseID =  dbs.DBId 
                                and dbs.ServerName = @ServerName
                                and dbs.dbid > 4  AND dbs.ServerName not in ('pubs','Northwind','Adventureworks')         
where trc.textdata IS NOT NULL AND trc.EventClass = 12
)
select 
       trc.dbname as db_connected ,       
       dbs.dbName as db_referenced,
       trc.applicationname,
       trc.hostname,
       trc.loginname,
       trc.starttime,
       cast(trc.textdata as nvarchar(1000)) as [definition]
from subselect trc, dbo.DataBases dbs
where dbs.servername = @ServerName 
    and dbs.DBId <> trc.DBId
    and (trc.TextData LIKE '%'+dbs.dbname+'.%'
           or textdata LIKE '%'+dbs.dbname+'].%'
           )  

Aqui veis el plan de ejecución, que siempre me gusta ver

Por último solo me queda comentar que esto mismo hay que hacerlo con los objetos de BBDD (procedimientos almacenados,funciones,…) y con servidores vinculados y aperturas tipo openrowset y similares.

Salu2!