Joshua Feierman

A DBAs Introduction to Mercurial – Branching and merging

February 19, 2016 by

Introduction

In my previous article, we went over the basics of Mercurial, as well as some arguments why using it is critical for database administrators. Among many reasons, it allows us to easily track history and changes to our scripts, which in turn makes it easier for us to experiment and enhance our toolkit, since we can do so safely without fear of permanently causing damage. In this installment, we are going to go into more depth on the specifics of two feature of Mercurial that, once harnessed, can add significant efficiency to our coding workflows.

Read more »
Ed Pollack

Insight into the SQL Server buffer cache

February 18, 2016 by

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

Read more »
Daniel Calbimonte

How to execute jobs on multiple SQL Servers

February 18, 2016 by

Introduction

In earlier chapter, we explained how to run queries in multiple SQL servers using the SQL Central Management Server. In this new chapter, we will show how to propagate a job from a SQL Server Master Agent Job to a target server.

This feature is called Multiserver Administration. In a multiserver administration, you need a Master Server and one or more target servers. In the master server, you create a copy of the job and then it is copied and executed in the target servers.

Read more »
Ed Pollack

Searching the SQL Server query plan cache

February 8, 2016 by

Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be difficult or impossible to acquire otherwise.

Understanding how to access and use the metadata about query execution will provide us the tools we need to answer questions about our server and gain fascinating performance data. I’ve found myself spending more and more time writing, tweaking, and using queries against the plan cache lately and look forward to sharing these adventures with you!

Read more »
Kimberly Killian

What is causing database slowdowns?

February 2, 2016 by

Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.

Read more »
Joshua Feierman

A DBAs introduction to Mercurial – Working with files and changes

January 28, 2016 by

Introduction

In my previous article, we went over the reasons why DBAs should use version control, as well as the benefits of Mercurial as a specific choice. We also gave three examples of instances where source control can come in handy (though to be honest they were picked from a much longer list). In this article, I’m going to go a step further and actually walk you through setting up your first repository (locally for now, we’ll go into setting up a remote one later), making your first commit, and making (and viewing) changes to your newly tracked files. Let’s get started!

Read more »
Rajendra Gupta

Database snapshot in SQL Server

January 28, 2016 by

Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.

Read more »
Luan Moreno M. Maciel

In-Memory OLTP Series – Data migration guideline process on SQL Server 2014

January 28, 2016 by

In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.

There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.

Read more »
Kimberly Killian

Simple SQL Server automated user auditing

January 25, 2016 by

As a DBA I am often asked to provide lists of all active users ID’s or groups for a specific server or database. In addition to this, I am also asked to provide a list of failed logins. These requests are frequent enough that I decided to automate the process of gathering this data and provide two nicely formatted HTML emails. I am not going lie, manager types love these reports, and anything that makes managers love my work I am all about! I’m also all about automating anything that makes my job easier. Call me lazy or call me prepared, I hate having to do something over and over that I could easily throw into an SSIS package or Agent Job and just schedule it to do it for me. This entire process consists of using SQL Server Integration Services (SSIS), 4 tables and a SQL Agent Job containing 2 reports (Failed Logins and Active SQL Server Users). The SSIS package, along with all of the queries and scripts are attached at the end of the article.

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 »
Daniel Calbimonte

How to run multiple queries using the Central Management Server

January 25, 2016 by

Introduction

When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks.

In this new article, we will show how to run T-SQL scripts against multiple Servers using SQL Server Management Studio (SSMS). To do this, we will use the Central Management Server. The main idea of this feature is to administer multiple servers in a centralized way using queries or policies. This feature is available in SQL Server 2008 or later versions and cannot be applied in older versions.

Read more »
Eli Leiba

Creating a stored procedure to fix orphaned database users

January 25, 2016 by

The Problem

This problem demonstrates a very common scenario. When a backed up database is restored to a different location or server, the restored database users that exist inside of it, lose their association to the new server login objects.

This problem occurs because the server id in the user data is still the old server and must be changed to the new one.

The DBA task is then to restore the connection between the login and the database user object and to link them together again.

Read more »
Kaloyan Kosev

MS SQL Server backup optimization

January 19, 2016 by

Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller.

But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on:

Read more »
Ed Pollack

Removing the risk from important maintenance tasks in SQL Server

January 18, 2016 by

As database professionals, we are often in very close proximity to important processes, data, and applications. While we adopt the mantra of “Do no harm”, many maintenance or reporting tasks that we create carry unseen risks associated with them.

What happens when a drive fills up during daily differential backups? What if an index rebuild job runs abnormally long and interferes with morning processing? How about if a data load process causes extensive resource contention, bringing normal operations to their knees? All of these are planned events, yet can cause considerable disruption to the very processes we are trying to safeguard.

Read more »
Shawn Melton

Connecting PowerShell to SQL Server

January 13, 2016 by

Introduction

PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.

There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.

Read more »
Rajendra Gupta

Contained databases in SQL Server

January 12, 2016 by

As we know there are two types of authentication available in SQL Server Windows authentication and SQL authentication. In Windows authentication we use Active directory authentication to connect with SQL Server which makes the most secure authentication method as it can have complexity, group policy configured at AD level applied to all domain servers while in SQL Authentication SQL users are created inside SQL and provided required permissions. The Permissions includes server wide and database wide. The logins can have certain permissions at the database level might be read or write etc.

Read more »
Stefan Daniel Plačkov

Step by step installation of SQL Server Management Studio (SSMS) 2012

January 12, 2016 by

SQL Server Management Studio (SSMS) is the official Microsoft application used for managing all components within Microsoft SQL Server. It is a powerful tool which allows users to control any objects in the SQL Server. It also provides script editing and GUI tools which are easy to use and user friendly.

SQL Server has multiple editions including: Enterprise Edition, Enterprise Core Edition, Business Intelligence Edition, Standard Edition, Web Edition, Express and Developer Edition each with its own features and functions.

Read more »
Miroslav Dimitrov

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by

Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.

Read more »