(SQL) Tip of the Day: Common Pitfall in SQL Azure Geo-DR

Today’s (SQL) Tip…

Now that we have released Active Geo-Replication for SQL Azure I have helped a few customers set it up. One pitfall that we have run into (and often isn’t noticed until you try to use the replica) is that there are additional security setup steps that need to be taken.

In order for the Active Geo-Replication active secondary to be usable as a read-only database or a viable database copy in a failover situation, the database must be maintained as an identical copy of the primary. This means that the active secondary objects must have all the same object properties as the primary database objects, including the property that is used for mapping server logins to database users, the login SID. You can find out how to properly setup your SIDs and accounts here.