• AlwaysON,  DBA

    AlwaysON SQL server database blocking SSISDB Active Operations

    AlwaysON SQL server database blocking I had two blocking case on one of my alwaysON databases. Backup is third party tool and it got blocked by each other sessions. SSIS package deployed in SSIS folder and it run long and had blocking as well.   The first one, tried to kill the session ID, it went in to rollback state and it took more than half day, no response. Fix: Just suspend the database in the primary AG this will remove the killed SPID. The second one, we can look into the Active Operations of packages. Integration service catalog – right click SSISDB – Active Operations  

  • AlwaysON,  DBA

    Multi subnet sql alwayon cluster not connecting to sql database when application or alwayon failover

    Multi subnet SQL alwayon cluster not connecting to SQL database when application server restarts or alwayon failover. In multi subnet we will have more than two ips, the active IP will be online and other ips will be offline. https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334 This Microsoft link will have more information. I just used this to give a workaround for one of our application. The application has MultiSubnetFailover=True. Open powershell as administrator. Run the following and pass the resource name. Get the resource name, it could be AG group name_Listener name – Get-ClusterResource Check the ‘RegisterAllProvidersIP’ value, if it is 1 change to 0 Get-ClusterResource “Cluster Resource Name” | Get-ClusterParameter   Change the ‘RegisterAllProvidersIP’…

  • AlwaysON

    None of the IP addresses configured for the availability group listener can be hosted by the server Either configure a public cluster network 19456

    Requirement: Remove a node from one of availability alwayson group and added it on another group. It was a multi-subnet and the node is a DR node on a different subnet. You have to remove the IP from the group in the windows cluster go to run — cluadmin.msc — click roles — click the group –> click on server name expand it — remove the IP of the node it could be offline. Once deleted add it on the other group by the listener or from the cluster. Background: It is a three node cluster two in same data center and third node is in DR data center. TITLE:…

  • AlwaysON,  SQL party

    AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday

    This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar.     Here is my bug report: https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active. It happen one of my production database, when I tried to remove the database from alwaysON and other important databases went recovery pending and inaccessible. I have no idea what happened to all other databases, then I came to know it is a bug.   ” The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state,…