Techniques to identify blocking queries and causes of SQL Server blocks

Blocked processes caused by locks on database objects are a common issue. Locking ensures the integrity of data stored within databases by forcing each executed SQL Server transaction to pass the ACID test. which considers that every transaction must meet the following requirements:

December 12, 2016

How to automatically synchronize the data in two SQL Server databases on a schedule

Challenge

It’s often needed to have a way of synchronizing data between SQL databases in real time, e.g. when a certain data is changed/added in one place (database) to be synchronized with another database. Changes need to be detected, automatically, on a schedule, and automatically synchronized with another database. This process should be fully automated and run totally unattended.

December 9, 2016

How to automate monitoring and alerting on SQL Server Agent status changes

SQL Server Agent overview

The main purpose of SQL Server Agent is executing administrative tasks within SQL Server, mostly as on-demand user actions. It is Windows service which runs continuously in the background, but it stores necessary information within SQL Server itself, in the msdb system database.

December 6, 2016

Continuous SQL Server database auditing and reporting using the transaction log

SQL Server database transaction log files are continuously pumped with the transactional information and details on database changes by the SQL Server itself. Even though the information from the transaction log files and backups can be used as a solid resource for database auditing, SQL Server does not provide a solid solution to utilize transaction log files to their full potential neither does it offer any simple way to explore those transaction log files or analyze the information within in order to perform continuous SQL Server database auditing.

November 10, 2016

How to detect SQL Server performance issues using baselines – Part 3

Part 1 of this series describes some basics about performance baselining, while Part 2 explains how to establish and use baselining for detecting SQL Server performance issues in SQL Server using user defined tables, scripts and baseline calculations. In this final article, we’ll demonstrate how to implement baselining with a 3rd party tool, including some value added functionality

October 26, 2016

How to create and automate test data generation for a SQL Server database


Challenge

Generating test data for SQL Server database should be a process that easily populates a SQL database with data (where needed). Adding of test data can be done by manually creating scripts or by using a specific tool. But for repeatable processes like continuous delivery (aka DLM, CI, continuous integration), automation of test data generation is an important consideration.

October 24, 2016

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