Creating a SQL Login with SQL Server Availability Groups
The issue The logins used in the databases included in an AG must share the same SID if you have SQL Server AlwaysOn, availability groups. How come? Let's investigate further... A row is added to the [syslogins] table in the [master] system database each time a user is created. Now, when you map a user to a database and begin adding roles to that database for the specific user principal, a row with the pertinent data is added to the [sysusers] user database table. The [syslogins] and [sysusers] tables are connected based on SID information. Tada!. You can query sys.database_principals to get a list of database principals. As shown below, we have similar SID’s for a login [SQLAg2User] and database user [SQLAg2user]. Environment information The SQL Server Always On Availability Groups for these two nodes have the following characteristics for the purposes of this article: Primary Replica: SQLNode1\INST1 Secondary Replica: SQLNode2\INST1 Database: [MyNewDB] The Solution Enters T-SQL: You create the login on the primary replica node with the following script or something like that, that fits your needs: -- Connect to the PRIMARY REPLICA node of the AG USE [master] GO -- Create the login CREATE LOGIN user1 WITH PASSWORD = N'aStrongPasswordHere'; If everything runs successfully you run the next to get the SID of the user: USE [master] GO -- Get the SID of the user createdSELECT [sid], [name] FROM [syslogins] WHERE [name] = 'user1'; GO Now, that you have the SID, switch the connection the secondary replica and execute the following query: -- Connect to the **SECONDARY** REPLICA node of the AG USE [master] GO CREATE LOGIN user1 WITH PASSWORD = N'useTheSamePassword', sid=<copy-paste the SID from above>; Now, you are ready to go back to your Primary Replica that has the databases you want to grant the rights for the user and do so freely. The next time your database fails over to the other node it will work without a problem. Just a side note: why Windows Authentication mode does not have this SID problem? Well, if you think about it, the SID is governed by one source: the Windows Active Directory.
Availability Groups in Big Data Clusters. Two or more replicas in the same node?
Recently we have observed a couple of occurrences where 2 or more AG replicas in a Big Data Cluster deployment are being executed within the same node. In this post we will discuss this scenario, its cause and how can we avoid it.
