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.

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.

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

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”

How to back up a database without the index data

This article explains how to create filegroups and move indexes into a different file group, 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. We will also show how to use the ApexSQL Manage solution for filegroups backup as a first part of the series in which we will show how to backup and restore a database without indexes, and to recreate the indexes after a restore.

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.

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.

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