How to implement HIPAA regulatory standard for SQL Server – Part 1

The Health Insurance Portability and Accountability Act of 1996 (HIPAA) sets guidelines mandating the adoption of Federal privacy protections for health information of individuals which gives patients an array of rights with respect to that information. The HIPAA Privacy Rule ensures federal protections for individually identifiable health information and gives patients a range of rights with respect to that information. The Security Rule defines administrative, physical and technical safety measures to ensure the availability, confidentiality and integrity of electronic protected health information.

August 31, 2015

Before and after auditing in SQL Server

Simply archiving information to audit a database is one thing, but successfully reconstructing an audit history to provide meaningful forensic data is another. It is important to be able to see a full history of user changes, as well as to be able to reverse changes that may have been accidental or malicious.

Ideally, such value-added information can be obtained without requiring a prodigious amount of archived data or creating significant performance impact on audited servers.

In this article, we are going to present two different approaches and solutions to before and after auditing.

August 11, 2015

SQL Server database security auditing

The following auditing implementations are recommended on a database level as part of any database security auditing system:

  1. Schema level auditing:
    • DDL activity
    • Changes made to stored procedures and triggers
    • Changes to privileges, users, and security attributes
August 7, 2015

How to implement SOX compliance requirements for SQL Server – Part 1

Achieving SOX compliance requirements is the mandatory for all publicly traded companies. But when it comes to most IT teams, SOX compliance can be quite vague and confusing. SOX compliance is not written with technology mandate in mind, but rather a mandate for accounting, legal, and financial reporting. In the SOX Act there’s no reference can be found to anything specific related to IT. It is often said that SOX was “written by lawyers, for lawyers”

July 23, 2015

How to back up a database without the index data

This article explains how to create filegroups and move indexes into a different filegroup, and how to backup a database without indexes with the purpose to reduce the amount of data required to perform backups thus reducing backup time and space required.

July 2, 2015

How to automate DBCC CHECKDB after a database restore

Why running DBCC CHECKDB?

DBCC CHECKDB checks the logical and physical integrity of all the objects in a database and provides information of any corruption.

As performing DBCC CHECKDB is a resource exhaustive task it is recommended to run it on a production server when there is as less traffic as possible, or even better, as one of the ways to speed up the DBCC CHECKDB process, is to transfer the work to a different server by automating a process and run CHECKDB after a database restore. As a backup process is a copy of a database and a restored database will be exactly the same as an online database therefore if there were any inconsistencies or issues they will be in the backup, and found in a restore. By using this approach both restores will be tested and backups verified without any impact on a production database.

June 22, 2015

How to perform a SQL backup and restore history cleanup

SQL Server stores a complete history of all SQL backup and restore operations, and other historical activities such as activities like Database Mail, Jobs, Log Shipping, Policies, Maintenance Plans, etc. on a server instance in the msdb database.

June 12, 2015

SQL Server auditing – how to be alerted about important auditing events

While numerous native auditing methods are available for SQL Server, none of them provides an out-of-the-box feature to generate an alert when a specific SQL Server event is detected. We will look to see how to come close with native solutions and also an out of the box solution, ApexSQL Audit

May 29, 2015

Troubleshooting SQL Server performance issues using wait statistics

The ability to successfully solve a problem and pinpoint the root cause of an issue that affects SQL Server performance depends on knowledge of the particular SQL Server system and environment, but also on personal experience which can help in determining where to start SQL Server performance troubleshooting.

May 20, 2015

How to use regular expressions (RegEx) in SQL Server to generate randomized test data

A regular expression (abbreviated regex or regexp and sometimes called a rational expression) is a sequence of characters that forms a search pattern, mainly for use in pattern-matching and "search-and-replace" functions. They can be also used as a data generator, following the concept of reversed regular expressions, and provide randomized test data for use in test databases.

May 11, 2015

How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting

To create a custom system for performance base lining we need the following:

  • A metric to track
  • A database and tables to store our collected data and processed information
  • A means to collect performance data
  • A means to process the performance data into meaningful information e.g. Mean, Standard deviation
  • A line chart of the data
  • A query to create a report of the measurements that exceeded the threshold, we set, for raising alerts
May 8, 2015

Manage multiple database backups across different SQL Server instances

One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plan for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to the nearest available point in time, prior to the disaster.

May 6, 2015

How to detect SQL Server performance issues using baselines – Part 1 – Introduction

In the following series of articles, we will discuss what baselines are, how they work and how to apply them to everyday SQL Server performance monitoring. This article will provide a brief overview of baselines and the statistical calculations behind them. Later we’ll apply this to real information gathering techniques to allow DBAs to create their own baseline.

May 4, 2015

How to ensure continuous auditing of SQL Server with zero audited data loss

An optimal continuous SQL Server auditing approach must include:

  1. Continuous auditing
  2. Real time data collection
  3. Ability to generate meaningful reports
  4. Alerting on unwanted activities
  5. Tamper proof store of audit data

In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.

April 28, 2015

How to implement check out and lock policies in the shared database source control development model

In a multi-user database-development environment, avoiding conflicts and overwrites with edits, and ensuring all changes are audited and recorded is important. Until recently however, effective tools for SQL development management have lagged well behind their client developer equivalents, like Visual Studio. In this article, we will look on specific database source control challenges and a way to address them use new SQL developer tools that make meeting these straightforward and easy

April 22, 2015

How to analyze SQL Server database dependencies in client code e.g. C#

ApexSQL Clean has, among others, a useful feature that most people are not even aware of. It provides a client SQL code analysis in C#, Delphi, VB.NET, XAML, XML, ASP.NET, HTML, CSS code etc., and detects which SQL objects are actually being used and which ones aren’t in the referenced database. The SQL code analysis is performed by inspecting the code file for objects in the SQL database. This feature helps keeping SQL databases clean and organized

April 16, 2015

How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy, but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check-in/check-out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.

April 15, 2015

How to schedule a database backup restore in SQL Server

When recovering from a SQL Server database failure, a database administrator needs to restore a set of SQL Server database backups in a logically correct and meaningful restore sequence. With this in mind, the goal is to devise a disaster recovery strategy by creating a solid backup plan, as well as a proper database restore plan in SQL Server. This article will describe 2 different solutions for creating and scheduling a database restore in SQL Server.

April 8, 2015