[7/9/2023, 12:17 AM] Preethi: [Link]

in/windows-cluster-interview-questions-and-
answers/

[7/9/2023, 5:11 AM] Preethi: Appearing for interviews and don't know about Always On errors don't
worry. Just have this 10 cases handy with you.

Below are the Scenarios what I have shown during my training

1) Always on AG didn't get failed over to secondary replica due to missing permissions for NT Authority\
system

Reason: Cluster Health checks will be carried by that account.

2) Secondary Replica got disconnected from Primary

Reason: Due to incompatible Endpoint Algorithms

3) Secondary Replica got disconnected from Primary (This Appears only on Azure)

Reason: I disabled the network adapter which made the secondary replica to go down.

4) Secondary Replica got disconnected from Primary (This Appears only on Azure)

Reason: I blocked the port 5022 from the Network Security Group

Watch out here: [Link]

5) Primary and secondary databases went out of sync all of a sudden

Reason: The replicas are operating in Async mode and I suspended the data movement from Primary
to secondary and then initiated the failover.
6) Always on Availability group goes in to failed state and it will try to come online however it will never
happen(this happens only on SQL Server 2022)

Reason: Even though secondary replica SQL Services are up and running we are not able to connect
because force strict Encryption is enabled and it is using TDS 8.0 protocol

Watch out here : [Link]

7) Even though we are able to reach secondary replicas on the ports 1433,5022 the secondary replica is
appearing as disconnected inside SSMS.

Reason: we don't have permissions on End points as the services are running with Local system
account. We need to add computer accounts of node2 on node1 and node1's account on node2

8) The database is not getting synchronized with primary replica

Reason: There are certain steps that we need to perform while adding TDE database to Always ON if
you don't follow them you will get that case

Watch out here: [Link]

9) Why the Always ON Availability groups is not getting failed over when there are no errors at cluster
level and no errors with Always ON Dashboard and no errors inside any of the error logs?

Reason: Force Protocol Encryption" configuration is selected for the client protocols on secondary
replica (target primary) though the replica isn't configured for encryption

10)Why I am able to connect to the always on listener only from Primary replica?
Reason: You need Load Balancer in place for the connectivity to work as expected from secondary
replicas

Part-1: [Link]

Part-2 : [Link]

[7/9/2023, 5:15 AM] Preethi: [Link]

availability-group/

[7/9/2023, 5:15 AM] Preethi: [Link]

groups/windows/configure-read-scale-availability-groups?view=sql-server-ver16

[7/9/2023, 5:15 AM] Preethi: [Link]

for-dbas-in-the-next-5-10-years-how-to-prepare/?amp

[7/9/2023, 5:15 AM] Preethi: SQL AZURE Tip 3: How to check deadlock information in the AZURE SQL
DB?

Solution: We can track the deadlocks using below query. Please execute below query in Master DB.

SELECT * FROM sys.event_log

WHERE event_type = 'deadlock';

Once you found the Deadlocks, you can further dig and find in-detail information on each deadlock using
below query.

Need to run below query in Master DB only.

WITH CTE AS (

SELECT CAST(event_data AS XML) AS [target_data_XML]

FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)

SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,

target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]',
'nvarchar(100)') AS db_name

FROM CTE

--where target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2')>='2020-03-01'

--and target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') like '%Employee%'

order by Timestamp desc

[7/9/2023, 5:15 AM] Preethi: You can copy and save the deadlock_xml column data in notepad file and
same with extension of .xdl (example [Link]) and open the saved file in the Management Studio.
You can see the deadlock information in the graph(s).

[7/9/2023, 5:15 AM] Preethi: Note: Like on premise, we don’t need to setup any trace flag in azure sql
db. Even you cannot enable any trace flags here

[7/9/2023, 5:15 AM] Preethi: Have you ever seen Always ON Listener with out IP Address when you
open that up in SSMS. Don't get confused and wanted to learn how it is below is the link.

[Link]

Did you wonder why there is no concept of Load balancer in on-premises but on Azure Virtual machines

watch out for [Link]

How about Distributed Availability Groups : [Link]

How about Contained Availability Groups (New Feature from SQL Server 2022) : [Link]

Last but not least a real time troubleshooting Always ON Play list Series :
[Link]

[7/9/2023, 5:15 AM] Preethi: How to Handle Large tables in RDBMS(SQL Server, Oracle, Postgres, MySql)

Watch my practical experience here.

[Link]

#Purging #archiving #partitioning

[7/9/2023, 5:15 AM] Preethi: [Link]

[8/9/2023, 1:10 PM] Preethi: [Link]

[10/9/2023, 6:56 PM] Preethi: [Link]

[11/9/2023, 11:06 AM] Preethi: [Link]

for-testers-2/

[16/9/2023, 9:27 AM] Preethi: Interview Questions

How to optimise Memory utilisation high

Tempdb contention

Basic availablity group

Copy only Backup full concept

Missing indexes how to find and how to troubleshoot.

Multi subnet Always on

Can we take backup of the resource database.

What are the ways to get a SQL server version.

Infinite:

Recently solved P1

About split backup

How to get the query plan

Can we migrate 2008R2 to 2017

Recently created any SOP document

What all the db team can do and cannot do/should do and should not do .

Mphasis:

What will happen if we set the database to Emergency.

What are the steps/possible causes for secondary to suspect/AG resolving.

Can we create a user without creating a login.

What are the causes if db go to suspect state.

What are the frequency of incidents./what type of incidents will get mostly.

SQL Installation is windows team scope/db team scope.

Learn the DMV frequently used queries

GAVS:

Why choosing Side by side instead Inplace even if Side by side using the same VM name as old name

How to assign the dynamic port for the server

How to connect to the dynamic port SQL server syntax for that.

What happens if the Named Pipes protocol is disabled.

In the connection tab what has to be enabled to connect to SQL server from client machine or any other
server.

Provide permission to the user to grant the access to them to grant another user to access certain
stored procedures.

Configuration of integration services

Timeout default value,why that needs to be set to Zero

SQL server memory configuration,how to allocate it for three instances if available for [Link] will
you determine how much memory to [Link] you use DMV command to get memory
recommendations.

DMV for job failure.

How to get the job failure notification apart from email settings.

United Techno:

How will you compare the logins in SQL server from Active directory.

How will you fetch the logins with status

FIS:

Query writing - Basic SQL queries

Failover in different network like DRA what are the things you check as [Link] will you confirm that
it failback successfully.

When select is running a lot of time ,how to troubleshoot it.

How to perform Log shipping in Azure.

[16/9/2023, 11:13 AM] Preethi: [Link]

contention

[16/9/2023, 11:38 AM] Preethi: Distributed availability group and basic availability group

[16/9/2023, 12:40 PM] Preethi: SQL Server AlwaysOn Multi-Subnet AG

[Link]
utm_source=share&utm_medium=member_android&utm_campaign=share_via

[3/10/2023, 7:30 AM] Preethi: [Link]

activity-7114255154710818816-DUGr?utm_source=share&utm_medium=member_android

[8/11/2023, 11:30 AM] Preethi: Page splits

[8/11/2023, 11:31 AM] Preethi: Joins cardinality histogram allocation map

[8/11/2023, 11:31 AM] Preethi: Partition statistics timeline in restore process marks DOP and ctp
[8/11/2023, 11:32 AM] Preethi: RPO and RTO

[8/11/2023, 11:32 AM] Preethi: New futures in all the SQL versions

[8/11/2023, 11:32 AM] Preethi: DM vs

[8/11/2023, 11:33 AM] Preethi: If we have 4 notes in always on and then after out of them two of them
are synchronous and into of them of non synchronous while synchronous sending the transactions to
the other synchronous but at the situation that transactions are getting stop and then its not moving to
the primary server from the secondary level so what will happen to the primary server and what will
happen to the secondary server and what is the parameter used in this

[8/11/2023, 11:33 AM] Preethi: Physical and logical files in index fragmentation

[8/11/2023, 11:33 AM] Preethi: Performance counter

[8/11/2023, 11:33 AM] Preethi: If the temp drive is totally down then how can we recover the

[8/11/2023, 11:34 AM] Preethi: Database upgradation and migration

[8/11/2023, 11:42 AM] Preethi: Parameter sniffing

[8/11/2023, 11:42 AM] Preethi: If you are upgrading the database what are the prerequisites as a
database team we need to check and how we are doing the upgradation process like if database server
is 2005 then how can we check if the database server is 2005 to the new upgradation server with the
compatible

[8/11/2023, 11:44 AM] Preethi: We have one parameter while upgrading the database to check the
compatibility what is it parameter

[8/11/2023, 12:22 PM] Preethi: Powershell first use pandra command ennathu

[8/11/2023, 12:27 PM] Preethi: Auto parameterization

[8/11/2023, 12:59 PM] Preethi: Rebuild the system databases in sequel server

[8/11/2023, 1:00 PM] Preethi: If the model databases deleted whether the sequel server will act as a
normal server

[8/11/2023, 1:00 PM] Preethi: If user want to decommission the user data bases whether we can
become the model database as well

[8/11/2023, 1:02 PM] Preethi: Upgrade tuning advisor

[8/11/2023, 1:21 PM] Preethi: If you want to restore the TD enabled backup to another server in the
restore process so while we are saving the certificate and master key and in backup in one folder
whether that will create the security issue
[8/11/2023, 1:28 PM] Preethi: New backup features for sequels over 2019 and 2022

[8/11/2023, 1:53 PM] Preethi: I want to add a file in always on server and I want to add the firing both
primary and secondary I just a kind able to add in primary saver but not able to add in 2nd December
because the drive is not present in the secondary the common drives so what can I do now

[9/11/2023, 10:20 PM] Preethi: We have a sequel server cluster instance that as business required we
would like to install your New database which is not critical and fail over not required in case of any
issues so can we keep this database files on local disc instead of cluster disc in sequel server 2016

[9/11/2023, 11:41 PM] Preethi: If partition enabled in table level in SQL server then how can we export
only one partition in table to the next

[10/11/2023, 8:16 PM] Preethi: If lock file is corrupted then how can we recover the database

[10/11/2023, 8:16 PM] Preethi: Database upgradation and migration

[10/11/2023, 8:17 PM] Preethi: Database file movement for master and other data basis

[10/11/2023, 8:17 PM] Preethi: How to delete a secondary file and what is the process technical process

[10/11/2023, 8:18 PM] Preethi: In always on how can we migrate the logins to secondary server

[11/11/2023, 5:14 PM] Preethi: Definition of master msdb mtb model resource database distribution
database

[11/11/2023, 5:15 PM] Preethi: Restore of master msdb model 10db resource database

[11/11/2023, 5:15 PM] Preethi: Rebuild of master msdbt MP DB model resource DB

[11/11/2023, 5:16 PM] Preethi: If master data basis corrupted and if model databases corrupted and if
tem databases corrupted and if the research data bases corrupted and if msdb data bases corrupted

[11/11/2023, 5:16 PM] Preethi: If primary file is character and if secondary file is corrupted and if log file
is corrupted

[11/11/2023, 5:16 PM] Preethi: How to delete a secondary file and how to delete a primary file and how
to delete a log file

[11/11/2023, 7:06 PM] Preethi: If ldf corrupted in how can we take the tail lock

[12/11/2023, 10:51 AM] Preethi: If data bases corrupted and if backup is also corrupted then how can
we recover the database

[12/11/2023, 11:05 AM] Preethi: How many synchronous and how many a synchronous can be kept in
server
[12/11/2023, 11:06 AM] Preethi: Okay if we keep seven synchronous and one a synchronous means why
we can't keep all the eight as synchronous

[13/11/2023, 12:44 PM] Preethi: [Link]

ugcPost-7127561060156420096-JshF?utm_source=share&utm_medium=member_android

[14/11/2023, 2:46 PM] Preethi: If databases online and if database is offline and if database is
emergency state and if database is in suspect state and if database is in restoring state

[14/11/2023, 2:55 PM] Preethi: If MDF and ldf files are corrupted then what can we do

[14/11/2023, 7:35 PM] Preethi: ROw over flow data allocation unit

[16/11/2023, 8:31 AM] Preethi: [Link]

files-to-another-drive/

[16/11/2023, 11:20 AM] Preethi: How many tempdb files can be created in temp database

[16/11/2023, 12:15 PM] Preethi: Background process of database backups in SQL server

[16/11/2023, 12:16 PM] Preethi: If you have full backup on Monday with minimum size and differential
backup with minimal size but on Tuesday suddenly differential backup size vary and it gets higher what
is the cost here

[16/11/2023, 12:17 PM] Preethi: If MSI file got missed then how can we do the patching

[16/11/2023, 12:18 PM] Preethi: If you have always on server and out of that only one database is in
suspects state whether fail over will happen or not

[16/11/2023, 12:19 PM] Preethi: How can we migrate the logins without queries

[16/11/2023, 12:19 PM] Preethi: Orphan user queries

[16/11/2023, 12:20 PM] Preethi: I create the login in SQL server and provided read access and he can
able to access the server but he can't able to access the database . What should be the resolution

[18/11/2023, 5:43 PM] Preethi: Table value functions and system valued function

[20/11/2023, 8:04 PM] Preethi: If I have a good procedure and suddenly it's getting very slow and even
its involving with 20 tables so how can I resolve this using query tuning and make it faster

[20/11/2023, 8:19 PM] Preethi: Backup architecture in SQL server

[20/11/2023, 10:31 PM] Preethi: [Link]

server/
[20/11/2023, 10:36 PM] Preethi:
[Link]
optimization?view=sql-server-ver16

[21/11/2023, 5:21 AM] Preethi: Backup enhancement in 2016 2019 and 2022

[21/11/2023, 5:21 AM] Preethi: Always on improvements added in 2019 2022

[21/11/2023, 5:29 AM] Preethi: Filestream filegroup

[25/11/2023, 9:43 PM] Preethi: [Link]

activity-7128395819963084800-t4m6?utm_source=share&utm_medium=member_android

[13/12/2023, 1:21 PM] Preethi: [Link]

optimization

[15/12/2023, 10:38 PM] Preethi: [Link]

[Link]

[20/12/2023, 11:39 AM] Preethi: [Link]

[28/12/2023, 11:01 PM] Preethi: Filestream file group

[28/12/2023, 11:13 PM] Preethi: FqDN

[29/12/2023, 5:05 AM] Preethi: Table partitioning

[29/12/2023, 5:19 AM] Preethi: B-Tree structure

[02/01, 6:46 AM] Preethi: Replication

[06/01, 6:30 AM] Preethi: Check point

[06/01, 6:30 AM] Preethi: Dirty pages

[06/01, 6:30 AM] Preethi: Lazy writer

[06/01, 7:40 AM] Preethi: Utility for dumping

[06/01, 7:40 AM] Preethi: Utilities for exception handling

[06/01, 7:50 AM] Preethi: Cursor

[06/01, 8:15 AM] Preethi: Can we able to perform the log backup after the copy only full backup

[06/01, 8:16 AM] Preethi: If full backup happened on 1:00 p.m. and differential backup happened on
1:15 p.m. and lock backup up and down 1:30 p.m. and again differential backup happened on 1.45 p.m.
then how can we recover the database if the differential backup gets corrupted
[06/01, 8:17 AM] Preethi: Full backup happened on 1:00 p.m and happened on 1:15 p.m. but suddenly
won full backup happened on 1:30 p.m. and it got corrupted then how can I recover the database by
1:45 p.m.

[06/01, 8:18 AM] Preethi: Why we need to keep the minimum memory and maximum memory in SQL
server and what is the usage of the minimum memory in SQL server how can we utilise it

[06/01, 8:18 AM] Preethi: Can we able to change the minimum memory configuration in SQL server

[07/01, 9:11 AM] Preethi: In memory objects

[07/01, 10:15 AM] Preethi: [Link]

dbachallenges-activity-7149384280425525248-RuXO?
utm_source=share&utm_medium=member_android

[07/01, 7:20 PM] Preethi: Custom statistics in SQL server

[10/01, 2:57 AM] Preethi: Post in place migration if any disaster happened then how to rollback the
server

[11/01, 1:23 PM] Preethi: 1) How will you Migrate a DB from standalone instance which is on 2017
instance with 4 TB to 2019 instance in less than 1 minute of downtime?

Ans: Firstly DAG was introduced from SQL 2016 and on 2016 we need the databases to be part of Always
ON.

However from SQL Server 2017 even for standalone machines we can configure DAG. You don't need to
configure even windows failover cluster for this and also no need for availability group listener. As we
are using DAG it transfers the log records instantly and once we are ready with the cutover it is an
instant flip.

2) Can we configure Log shipping to/from Azure Managed instance?

Ans: We can't configure traditional log shipping....You may ask why because we need to place the
backups on to storage container so we should use backups to URL and while restoring

RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs to
be applied after the initial restore.

The option is to use Log replay Service which runs almost similar to the Log shipping in On-premises.
3) Is it possible to configure the replication where publisher is on 2019 instance and distributor on SQL
2017 and subscriber on 2022 instance?

Ans: No it's not possible to configure. Because a publisher server version can't be greater than to that of
Distributor.

Always remember our publisher server version should be <=Distributor server version.

4) How will you get to know if the latency in replication is happening from Publisher to Distributor or
from Distributor to Subscriber?

Ans: By Inserting tracker tokens we can figure out where the latency is as in transactional replication
apart from the snapshot agent which is used for initial sync. The other 2 agents Log reader and
distributor agent will run continuoulsy (can be tweaked based on our requirements) and we need to see
where the lag is. We need to figure out whether the problem is from Log reader agent to Distributor or
from Distributor to Subscriber.

5)If you got to know that replication lag is happening from publisher to Distributor what will be your
steps to overcome that?

Ans: Well internally there are 4 threads that runs when we configure replication.

Log Reader Agent Reader Thread – It scans the publisher database transaction log using sp_replcmds

Log Reader Agent writer Thread -Add the queued transactions to the Distribution database using
sp_MSadd_repl_commands

Distribution Agent Reader Thread – It finds the watermark from the table
Msreplication_subscriptions(on subscriber) and uses this information to retrieve pending commands
from the Distribution database. It basically uses the stored procedure sp_MSget_replcommands to
achieve it.
Distribution Agent Writer Thread – Writer thread uses the Batched RPC calls to write the information to
subscriber database.

when we have Log Reader Reader-Thread Latency then the possibe causes are: High VLFS, Slow Network
I/O, Slow Read I/O and large batch of replicated transaction.

when we have Log Reader Writer-Thread Latency then the possibe causes are: Blocking, High I/O, Slow
Write I/O and Slow Network I/O.

6) is it possible to Configure DAG on top of Always On configured with DNN?

Ans: for the moment it is not possible to configure DAG on top of Always configured with Distributed
Network Name

7)suppose I have 3 nodes using Node Majority configuration and because of some unforeseen issues say
2 nodes went down at the same time then what will happen to our Always ON Databases?

Ans: Microsoft has done various enhancements to the quorum right from windows 2003. With windows
2012 we have the concept of Dynamic quorum and with 2012 R2 we have Dynamic witness. However all
of these works only if the servers are turned off using graceful approach. if it is ungraceful shutdown like
what we have in the question then to prevent the split Brain situtation the windows cluster would
shutdown itself. To resolve this we need to make use of Force [Link] are the steps for single site
or Multi site

(i) Log in to the only node that is up and running and then shutdown the cluster service -->Net stop
clussvc

(ii) Bring up cluster service with Forcequorum-->Net start clussvc /forcequorum

(iii) Failover AG to the node which is up now (as other 2 nodes are down) with Allow Data Loss-->ALTER
AVAILABILITY GROUP AGTest FORCE_FAILOVER_ALLOW_DATA_LOSS;

(iv) This will bring AG up and once the other 2 servers are backup you need to resume the data
movement-->ALTER DATABASE [AGplaceHolder] SET HADR RESUME;
Note: Most importantly, be aware that log truncation will be delayed on a given primary database while
any of its secondary databases is suspended. Therefore, if the outage period is prolonged, consider
removing the failed replica from the AG to avoid running out of disk space due to log truncation delay.

8) Say we have sensitive information in one of the columns and you encrypted that column then can I
restore this DB with out exporting Private Keys?

Ans: Many people are getting confused between TDE and column level encryption....in case of TDE we
have the concept of private keys but in case of column level that is not so we can backup the database
and restore it happily. The problem comes only if you don't know the password for DMK and if the
clients are accessing the table that contains column level encryption.

9) Can we create multiple passwords for Database master key?

Ans: yes we can create multiple passwords unlike SMK where it is not possible.

10)Can we configure automatic seeding when we opt DAG?

Ans: Distributed availability groups were designed with automatic seeding to be the main method used
to initialize the primary replica on the second availability group.

For simplicity, the target SQL Server instance should match the version of the source SQL Server
instance. If you choose to upgrade during the migration process by using a higher version of SQL Server
on the target, then you will need to manually seed your database rather than relying on autoseeding

[13/01, 2:28 PM] Preethi: How can we rollback the migration or upgradation activity

[14/01, 1:25 PM] Preethi: Study DML commands

[14/01, 1:25 PM] Preethi: Study some queries regarding to Always on cluster mirroring log shopping and
replication

[14/01, 1:26 PM] Preethi: Some normal queries related to SQL

[14/01, 1:26 PM] Preethi: Some powershell comes related to clustering

[16/01, 8:10 PM] Preethi: Wait types

[17/01, 9:02 AM] Preethi: Sys.dm_os_memory_clerks

[17/01, 12:53 PM] Preethi: sys.dm_exec_sessions - Sessions in SQL Server

sys.dm_exec_connections - Connections to SQL Server

sys.dm_db_index_usage_stats - Seeks, scans, lookups per index

sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server

sys.dm_os_wait_stats - Returns information what resources SQL is waiting on

sys.dm_os_performance_counters - Returns performance monitor counters related to SQL Server

[17/01, 12:54 PM] Preethi: sys.dm_db_log_stats

sys.dm_db_log_info

sys.dm_db_stats_histogram

sys.dm_db_file_space_usage