Author: Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.

Using Transaction ROLLBACK in SQL Server

Kenneth Igiri
Total: 1 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

Kenneth Igiri
Total: 3 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

Kenneth Igiri
Total: 2 Average: 4.5

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

Kenneth Igiri
Total: 2 Average: 4.5

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

Kenneth Igiri
Total: 3 Average: 3.3

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

Kenneth Igiri
Total: 2 Average: 4.5

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

T-SQL Datetime Data Type

Kenneth Igiri
Total: 5 Average: 3.8

Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

  1. Datetime
  2. Smalldatetime
  3. Date
  4. Time
  5. Datetime2
  6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server. Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Kenneth Igiri
Total: 1 Average: 3

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More

Concatenation in Transact-SQL

Kenneth Igiri
Total: 3 Average: 3.7

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

Read More

In-depth Exploration of Row Level Security

Kenneth Igiri
Total: 3 Average: 4

Introduction

Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many relationship between instances and databases. However, there are cases where the solution demands that data is consolidated into one table. In such a case, there may be concerns about how to restrict access to the data.

Row Level Security was introduced in SQL Server 2016 as a solution to scenarios similar to the above. It allows you to restrict access to rows in a table based on conditions defined in an inline Table Valued Function called a Predicate Function. When a Predicate Function is applied to a user table containing consolidated data, the system can be configured to return different data sets to different users depending on their roles which in turn depends on their job descriptions or departments for example.

Read More