Jump to Start Test-Driven Database Development (TDDD) – Part 2

We discussed the basics of test-driven database development (TDDD) with examples and compared it with traditional database development in the first part of this article.

In the second part, we are going to move beyond basics to focus on a more realistic scenario of meeting report requirements by using TDDD.

A quick recap of TDDD at this point is handy in understanding how it can help us to achieve such goals.

Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2

In my previous article, I have covered the following topics:

  1. What are Registered Servers and their usage.
  2. How to create and configure a Local Server Group and Central Management Studio.

In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers. Read More

Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1

Managing numerous database servers in an organization is difficult. Either we must maintain a list or maintain an Excel file that has a list of database servers. To retrieve basic information about SQL Instance or databases, DBA must query on individual database servers. If a developer wants a DBA to execute an SQL Script on all database servers, DBA must review the list of servers and execute them one by one, which is time-consuming and error-prone. It might happen that DBA missed any server where it was required to execute. Imagine the cases when DBA must execute the script on specific database servers. It becomes very painful. This task can be performed using the PowerShell scripts but again, it requires some complex PowerShell coding. This task can be managed more efficiently using “Registered Servers”. Read More

Jump to Start Test-Driven Database Development (TDDD) – Part 1

The most common approach to developing database solutions is to start creating database objects based on business requirements which is also known as “Conventional Database Development”.

In this article, we are going to explore the implementation of such approaches as conventional database development and test-driven database development on the particular examples.

To begin with, have a closer look at the conventional database development.

Read More

Analysis of MS SQL Server for those who see it for the first time – Part 2

Analysis of MS SQL Server for those who see it for the first time – Part 1

We continue to analyze what is happening on our MS SQL Server. In this article, we are going to explore how to retrieve information about user performance: who makes what, and how much resources are consumed.

I think the second part will be interesting for both database administrators and developers who need to understand what is wrong with the requests on the production server that used to work fine on the test server.

Read More

Analysis of MS SQL Server for those who see it for the first time – Part 1

Ive recently encountered a problem – SVN went down on ubuntu server. I develop for Windows and I do not have much experience with Linux. I googled the errorwithout success. The error turned out to be the most typical one (the server unexpectedly closed the connection) and does not indicate anything. Therefore, it was necessary to go deeper and analyze logs/settings/rights/etc.

Finally, I figured out the mistake and found everything I needed, but I spent a lot of time. After solving this problem, I thought about how to reduce the uselessly spent hours and decided to write an article that will help people quickly get the understanding of the unfamiliar software.

Read More

Transparent Data Encryption and Always Encrypted

If you need to store confidential data in your database, you can use data encryption. SQL Server supports encryption with symmetric keys, asymmetric keys, certificates, and password phrases. I assume that you, the reader, are already familiar with these terms. In this article, I will focus on two out of many encryption options provided by SQL Server:

  • Transparent Data Encryption (TDE)
  • Always Encrypted (AE)

Read More

What Can Query Plan Tell?

Introduction

SQL query describes the expected result, not the way to get the result. The set of specific steps the server must take to return the result is called the query execution plan. The plan is built by the optimizer. Selection of a plan affects execution speed, what makes it one of the most important elements of the query performance problem analysis.

Execution plan comprises operators and their properties that are interrelated with each other in the form of the tree structure. Each operator is responsible for a separate logical or physical operation. All together, they ensure the result described in the query text. Inside the tree, operators are represented by the class objects in the memory of SQL Server. Server users (that is, you and me) see the description generated in XML format with a specific schema, that is displayed graphically by the SQL Server Management Studio (SSMS) environment.

There are many various plan operators and even more properties. Besides, new ones emerge from time to time. This article does not dare to describe all possible variety of operators. Instead, I would like to share the most interesting additions in this subject and to remind some old but useful elements. Read More

Troubleshooting long running queries in MS SQL Server

Preface

There is an information system that I administer. The system consists of the following components:

1. MS SQL Server database
2. Server application
3. Client applications

These information systems are installed on several objects. The information system is used actively 24 hours a day by 2 to 20 users at once on each object. Therefore, you cannot perform routine maintenance all at once. So, I have to «spread» SQL Server index defragmentation throughout the day, rather than defragmenting all the necessary fragmented indexes at one stroke. This applies to other operations as well.

Read More