ALWAYS ON – GROWING LOG SEND QUEUE ON SYNCHRONOUS REPLICA
JBs Wiki
by Vivek Janakiraman
6M ago
-> Check this article for more details on high Log Send Queue on Asynchronous Replica. -> Log Send Queue shows the amount of log records in the log files of the primary database that has not yet been sent to the secondary replica. This value is sent to the secondary replica from the primary replica. Queue size does not include FILESTREAM files that are sent to a secondary. -> I faced this issue recently on a Synchronous replica. We are aware of the reason why the log send queue increased, that was due to an Alter Index job as part of weekly maintenance. But we wanted to understand if ..read more
Visit website
This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.
JBs Wiki
by Vivek Janakiraman
8M ago
-> Availability group secondary database was “Not Synchronizing”. -> Below is the view of Always On Dashboard, -> SQL Server Error log JBSAG1 2022-06-14 05:08:04.130 spid11s Always On: DebugTraceVarArgs AR ‘[HADR] [Primary] operation on replicas [CF81C5D4-0DBF-4573-AB9D-018423FB92B2]->[0E5B3832-3DDA-41CB-9730-0F119639B535], database [JBSAG12], remote endpoint [TCP://JBSAG2.JBSWIKI.com:5022], source operation [708829B5-E20D-4112-9D88-050797FE9D91]: Transitioning from [PENDING] to [CHECK_IF_SEEDING_NEEDED].’ 2022-06-14 05:08:04.130 spid44s A connection for availability group ‘JBAG1 ..read more
Visit website
Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. (Microsoft SQL Server, Error: 983)
JBs Wiki
by Vivek Janakiraman
8M ago
-> Application was failing with below error, TITLE: Connect to Database Engine Cannot connect to JBSAG1. ADDITIONAL INFORMATION: Unable to access availability database ‘JBSAG12’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. (Microsoft SQL Server, Error: 983) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-983-database-engine-error Microsoft ODBC Driver 17 for SQL Serve ..read more
Visit website
Auto close is enabled. Only databases with auto close disabled can be added to an availability group. To turn off auto close, set the Auto Close database property to False.
JBs Wiki
by Vivek Janakiraman
8M ago
-> I was trying to add a database onto existing Always On Availability group. The database that I wanted to add was grayed out, -> I was able to see the status for that database as “Does not meet prerequisites”. I got below message when I clicked on it, TITLE: Microsoft SQL Server Management Studio Auto close is enabled. Only databases with auto close disabled can be added to an availability group. To turn off auto close, set the Auto Close database property to False. This database lacks a full database backup. Before you can add this database to an availability group, you must perfor ..read more
Visit website
Create failed for Availability Group Listener ‘JBApplication’. The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.
JBs Wiki
by Vivek Janakiraman
9M ago
-> I tried creating an Always On Listener on an existing 4 node Always on Availability Group with below details, -> It failed with below error, TITLE: Microsoft SQL Server Management Studio Create failed for Availability Group Listener ‘JBApplication’. (Microsoft.SqlServer.Smo) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476 ADDITIONAL INFORMATION: An exceptio ..read more
Visit website
Disabling Deprecated feature sp_configure Remote access
JBs Wiki
by Vivek Janakiraman
11M ago
-> We had an audit on the database servers. An auditor advise us that “Remote access” feature should be disabled due to below reasons, [$] It is an obscure SQL Server to SQL Server communication feature that is deprecated feature. Please refer article for more details. -> It was decided that we should disable it on our SQL Server instances. We disabled it using below code, EXEC sp_configure 'remote access', 0 ; GO RECONFIGURE ; GO OR -> Right Click SQL Server Instance -> Properties -> Connections -> Uncheck “Allow remote connections to this server”, -> Please note ..read more
Visit website
Configure Azure Load Balancer – Part5
JBs Wiki
by Vivek Janakiraman
1y ago
Load Balancer Configuration – East US -> Search for “Load Balancers” on Azure portal, -> Basics Tab, -> “Frontend IP Configuration” tab. Click “Add a frontend IP configuration”. This one will be utilized for the creating windows cluster, -> Again, click “Add a frontend IP configuration”. This one will be utilized for Always On Listener, -> “Backend Pools” Tab, -> Click “Add” and select JBSAG1 and JBSAG2, -> “Inbound Rules” Tab, -> Click “Add a load balancing rule”, -> Click “Create new” under “Health probe”, -> Click “Add a load balancing rule”, -> Cl ..read more
Visit website
Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
JBs Wiki
by Vivek Janakiraman
1y ago
Active Directory Configuration – JBSDC -> Connect to Azure Virtual Machine JBSDC and open a PowerShell command window. -> Execute below command for installing Active Directory Domain Services, Install-WindowsFeature AD-Domain-Services -IncludeManagementTools -> Execute below command to configure an appropriate domain, Install-ADDSForest -DomainName JBSWIKI.com -> Virtual Server JBSDC will restart after the configuration. Login to Virtual Server JBSDC using your domain account, in my case I will use account jbswikijbswiki. Add Database Server JBSAG1 to Domain JBSWIKI.COM -> ..read more
Visit website
Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2
JBs Wiki
by Vivek Janakiraman
1y ago
Provision Appropriate Virtual Network and associated Subnet Virtual Network and Subnet – East US -> Search for “Virtual Network” in Azure portal and provision it as per below guidance, -> Click on ” Add subnet” on “IP Address” Tab and fill it up with appropriate details as shown in the screenshot, -> I will go with the default values for “Security” and “Tags” tab. -> Click Create and wait for the Virtual Network creation. Virtual Network and Subnet – West US -> Search for “Virtual Network” in Azure portal and provision it as per below guidance, -> I will go with the defa ..read more
Visit website
Deploy required Azure IaaS Virtual Machine – Part3
JBs Wiki
by Vivek Janakiraman
1y ago
Domain Controller – JBSDC -> Below Azure Virtual Machine will be provisioned to be used as a Domain Controller. I have allowed In-Bound port 3389 for RDP as this is a Lab setup. -> Disks Tab, -> Networking Tab. Make sure a Standard SKU is selected for Public IP Address. -> Tabs “Management”, “Advanced” and “Tags” are default in my case. Click “Create”, Database Server – JBSAG1 -> Search for “SQL Virtual Machine” on Azure portal. Please note that I am selecting a “Free SQL Server License: SQL 2019 Developer Edition” as this is a lab. In reality you will need to utilize an ent ..read more
Visit website

Follow JBs Wiki on Feedspot

Continue with Google
OR