SQL Server compliance auditing for Title 21 Code of Federal Regulations Part 11 requirements – Part 1

Title 21 Code of Federal Regulations Part 11 (in the rest of the text it will be referred to as Title 21 CFR Part 11) is part of the Code of Federal Regulations established by the United States Food and Drug Administration (FDA) as a set of regulations on electronic records and electronic signatures (ERES). The CFR Part 11 specifically defines the standards that have to be imposed in order to consider electronic records and electronic signatures as trustworthy, reliable, and equivalent to paper records

February 25, 2016

SQL Server before and after auditing of DML/data changes

Before and after auditing tracks changes to data, showing the old and new values after each change. This data can be re-constructed to show an entire history of row changes and is important for forensic auditing in the case of malicious or inadvertent data changes

February 25, 2016

How to script SQL Server database users with roles

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.

February 5, 2016

How to format all SQL objects in your database

The Format SQL objects feature allow formatting one or more database objects with the specified formatting profile, without having to script them first.

There are three ways to invoke the Format SQL objects feature. First, you need to select a database from which you want to format objects. Otherwise, when you try to initiate the Format SQL objects feature, you’ll be prompted with the following message:

January 25, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 4

In part 1 and part 2 of this series information was provided on how to configure ApexSQL Audit to accomplish PCI requirements from 3 and up to 8, while in part 3 the addressing requirements 10.1, 10.2 and 10.3 of the PCI DSS 3.1 standard via ApexSQL Audit was explained

In this part, the rest of the PCI DSS 10-Track and monitor all access to network resources and cardholder requirements section will be described and as well as some requirements from section 12 that can be met using the ApexSQL Audit. This article is based on the latest PCI DSS 3.1 compliance regulation

January 22, 2016

How to restore only a specific filegroup – A piecemeal restore

What is a piecemeal restore?

A piecemeal restores allow the user to restore only a specific filegroups from a database instead of restoring a whole database. In cases of emergency the important thing is to get the needed data as quickly as possible, and restore the rest of the data later while having the most significant data online. This approach minimizes the downtime when a disaster occurs.

January 12, 2016

How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 3

Part 1 and part 2 of this article described configuring ApexSQL Audit to meet PCI DSS 3.1 standard developed to ensure security of cardholders’ payments and data. In this part, the requirement sections 10-Track and monitor all access to network resources and cardholder will be described. For more details about differences between PCI DSS 2.0 and PCI DWW 3.0 and differences between PCI DSS 3.0 and PCI DSS 3.1, check the official PCI Security Standards Council LLC documents Summary of Changes from PCI DSS Version 2.0 to 3.0 and Summary of changes from PCI DSS Version 3.0 to 3.1

January 6, 2016

SQL Server database shrink – How and when to schedule and perform shrinking of database files

From time to time, DBAs find themselves in a situation where a SQL Server database becomes too large for their production environment, and needs to be shrunk in size in order to free space and allocate it back to the system.

Before shrinking a SQL Server database or database files, it is very important to understand how the process works and what are the immediate consequences of the shrinking process.

January 4, 2016

Why, when and how to rebuild and reorganize SQL Server indexes

The purpose of SQL Server index is pretty much the same as in its distant relative – the book index – it allows you to get to the information quickly, but instead of navigating through the book, it indexes a SQL Server database.

January 4, 2016