Server not showing joined to AG but primary still shows it as joined.

Symptoms –

Server is still added to the availability group if you view the primary.

When browsing to the AlwaysOn High Availability from the affected server, it shows up blank

The affected server is still in the failover cluster

The affected server still has the checkbox for being added to the Availability Group (SSCM)

Troubleshooting –

Rebooted the affected server.

Failed over Cluster resource to simulate a restart, no change.

Steps to fix –

• Stop SQL Services on the affected node.
• From the Primary Replica, remove the affected node from the AlwaysOn Availability Group.
• Clear the checkbox for Enable AlwaysOn Availability Groups through SSCM, hit apply.
• Check the checkbox for Enable AlwaysOn Availability Groups through SSCM, hit apply.
• Start SQL services on the affected node.
• At this point, any databases that were in the availability group will need to have T-log restores, or, in my case, need to be dropped and manually re-seeded.
• Once all databases are seeded, verify the health of the AlwaysOn Availability Group via the dashboard.

While trying to install a service pack on one of my systems, I ran into a server that kept exiting setup stating it was missing files from C:\Windows\Installer.  This is unfortunate as some people will actually clear out the C:\Windows\Installer directory trying to free up space.  Luckily, Microsoft has a few tools for fixing this issue.  You can either use the FixMissingMSI tool, or the FindSQLInstall.vbs script.  I prefer the FixMissingMSI tool as sometimes getting the approval to run a .vbs script can be difficult to obtain.  Both tools can be found here:

https://support.microsoft.com/en-us/help/969052/how-to-restore-the-missing-windows-installer-cache-files-and-resolve-p

Update:  Several co-workers have asked me if there is anything that is safe to remove from this directory.  The answer is that it depends.  If you see a ton of .tmp files and they each 133MB and there’s a hundred of them, by all means delete them.  Typically .tmp files are caused by a failed download from the updates server.  Remove these without a problem, then download the actual update file that is being installed and install it manually.  This should clear up any issues like that.

I run into this from time to time and I figured I’d share my experience since I don’t see it many other places.  Let’s say you have a database, pretty high transaction, and for whatever reason, you need to shrink the transaction logs.  I’m not going to give you the spiel about how you shouldn’t do this in production, truth is, sometimes you need to.  You click through the GUI, tell it where to backup the transaction log, sit 10 minutes and when it completes you try to shrink it.  And nothing.

If this database is in an AlwaysOn High Availability group, you probably went to the AlwaysOn dashboard to check synchronization status.  Yep, all synchronized.  At this point, you spend 10-15 minutes wondering what’s going on, so you decide to try to backup the transaction log again, this time you only have to wait a minutes, try the shrink and the same thing happens.

What’s going on is that data is being written to the transaction log during your backup.  In this scenario, what I do is script out several backups and shrinks of the transaction log and run it at once.  You might need to run this a few times, but eventually you will be able to get the transaction log shrunk.  Note, that I used NUL backups here, definitely not recommended for production unless you remember to take a full backup afterwards and are willing to give up point-in-time recovery since the last successful t-log backup.

Brian just said so.  Cray huh.

When it comes to setting up a test environment, Microsoft has a free version of SQL Server that you can play with, SQL Server Express.  SQL Server express is a stripped down version of SQL Server that is meant for smaller, embedded applications.  There are some limitations to SQL express, including a maximum database size of 10 GB and being restricted to running on only a single, physical CPU.
Read More

Excellent article on how to add yourself to SQL roles if you don’t have access to SQL.  Note that this REQUIRES that your account is a member of the Local Administrators Group and your SQL Services will need to be restarted with an additional startup switch.  This WILL result in a 10-15 minute SQL outage.

http://sigkillit.com/2013/01/02/recover-sa-password-on-microsoft-sql-server/