Peer to peer replication

February 18, 2016 by Rajendra Gupta

Nowadays many customers are spread globally and need to manage the requirement for users to connect from/to any location, perform an activity (insert, update, delete), and the databases should be kept synchronized across multiple sites.

How to execute jobs on multiple SQL Servers

February 18, 2016 by Daniel Calbimonte

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.

Searching the SQL Server query plan cache

February 8, 2016 by Ed Pollack

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!

What is causing database slowdowns?

February 2, 2016 by Kimberly Killian

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.

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

January 29, 2016 by Luan Moreno M. Maciel

On the last article about the best modes to move the disk-based tables to using the In-Memory feature we covered all the aspects and styles available on SQL Server 2014. Continuing on the migration process now we’re going to look at some of the new enhancements of makes SQL Server 2016

How to run a local SQL Report from an Azure VM Database

January 28, 2016 by Daniel Calbimonte

Introduction

In this new article, we will create a SQL Server Report using SQL Server Reporting Services from a SQL Server machine installed in an Azure Virtual Machine. We will show step by step how to connect from a local machine to a VM Azure Database and generate a SQL report.

A DBAs introduction to Mercurial – Working with files and changes

January 28, 2016 by Joshua Feierman

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!

Database snapshot in SQL Server

January 28, 2016 by Rajendra Gupta

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.

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

January 28, 2016 by Luan Moreno M. Maciel

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.

Simple SQL Server automated user auditing

January 25, 2016 by Kimberly Killian

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.

Understanding the distribution scale of transactional and snapshot replication

January 25, 2016 by Timothy Smith

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.

How to run multiple queries using the Central Management Server

January 25, 2016 by Daniel Calbimonte

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.

Creating a stored procedure to fix orphaned database users

January 25, 2016 by Eli Leiba

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.

How to install SQL Server Management Studio 2008

January 20, 2016 by Marko Zivkovic

SQL Server Management Studio is a software application that helps users to easily access the SQL Server database engine and manipulate objects and data stored on SQL Server databases. The first version was released with Microsoft SQL Server 2005, replacing Enterprise Manager as the primary interface for Microsoft SQL Server.

MS SQL Server backup optimization

January 19, 2016 by Kaloyan Kosev

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:

Removing the risk from important maintenance tasks in SQL Server

January 18, 2016 by Ed Pollack

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.

Connecting PowerShell to SQL Server

January 13, 2016 by Shawn Melton

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.

Contained databases in SQL Server

January 12, 2016 by Rajendra Gupta

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.

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

January 12, 2016 by Stefan Daniel Plačkov

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.

SQL Server non-clustered indexes with included columns

January 11, 2016 by Ahmad Yaseen

SQL Server indexes are created to speed up the retrieval of data from the database table or view. The index contains one or more columns from your table. The structure of these keys are in the shape of B-tree distribution, enabling SQL Server to find the data quickly.

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by Miroslav Dimitrov

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.

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by Ahmad Yaseen

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

SQL Server lessons from a TFS installation

January 8, 2016 by Sifiso Ndlovu

Introduction

More often than not (at least in places I’ve worked at), the job of installing, configuring and subsequent administration of Team Foundation Server (TFS) is performed by different individuals to those administering enterprise applications such as Windows Server, SQL Server, SharePoint etc. This is because TFS, unlike other enterprise applications, often has to be administered from both the server side (i.e. TFS Administration Console) as well as the client side (i.e. using Team Explorer in Visual Studio) – it is thus not surprising that a TFS administrators may once had been a developer. The benefit of having a TFS administrator with a software development experience is that it may be easy to get developer-buy-in into the tool. However, the disadvantage to this is that developers usually get accustomed to doing things in a certain way – like connecting to SQL Server using (local) convenience names.

Data boundaries: Finding gaps, islands, and more

January 6, 2016 by Ed Pollack

One of the more difficult challenges we face when analyzing data is to effectively identify and report on boundaries. Data sets can contain any number of significant starting and stopping points that may indicate significant events, such as missing data, important business events, or actionable changes in usage. Regardless of the use case, knowing how to quickly locate and manage data boundaries is extremely useful. Knowing how to design solutions that can effectively avoid these scenarios can also be helpful in the long run.

Creating a gap in sequences – TSQL Stored Procedure advisor

January 6, 2016 by Eli Leiba

Introducing the Problem

Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons.

The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth.