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 →

Calculating the number of weekdays between two dates

2016-05-152016-05-13 / Daniel Hutmacher / 6 Comments

I wish the DATEDIFF() function would count the number of working days (mondays through fridays) between two dates for me, but until that happens, I’ve had to roll my own scalar function. I tried to think of a smart way involving perhaps a modulus calculation, but I quickly succumbed to a more down-to-earth approach.

Continue reading →

Detaching a database also alters file permissions

2016-05-112016-05-10 / Daniel Hutmacher / 4 Comments

Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).

But when you try to attach the database, you might get something like

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf".
Operating system error 5: "5(Access is denied.)".

The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.

Continue reading →

Joining two SCD2 tables

2014-11-302014-11-28 / Daniel Hutmacher / 8 Comments

A number of OLTP systems store dimension data in SCD2-like tables in order to retain all the revisions whenever the dimension information changes. In certain situations, you may come across a need to join two or more SCD tables, while keeping all the versions information intact. Sound tricky? Not really.

Continue reading →

The SQL Server security model, part 4: execution context

2014-08-102014-08-04 / Daniel Hutmacher / 6 Comments

This is the fourth installment in this series on SQL Server security. Today, we’re going to take a closer look at some key security concepts concerning object ownership and ownership chaining as well as execution context and impersonation in SQL Server. This could get technical.

Continue reading →

The SQL Server security model, part 3: permissions

2014-08-032017-09-26 / Daniel Hutmacher / 4 Comments

This is the third installment in a series on assigning SQL Server permissions. In the previous posts, we’ve looked at security principals (the users and groups that carry the permissions) as well as the securables (the objects you want to control access to). Building on this understanding, we’re getting ready to look at the fabric that connects principals with securables: Permissions.

Continue reading →

The SQL Server security model, part 2: securables

2014-07-272014-08-04 / Daniel Hutmacher / 6 Comments

Continuing on last week’s post on security principals, this week’s installment in the series on SQL Server security takes a look at securables.

Continue reading →

The SQL Server security model, part 1: principals

2014-07-202014-07-28 / Daniel Hutmacher / 5 Comments

There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.

In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.

Continue reading →

Late-arriving dimension members

2014-05-042014-04-01 / Daniel Hutmacher / Leave a comment

In datawarehousing, a common scenario you may have encountered is the phenomenon of late-arriving dimension members. Imagine a situation where you get dimension members and fact data from two entirely different data sources, which may or may not provide data at the same time. Some fact data will probably be ready to load before its corresponding dimension member(s) arrive. How do you tag your fact data, and what consequence does that have for the integrity of your data warehouse?

Continue reading →

Efficient data, part 6: Versioning changes

2013-12-222013-12-05 / Daniel Hutmacher / Leave a comment

This installment in the series on efficient data is on versioning changes in a table. The article is a re-post of a post I wrote in september on compressing slowly changing dimensions, although the concept does not only apply to dimensions – it can be used pretty much on any data that changes over time.

The idea is to “compress” a versioned table, so instead of just adding a date column for each version, you can compress multiple, sequential versions into a single row with a “from” date and a “to” date. This can significantly compress the size of the table.

Continue reading →