Category: High Availability
-
Endpoint
Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number. An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network. Creation of an end point:- Create endpoint <endpoint name> State=started/stopped/disabled…
//
-
SQL Server Browser
Start and Stop SQL Server Browser Service It’s possible to start/stop the service using SQL Server Configuration Manager or Services.msc. If we stop the SQL Server browser services then we will not connect through named instance at SSMS by manually. Now see if we stop the browser services then we will not connect with named…
//
-
Important DMVs for Database Mirroring…
Below are few important DMVs for Database mirroring…. select * from sys.dm_db_mirroring_connectionsselect connection_id,* from sys.dm_exec_connectionsselect * from sys.database_mirroringselect * from sys.database_mirroring_endpointsselect * from sys.database_mirroring_witnesses
//
-
Cluster node was removed from the active failover cluster membership
Below was the cluster setup where the issue came… Error messages which logged in Cluster error log… Cluster node ‘ABCD-xyz05’ was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster.…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL PostgreSQL SQL Server
Mirroring Witness getting disappear from Mirroring Setup…
Recently we have faced one issue where DB mirroring witness getting disappear from mirroring setup. This is happening once Principal get down or SQL services restarts. In the case, we found DB fail-over is also is not working. Once the Principal server come online the DB start serving role as Principal DB and get in…
//
-
Azure Azure SQL Database DBA Worries Continued High Availability Installation and Configuration SQL Server
Cloud Witness in Azure
Cloud Witness is a type of Fail-over Cluster quorum witness that uses Microsoft Azure to provide a vote on cluster quorum. There are significant benefits which this approach: Problem with traditional cluster setup Lets consider a typical cluster setup which is shown in below diagram. Here we have file share witness in primary DC and unfortunately…
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
//
-
Step by Step implementing Log Shipping
Log Shipping enables you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. ADVANTAGES OF LOG SHIPPING- DISADVANTAGES OF LOG SHIPPING- TYPES OF LOG SHIPPING –…
//
-
Step by step implementation of SQL Server Always on group
Hey guys, In this blog I am going to explain you Step by Step implementation on SQL Server Always on Availability Group Always on Availability group – The Always On availability groups feature is a high availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012 (11.x), Always on availability groups maximizes…
//
-
Azure Azure SQL Database Azure SQL Database High Availability L1 DBA Monitoring pg_ High Availability PostgreSQL SQL Server
New features of SQL Server 2022
Hello guys, hope you are doing well. In this blog I’m going to discuss about the “New features of SQL Server 2022” The release of the SQL Server 2022 is much anticipated by the audience since the announcement of it being in the works on November 2nd, 2021. Although the complete details of this brand-new version will…
//
-
AOG on Azure Blob with a different region SQL Server instance
Hello there, everyone! I hope everything is going well for you. Today, I’ll show you AOG on Azure Blob with different region SQL Server Instance. Ø Created SQL disk layout and formatted with 64K- · Disk E: SQL Binaries · Disk T: TempDB · Disk R: User Databases · Disk L: User DB transaction logs Ø SQL Server Memory Configuration as…
//
-
AAOG Log Sync Analysis
SELECT r.replica_server_name AS Replica, DB_name(rs.database_id) ‘database_name’, rs.is_primary_replica IsPrimary, rs.last_received_lsn, rs.last_hardened_lsn, rs.last_redone_lsn, rs.end_of_log_lsn, rs.last_commit_lsn FROM sys.availability_replicas r INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id ORDER BY replica; select log_reuse_wait_desc from sys.databases where name like ‘DB_Name’ SELECT ar.replica_server_name as Replica, adc.database_name, drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, drs.synchronization_health_desc, drs.recovery_lsn FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id ORDER BY Replica; Secondary replica is lagging behind the Primary replica SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, db_name(dr_state.database_id) AS database_name, is_ag_replica_local = CASE WHEN ar_state.is_local = 1 THEN N’LOCAL’ ELSE ‘REMOTE’…
//