SQL Traces and c2 auditing are different

Sometimes end users interchange the terms : C2 auditing and Traces.
They server different purposes.

c2 auditing records all attempts (successfull and failed) on objects and statements. Typically a SQL Server DBA will use a c2 audit to monitor security compliance .

A trace will will monitor an event or a series of events. Events can be any source producing a trace events. Examples are deadlocks and DTC transactions, SQL Server Backups.Read BOL for a comprehensive list of events

To view if c2 auditing is turned on use:

--To view if c2 auditing is turned on use:

EXEC sp_configure 'c2 audit mode'


--To view if traces including user defined ones are running on SQL server 2005 use:

SELECT * FROM sys.fn_trace_getinfo(0) ;

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server test data generation testing tools

Question:  How do I generate test data for SQL Server ?  I don’t want to buy a benchmarking tool, but I  require a representative test case with real SQL statements.  How do the professional DBA’s capture SQL Server workloads for testing?

Answer:  First, try to use real-world data, not artificial test data sets.  You want a representative benchmark.  

A one dimensional  view of SQL Server can lead to vastly different results. Data that is not representative and workloads executed on the data can create a false sense of results.

Always remember that there is no substitute for a real-world test that simulates the actual behaviour of your production systems. However , this is not always possible.

SQL Server  test data generation testing tools

 There are several tools and methods for generating test data for SQL Server:

• SQL Server Profiler- using the T-SQL Replay template. 
• DBGEN - The TPC-H tools, dbgen and qgen allow for the development of up to a terabyte databases.
    
• Quest Benchmark Factory for Databases – Track the effects of SQL Server changes on the environment
  
   

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Speed up copying large files with ESEUTIL

ESEUTIL is the best tool I've used so far - to copy large files across the network.
By "large file" I mean at least 50 GB.
I found the tool on our Exchange Server installation - where it's used for defragmentation

Normally , if using COPY , Buffered I\O uses Paged Pool - so , the larger the file there is a greater chance resources will be consumed - to the point of a COPY failing.

ESEUTIL avoids this process, and uses Unbuffered I\O

Using ESEUTIL I've been able to halve a 95 GB file transfer

The syntax is :

 ESEUTIL /Y \\myserver1\f$\mydb.bak /D  \\myserver2\f$\mydb.bak

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server DBA Tools

I thought it would be useful to list the range of software utilities I use when managing SQL Server.
They cover object management , monitoring , troubleshooting , code writing , copying lareg files .
I specifically haven't added Profiler even throughI use it extensively .

I'd be interested in hearing your feedback and also finding out what other tools other utilites DBAs use .

SQL server Management Studio
Powershell - great for task automation and manageing large SQL server inventories
Command Shell
Tivoli Storage Manager
Winzip
ESEUTIL - Use to copy very large files - non buffering approach . Much faster than COPY
Tivoli Monitoring
Microsoft Management Console - Allows me one console to manage RDP , Active Directory,Compute Management
SQLCMD - Utility to run statements and script files. Command line interface
Programmers Notepad - Excellent and probably the best code editor I've used
SQLIO - returns IO capacity data . Free utility from Microsoft
TableDiff.exe - very useful utility ,for comparing two tables . Installed with SQL Server.
SQLDumper.exe

Author: Jack Vamvas (http://www.sqlserver-dba.com)