I just had a heck of a time getting failover of a SQL 2012 Availability Group to work correctly with SharePoint 2010, so I thought I would share the outcome in case it helps anyone else. In short, I had my SQL 2012 Availability Group all set up and it appeared to be working correctly. I created a new content database on the primary node in the group, then backed it up and added it to the list of databases managed by the Availability Group (AG). So far, so good. I could hit the SharePoint site and it rendered just fine. However, after I failed over the AG to a new node, my SharePoint site would not come up any more. Instead, I would get a 403 Forbidden error instead of the page content. What was really vexing though is that I could open up SQL Server Manager and connect to my AG Listener just fine – I could query and get results for any of the tables in my content database that was now hosted on a different server.
After spending mucho time trying to figure this out, my friend and resident SQL nut job (in a good way!) Bryan P. pointed out that while the database account for my app pool account had moved over with my database, the SQL login did not. What I mean by that is if I look in SQL Manager at the content database and look at Security…Users I will see the SQL account for the app pool. However, if I look at the top level Security node for the server and then Logins, there is not a corresponding login account for the app pool account. So, I just created the login for the app pool account and then granted it rights to the content databases I was managing with the AG. After making that change, everything worked fine on the SharePoint side – I can now fail over to any node in the cluster and my SharePoint site continues to work just fine.
This sounds like a good fact to be aware of, especially as you are creating app pools with new accounts and want your content databases to be protected with an AG – make sure you add those new accounts to the logins for each SQL 2012 server that is participating in your AGs.