Collation in SQL Server

Total: 1 Average: 5

Introduction

You must have already heard the term “Collation” in SQL Server. Collation is a configuration that determines how character data sorting is done. This is an important setting that has a huge impact on how the SQL Server database engine behaves in dealing with character data. In this article, we aim to discuss collations in general and show a few examples of dealing with collations.

Read More

Using Transaction ROLLBACK in SQL Server

Total: 2 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 Server High availability: Add new disk to an existing failover cluster instance

Total: 0 Average: 0

In my previous article, I have explained the step-by-step process of installing a node in existing SQL Server Failover Cluster Instance. Along with that, I have also demonstrated manual failover and automatic failover.

In this article, I am going to demonstrate the process of adding a disk in a failover cluster and then move the existing database to a new drive. Read More

Dynamic Data Masking in SQL Server for beginners

Total: 1 Average: 5

The article introduces the basics of dynamic data masking (DDM)  in SQL Server along with its overview supported by a simple example of data masking implementation. Additionally, the readers are going to get familiar with the benefits of dynamic data masking. This paper also highlights the importance of data masking in day to day database development tasks when some fields must be masked due to their sensitive nature in order to comply with standard practices.

Read More

SQL Always On Availability Groups: Computer Objects

Total: 4 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

Analytic Functions in SQL Server

Total: 1 Average: 4

Aggregate and Analytic functions in SQL Server operate on a set of rows. However, unlike such aggregate functions as sum, count and average that return scalar values, analytic functions return a group of rows that can be further analyzed. In this article, we will see some of the most commonly used analytic functions in SQL server. We will be discussing the following functions:

  • CUME_DIST
  • FIRST_VALUE
  • LAST_VALUE
  • LEAD
  • LAG

Read More

Automating the Tabular Model Processing of Analysis Services (SSAS) Databases in SQL Server

Total: 1 Average: 5

This article talks about automating the processing of the Analysis Services database in SQL Server. Automation is one of the most important tasks managed by database administrators or DevOps engineers.

Additionally, we’ll get an overview of Analysis Services databases and how they differ from SQL Server databases.

This article also highlights the importance of automating tasks in SQL Server to minimize the errors and maximize the productivity, particularly while working with complex scenarios like data warehouse business intelligence solutions. Read More

Automate Database Test Restore in SQL Server

Total: 4 Average: 4

This article talks about automating the database restoration process which is often done manually by the DBA or the infrastructure team responsible for the management of database server(s) and database(s).

This article also highlights the importance of automating database administration tasks such as database backup and restoration to ensure that consistency and reliability of production database(s) are intact.

Additionally, there will be some tips about how to plan and implement database restoration task automation in a real-life scenario. Read More