This is an excerpt from my free eBook, Brad’s Sure Guide to SQL Server 2008, which can be downloaded here.

As DBAs, one of our goals is to separate OLTP (On-Line Transaction Processing) and OLAP (On- Line Analytical Processing) activity, preferably onto different servers. This is because performing both types of activities in the same database, or even in different databases on the same server, can lead to performance problems. In order to accomplish this goal, DBAs must figure out the best way to move transactional data from OLTP servers to servers designated for OLAP activity so that the data remains more or less synchronized. This sounds simple, but can prove to be difficult in practice.

Continue reading →

In March, I ran a Question of the Month that asked, “What is the biggest mistake/problem you ever found on a SQL Server instance, and how did you fix it.” There were so many great responses that I thought I would summarize many of them here, as this list provides many remarkable examples of real mistakes made by developers and DBAs. To see all of the responses (and who contributed them), visit this webpage. Of course, this list is not a comprehensive list of every potential mistake that can be made, but it offers you a glimpse of the kind of mistakes (some very amusing) that some people make when working with SQL Server. Keep in mind that this list applies to the specific examples provided by those who responded to the question, and that there are always exceptions to every rule. Thanks to everyone who contributed to this list.

Continue reading →

Excerpted from Brad’s Sure Guide to SQL Server 2008, which is available as a free eBook.

Previous versions of SQL Server have included a variety of built-in ways to audit activity inside SQL Server. These included:

Login Auditing: Only tracks user login successes and/or failures. Results are sent to the OS Application Log.

SQL Server Profiler (SQL Trace) Audit and Related Events: Profiler includes over 40 specific audit events, and over 130 additional events that can be traced. Results are stored in trace files.

DDL Triggers: DDL triggers can be added to a database to identify when any DDL event occurs.

C2 Audit Mode: This former auditing standard (now superseded by Common Criteria Compliance) uses SQL Trace to capture audit events, which are stored in trace files.

Common Criteria Compliance: A new international auditing standard which also uses SQL Trace to capture data, which are stored in trace files.

None of the above methods offer much granularity, most are not easy to administer, and with the exception of Login Auditing, they can add a large amount of overhead to running SQL Server, hurting its performance.

To help overcome these problems, SQL Server 2008 includes a feature called SQL Server Audit. SQL Server 2008 Enterprise Edition includes all of the features described in this article, which includes both the SQL Auditing Foundation and Fine Grained Auditing. SQL Server 2008 Standard Edition only provides the SQL Auditing Foundation. Continue reading →