Database development

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!

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!

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.

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.

Introduction to SQL Server database continuous integration

January 5, 2016 by Marko Radakovic

What is SQL database CI?

Continuous integration (CI), in the context of databases, refers to the practice of isolated database changes to be integrated, as soon as they are made and pushed to a source control repository. In the early days of CI, daily integration was a rule of thumb to follow. However, today, it is more common, than not, that database changes are integrated several times a day. Each change needs to pass an established testing plan that executes automatically on detected changes committed to the repository and if everything is ok, changes will be automatically merged using the build script. with the remaining code, into a new build that will be pushed to other environments (i.e. production, QA).

Construct a special multi-statement table function for checking SQL Server’s health

December 24, 2015 by Eli Leiba

The Problem

Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report.

Creating an automatic View to an In-line table function conversion stored procedure

December 22, 2015 by Eli Leiba

Introducing the Problem

We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.

When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.

A DBA’s introduction to Mercurial – When and why we should use version control

December 18, 2015 by Joshua Feierman

Introduction

As DBA’s we usually have a lot of utility scripts sitting around that we use in our daily work. Examples might include things like common administrative duties (setting up users for an application for example), installing standardized maintenance routines, or even something as complex as a home grown utility database. Often times these scripts languish on some network share or (worse) our own computers, with previous versions lost for all time when changes are made. For those of us that come from a development background, this approach makes us shiver. Keeping code that is not in some kind of version control system is inexcusable for any level of software professional, and DBA’s should be no exception. Used for quite some time by software developers, version control is a wonderful tool for administrators as well, for reasons we will discuss. But first, I want to talk a little bit about my version control system of choice, Mercurial.

Where’s my Team Explorer 2015?

October 28, 2015 by Sifiso Ndlovu

Introduction

If you have been using Team Foundation Server (TFS) for as long as I have, you will recall that just about every release of TFS has always been accompanied by standalone Team Explorer and Team Explorer Everywhere software installers. So you will understand my bewilderment when I couldn’t locate these installers following a successful setup of on-premise (on-prem) TFS 2015 server instance for my employer. After going through every possible link on search engine results relating to “Team Explorer 2015,” I concluded that only Team Explorer Everywhere 2015 was released this time around.

SQL Server Database Source Control using Visual Studio Online: Git

October 23, 2015 by Sifiso Ndlovu

Introduction

Source-code versioning control has always been at the core of Continuous Integration (CI) development practice. In fact tools by JetBrains and Microsoft (amongst, many vendors) have provided development teams an ability to implement some form of CI in their Software Development Lifecycle (SDLC). In recent years, however, the focus of an effective CI strategy has expanded to include mechanisms of keeping track of database files. The possibility of this gradual expansion has largely been driven by two factors:

Stability and maturity of source control systems such as TFS and Git
Availability of freeware and proprietary tools that make it convenient for data professionals to keep control of their database source code

Using the “Row_Number” function and a WHILE loop to create the rolling average report

May 27, 2015 by Steve Simon

Introduction

A few days ago I received an email from a gentleman in the healthcare industry. He had read an article that I had previously published a few years back on a well known SQL Server website. Based on the original article, he requested a few ideas on how to expand the sample code to do a rolling three-month revenue summary, in addition to a rolling three-month revenue average.

In today’s get together, we shall be looking at doing just this!

String Concatenation Done Right – Part 2 – An Effective Technique

April 16, 2015 by Matija Lah

Previously I have introduced string concatenation by demonstrating how not to use Transact-SQL to concatenate strings. In this part I will focus on XML composition, and will demonstrate how to correctly implement it in order to concatenate strings.

SQL Concatenation Done Right – Part 1 – Dubious Practices

April 15, 2015 by Matija Lah

This article is a part of three articles series to explore SQL Concatenation techniques.

Having to represent sets of data as strings is a very common requirement in information management, even in modern times where a variety of more or less elaborate standards for storing, and moving, data are at our disposal. For instance, XML, JSON, or similar techniques, allow the data to be extracted from one data source, using a well-known standard, and be stored temporarily until being loaded into a destination data store, or until being consumed in some other way. Actually, both XML as well as JSON might even be used as a standard way of storing data permanently; especially, if the consumers expect the data to use one or the other format.

The SQL Server system views/tables/functions. Common questions and solutions to real life problems

December 25, 2014 by Daniel Calbimonte

Introduction

In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.

The system views are views that contain internal information about a Database.

The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.

In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.

Automating database tests with Visual Studio and Team Foundation Server

October 31, 2014 by Régis Baccaro

This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.

Post 1: Continuous Integration with SSDT and TFS

Post 2: Unit testing with SQL Server Data Tools

You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »

Backup testing with PowerShell – Part 2: Reporting results

October 22, 2014 by Derik Hammer

Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece. Read more »

Backup testing with PowerShell – Part 1: The test

October 21, 2014 by Derik Hammer

Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon. Read more »

SQL Unit testing with SQL Server Data Tools

October 16, 2014 by Régis Baccaro

This article on SQL Unit Testing is the second part on the series about SSDT and database development

Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server

Introduction

In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.

Continuous integration with SQL Server Data Tools and Team Foundation Server

October 1, 2014 by Régis Baccaro

SQL Server Data Tools – a free standalone download or and add-in to visual studio comes in different flavors and versions. Although this blog post uses Visual Studio 2012 and SSDT stand-alone the principles are example are also valid on Visual Studio 2013.

Background to exception handling in SQL Server

June 26, 2014 by Sifiso Ndlovu

Since the advent of SQL Server 7.0, the best way of raising T-SQL related errors back to calling applications has been through the usage of the RAISERROR statement. Figure 1 demonstrates a combination of mandatory parameters (i.e. msg_id/msg_str, severity, state) as well as optional parameters (i.e. WITH option) that ought to be provided for the purposes of successfully using the RAISERROR function. Read more »

Creating and using CRUD stored procedures

April 7, 2014 by Milica Medic

A typical N-tier application design contains of the several layers from the client (Web or Windows), to the Business Logic Layer, to the Data Access Layer, and finally to the Data Storage Layer. Read more »