Accelerated Database Recovery in SQL Server 2019

Total: 1 Average: 5

An Overview of Traditional Recovery

As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym ACID which refers to the characteristics of transactions in relational databases means that we can be assured that if the database fails suddenly, our data is safe.

SQL Server implements this capability using the transaction log. Changes made by all Data Manipulation Operations in SQL Server are captured in the transaction log before being applied to data files (through the checkpoint process) in case it’s needed to roll back or roll forward.

Read More

Using Alerts and Operators in SQL Server

Total: 3 Average: 4.3

Introduction

With all the advancements in SQL Server and data, it feels great that such native SQL tools as Alerts and Operators are still available. They are the key SQL Server Agent features that are related to anything in SQL Server automation. 

Their roles are clear:

  • Alerts, when configured, let database administrators know when a specified event has occurred. 
  • An operator is an object defined within SQL Server that maps anyone or a group that can receive notifications when these events occur.

Read More

Deploying a SQL Server Docker Container

Total: 4 Average: 4.8

Introduction

Containerization has become the dominant topic after the Server Virtualization and Cloud Computing Eras. While many thought leaders assert that current computing paradigms are not particularly new, containerization, as we have it today, can be viewed as having been born after cloud computing. Using containers on-premise allows us to perform migration to the cloud while building a containerized environment mimics the features of solutions deployed in the cloud.

Progression of Computing Read More

Filtering Data in T-SQL

Total: 5 Average: 4.2

Introduction

In most scenarios when working with data, users do not require all the data in each instance of a query. T-SQL (and SQL of course) provide means of retrieving only part of a data set in a table or a similar structure. The WHERE, HAVING, TOP and OFFSET-FETCH clauses are used to accomplish this in different ways. Read More

Using Transaction ROLLBACK in SQL Server

Total: 3 Average: 5

Introduction

Very recently, a colleague of mine came to me in desperation owning up that he had issued an update statement without a WHERE clause on a key application table. The implications on the front end would be dire, so he came to me directly because he urgently needed help with reversing the situation by any means before the emails and escalation started pouring in.

When we looked into the situation, we found that the changes have not been applied in the secondary database. In most cases, the lag between our primary and secondary databases is twenty minutes (we have a little staggering to avoid performance problems). Because my colleague asked for help immediately after realizing the error, we were able to recover the data from the secondary database. I described the value of such a delay in this article. Read More

SQL Always On Availability Groups: Computer Objects

Total: 6 Average: 5

SQL Server Always On Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations that use SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. We experienced the following key benefits of AlwaysOn:

  1. We can group related databases as part of a single Availability Group and have them failover together in case this is needed. This is especially useful for applications that depend on more than one database, such as Microsoft Office SharePoint, Microsoft Lync, and Sage.

  2. When compared to SQL Server Failover Cluster Instances, we find that storage as a single point of failure has been eliminated since each instance which constitutes a replica is assigned its own storage.

  3. With AlwaysOn, it is possible to configure HA and DR at once. This is achieved by creating a Multi-site Windows Failover Clusters as the foundations of your AlwaysOn configuration. Performing a Role Switch when using AlwaysOn is significantly simpler than doing it when using Transaction Log Shipping.

Read More

Switching Out Table Partitions in SQL Server: A Walkthrough

Total: 3 Average: 4.7

Introduction

A few years ago we were tasked with a business requirement for card data in a specific format for the purpose of something called “reconciliation”. The idea was to present the data in a table to an application which would consume and process the data which would have a retention period of six months. We had to create a new database for this business need and then create the core table as a partitioned table. The process described here is the process we use to ensure that data older than six months is moved out of the table in a clean fashion. Read More

AlwaysOn Availability Groups: Quorum

Total: 4 Average: 4.8

SQL Server AlwaysOn Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations using SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. The key benefits of AlwaysOn that we have experienced are as follows: Read More

Database Normalization: A Primer

Total: 5 Average: 4

The Relational Model of data management was first developed by Dr. Edgar F. Codd in 1969. Modern relational database management systems (RDBMSes) are aligned with the paradigm. The key structure identified with RDBMS is the logical structure called a “table”. Tables are primarily composed of rows and columns (also called records and attributes or tuples and fields). In a strict mathematical sense, the term table is actually referred to as a relation and accounts for the term “Relational Model”. In mathematics, a relation is a representation of a set.

The expression attribute gives a good description of the purpose of a column – it characterizes the set of rows associated with it. Each column must be of a particular data type and each row must have some unique identifying characteristics called “keys”. Data change is typically more efficient when done using the relational model while data retrieval may be faster with the older Hierarchical Model which has been redefined in model NoSQL systems. Read More

Parameter Sniffing Primer

Total: 3 Average: 4.7

Introduction

Developers are often told to use stored procedures in order to avoid the so-called ad hoc queries which can result in unnecessary bloating of the plan cache. You see, when recurrent SQL code is written inconsistently or when there’s code that generates dynamic SQL on the fly, SQL Server has a tendency to create an execution plan for each individual execution. This may decrease overall performance by:

  1. Demanding a compilation phase for every code execution.

  2. Bloating the Plan Cache with too many plan handles that may not be reused.

Read More