How to create and run SQL Server database unit tests automatically

It’s important to run SQL Server database unit tests to ensure that any changes made will not “break” the database or in other words violate the integrity, relationships or the functionality of the database itself. Ideally, many issues can be found in development, even pre-QA, if unit test coverage is created and run early and often. So even though we may understand that running unit tests against database changes is important, we may also experience the fact that creating and running SQL Server database unit tests manually can be time consuming, especially for lager databases which are under active development.

October 12, 2016

SQL Server disaster recovery – How to quickly recover data lost due to an Inadvertent delete operation

It is not that uncommon to find yourself in a situation when some inadvertent delete operations have deleted important data from a SQL Server database. The highest priority in these situations is to discover what exactly was deleted and how to get the deleted data back as quickly as possible. In this article, we are going to show you how to quickly recover data lost due to inadvertent delete operation using ApexSQL Log and ApexSQL Recover tools. Even though both tools generate the same output, a recovery script which rollbacks unintended deletes, the mechanisms used by them to acquire recovery information and create rollback script are quite different, so if the recovery with one tool doesn’t help, the other tool may.

October 11, 2016

How to commit SQL Server table static data to a source control repository


Once a SQL database is committed to a source control repository with all of its objects, the next task is to commit the static data. Static data is non-transactional data from tables that generally don’t change often like postal codes, department names etc. Many teams treat this type of data akin to the database structure itself, by versioning it to track changes and deploying it from source control just like database objects. As data is versioned in source control, managing it can be automated as well.

October 7, 2016

How to automate adding SQL auditing triggers to a new, or changed database

Maintaining a before-and-after audit trail for sensitive tables can be time consuming, especially with a database that is under continuous development, and particularly in teams that use continuous integration. Most table changes will break existing triggers and necessitate their update. The ability to automate the refactoring and re-creation of a trigger based auditing layer, to keep up with underlying database changes, and run this process unattended or as part of a continuous integration process can be a huge time saver.

September 28, 2016

How to get notifications on SQL Server performance issues

While Performance Monitor can be used for monitoring performance parameters of SQL Server, it doesn’t provide an option to get notifications when certain performance metrics values breach specified thresholds. The main reason why this is important is to be able to identify SQL Server performance issues as soon as possible, and react before they affect too many users. In addition, notifications on potential SQL Server performance issues could help avoid more serious problems by forecasting when they are likely to occur in order to avoid them. Therefore, one of the primary tasks for the database administrator is to establish a system that will ensure that he/she is going to be notified in case when certain performance metrics values are beyond the expected/desired range

September 8, 2016

How to identify and troubleshoot slow-running queries in SQL Server

Probably the most common issue when maintaining SQL Servers are slow-running queries. It is not unusual that a DBA gets information that the application or user database is slow, or even that users are getting timeout messages when working with SQL Server or SQL Server applications. Generally, when such SQL Server performance related issues are encountered, the first step in troubleshooting such an issue is to quickly identify whether and what slow-running queries in SQL Server are the cause of such a problem. The next step is then to determine why these queries run slow and what is the root cause for such behavior.

September 8, 2016

Performance monitoring of AlwaysOn Availability Groups – Part 2

In Part 1 of this series using native SQL Server tools for monitoring AlwaysOn Availability Groups performance is described. In this part the focus will be on a third party solution – ApexSQL Monitor; designed to overcome most of the limitations of the aforementioned native SQL Server tools

August 9, 2016

SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script

In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.

In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.

July 4, 2016

Create daily database backups with unique names in SQL Server


When working with a large number of databases on multiple SQL Servers, creating a foolproof disaster recovery plan can be challenging. Well organized backup and restore strategies will definitely help with this. In order to successfully implement these strategies in a larger environment, configuring automated backup and restore processes is a must. Some database administrators use the batch or power shell scripts for automation, while other prefer to use various 3rd party solutions. In both cases, it is necessary to format database backup names properly. Properly formatted backup names make the job of organizing and maintaining of the backup sets much easier. Old backup files are usually obsolete, and they can be easily identified and deleted from the drive either manually, or by using a script.

June 30, 2016