I am just getting my feet wet with always on availability groups, trying to learn as I go along.
A company I am doing some work for just deployed MSSQL 2017 SE to a 2-node always on installation, with the intent of migrating a number of databases currently on MSSQL 2008 & 2012 to it.
Today I migrated a small DB to the primary node (server1) by restoring a backup, created necessary logins etc., then restored same DB to secondary node(server2), leaving it in recovery mode, then created logins etc for the secondary.
I then created a new availability group for this database, choosing "join" in the wizard and all went ok.
On this server, only one AG has a listener (server3) attached to it, this was the first AG created with the first DB on the server.
The application (which connects to only this specific DB on the server) could connect okay and access the database by using the server3 listener name in the connect string, so I then did a manual fail over to the server2 node for this AG, so it became the primary. When I tried to connect via the DB user account with SSMS I had to delete the user from the database and recreate the login before I could connect to the now primary DB on server2, and I was able to open the database (via SSMS) and see tables, etc ok.
However when the developer tried to reconnect the app he was getting an error that the database was part of a availability group that did not have read access, he got this either connecting via the server3 listener node or direct to the server2 node. As said, I was able to connect fine via SSMS with the same user account, and view tables etc. on what was now the primary database on the secondary node.
I then manually failed over back to the server1 node, and before the developer could reconnect the application I had to delete the user from the database and recreate the login before he was able to connect (through the listener node)
Once the AG/DB on server1 was back as the primary AG, application could again connect ok.
My questions:
Also curious why I could connect okay with the sqlserver account via SSMS & view data, but the application could connect but not see any data.
Thanks in advance.
February 26, 2020 at 2:15 pm
I'm way out of my depth here (and I was only reading this because i'm doing the same thing.
The AG will take the database and the "User" but not the login ?
which might be a logical idea as to why you (maybe a sysadmin) can get to both, but the app struggles on node 2
Check the logins and make sure the SID maps up to the user on node 2... best guess
let me know how you get on because I'm going to hit this problem
MVDBA
February 26, 2020 at 6:48 pm
Let's start with the login/user issue...
Logins are created at the server level - and users are created in the database. SQL Server know that login ABC is the same as user ABC based on the SID. For example, when you create the login on server A it has a SID of 1234 - then you create the user in database A it also has the SID 1234.
Now - when you fail over to the other node, the login on server B has a SID of 5678 which does not recognize user ABC in database A because that user has a SID of 1234.
The way to avoid this issue is the make sure you create the logins on both servers using the same SID. Once you have created the login on server A - you can get the SID using:
Select *
From sys.server_principals sp
Where sp.name = 'your login name'
Now that you have the SID - you can create the login on server B:
CREATE LOGIN {login_name} WITH SID = {sid}You are going to want to also create that login with the correct password.
Once you have done that - the login on both servers have the same SID (ex: 1234) and the user in database A has the SID 1234. Now, when the database is failed over the login ties to the correct user in the database.
Note: if you set server level permissions for the login you also have to create those on the other server.
You don't actually have to remember all of this as someone was good enough to create a whole bunch of utilities in Powershell for you: https://dbatools.io/
Using the tools copy login functionality will create the login on the other server with the same SID.
Finally - this only affects SQL logins. Windows logins utilize the domain SID which does not change.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 26, 2020 at 6:56 pm
As for the application connection - this often falls to how that connection information is cached on the application server and how the connection string is built.
As for the listener - since you are using SE you can only have one database per AG (called a BAG for Basic Availability Group). Because of that you would need a separate listener for every BAG on that instance of SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 26, 2020 at 7:52 pm
Thanks for the helpful replies
I had time to do some more research on this and found out about the need to have the same SID on each server, and found a script to create a user script on the primary node that can be run on secondary nodes. I will check out your link though, if there's a better more automated way of doing it I'm all for that.
I am used to Oracle, where the database user accounts just stay with the database.
For the listener issue - am I correct in assuming then that in order to failover just one (of many) BAG's to the secondary, I need a separate listener for each BAG? Because the current way we have it just having a single listener for one BAG seems to work for all the others, as long as all of them are the same status (primary)
Thanks for the helpful replies
I had time to do some more research on this and found out about the need to have the same SID on each server, and found a script to create a user script on the primary node that can be run on secondary nodes. I will check out your link though, if there's a better more automated way of doing it I'm all for that.
The dbatools utility not only handles this type of issue - but has so many commands built in that you can easily automate almost anything. If you find you are doing the same thing over and over...it definitely is a tool that can help.
With that said - I wrote a script that is run in SQLCMD Mode that creates the login on the primary, adds the user to the specified databases with the appropriate group memberships - then creates a script and executes that script on the secondary to create the login on that system. This was written for a read-only secondary where I needed to be able to add a login/user that was available on the secondary.
I am used to Oracle, where the database user accounts just stay with the database.
For the listener issue - am I correct in assuming then that in order to failover just one (of many) BAG's to the secondary, I need a separate listener for each BAG? Because the current way we have it just having a single listener for one BAG seems to work for all the others, as long as all of them are the same status (primary)
Yes - the reason it works using a single listener is because the listener is redirecting to the active instance of SQL Server for that BAG. You can also access the instance directly - so using a single listener works because it acts like an alias to the instance. When the listener fails over for that BAG - it is now redirecting to the instance on the secondary and would no longer work for the other databases that are still on the primary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2020 at 8:47 am
This was removed by the editor as SPAM
February 27, 2020 at 9:01 am
In my best Britney "Whoops I did it again, I hit report, when I should of it quote", more coffee needed obviously
Sorry Mike.
While sp_change_users_login will work, it will need to be done on every failover. All it does is remap the USER_SID to the LOGIN_SID, then then it fails back the SIDS again don't match so your in a forever rinse and repeat cycle.
As mentioned dbatools is great for this, or look at things like the old sp_help_revlogin and make it policy that when creating a login at the server level, sp_help_revlogin is run and the output executed against the other replicas.
February 27, 2020 at 9:09 am
its ok
we've all done it.... mostly when i'm hungover 🙂 accidental deletes occur. 🙂
MVDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply