How to automate trigger-based SQL Server auditing

More often than not, a top level requirement for all SQL Server DBAs is to have mechanisms to capture who changed what and when and to have stored audit information available for further investigation. In order to achieve this, database auditing needs to be set up using various mechanisms. One of those mechanisms is a trigger-based auditing.

April 27, 2016

Document SQL Server data model diagrams

This article will be about how to include Data model diagrams in the documentation generated by ApexSQL Doc. With this feature it’s possible to see graphically display database tables and their relationships.

April 26, 2016

Synchronize SQL database changes between local and remote repositories in distributed source control systems

Having a SQL database being version controlled locally, by storing all changes in a repository on a local machine can be quite handy. In the context of team based database development, it is necessary to establish the environment where changes can be tested locally, specific revisions reverted from the commit history, and doing such things before pushing changes to the remote repository where the rest of the team will be able to review them, and apply against a local database copy.

April 26, 2016

How to create and manage SQL backup policies

Performing regular database back-ups is a crucial part of any disaster recovery plan. There are many aspects that should be taken into consideration when planning long term backup strategies. It is necessary to choose the backup type and backup location and to plan how frequently to perform the backups. Additionally, the processes of verification or encryption could also be included into the plan.

April 22, 2016

SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository

Nowadays, it’s quite common that database changes are made several times a day and that a number of developers are making changes against the same database. Due to these often changes, a certain “process” has to be followed in order to avoid any potential problems in the later stages of development (Quality Assurance (QA), Staging, User Acceptance Test (UAT)) and at the end in production. When talking about the Continuous integration (CI) for SQL databases today, it’s often referred to a process of several steps:

April 22, 2016

Revision history of an object change in a SQL database using Git

The essence of each source control system is the ability to easily review the history of committed revisions. In addition, to comparing revisions, a user needs to get a specific revision and apply it against a database. Each revision should have a unique timestamp and should carry the information about the user who did the commit. Such system ensures that it is easy to determine who committed what and when, and in some way provide a complete auditing trail of committed revisions.

April 22, 2016

Manage and monitor SQL Server backups from a central location

Introduction

Running and maintaining multiple SQL Server instances can often be a formidable challenge, especially if these instances run on multiple servers. It is easy enough to set up a SQL Server agent job for each server to automate the backups, but what happens if there are 20, 30, or 100 servers that need maintenance? In this scenario, configuring agents on each server would take forever, and monitoring the entire setup would prove to be a nightmare for any administrator. Of course, there are several solutions for this scenario:

April 15, 2016

SQL Server database object search

Developers and DBAs often perform database searches in order to find and obtain additional information related to specific objects, sometimes without knowing the full name of the object.

April 11, 2016

Recover a SQL Server database using only a transaction log file (.ldf) and old backup files

Having a good recovery plan that ensures database backups are being taken on a regular basis and safely stored on specific or multiple storage drives greatly increases database safety and recovery options when a disaster or accident occurs. Nevertheless, creating regular full database backups doesn’t provide full point-in-time recovery, since restoring the latest full backup will restore a database to a proper state at the time a backup was created, but all changes, both schema and data, which have occurred after the last full backup will be practically lost.

April 5, 2016