Availability Groups: How to sync logins between replicas

2016-10-112020-05-16 / Daniel Hutmacher / 76 Comments

AlwaysOn Availability Groups are a reasonably simple way to set up disaster recovery (DR) for your SQL Server environment, and with fairly little effort, you can get a bit of high availability (HA) from it as well. But there are a few gotchas, the most obvious of them being that Availability Groups only synchronize specific user-databases, not the entire server setup.

Things that are not included in AGs include logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings. You could sync these manually (as is often the case), but wouldn’t you just love to have an automated process do all this for you?

In this post, we’ll look at logins. For the sake of simplicity, I’ll assume that you have a primary replica with a single AG and any number of secondary replicas. The logic holds true if you have multiple AGs, it just gets trickier.

Continue reading →

Building a calendar dimension with public holidays

2014-07-132018-07-15 / Daniel Hutmacher / 7 Comments

Whenever you’re building a data warehouse or similar solution, you’ll probably want to have a “calendar dimension”, a table that contains all days in a range of years. A challenge with this type of table is getting all the public holidays right, which could be particularly important if your business depends on this, like financial markets or logistics.

Continue reading →

Calculating the date of easter sunday

2014-07-062018-07-15 / Daniel Hutmacher / 3 Comments

We’ve previously looked at how to calculate recurring public holidays. However, calculating the date of easter sunday is not as simple as you might think, because it involves calculations of lunar phases. This short post contains a T-SQL translation of the popularly used Meeus-Jones-Butcher formula.

Continue reading →

An indented representation of a parent-child hierarchy

2014-03-302018-07-15 / Daniel Hutmacher / 1 Comment

When you’re designing reports, they can often be based on hiearchies represented by “nodes” in a parent-child setup. To the end-user, the parent-child representation doesn’t provide very much readability, so you need to output this information in a human-readable form, for instance in a table where the names/titles are indented.

Continue reading →

Splitting a range string into a table

2014-03-162018-07-15 / Daniel Hutmacher / Leave a comment

This week’s post is a requirement that I see very regularly as a developer. You get a plaintext string containing one or more ranges. Each range is comma delimited, and the start and end values of the range are separated by a dash. The string could look something like this, for example: 100-120,121-499,510,520,790-999.

Wouldn’t it be practical if we could construct a table value function that returns one row for each range, with columns for the start and end of each range?

Continue reading →

Parsing parenthesis expressions

2014-03-092018-07-15 / Daniel Hutmacher / Leave a comment

Today’s post illustrates a pretty cool application of SQL Server’s built-in XML and XQuery support, used to parse parenthesis-delimited expressions. You may want to get your reading glasses on for this one..

Continue reading →

A function to calculate recurring dates

2013-07-072018-07-15 / Daniel Hutmacher / 39 Comments

When you’re using Microsoft Outlook, or pretty much any other personal information manager, you can create calendar appointments that are “recurring”, i.e. you can have them repeat at a defined frequency. This, however may not only apply to your project meeting appointments, but also to some database solution. I decided to give it a go at building a table value function that returns a list of dates, based on a given set of parameters.

Continue reading →

A function to parse ranges

2013-06-022018-07-15 / Daniel Hutmacher / 2 Comments

Here’s a quick function to parse a list of ranges (in a varchar variable) into a table of ranges. Might come in practical, for instance when parsing user arguments for a report.

Continue reading →

Ben-Gan on virtual auxiliary table of numbers

2013-05-072018-07-15 / Daniel Hutmacher / Leave a comment

Check out this interesting article from SQL Server superstar Itzik Ben-Gan on Virtual Auxiliary Table of Numbers.

Decrypting SQL objects

2013-03-242018-07-15 / Daniel Hutmacher / 4 Comments

Ever wished you could decrypt a database object in SQL Server? The good news is, you can, even in newer versions of SQL Server! This article will take you through the basics of how to decrypt a database object, and it will hopefully give you some deeper knowledge of how encrypted objects are stored in the database, and how to access them.

Continue reading →

sqlsunday.com

T-SQL tips and tricks, best practices and query plans from the field.

Scripts

Building a calendar dimension with public holidays

Calculating the date of easter sunday

An indented representation of a parent-child hierarchy

Splitting a range string into a table

Parsing parenthesis expressions

A function to calculate recurring dates

A function to parse ranges

Ben-Gan on virtual auxiliary table of numbers

Decrypting SQL objects