Features

Esat Erkec

How to create an email alert in SQL Server Agent for job failures

November 28, 2018 by

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

Read more »
Sreekanth Bandarla

Deploy SQL Server with Cluster Shared Volumes – part 2

September 19, 2018 by

In the other article in this series: Deploy SQL Server for failover clustering with Cluster Shared Volumes – part 1 we have seen what a cluster shared volume is and what are the advantages and other considerations to keep in mind when deploying CSVs for SQL Server workloads. In this article, I will walk though actual installation of a failover cluster Instance leveraging CSVs.

Read more »
Prashanth Jayaram

SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup

September 13, 2018 by

A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

Read more »
Prashanth Jayaram

SQL Replication: Basic setup and configuration

September 12, 2018 by

This is article is a continuation of the previous: SQL Server replication: Overview of components and topography.

By now, you’re familiar with the components of replication. So far, we’ve seen a lot of theory about replication. It’s a time for practical walkthrough of setting up a basic transactional SQL Replication system. The best way to get a feel for how SQL Replication is implemented and how it works is to see it in action.

Read more »
Prashanth Jayaram

How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases

September 12, 2018 by

In this data-driven era, replication is often a critical requirement for achieving a modern, agile database management environment. It is believed designing an enterprise-grade dataset is the to achieving this requirement but building datamarts from datasets always presents certain challenges

In this article, we’ll discuss what it takes to setup “central subscriber with multiple publishers” replication model, to create an aggregate dataset from multiple sources, and you’ll also see how to scale with the data.

Read more »
Prashanth Jayaram

SQL Server replication: Overview of components and topography

September 11, 2018 by

The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.

Read more »
Sreekanth Bandarla

Deploy SQL Server for failover clustering with Cluster Shared Volumes – part 1

September 4, 2018 by

Microsoft SQL Server provides us with a wide variety of solutions to architect High availability (HA) and Disaster Recovery (DR) solutions for mission-critical workloads. In this article, let’s just focus on HA, specifically Failover Clustering. Failover clustering is probably the most mature, robust and stable high availability solution which Windows Server Operating system offers. It’s been there around for few decades now and did evolve over time along with SQL Server. In this article Let’s see a hidden feature of windows server failover cluster which helps in making our already highly available SQL Server Failover clustered instances even more highly available. The new feature which we are going to talk about is Cluster Shared Volumes, AKA CSVs. Considering windows server 2019 is around the corner, I say CSVs are not a new concept in clustering, it’s been there for almost a decade now. Microsoft introduced CSVs in windows server 2008R2, but at that time SQL Server was not supported on CSVs. Well, CSVs were originally designed for Hyper-V workloads and later on enhanced for File servers and eventually landed into SQL Server beginning version 2014.

Read more »
Prashanth Jayaram

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

August 31, 2018 by

In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

Read more »
James Rhoat

Considerations and concerns when setting up Database mail for SQL Server

August 31, 2018 by

Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack.

Read more »
Prashanth Jayaram

How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases

August 29, 2018 by

In this article, you’ll learn how to setup a simple, custom distributed database replication system.

Introduction

In general, a typical setup of transactional replication model of a central publisher with multiple subscribers includes the creation of a replica database(s) which may serve multiple purposes including:

Read more »
Prashanth Jayaram

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

August 23, 2018 by

Outline

In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?

Read more »
Prashanth Jayaram

How to set up a DDL and DML SQL Server database transactional replication solution

August 22, 2018 by

In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.

Read more »
Gerald Britton

Shrinking your database using DBCC SHRINKFILE

August 16, 2018 by

Introduction

SQL Server is pretty good at managing disk space. As long as we do our part to set up appropriate storage types and place files and filegroups properly and set reasonable AUTOGROW settings, it’s almost a set-it-and-forget-it operation. Mind you, I said, “almost!” Sometimes, things do go BUMP! in the night and we need to act. Here’s what happened to me not too long ago:

Read more »
Prashanth Jayaram

SQL Server database migrations with zero data loss and zero downtime

August 15, 2018 by

The growing importance and complexity of data migration, in an era of exploding data volumes and ever-changing business requirements, means that old approaches will no longer get the job done. We are in a world where everything needs to run instantly. Every Database Administrator or Developer would have definitely heard about database migrations with zero downtime and with zero data loss.

Read more »
Prashanth Jayaram

The BCP (Bulk Copy Program) command in action

August 13, 2018 by

There are various methods available for bulk data operations.

  1. BCP utility
  2. BULK INSERT
  3. Using OPENROWSET
  4. Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong..

We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data.

Read more »
Prashanth Jayaram

Using transactional data replication to replay and test production loads on a staging server

August 9, 2018 by

Outline

In this article, you’ll see how to simulate production loads on a test server with a “record and replay” type situation using the transaction log, batch scripting, PowerShell and a SQL Server agent job.

We’ll be walking through the scenario in the following steps

  1. Record the production load and write the transactions to disk by generating a timestamped replay script
  2. Create a batch file to automate the task at an interval of every 1 minute
  3. Create a SQL Server agent job to schedule the batch file
  4. Replay the production workload to the target/test database by running a PowerShell script to open and execute the scripts at the same interval as they were created, every 1 minute
  5. Validate the data between the source and the target databases to make sure our job works
  6. Monitor the load with a monitoring tool, solution of your choice
Read more »
Aamir Syed

Use cases for Query Store in SQL Server

July 18, 2018 by

Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data.

Read more »