upgradeprofilertable.sql

本人使用SQL SERVER 数据库3年

/* This script assumes the following is true before it is run:
 * 1. The msdb..LUMIGENT_PROFILER table exists.
 * 2. The %LUM_NEW_PROFDB% database exists.
 */

/* Create the new profiler table */
USE %LUM_NEW_PROFDB%

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[leAuditCollectEventData]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	
BEGIN
 /* Drop existing indexes because table already exists */
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx1')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx1]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx2')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx2]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx3')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx3]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx4')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx4]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx5')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx5]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx6')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx6]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx7')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx7]
 IF EXISTS (SELECT name FROM sysindexes WHERE name = 'leEventDataIdx8')
	DROP INDEX [leAuditCollectEventData].[leEventDataIdx8]
END
ELSE
BEGIN
CREATE TABLE [leAuditCollectEventData] (
	[seqnum] [int] IDENTITY (1, 1) NOT NULL ,
	[eventtime] [datetime] NULL ,
	[eventclass] [int] NOT NULL ,
	[alerttypeid] [int] NULL ,
	[eventsubclass] [int] NULL ,
	[starttime] [datetime] NULL ,
	[endtime] [datetime] NULL ,
	[textdata] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[success] [int] NULL ,
	[clienthostname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[applicationname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ntusername] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ntdomainname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sqlsecurityloginname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[spid] [int] NULL ,
	[databaseid] [int] NULL ,
	[databasename] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[objectid] [int] NULL ,
	[objecttype] [int] NULL ,
	[objectname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[objectowner] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[permissions] [int] NULL ,
	[targetrolename] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[targetloginname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[targetusername] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[transactionid] [decimal] (19,0) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]	
END
GO

/***
 Copy the old profiler data to the new profiler table.
 For each record found in LUMIGENT_PROFILER, a record
 will be generated in leAuditCollectEventData with
 event class 15 and alerttypeid 1201 (ALERT_LOGOUT).
 
 Caveats:
  1. Because sql server datetime precision can not handle subtracting
     1 millisecond, there will be a 3 millisecond hole where no 
     nt session information will exist between session logout
     and login.  I don't think we'll ever see records that
     exist in the log during this hole, but if it happens then LE will
     show no NT session info for a log record within that time period.
  2. Database connections still open during the the upgrade 
     will not have an entry in the new table until that user 
     disconnects and the new profiler trace catches that
     logout information.  If, during the time of the upgrade,
     the user disconnects, we could lose the association
     of their NT user info with the changes made.
     We could try to have a more complex script that looks
     at existing connections and puts that information
     in the new profiler table, but there would always
     be a potential for missing information.
***/

INSERT INTO [%LUM_NEW_PROFDB%]..[leAuditCollectEventData]
([eventtime],[eventclass],[alerttypeid],[starttime],[endtime],
 [clienthostname],[applicationname],[ntusername],[ntdomainname],
 [sqlsecurityloginname],[spid])
SELECT dateadd(ms,-2,b.STARTTIME) as eventtime, 15 as eventclass, 1201 as alerttypeid,
		a.STARTTIME as starttime, dateadd(ms,-2,b.STARTTIME) as endtime,
		a.clienthostname, a.applicationname, a.ntusername, a.ntdomainname,
		a.loginname as sqlsecurityloginname, a.spid
from [%LUM_OLD_PROFDB%]..[LUMIGENT_PROFILER] a, 
     [%LUM_OLD_PROFDB%]..[LUMIGENT_PROFILER] b
where a.spid = b.spid and a.starttime = b.previousstarttime 
and a.eventclass = 14
GO

/* Now create the indexes on the new table to enhance LE query speed */
USE %LUM_NEW_PROFDB%
CREATE INDEX [leEventDataIdx1] ON [leAuditCollectEventData]([ntusername]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx2] ON [leAuditCollectEventData]([sqlsecurityloginname]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx3] ON [leAuditCollectEventData]([applicationname]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx4] ON [leAuditCollectEventData]([clienthostname]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx5] ON [leAuditCollectEventData]([eventtime]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx6] ON [leAuditCollectEventData]([starttime]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx7] ON [leAuditCollectEventData]([endtime]) ON [PRIMARY]
GO

CREATE INDEX [leEventDataIdx8] ON [leAuditCollectEventData]([spid]) ON [PRIMARY]
GO

/* set database back to master because SetupServer expects this */
USE master
GO