How to monitor and detect SQL Server round-trip performance issues using custom designed metrics

For every query issued by the application, time is needed to reach the SQL Server and then the time needed for results to get back to the application. As all communication between an application and SQL Server goes via some network (LAN or WAN), network performance could be a critical factor affecting overall performance. Two factors affect network performance: latency and throughput.

December 8, 2017

How to update a SQL database from source control while avoiding dependency errors

Working with a database under source control has many benefits. Beside tracking all the changes made against a database, including the information what the changes were and who made them, you can also track history of committed versions of all database objects which can be restored on a database at any point.

However, one of the benefits which will be explained in this article is comparing and syncing two copies of the same database. Using ApexSQL Source Control, all objects that are missing in one copy of a database can be created/restored in the second copy of a database, preserving the integrity of both databases.

December 7, 2017

How to add self data change auditing capabilities to commercial, shrink-wrapped software with a SQL Server backend

There are thousands of distributed. commercial software applications aka shrink-wrapped, from small shareware apps to large corporations, that use a SQL Server backend. These systems, although distributed, could often benefit from and sometimes require data change auditing at the client location.

Although ApexSQL sells a successful, enterprise auditing tool for SQL Server, ApexSQL Audit, this tool is often not appropriate for distributed, commercial software.

November 29, 2017

How to analyze query performance characteristics in SQL Server

Introduction

There is a maxim that comes from the world of Python programming: “There should be one – and preferably only one – obvious way to do it.” (See The Zen of Python) in the references. While that is a good goal for any language, it is a difficult goal to achieve. T-SQL is no exception here!

Consider a simple problem: You have a customer transaction table with dated rows. You are asked to produce a list of the top transactions per day. “Top” could be defined as the one with the most items, the highest value, the most important customer or a variety of other criteria. However, you define it, at some point, you will want the maximum (or minimum) value of one of the columns. Then you will want to output the entire row (or rows, in the case of a tie) matching that value. Sounds simple, right? Well, it actually is simple, but the number of ways to do it may surprise you!

November 22, 2017

How to execute an unattended best practices review against a database

One of the many tasks of the DBA in charge on a development project is to make sure the team stays consistent in their code. Routine checks for SQL best practices such as naming conventions, right data types usage, problematic things like cursors, potentially fatal things like Deletes without Where clauses can cause a lot of headaches.

November 10, 2017

How to Detect SQL Server Performance Issues Caused by Incorrect Clustered Indexes

Description

A clustered index is critical to performance on any table. Not only does it dictate the logical storage of data for the entire table, but it also is the index that all others will reference when additional data is required. A poorly chosen clustered index can greatly hamper performance, causing latency, contention, and excessive IO. In this article, we will explore clustered indexes, and use ApexSQL Tools to assist in identifying those that could be improved upon.

November 9, 2017

How to create Copy-Only backups in SQL Server

Creating a foolproof disaster recovery solution is imperative for every business. After the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined, a database backup plan should be created to match these objectives. Most DBAs tend to automate the majority of tasks related to database backup plans. Regular database backup schedules are set in order to create continuous backup chains, that can later be used to recover a database in the case of a disastrous event. By setting the backup schedule, the continuity of the backup process is ensured, and most of the job is performed automatically on a regular basis.

November 7, 2017

How to version control SQL scripts alongside SQL database objects

This article explains how SQL database source control can help in version control SQL scripts alongside SQL database objects. Developing a software sometimes requires versioning not only database objects, but custom SQL scripts for migration, configuration, automation or other purposes

November 3, 2017

“What broke my database?” Using SQL database source control for forensic auditing and database troubleshooting

Besides the auditing trail of all the changes made against a database and having the information of who committed what and when, the main benefit of having a database under version control is the fact that the history of all the previously committed versions of any object (or a group of objects) can be used to find the exact version that broke the database at any point. Assuming that a SQL database under source control is being deployed on a daily basis or even more frequently, it is essential to ensure that only tested changes are committed so that a SQL database can be deployed in a working state, without any issues.

November 1, 2017

Using SQL source control to track database changes

The goal of this article is to explain how SQL database source control can help in auditing database changes. Tracking a change by itself is not the only task required for successful implementation of SQL source control, as we also want to know who made the change, when and why. After all of these inputs are known, any problems and be diagnosed and be fixed efficiently. We’ll see how having a SQL database under version control can help in keeping the auditing trail with a possibility to revert back to any of the previous version of an object.

October 31, 2017