SQL Database design

Timothy Smith

Some replication architecture errors and their resolutions

May 16, 2016 by

Background

From time to time, I’ve run into replication issues in inherited environments that I did not architect and some of these environments experienced errors in replication because of how it was constructed from the beginning. In this tip, we look at some of the basics in replication architecture and then at solving some of these problems. Some of the replication issues I’ve seen are caused by misunderstanding what is impossible and possible with replication.

Read more »
Sifiso W. Ndlovu

SQL Server lessons from a TFS installation

January 8, 2016 by

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.

Read more »
Ed Pollack

Data boundaries: Finding gaps, islands, and more

January 6, 2016 by

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.

Read more »
Ahmad Yaseen

Best practices after installing Microsoft SQL Server

December 15, 2015 by

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.

Read more »
Murilo Miranda

Understanding backups on AlwaysOn Availability Groups – Part 1

November 30, 2015 by

Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.

Read more »
Ed Pollack

Mapping schema and recursively managing data – Part 2

November 20, 2015 by

Introduction

In Part 1 of this article, we built a case for the benefits of documenting our schema via the use of a stored procedure, as well as the ways this information can be used for DML operations on a relational database. Below, we will continue where we left off by speeding up our SQL queries and then putting everything together so that we can demo a usable script that will hopefully make your life easier!

Read more »
Ed Pollack

Mapping schema and recursively managing data – Part 1

November 18, 2015 by

Introduction

In a typical OLTP environment, we want to maintain an acceptable level of data integrity.  The easiest way to do this is through the use of foreign keys, which ensure that the values for a given column will always match that of a primary key in another table.

Over time, as the number of tables, columns, and foreign keys increase, the structure of that database can become unwieldy.  A single table could easily link to thirty others, a table could have a parent-child relationship with itself, or a circular relationship could occur between a set of many tables.

Read more »
Murilo Miranda

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 3

October 9, 2015 by

In continuation to the Availability Groups series, here you have another curiosity coming to make your job easier and help you to provide a solution to your customers 🙂

This time we will be talking about listeners. Basically, we will be talking about the limitation of one listener per Availability Group. If you never tested / tried this, this is what happens when you already have a listener in the Availability Group, and try to create another one:

Read more »
Murilo Miranda

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 1

September 7, 2015 by

We all love Availability Groups! Since its introduction in the SQL Server 2012, some things changed. In the beginning it was seen as “just a replacement for the database mirroring”, but when we discovered that this would support readable secondary’s, the possibility of having a listener and get rid of the shared storage – even being based on a Failover Cluster – we saw that Availability Groups is a special feature. Read more »

Murilo Miranda

AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 3)

May 12, 2015 by

We have already configured our Availability Group, now we need to make it flexible and accessible. It’s time to check on how to create a listener in order to make a single access point for you AG!

In continuation to our previous article, we are going to pass to another phase of this setup, as we already have our database in sync and safe, or highly available, depending of the chosen mode/architecture.

Read more »
Murilo Miranda

AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 1)

April 24, 2015 by

In this article we are going to explore how to configure an Availability Group between a clustered instance and a standalone instance, showing, step-by-step, how to setup a possible Disaster Recovery environment.

Introduced on SQL Server 2012, the Availability Groups brought the expectation to be an improved version of the database mirroring, which will be discontinued soon. The AlwaysOn Availability Groups was improved on SQL Server 2014, giving the capability of have more replicas, better troubleshooting possibilities and improving its availability. Comparing the Availability Groups with the database mirroring, in a very high level, we gained the possibility of have a listener to dynamically redirect the connection to the current active instance and also the capability of distribute the read workload between readable replicas. However, only the primary replica is able to write.

Read more »
Murilo Miranda

SQL Server 2014 – Install a clustered instance – step-by-step (3/3)

March 12, 2015 by

This article is divided in three parts, if you started here, check the first and second part to find the previous steps.

Now we need to define if the instance will work in “Windows Authentication Mode” or “Mixed Mode”. I usually choose “Mixed Mode” because there’s always an application that don’t work with domain accounts. By choosing this option you need to specify the password for the famous “sa” login. Notice that you can change the authentication mode anytime! You also need to add accounts to receive the Sysadmin privilege, this is very important… Even more if you choose the “Windows Authentication Mode”.

Read more »
Page 1 of 212