Features

Vitor Montalvão

How to download and install the SQL Server Database Experimentation Assistant (DEA v2.0)

April 19, 2017 by

Introduction

I had the opportunity to work and perform various tests with Database Experimentation Assistant (DEA) Technical Preview 1 (DEA v1) and also worked closely to DEA’s development team by reporting some issues and have their feedback on how to solve most of them. Some issues were really bugs and they had them fixed in this new release, the DEA Technical Preview 2 (DEA v2.0).

Read more »
Vitor Montalvão

How to use the SQL Server Database Experimentation Assistant (DEA) tool

February 24, 2017 by

Introduction

This is my second article about Database Experimentation Assistant (DEA). Please refer to my previous article to see how to download and install DEA.

The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.

Read more »
Vitor Montalvão

How to download and install the SQL Server Database Experimentation Assistant (DEA)

January 12, 2017 by

Introduction

The Database Experimentation Assistant (DEA) is a new Experimental solution provided by Microsoft used to help upgrading databases from older SQL Server versions (MSSQL 2005 or higher versions) to the more recent ones (MSSQL 2012 or higher versions). It accomplished this based in a given workload that is previously captured from the source database and later replayed on the target SQL Server instances for comparison.

Read more »
Aamir Syed

How to configure Transparent Data Encryption (TDE) in SQL Server

December 19, 2016 by

Introduction and Overview

Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

Read more »
Aamir Syed

PowerShell: Get a daily database status email

November 22, 2016 by

The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.

Read more »
Aamir Syed

Have your database server talk to you: Send emails directly from SQL Agent Jobs

November 7, 2016 by

Recently, I was asked if I can write and run a specific query and have the results, (if any) emailed to a specific group of people every day? But only send the email if the query returns results. Who wants to open and empty email after all?

As with any DBA, we need a way to automate this process, as our time is valuable. Who wants to manually run a script, paste it into excel and then email it directly to users every morning?

Read more »
Ahmad Yaseen

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.

Read more »
Jean-Pierre Voogt

Format DBMail with HTML and CSS

June 23, 2016 by

Background

In my organization, the developers send a lot of database generated emails for informational purposes to ensure that everything in the system is still working 100%.We can call them production messages. Normally the way the email is presented is not important as it is only meant for the developer to help with system health check, but every now and again a business user would ask for the report (which was never meant to be an actual report) and then we would include the business user in the distribution list for this email.

Read more »
Timothy Smith

Useful DBCC log commands

June 1, 2016 by

Background

When we’re architecting or troubleshooting issues in an environment, understanding how the log is set up and its history helps us identify if improving it will carry significant impacts overall. We have some built in tools that we can use, such as DBCC LOGINFO and DBCC SQLPERF and in this tip we look at how we can use them when we’re debugging issues, designing our logs for scale, or monitoring our current setup. We also look at some ways we can retain the information these provide so that we can use this for historic measurements and benchmarking, as well as using them for potentially alerting.

Read more »
Timothy Smith

Some replication architecture errors and their resolutions

May 16, 2016 by

Background

From time to time, I’ve run into replication issues in inherited environments that I did not architect and some of these environments experienced errors in replication because of how it was constructed from the beginning. In this tip, we look at some of the basics in replication architecture and then at solving some of these problems. Some of the replication issues I’ve seen are caused by misunderstanding what is impossible and possible with replication.

Read more »
Jefferson Elias

How to take advantage of the SQL Server’s transaction log?

April 11, 2016 by

Introduction

SQL Server keeps track of all database modifications and every database transaction. This is done in a file called the transaction log or TLOG. This transaction log is particular to a SQL Server database and there is, at least, one transaction log per SQL Server database.

As explained on MSDN, the transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

Read more »
Ahmad Yaseen

How to configure SQL Server mirroring on a TDE encrypted database

February 19, 2016 by

Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.

SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.

The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.

Read more »
Timothy Smith

Understanding the distribution scale of transactional and snapshot replication

January 25, 2016 by

Background

If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical end users will plot something similar to the below image, where we see a table with data being copied to another table with data.

Read more »
Miroslav Dimitrov

SQL Server Transactional Replication Moving Distribution Database – step-by-step guide

November 27, 2015 by

SQL Server replication is a relatively old high-availability solution part of the Microsoft world. In fact, there have not been any significant changes to this solution in the recent versions SQL versions, but it is still a widespread mechanism for distributing objects from one database to another and synchronize them. Replication is very useful when you have remote and mobile users accessing your data. Let’s cut to the chase and give you more details about our specific case. 🙂

Read more »
Ed Pollack

The SQL Server Query Store in Action

November 26, 2015 by

Introduction

The Query Store is a new feature in SQL Server 2016 that will greatly improve the process of performance tuning! The biggest limit of the query plan cache and the dynamic management views that relates to it is that it is very temporary. Queries are only accessible for as long as they are in cache, and as a result there is no way to travel back in time to determine what happened at 9am yesterday, or why performance worsened over a week-long period.

Read more »
Brian Bønk Rueløkke

Query Store – the next generation tool for every DBA

November 13, 2015 by

Along with the release of SQL server 2016 CTP 3 now comes the preview of a brand new feature for on premise databases – the Query Store. This feature enables performance monitoring and troubleshooting through the log of executed queries.

This blogpost will cover the following aspects of the Query Store feature:

  • Introduction
  • How to activate it
  • Configuration options
  • What information is found in the Query Store
  • How to use the feature
  • What’s in it for me
Read more »
Miroslav Dimitrov

SQL Server Transaction Log – Part 3 – Configuration Best Practices

May 4, 2015 by

It has been a while since the last transaction log article has been published, so I hope you do remember where this series is heading to. In the former posts, we have examined the Log Structure and Write-Ahead Algorithm (part 1) and the Top Reasons for Log Performance Problems (part 2). Taking into consideration this knowledge, we will review some best-practices for transaction log configuration in order to decrease the chance of experiencing log bottlenecks.

Read more »
Daniel Calbimonte

How to create a Database Mirroring

March 6, 2015 by

Sometimes our Database fails and we need to reestablish the Database services immediately. Is there a SQL Server solution for high availability?

With Database Mirroring, we can have a replica of the database ready to be used if the main database fails. In this article, we will give a step-by-step tutorial about Database Mirroring. There are other solutions for high availability like high AlwaysOn, Clusters and Log Shipping, but in this chapter, we will start with Mirroring.

Read more »