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.

Concatenation in Transact-SQL

Kenneth Igiri
Concatenation in Transact-SQL
5 (100%) 1 vote

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
In-depth Exploration of Row Level Security
5 (100%) 2 votes

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

Dealing with NULLs in SQL Server

Kenneth Igiri
Dealing with NULLs in SQL Server
3.3 (66.67%) 3 votes

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More

Query Store: Showing the Impact of Indexes on Inserts

Kenneth Igiri
Query Store: Showing the Impact of Indexes on Inserts
4 (80%) 6 votes

Introduction

It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More

Navigating SQL Server Error Logs

Kenneth Igiri
Navigating SQL Server Error Logs
3.9 (77.14%) 7 votes

Introduction

One of the key skills you need as a database administrator or an IT person is generally the ability to monitor systems very carefully. Lack of this key skill can lead to misdiagnosis when troubleshooting issues. SQL Server exposes a number of tools that can aid the DBA in troubleshooting problems that occur in production. The SQL Server Error Log and the SQL Server Agent Log are two of the most important facilities for troubleshooting SQL Server. In this article, we shall explore the ways we can manipulate the Server and Agent logs.

Read More

Monitoring Backups Across Instances

Kenneth Igiri
Monitoring Backups Across Instances
3.8 (76%) 10 votes

Introduction

In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:

  1. Using Linked Servers, catalog views, SQL Agent Jobs and Database Mail
  2. Using Central Management Server

In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later. Read More

Move Datafiles in SQL Server – Part 2

Kenneth Igiri
Move Datafiles in SQL Server – Part 2
3.8 (76%) 10 votes

Introduction

In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different. Read More

Move Datafiles in SQL Server – Part 1

Kenneth Igiri
Move Datafiles in SQL Server – Part 1
3.6 (72.5%) 8 votes

Introduction

There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:

  1. The need to format the volume assuming it was not formatted properly when SQL Server was installed. Recall that when installing SQL Server, it is recommended that 64K allocation unit size is used to format the volumes. If this is not done at the point of installation and needs to be done later, it will obviously require preserving a backup of the database fist or creating a new, properly formatted volume and moving the database to this new volume.
  2. The need to use a new volume assuming the limits have been reached for the underlying storage. A good example would be the 2TB limit of a VMware Data Store. This is the case as of VSphere 5.0. Higher versions of VSphere have much higher limits.
  3. The need to improve performance by managing IO. One more reason you may want to move datafiles is performance. There are cases where a database is created with multiple datafiles all sitting on one disk until it becomes obvious, as the database grows, that the you have created a “hot region” in the storage layer. One solution would be creating new data files and rebuilding clustered indexes, another would be moving data files.

Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Kenneth Igiri
Fixing Data Loss Using Log Shipping with Delayed Recovery
4 (80%) 6 votes

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

Consolidating SQL Server Instance by Clustering and Stacking

Kenneth Igiri
Consolidating SQL Server Instance by Clustering and Stacking
3 (60%) 4 votes

Stacked SQL Cluster Instances

NOTES:

  • Windows Failover Clustering comprising two nodes.
  • Two SQL Server Failover Cluster Instances. This configuration optimizes the hardware. IN01 is preferred on Node1 and IN02 is preferred on Node2.
  • Port Numbers: IN01 listens on port 1435 and IN02 listens on port 1436.
  • High Availability. Both nodes back up each other. Failover is automatic in case of failure.
  • Quorum Mode is Node and Disk majority.
  • Backup LAN in place and routine backup configured using Veritas

Read More