How to take advantage of the SQL Server’s transaction log?

April 11, 2016 by Jefferson Elias

Introduction

SQL Server keeps track of all database modifications and every database transaction. This is done in a file called the transaction log or TLOG. This transaction log is particular to a SQL Server database and there is, at least, one transaction log per SQL Server database.

As explained on MSDN, the transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

The benefits, costs, and documentation of database constraints

April 7, 2016 by Ed Pollack

Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?

Perform volume maintenance tasks security policy

April 4, 2016 by Shawn Melton

Introduction

You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.

TFS tools for managing SQL Server development

March 29, 2016 by Sifiso Ndlovu

In today’s world of DevOps and ALM practices, there is often a strong emphasis on the visibility and traceability of the work we perform towards software development. This means that as developers, we often have to account (to clients, business owners, project teams etc.) for a list of development tasks that will be undertaken to deliver a given project requirement or user story. Amidst several tools that can be used to facilitate such a practice, Team Foundation Server (TFS) is one such tool that can be used by developers to keep track of their work items.

How to install and use Microsoft Baseline Security Analyzer (MBSA)

March 25, 2016 by Jefferson Elias

Introduction

Microsoft Baseline Security Analyzer is one of the tools provided by Microsoft to help administrators to scan systems (local and remote) for missing security updates and common security misconfigurations. It can scan the server operating system and SQL Server but also other products as well, such as Microsoft web server IIS.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

SQL Server indexed views

March 17, 2016 by Ahmad Yaseen

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.

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.

Working with ragged right formatted files in SSIS

March 16, 2016 by Sifiso Ndlovu

In the world of SSIS development architecture, preference should be given to extracting data from flat files instead of non-Microsoft relational databases. This is because you often don’t have to worry about driver support and compatibility issues in your SSIS development/server machine that is often attributed to non-Microsoft database vendors. In fact, I’ve been in several situations whereby we cannot upgrade to another version of SSIS (i.e. BIDS to SSDT) due to the lack of external vendor driver compatibility issues in the newer versions of SSIS.

Compare and synchronize images between two SQL Server databases

March 11, 2016 by Daniel Calbimonte

Introduction

Sometimes we lose data by mistake and we want to compare the data with older backups and synchronize the data.

There are several ways to do this. For example, you can create T-SQL scripts with some outer joins. Another option is to use the tablediff command line.

Searching SQL Server made easy – Building the perfect search script

March 9, 2016 by Ed Pollack

The need to search through database schema for specific words or phrases is commonplace for any DBA. The ability to do so quickly, accurately, and completely is critical when developing new features, modifying existing code, or cleaning up the vestiges from an application’s ancient history.

Searching SQL Server made easy – Searching catalog views

March 9, 2016 by Ed Pollack

The new SQL Data warehouse in Azure

March 8, 2016 by Daniel Calbimonte

Introduction

In previous chapters, we taught how to create SQL Databases in Azure. In this new chapter, we will show you the new SQL Data Warehouse Service in Azure.

Analyzing SQL Server backups

March 8, 2016 by Shawn Melton

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

How to track changes in SQL Server

March 8, 2016 by Ahmad Yaseen

As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.

Using and troubleshooting SQL Server IntelliSense – For SQL Server 2012 or higher

February 29, 2016 by Minette Steynberg

When I first heard that Microsoft was going to introduce IntelliSense for SQL Server I could hardly contain my excitement. Much to my disappointment the initial version of IntelliSense left a lot to be desired. From issues like just not popping up at all, to not working on remote servers and sometimes picking incorrect entries could make it a bit tedious to use and for those of us who started out on SQL Server before the advent of IntelliSense it was easier just to revert back to good old fashioned typing from memory. I am happy to report however, that there has been great strides in IntelliSense to such an extent that if I suddenly had to live without it, I would be really really sad and my productivity would probably be affected somewhat as well. Read more »

Azure SQL Database vs SQL Server on Azure Virtual Machines

February 22, 2016 by Daniel Calbimonte

Introduction

There are two main options in Azure to handle SQL databases:

Azure SQL Database.
SQL Server on Azure VM.

In this chapter, we will talk about the advantages and disadvantages of each option.

SQL Server pivoting on non-numeric data types

February 19, 2016 by Sifiso Ndlovu

Introduction

In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.

How to configure SQL Server mirroring on a TDE encrypted database

February 19, 2016 by Ahmad Yaseen

Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.

SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.

The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.

A DBAs Introduction to Mercurial – Branching and merging

February 19, 2016 by Joshua Feierman

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.

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:

Insight into the SQL Server buffer cache

February 18, 2016 by Ed Pollack

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

How to connect to a remote SQL Server

February 18, 2016 by Marko Zivkovic

In this article, we will explain step by step how to connect remotely to a SQL Server Express instance. Remote access is the ability to get access to a SQL Server from a remote distance in order to manipulate data which are located on that SQL Server.

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.