Always on troubleshoot

 Scenarios where the failover not happening in Always on.

1. Port number is not allowed 1433,5022 in firewall settings.

2. Event viewer no erros found.

3. Cluadmin- Check preffered nodes in node properties.

4. check if connect permission is not enabled  for service account.

5. check alteravailability group and serverstate permission for NTAuthority/system account  on both the nodes.  (why Ntauthority/system- coz healthcheck is authorised to this account)

Failover disconnected .

1. Check Endpoint algorithm on both the nodes which must be compatible i.e AES from 2016 version or rc4 from 2014 version.

2. Check nodes are up in cluadmin.

Logins not working to connect SQL Instance.

Connect SQL SERVER instance when there is no logins working.

Sometimes we may miss password or logins not working to connect sql server in this case follow the below steps to connect the instance. 

1. Stop the instance.

2. Open CMD in Adminstator mode- Root to SQL Server bin folder  with -m which will start the sql server in single user mode.

3. Open another CMD in adminstrator mode- Run the script to set the new login or create login .

4. Exit  both CMD and start the instance.   

LOG SHIPPING Interview QA

Log shipping can be configured in multiple secondary databases.
Log shipping can be configured in diff domain or in diff workgroups.
There are two modes in Logshipping
 Standby/Readonly mode : we can access the database for read only operations
Norecovery mode : restoring mode where we cannot access the database

For backup folder , Need Read/write permissions for Sql agent service account from secondary server.

Monitoring Log shipping :
Monitoring log shipping can be done through
1. Error logs- EXEC xp_readerrorlog 0,1,"Shipping",Null
2. System Stored procedure--> sp_help_log_shipping_monitor, sp_help_log_shipping_monitor_primary, sp_help_log_shipping_monitor_secondary
3. SSMS Report- Management Studio > Reports > Standard Reports > Transaction Log Shipping Status.
4.Querying MSDB database-->
SELECT *  FROM [msdb].[dbo].[log_shipping_monitor_error_detail] WHERE [message] like '%Operating system error%'
5. Application/Event Viewer log.

How to failover in the log shipping 

 Steps to failover in the Log shipping.

1. Disable the Log shipping jobs on both Primary and secondary server.

Primary server :    

    Backup Job

Secondary server 

Copy Job

Restore backup job

2. Backup the database in the primary server with tail log backup  with norecovery.

This can be achieved via backup Script with NO_truncate cmd or in the GUI -backup-T log - Tail log backup option.  With no recovery the db will be moved to recovery state.

3. Manually copy the tail log backup to secondary server .

4. Restore the tail log backup with recovery 

The secondary DB will be restored with recovery.

5. Migrate the logins (orphaned) to server via script or manually for the missing logins.

Run the login script  and sp_helprevlogin in primary server  which will script out the login command and exec these scripts in secondary server .     The user will be migrated once the db is restored in secondary. 

  Courtesy : https://www.youtube.com/watch?v=-1M_Xdrq1SY