Read-only Routing for an Always On

As DBAs, we generally come across our clients complaining that the current Production Server is not able to hold the load on the server and whether the load may be balanced with the Secondary Server. This is possible with a database in DR Server with Read-only database in Log Shipping and Secondary SQL Server replicas in Always On Availability Group. The biggest advantage of Always On Groups is that it allows us to set up group level HA for any number of databases and we can create up to four secondary replicas and this is a combination of Clustering, Log Shipping and Database Mirroring where the data transmission is more flexible and functional.

Read More

Art of Isolating Dependencies and Data in Database Unit Testing

All the database developers more or less write database unit tests that not only help in detecting bugs early but also save a lot of time and efforts when the unexpected behavior of database objects becomes a production issue.

Nowadays, there are a number of database unit testing frameworks such as tSQLt along with third-party unit testing tools including dbForge Unit Test.

On the one hand, the benefit of using third-party testing tools is that the development team can instantly create and run unit tests with added features. Also, using a testing framework directly gives you more control over the unit tests. Therefore, you can add more functionality to the unit testing framework itself. However, in this case, your team must have time and a certain level of expertise to do this.

This article explores some standard practices that can help us to improve the way we write database unit tests.

Read More

Identifying and Fixing Forwarded Records Performance Issue

Before going through the Forwarded Records performance issue and resolving it, we need to review the structure of the SQL Server tables.

Table Structure Overview

In SQL Server, the fundamental unit of the data storage is the 8-KB Pages. Each page starts with a 96-byte header that stores the system information about that page. Then, the table rows will be stored on the data pages serially after the header. At the end of the page, the row offset table, that contains one entry for each row, will be stored opposite to the sequence of the rows in the page. This row offset entry shows how far the first byte of that row is located from the start of the page.

Read More

Implementing Failover in MS SQL Server 2017 Standard

Introduction

Often, we need to ensure fault tolerance of MS SQL Server DBMS, especially, when there is no Enterprise edition, but only the Standard one.

We would like to note that we are not going to examine the Express edition because there are significant restrictions to this instance. Sure, we can bypass some of them. For example, to resolve the issue with the database size of 10 GB, we can split a large database into smaller ones. To do this, we can create a new database based on a certain property, and combine the selections from the same tables of different databases in the views in the principal database. However, fault tolerance in the Express edition will be performed either by a system administrator or by using your own or third-party software.

In this article, we are going to explore all existing standard fault-tolerance technologies for MS SQL Server 2017 and an example of implementing the most suitable unified standard of fault-tolerance in the Standard edition.

Read More

Getting Started with SQL Server 2017 on Linux in the Azure portal

SQL Server 2017 now is considered as a hybrid database enterprise solution as it expands its market and is ported to other operating system platforms. It also includes mainstream support for Linux machines. The Cloud makes the life of administrator much easier, now it’s no longer daunting task to configure the SQL Server instance. The easiest way to explore SQL Server on Linux is to provision a virtual machine through Microsoft Azure portal – portal.azure.com. The Linux azure virtual machine will come pre-configured with Linux and SQL Server 2017.

Read More

How to Use Default and Custom Widgets in SQL Server Operations Studio

There are already a number of articles and blog posts that reveal the benefits of the SQL Operations Studio tool. The demand created across the software and the related tools are relatively unimaginable than ever before. The trend and growth will continue to increase in the upcoming days.

The graphical representation is in the vogue today. Visualizing data helps us to better understand this and to make decisions. It’s no wonder that data visualization continues to attract a growing number of users. The development of any toolset could provide opportunities to speed up the software development lifecycle process.

This article demonstrates the advantages of using custom SQL queries or complex T-SQL to provide a great insight into the database and explains how one can use this insight to build custom widgets. In this case, the SQL Operations Studio Widget is a customized piece of the code to personalize the SQL Server Dashboard for effective management of SQL instances.

Read More

The Art of Aggregating Data in SQL from Simple to Sliding Aggregations

Let us start our SQL journey to understand aggregating data in SQL and types of aggregations including simple and sliding aggregations.

Before we jump to the aggregations, it is worth considering interesting facts often missed by some developers when it comes to SQL in general and the aggregation in particular.

In this article, SQL refers to T-SQL which is the Microsoft version of SQL and has more features than the standard SQL. Read More