Database development

Jean-Pierre Voogt

Running with running totals in SQL Server

July 29, 2016 by

Background

Running totals have long been the core of most financial systems, be statements or even balance calculations at a given point in time. Now it’s not the hardest thing to do in SQL Server but it is definitely not the fastest thing in the world either as each record has to be evaluated separately. Prior to SQL Server 2012, you have to manually define the window/subset in which you want to calculate you running total, normally we would define a row number with a window on a specific order or a customer depending on the requirements at hand.

Read more »
Ahmad Yaseen

Querying remote data sources in SQL Server

June 10, 2016 by

A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.

SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.

Read more »
Marko Radakovic

Revision history of an object change in a SQL database using Team Foundation Server

May 31, 2016 by

Similarly, as described previously in this article, where the revision history is covered for the Git source control system, we’ll present the workflow of reviewing the history of committed SQL database objects using Team Foundation Server (TFS) source control system. In order to use TFS and have SQL database objects being version controlled, Visual Studio is required, as well as TFS server, either installed on a machine or TFS through Team Services, which is actually TFS “in the cloud”.

Read more »
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

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 »
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 »
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 »
Marko Radakovic

Introduction to SQL Server database continuous integration

January 5, 2016 by

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).

Read more »
Eli Leiba

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

December 24, 2015 by

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.

Read more »
Eli Leiba

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

December 22, 2015 by

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.

Read more »
Joshua Feierman

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

December 18, 2015 by

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.

Read more »
Sifiso W. Ndlovu

Where’s my Team Explorer 2015?

October 28, 2015 by

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.

Read more »
Sifiso W. Ndlovu

SQL Server Database Source Control using Visual Studio Online: Git

October 23, 2015 by

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:

  1. Stability and maturity of source control systems such as TFS and Git
  2. Availability of freeware and proprietary tools that make it convenient for data professionals to keep control of their database source code
Read more »
Steve Simon

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

May 27, 2015 by

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!

Read more »