How to see the full version history of a SQL Server database object under source control and, if needed, revert changes

Challenge

One of the main benefits of a SQL database version control is that any version of an object committed to the repository, is available through the revision history. With that being said, browsing the history allows seeing all versions of the specific database object, committed over time, and reverting any version from the history in order to apply it against a database. By utilizing such functionality, a database can be brought back to a working state in case some change caused a problem

December 30, 2016

How to link and initially commit SQL Server database static data

Challenge

Non-transactional data in tables that is never (or rarely) changed, for example currency codes, postal codes or personal ID numbers, is often referred to as “static data” (also known as lookup or reference data). Although static data isn’t part of the database schema, it can be thought of in much the same way. Simply creating test data for static tables, for testing purposes, may allow the tests to be successfully executed, but often won’t render meaningful looking results and in some cases, could result in failure, if the static data was tightly coupled to application/database function. Static data tables also tend to be much smaller than transactional tables.

December 30, 2016

How to programmatically monitor system availability

System availability overview

Overall stability and availability of a machine in a specific time range is usually called system uptime. This measurement represents a period (sometimes percentage) when the system is stable and performing without unattended reboots, except for maintenance and administrative purposes, and works without issues. The opposite, system downtime is a period when machine is turned off (on purpose), or encounters experiences problems that result is the system being unavailable to users and processes. The combination of these two measurements is called system availability which is both identified and tracked with Windows Event Viewer (System Log).

December 23, 2016

The process of elimination: How to analyze SQL Server performance with baselines and wait statistics

Wait statistics = Data

SQL Server’s built in abilities to track query execution via wait statistics is critical to resolving performance problems. Wait statistics or response time analysis are metrics that provide the ability to measure the time needed for the database to respond to executed queries. It doesn’t simply represent the time needed for a query to complete execution, but the wait statistic is also the measurement of the time the query has taken for each step in its execution. That information becomes the basis for identifying bottlenecks that are affecting the execution time.

December 20, 2016

How to commit and/or update SQL Server database static data to a source control repository

Challenge

In order to version static data, just like database objects e.g. Tables, a mechanism needs to be created to identify only those tables designated as “static” (see below) and to come up with a reproducible process, that will Commit the data in these tables to source control. This article covers this process

December 14, 2016

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