Averiguar conexiones externas hacia nuestro SQL Server

Últimamente estoy participando en bastantes proyectos de migración de SQL Server 2000 a SQL Server 2008 R2. Independientemente de la arquitectura existente en cada cliente, siempre hemos de conocer la topologia de consultas que se lanzan y sobre todo QUIEN las lanza.

En un proyecto de migración nunca podemos dejar cabos sueltos y menos cuando uno de los cabos sueltos puede llevar a que aplicaciones (sean o no críticas) no funcionen.

La opción más sensata siempre es crear una traza de profiler para SQL Server 2000, que nos capture durante un tiempo prudencialmente amplio y significativo, la actividad de nuestro servidor

¿Qué nos interesa conocer?

1. La consulta que ha sido lanzada (posteriormente lo utilizaremos para que SSUA analice si hay patrones conflictivos)
2. El hostname desde donde se lanza
3. El login utilizado
4. El nombre de la aplicación
5. La BBDD sobre la que se está ejecutando la consulta

 

Dicho esto, nos podemos hacer una idea de los eventos e información que necesitamos capturar en SQL Server profiler…y ahora viene la parte divertida…la explotación de esos datos

-- Create table with data
--
CREATE TABLE [dbo].ExternalConnectionAnalysis(
    [ServerName] [nvarchar](256) NOT NULL,
    [databaseid] [int] NULL,
    [applicationname] [nvarchar](256) NULL,
    [hostname] [nvarchar](256) NULL,
    [loginname] [nvarchar](256) NULL,
    queries_executed bigint not null
) ON [PRIMARY]

GO

INSERT into dbo.ExternalConnectionAnalysis(ServerName, databaseid,
          applicationname, hostname, loginname,queries_executed)
SELECT 'atlante3' AS ServerName , databaseid, applicationname, 
       hostname, loginname,COUNT(*)
FROM ::fn_trace_gettable('path_to_file.trc', default)
GROUP BY databaseid, applicationname, hostname, loginname 
go

Tenemos esa opción que evidentemente te lee el fichero de traza .trc (y los que vengan detrás en caso de haberse creado con multiples ficheros) y agrupa por la información que queremos, o podemos optar por una forma algo más rebuscada y eficiente para procesar los datos como esta otra:

declare @srvname sysname = 'my_server'
declare @trcpath varchar(max) = 'path_to_file.trc'
;

with existent_data as(
  select ServerName, databaseid, 
            applicationname,hostname,loginname 
  from dbo.ExternalConnectionAnalisys
  where ServerName = @srvname
),
trc as (
    SELECT @srvname AS ServerName
                           , trc.databaseid, trc.applicationname, 
                           trc.hostname, trc.loginname
    FROM ::fn_trace_gettable(@trcpath, default) trc            
)
INSERT into dbo.ExternalConnectionAnalysis(ServerName, databaseid,
                     applicationname, hostname, loginname,queries_executed)
SELECT @srvname AS ServerName , trc.databaseid, trc.applicationname, 
            trc.hostname, trc.loginname ,COUNT(*)
FROM trc left join existent_data ed on
       (trc.DatabaseID = ed.databaseid 
       and trc.ApplicationName = ed.applicationname
       and trc.HostName = ed.hostname
       and trc.LoginName = ed.loginname       
       )
where ed.databaseid is null or ed.applicationname is null or 
           ed.hostname is null or ed.loginname is null       
GROUP BY trc.databaseid, trc.applicationname, trc.hostname, trc.loginname 

La gran ventaja de utilizar la consulta anterior radica principalmente en que es una consulta que solo añadirá las nuevas filas con información que le vayamos proponiendo. Es decir, que como es normal, tendremos ficheros de traza a traves del tiempo (cada dia previsiblemente tendremos .trc nuevos) y los podremos procesar independiemente de tener todos los ficheros de traza y procesarlos de golpe.

Evidentemente es una gran ventaja…pero otra ventaja oculta es si miras un poco más alla de la simple consulta y te das cuenta de que al utilizar el left join y el group by, nuestro SQL Server ha generado un plan de ejecución eficiente mediante un fantástico MERGE JOIN

 

Queda a tu disposición probar la consulta sin el left join, mediante un cruce de los de toda la vida y ver que ocurre…y sobre todo el tiempo que tarda, por culpa del super LOOP JOIN que te mete

 

Salu2!