Home Forums SQL Server 2008 Security (SS2K8) Logon Triggers. A good idea? Or a nerve-racking exprience? RE: Logon Triggers. A good idea? Or a nerve-racking exprience?

  • Leave off the file number, and use "default" for the number of files to read, and it will start with the first existing file and read through to the end.

    That can be a problem if you don't roll files over, or have huge files, in which case reading one at at time might be better. SSIS can run a For Each Next loop over the files in a directory, and can feed the file name to a variable used in a dynamic query. That might be better if you have a lot of big files and need to break the job down a bit for hardware resource reasons.

    If the files are created with a standard name on every server, it should be possible to query them in such a way that you can dump them easily into a warehouse on a central server. Add @@servername as one of the columns in the query, to make sure you can tell what server the data came from.

    If you're going to harden the data into a table, I suggest setting a relatively low rollover threshhold for the trace, and import to the table frequently. That way the table will grow, but the files won't. Otherwise, both will grow, and you'll have to allocate storage to files you really won't be querying directly anyway, since you'll use the table for that.

    You've probably already thought of this, but I suggest importing fast into a staging table, and then from there load into a more permanent table. You can dedupe in that process, instead of adding to the trace-query overhead by having dynamic Where clauses on that. The final table can have whatever indexes, constraints, et all, you find you need for whatever you're using the trace data for, while the staging table can be optimized for fast bulk loading.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon