AlwaysOn Availability Groups: Quorum

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

How to Write Stored Procedures for Professional SSRS Reports

In this article, we’ll talk about professional SQL Server Reporting Services reports, how to create stored procedures for these reports to meet business requirements, and the benefits of using stored procedures to run behind SSRS reports.

About Professional SSRS Reports

Let’s first get familiar with professional SSRS reports.

Simple Definition

A professional SSRS report is a report created with the standard practices of report development in mind and which has been thoroughly tested to be capable of fulfilling business requirements.

In other words, a professional SSRS report is very carefully designed, developed, tested, and deployed to the target environment to make sure it serves the purpose and is beneficial to the business. Read More

Calculate the median by using Transact SQL

The statistical median is the value which separates a dataset into halves – one comprises greater values, and the other comprises lesser ones. For a specified dataset, it can be considered as the “middle” value. For example, in the dataset {1, 3, 3, 4, 5, 6, 7, 8, 9}, the median is 5, which is fourth largest, and fourth smallest number in the dataset.

To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Read More

Database Normalization: A Primer

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

.NET – Tools for working with multithreading and asynchrony – Part 2

This article comprises the second part of my speech at the multithreading meetup. You can have a look at the first part here. In the first part, I focused on the basic set of tools used to start a thread or a Task, the ways to track their state, and some additional neat things such as PLinq. In this part, I will fix on the issues you may encounter in a multi-threaded environment and some of the ways to resolve them. Read More

Understanding SQL Server Always Encrypted

Security is one of the most important requirements for a data-driven system. Encryption is one of the ways to secure the data. Wikipedia defines encryption as:

Encryption is the process of encoding a message or information in such a way that only authorized parties can access it and those who are not authorized cannot.

In SQL Server 2016, Microsoft introduced an encryption feature called Always Encrypted. In this article, we will see what Always Encrypted is, and how it can be used to encrypt and decrypt data, with the help of simple examples.

Read More

Update SQL Server statistics using a database maintenance plan

Database backups, integrity checks, and performance optimizations are core regular tasks of DBAs. The client data is very important for a DBA to manage the database backup and make sure the integrity of the backups. So if something goes wrong with a production database, it can be recovered with minimum downtime. The database integrity checks are also important because, in the case of database corruption, it can be corrected with minimum downtime and data loss. Managing database performance is also important. Managing database performance is a combination of multiple tasks. Read More

Creating Customer-Focused SSRS Reports with Parameters

This article talks about how to create a basic customer-focused report using SQL Server Reporting Services (SSRS).

The article also highlights the importance of utilizing the full potential of the SSRS reporting technology by using parameters in SSRS reports – for example, to meet business specifications.

The main focus of this article is to prepare the readers who already have a T-SQL background to create SSRS reports for customers. In this way, the customers can run these reports based on their desired input values rather than on default values provided by report developers. Read More

How to Document Your SQL Server Database

The process of documenting a SQL Server database is a complete and continuous process that should start during the database design and development phases and continue during all database related life cycles in a way that ensures having an up-to-date version of the database documentation that reflects reality at any point in time. If performed properly, the generated database documentation file will contain an up to date and complete list for the database objects and a brief description for these database objects. Read More

Parameter Sniffing Primer

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