locked
SA Remediation RRS feed

  • Question

  • Hi All,

    What is the best way/approach to run the server side trace against multiple servers for SA account remediation

    Thanks

    David

    Monday, April 22, 2019 12:01 PM

Answers

All replies

  • Do you want to audit SA activities on the multiple servers ?

    http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by SQL_Dave Monday, April 22, 2019 12:45 PM
    Monday, April 22, 2019 12:11 PM
  • Thanks Uri

    But this is not for multi servers right? We need to run this on each server

    Monday, April 22, 2019 1:07 PM
  • Hi All,

    What is the best way/approach to run the server side trace against multiple servers for SA account remediation

    Thanks

    David

    There is no built-in multi-server Audit or Trace.

    The Auditing-Feature of SQL Server can be used with a Filter. Also Extended Events can be used, which are the foundation of the Audit Feature, but a more general tracing feature.

    The results of both techniques then can be transfered into a single target if wished.

    SQL Trace is deprecated and has a higher overhead.


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    • Marked as answer by SQL_Dave Tuesday, April 23, 2019 1:40 PM
    Tuesday, April 23, 2019 1:03 PM
  • I have found few server side trace scripts which are working in the below 2 steps

    1. Creating & running the trace files and the output would be .trc files

    2. Importing the .trc files to table

    Is is possible to save the trace files directly through a table instead of creating as files in a single script?

    Thanks

    David

    Tuesday, April 23, 2019 6:10 PM
  • I have found few server side trace scripts which are working in the below 2 steps

    1. Creating & running the trace files and the output would be .trc files

    2. Importing the .trc files to table

    Is is possible to save the trace files directly through a table instead of creating as files in a single script?

    ...

    The old SQL Trace technique can either work server-side and write to a trc-file (fastest)

    or it can interactively be used from SQL Profiler who could then write back into a table: This is the slowest technique that I could think of and not recommended at all.


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Tuesday, April 23, 2019 6:21 PM
  • So we need to setup like write to a trc file and than write to a table without interact with SQL profiler for fast is it?
    Wednesday, April 24, 2019 6:27 AM
  • The least overhead is done with the server trace to a file (See also: Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load)

    if the end goal is a table this has to happen afterwards

    Note that the same can be done using Extended events: Write to a file-target (Targets for Extended Events in SQL Server) first, and import into a table later using sys.fn_xe_file_target_read_file


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    • Marked as answer by SQL_Dave Thursday, April 25, 2019 10:03 AM
    Wednesday, April 24, 2019 8:16 AM
  • Thanks Andreas
    Thursday, April 25, 2019 10:03 AM