Recommendations for Index Maintenance with AlwaysOn Availability Groups

  SYMPTOMS Consider the following scenario The database is part of AlwaysOn Availability Groups You run long and log-intensive transactions like Index maintenance/rebuilds You observe one or more of the following symptoms: Poor performing DML operations in availability databases on the primary replica if synchronous secondary replicas are present. Huge log send queue in the…


SQL Server manages Preferred and Possible Owner Properties for AlwaysOn Availability Group/Role

As a clustered resource, the availability group clustered resource/role have configurable cluster properties, like possible owners and preferred owners. SQL Server actively manages these resource properties. These properties are set automatically by SQL Server and should not be modified using Failover Cluster Manager. SQL Server automatically manages an availability group’s Possible and Preferred Owner properties The current…


How to map logins or use contained SQL database user to connect to other replicas and map to availability databases

Assume a working AlwaysOn environment with an availability group. Suppose this availability group is failed over to another replica. When the application re-connects to the (new primary) replica with the same SQL authenticated user, using instance name or using listener, there may be login error like below. This error is mentioned below for reference: Error in SQLCMD…