Capture Connections to any SQL Server database

Scenario

As a member of project migration team, you are asked by your project manager to track all the connections coming to a particular database.

Solution

Solution in setting a server side trace. Server side trace is one of the automated task of DBA which runs continuously at background and collects number of events in a trace file (.trc). Trace file is the only option which is available to server side tracing. Contrary, we can save trace information in table also which is available in profiler tracing. This solution will show you how to set up an automatic 24*7 trace, archiving trace files and transferring data into SQL Server table. Following are steps of provided solution.

  1. Fetch trace definition from profiler
  2. Create SQL Server Job which performs following tasks and schedule it hourly/half hourly.
    1. Stops current trace
    2. Archives current trace to archival folder
    3. Starts a new trace
  3. Write a code to read all trace files into a folder

Schema creation

/****** Object:  Table [dbo].[MigrationFile]    Script Date: 12/09/2016 06:34:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[MigrationFile](

[Path] [varchar](255) NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

CREATE TABLE [dbo].[MigrationTrace](

[LoginName] [varchar](500) NULL,

[ApplicationName] [varchar](500) NULL,

[NTUserName] [varchar](500) NULL,

[StartTime] [datetime] NULL,

[HostName] [varchar](500) NULL

) ON [PRIMARY]

Fetch trace definition from profiler

  1. Open SSMS, go to Tools and open SQL Server Profile.
  2. Connect to any server
  3. Go to Events Selection tab and select Audit Login event. Deselect other events as those are not required right now.
  4. Hit Run button at the bottom.
  5. Stop the trace.

  6. Open File Menu > Export > Script Trace Definition>For SQL Server 2005-2014 and Save the definition SQL and copy code from the same.

SQL Server Job Steps

Stop Current Trace Code – 1st Step of SQL Job Login Trace

— get trace_id

declare @trace_id INT

select @trace_id = id

from sys.traces

where path LIKE ‘C:\Traces\LoginTrace%’

IF @trace_id <> 0

BEGIN

print ‘stopping’

— Stops the specified trace.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0

— Closes the specified trace and deletes its definition from the server.

EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2

END

 

SQL Server Job Step -2

      Rename and Archive current trace- 2nd Step of SQL Job Login Trace

                     –declare variables

declare @server varchar(30), @date char(14), @file char(100), @cmd varchar(250), @path varchar(255)

select @server = REPLACE(UPPER(@@servername),’\’,’_’)

select @date = replace(replace(replace(convert(char(19),getdate(),120),’:’,”),’-‘,”),’ ‘,”)

select @file = @server+’_’+@date+’.trc’

— change trace file name

select @path = path

from MigrationFile

select @cmd = ‘RENAME ‘+@path+’ ‘+@file

–exec master..xp_cmdshell @cmd

— move trace file to \archive

select @cmd = ‘MOVE C:\Traces\*.trc L:\Traces\archive’

exec master..xp_cmdshell @cmd

delete from MigrationFile

SQL Server Job Step -3

Re-run trace

/****************************************************/

/* Created by: SQL Server 2014 Profiler          */

/* Date: 12/09/2016  04:10:26 PM         */

/****************************************************/

 

 

— Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @filepath nvarchar(255)

 

set @maxfilesize = 5

set @filepath = ”

select @filepath = @filepath + N’C:\Traces\LoginTrace’ + ‘_’ + convert(varchar(12),getdate(),102)+’.’+replace(convert(varchar(12),getdate(),108), ‘:’, ‘.’)

 

— Please replace the text InsertFileNameHere, with an appropriate

— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

— will be appended to the filename automatically. If you are writing from

— remote server to local drive, please use UNC path and make sure server has

— write access to your network share

 

/*Save File Name for future*/

insert into MigrationFile(path)

select @filepath+’.trc’

 

 

exec @rc = sp_trace_create @TraceID output, 0, @filepath ‘, @maxfilesize, NULL

if (@rc != 0) goto error

 

— Client side File and Table cannot be scripted

 

— Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 14, 1, @on

exec sp_trace_setevent @TraceID, 14, 9, @on

exec sp_trace_setevent @TraceID, 14, 10, @on

exec sp_trace_setevent @TraceID, 14, 11, @on

exec sp_trace_setevent @TraceID, 14, 6, @on

exec sp_trace_setevent @TraceID, 14, 8, @on

exec sp_trace_setevent @TraceID, 14, 12, @on

exec sp_trace_setevent @TraceID, 14, 14, @on

exec sp_trace_setevent @TraceID, 14, 35, @on

 

 

— Set the Filters

declare @intfilter int

declare @bigintfilter bigint

 

exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Server Profiler – 28055c81-f01c-43a0-b0be-3565d746f941’

— Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

 

— display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

 

finish:

go

Schedule Job hourly…………….

Advertisements
Capture Connections to any SQL Server database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s