General database design

What is a SQL Server Data Dictionary and why would I want to create one?

December 26, 2016 by Alen Gubicak

A database

A SQL Server database can be thought of, in and of itself, as a data dictionary. It is self-describing, in that objects can be scripted into Data Definition Language aka DDL scripts to view all attributes, quickly and easily.

Microsoft SQL Server database deployment process

December 14, 2016 by Mustafa EL-Masry

Introduction

Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.

SQL Server consolidation – Hosting multiple databases on a single SQL Server instance

December 2, 2016 by Artemakis Artemiou

Introduction

Consolidating SQL Server databases and instances is a practice which, under certain criteria, allows organizations to benefit as they can reduce licensing costs, and if properly designed, hardware costs.

Concept and basics of Temporal tables in SQL Server 2016

November 24, 2016 by Mustafa EL-Masry

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

Understanding the SQL Server Proportional fill algorithm

November 22, 2016 by Ahmad Yaseen

When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.

How to check if a date is a holiday in SQL server

November 1, 2016 by Gerald Britton

Introduction

There are plenty of scripts to compute the date of various holidays given the year. Let’s look at the problem in reverse: Given a date, determine if it is a given holiday or not.

Database checkpoints – Enhancements in SQL Server 2016

September 21, 2016 by Ahmad Yaseen

When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.

Optimize NULL values storage consumption using SQL Server Sparse Columns

August 29, 2016 by Ahmad Yaseen

SQL Server 2008 introduces a new column attribute that is used to reduce the storage consumed by NULL values in the database tables. This feature is known as Sparse Columns. Sparse Columns work effectively in the case of the columns with high proportion of NULL values, as SQL Server will not consume any space storing the NULL values at all, which helps in optimizing the SQL storage usage.

Link a SQL Server to an Oracle database

August 15, 2016 by Jefferson Elias

There are some cases in the wild where we want to get back data from an external database without additional coding in an application and just write a T-SQL query that will do all the job for us. That’s why Microsoft provides the « linked server » feature.

The new SQL Server 2016 sample database

July 22, 2016 by Jean-Pierre Voogt

Background

We have all learned to love and hate the trusty Bike shop database. Almost every demo or presentation pertaining to SQL Server we do, we use the AdventureWorks sample database. Almost every code sample on books online references AdventureWorks for illustrations and practical explanations of a feature. When SQL Server 2005 was released Microsoft replaced the old Pubs & Northwind Sample databases with a more complete and more feature rich database called AdventureWorks. Ever since we have been using this new sample database for almost everything we want to test. Microsoft has now given us an even better sample database called WorldWideImporters, which utilises almost every SQL Server feature I can think of from Temporal Tables to In-Memory table.

Efficient creation and parsing of delimited strings

July 5, 2016 by Ed Pollack

Description

Converting a delimited string into a dataset or transforming it into useful data can be an extremely useful tool when working with complex inputs or user-provided data. There are many methods available to accomplish this task; here we will discuss many of them, comparing performance, accuracy, and availability!

Temporal tables – SQL Server

July 4, 2016 by Marko Zivkovic

What is a temporal table?

Temporal tables give us the possibility to retrieve data from any point in the past and for every data change (Update, Delete, Merge). With temporal table users can recover data from accidental changes (update/delete) as well as audit all changes to data, etc. Temporary table is a new feature in SQL Server available from SQL Server 2016 CTP2 version. Read more »

What’s new in SQL Server 2016 summary

June 22, 2016 by Minette Steynberg

SQL Server 2016 became publically available on the 1^st of June 2016. Some might feel that a new SQL Server release every 2 years might be too frequent with many organization still lagging behind by running SQL Server 2012 or earlier.

The system msdb database, introduction and tips

June 14, 2016 by Daniel Calbimonte

Introduction

In a previous article, we talked about the tempdb system database. In this article, we will talk about the msdb database.

How to migrate users to a partially contained database in SQL Server

June 13, 2016 by Ashish Kumar Mehta

Introduction

Microsoft introduced the Contained Database feature in SQL Server 2012. In this article we will take a look at how to migrate SQL Server Logins to a partially contained database in SQL Server to remove dependencies from the Instance of SQL Server where Contained Database resides.

SQL Server 2014 contained databases

June 1, 2016 by Ahmad Yaseen

SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.

Managing untrusted foreign keys

March 17, 2016 by Jefferson Elias

Introduction

Intended audience

This article is intended for application developers and database administrators who plan to develop, deploy, and/or assess solutions for Microsoft SQL Server on a Microsoft Windows platform.

SQL Server convenience names

February 19, 2016 by Sifiso Ndlovu

Introduction

A while ago I discovered that you can conveniently connect to a local instance of SQL Server by typing one of the following names:

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.

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.

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.

The tempdb database, introduction and recommendations

January 5, 2016 by Daniel Calbimonte

Introduction

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

SQL Server: Lock settings to use to enhance partitioning capability

December 30, 2015 by Pinal Dave

When talking about performance and scalability inside SQL Server, I don’t see anyone missing out on the fact to mention how locks get involved. I often see DBA’s complain to developers that their code is not scalable and they are experiencing heavy locks in the system because of the way the code has been written. The more I work with SQL Server, the more I start to understand some of these nuances.

Best practices after installing Microsoft SQL Server

December 15, 2015 by Ahmad Yaseen

Introduction

Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start the normal installation process.

After completing the installation, you need to apply specific configurations on the server to make sure that the server can host production databases and the users can connect to it successfully.

In this article, I will describe the common configurations that you should apply after the SQL Server installation.