SQL Server DBA Ultimate Interview Guide – 200+ Expert Questions & Answers | FreeLearning365

Q: What is FreeLearning365?

FreeLearning365 is a free learning platform offering daily tutorials on technology, programming, and certifications.

Question

1 What are the major new features in SQL Server 2025?

Answer 1

Answer: SQL Server 2025 introduces Azure Arc–enabled management, Ledger tables with improved history, S3‑compatible backup, persistent DOP & parameter sensitive plan optimization, contained availability groups, PolyBase REST API connector, snapshot backup, TDE with Azure Key Vault Managed HSM, and enhanced Query Store on secondaries.

Answer 2

Ledger tables provide cryptographic proof of data integrity. Every transaction is hashed and linked. Updatable ledger tables maintain a history table automatically. Verify with sys.sp_verify_database_ledger.

CREATE TABLE dbo.Accounts (AccountID INT PRIMARY KEY, Balance DECIMAL(18,2)) WITH (LEDGER = ON);

Answer 3

CREATE CREDENTIAL [s3://mybucket] WITH IDENTITY = 'S3 Access Key', SECRET = 'mysecret';
BACKUP DATABASE Sales TO URL = 's3://mybucket/sales.bak' WITH FORMAT, COMPRESSION;

Direct backup to any S3‑compatible object storage, including on‑prem MinIO.

Answer 4

Azure Arc extends Azure management to on‑prem SQL instances. You can inventory, monitor, enforce policies, and even deploy Azure SQL Managed Instance on Kubernetes. It unifies management across hybrid environments.

Answer 5

PSP automatically detects parameter values that cause plan quality regressions and creates multiple cached plans for different parameter ranges. Reduces reliance on OPTION (RECOMPILE).

Answer 6

DOP feedback now persists across restarts. The engine remembers the optimal DOP for a query based on runtime stats and adjusts it gradually, reducing CXPACKET waits.

Answer 7

New connectors for REST APIs (query JSON endpoints), Azure Cosmos DB, and improved Oracle/Teradata pushdown. Define external data sources pointing to HTTP endpoints and query with T‑SQL.

Answer 8

Contained AGs replicate server‑level objects (logins, Agent jobs, linked servers) alongside databases. Secondary replicas are fully configured, simplifying failover.

Answer 9

Synapse Link replicates tables to Azure Synapse Analytics in near real‑time using change feed, enabling analytics on operational data without ETL.

Answer 10

TempDB now supports automatic multi‑file configuration based on CPU cores during install. It also uses memory‑optimized metadata tables to reduce system table contention.

Answer 11

Enables instant backups using storage snapshots on Azure VMs or supported storage. BACKUP DATABASE ... WITH SNAPSHOT and near‑instant restores.

Answer 12

Deploy on Kubernetes with Azure Arc–enabled Managed Instance. ARM64 support, fully managed patching, backup, and HA. Official Docker images available.

Answer 13

Options: LEDGER_DIGEST_STORAGE, PARAMETER_SENSITIVE_PLAN_OPTIMIZATION, MEMORY_OPTIMIZED_TEMPDB_METADATA. They allow per‑database feature control.

Answer 14

Supports secure enclaves in containers, richer operations (LIKE, range) on encrypted columns, online key rotation without downtime.

Answer 15

SSMS 20.x shows estimated vs actual rows, spills, and wait stats directly in the plan viewer, making performance troubleshooting faster.

Answer 16

UTF‑8 collations reduce storage for English text by up to 50% compared to UTF‑16. Server‑level default UTF‑8 makes international support easier.

Answer 17

Arc‑enabled SQL Server can authenticate using Azure AD credentials, enabling unified identity and MFA without on‑prem domain controllers.

Answer 18

Query Store now available on readable secondaries, automatic time‑based cleanup, and better integration with Intelligent Query Processing for plan forcing history.

Answer 19

Leverage Azure Arc for management, S3 backup to Azure Blob, bidirectional SQL Managed Instance link, and PolyBase for cross‑querying.

Answer 20

Agent supports jobs in Kubernetes pods, Azure Automation runbooks, and Logic Apps integration. Managed identities for Azure resources.

Answer 21

Dedicated service accounts (least privilege).
Instant File Initialization enabled.
TempDB multiple files (1/CPU, max 8).
Separate drives for data, log, tempdb.
Max server memory 80‑90% of RAM.
Latest CU applied.

Answer 22

Setup.exe /ConfigurationFile=MyConfig.ini /Q /IACCEPTSQLSERVERLICENSETERMS

Configuration file includes features, accounts, paths.

Answer 23

Run DMA for breaking changes, test new CE (CE 2025), use Query Store to mitigate plan regressions, upgrade in test first.

Answer 24

sp_configure shows/changes server settings. New in 2025: automatic soft‑NUMA, backup checksum default, etc. Use RECONFIGURE.

Answer 25

Official Docker image mcr.microsoft.com/mssql/server:2025-latest. Kubernetes with Azure Arc data controller. Full parity with Windows features.

Answer 26

EXEC sp_configure 'max degree of parallelism', 8;
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

MAXDOP ≤ cores per NUMA node; CTFP >=50 to avoid trivial parallel plans.

Answer 27

EXEC sp_configure 'max server memory (MB)', 32768;

Leave 2‑4 GB for OS and other services.

Answer 28

Default uses port 1433; named uses dynamic port via SQL Browser. Named instance identified by Server\Instance.

Answer 29

Grant SQL service account "Perform volume maintenance tasks" security policy. Speeds data file growth.

Answer 30

Place on fast SSD, multiple equally‑sized data files, pre‑size to avoid autogrowth. Monitor with sys.dm_io_virtual_file_stats.

Answer 31

Change startup parameters for master (-d, -l). For model/msdb, use ALTER DATABASE MODIFY FILE, then move files offline.

Answer 32

Default TCP 1433. Change via SQL Server Configuration Manager > TCP/IP properties > IP Addresses tab. Restart service.

Answer 33

EXEC msdb.dbo.sysmail_add_profile_sp @profile_name='DBA_Profile';
EXEC msdb.dbo.sysmail_add_account_sp ...;

Answer 34

Use SQL Agent alerts: sp_add_alert @severity=17, add operators. Or Windows Event Forwarding.

Answer 35

Separate drives for data, log, tempdb. 64KB allocation unit size. RAID10 for data, RAID1 for log. Premium SSD on cloud VMs.

Answer 36

Use SqlServerDsc module with resources SqlSetup, SqlServerConfiguration for consistent, automated deployments.

Answer 37

Checks database integrity. Schedule weekly with Ola Hallengren's scripts. Use PHYSICAL_ONLY for large DBs.

Answer 38

Installation wizard can onboard directly to Azure Arc, enabling cloud management from the start. Provisions Azure extension and optional auto backup.

Answer 39

Use log shipping or Always On AG for rolling upgrade. Set up 2025 replica, sync, failover. Or Azure DMS for cloud targets.

Answer 40

Check @@VERSION, error log, verify all DBs online, run DBCC CHECKDB on critical DBs, test connectivity, run workload replay.

Answer 41

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '...';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE';
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE UserDB SET ENCRYPTION ON;

Can store cert in Azure Key Vault Managed HSM for HSM protection.

Answer 42

CREATE FUNCTION dbo.fn_RLS(@UserID INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS r WHERE @UserID = USER_ID();
CREATE SECURITY POLICY rls_policy ADD FILTER PREDICATE dbo.fn_RLS(UserID) ON dbo.Orders;

Answer 43

ALTER TABLE Customer ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

Data masked at presentation layer without changing stored values.

Answer 44

CREATE LOGIN [DOMAIN\user] FROM WINDOWS;
CREATE USER [user] FOR LOGIN [DOMAIN\user];
ALTER ROLE db_datareader ADD MEMBER [user];

Answer 45

Use ADD SIGNATURE to grant permissions to a stored procedure without direct user permissions. Enhances security by encapsulating logic.

Answer 46

Create column master key in Azure Key Vault, define column encryption keys, set encryption on column. Enclaves allow rich computations (LIKE, range).

Answer 47

CREATE SERVER AUDIT MyAudit TO FILE (FILEPATH = 'C:\Audit\');
CREATE DATABASE AUDIT SPECIFICATION ... FOR SERVER AUDIT MyAudit;
ALTER SERVER AUDIT MyAudit WITH (STATE = ON);

Answer 48

Create SQL logins with CHECK_POLICY = ON, which uses Windows password policies. Also enforce CHECK_EXPIRATION.

Answer 49

Always use parameterized queries with sp_executesql or stored procedures. Avoid string concatenation. Validate input length.

Answer 50

Windows Firewall or Azure NSG restricts access to port 1433. Allow only trusted IP ranges.

Answer 51

Enable managed identity on Azure VM/App Service, create user from external provider, grant permissions. No password management.

Answer 52

Ledger tables, Azure AD auth for on‑prem, BYOK for TDE in Managed HSM, advanced Always Encrypted enclave operations, and enhanced audit to S3.

Answer 53

Use ADD SENSITIVITY CLASSIFICATION to tag columns (e.g., PII, GDPR). Audit and track access with SQL Audit. Use Azure Purview for scanning.

Answer 54

Grant only necessary permissions. Use roles (db_datareader, custom roles). Avoid sysadmin for routine tasks.

Answer 55

Use encrypted backups with certificates. Restrict backup folder access. Store backups in Azure Blob with encryption.

Answer 56

Allows objects to access resources across databases based on ownership. Should be disabled unless carefully managed.

Answer 57

Register SPNs for SQL service account. Use SETSPN -A MSSQLSvc/server.domain.com. Ensure domain trusts.

Answer 58

Contained database users exist within the database and authenticate directly, making databases portable. No server login needed.

Answer 59

Install a trusted certificate on the SQL Server. Set Force Encryption to Yes in configuration manager. Clients use encrypted connections.

Answer 60

Use Server Audit Specification to track CREATE/ALTER LOGIN, changes to server roles, and database ownership changes.

Answer 61

AGs replicate databases via log transport. Up to 8 synchronous replicas, 32 secondaries total. Contained AGs replicate server objects. Read‑scale secondaries support Query Store.

Answer 62

Synchronous waits for log hardening on secondary before committing – zero data loss. Asynchronous commits immediately, potential for some data loss.

Answer 63

Basic AG (Standard edition) supports one database, one secondary. Configuration via wizard or T‑SQL. No read‑scale or multiple DBs.

Answer 64

Spans two separate AGs across different clusters or geographic locations. Useful for DR and migration across domains.

Answer 65

Use ALTER INDEX ... REBUILD WITH (ONLINE = ON). Consider MAXDOP to avoid overloading replica.

Answer 66

WSFC quorum ensures enough votes to keep cluster online. Dynamic quorum and file share witness are used to avoid split‑brain.

Answer 67

ALTER AVAILABILITY GROUP [MyAG] ADD LISTENER N'AGListener' (WITH IP ((...), PORT=1433));

Provides a single connection point.

Answer 68

Primary flushes log records to secondary; secondary hardens and acknowledges. Redo thread applies changes. Latency monitored via sys.dm_hadr_database_replica_states.

Answer 69

SELECT * FROM sys.dm_hadr_availability_replica_states;
SELECT * FROM sys.dm_hadr_database_replica_states;

Answer 70

Automatic failover requires synchronous commit and a configured partner. Manual requires DBA intervention.

Answer 71

Primary takes log backups, copies to secondary, restores with NORECOVERY or STANDBY. Simple, supports multiple secondaries.

Answer 72

Restore the last log with RECOVERY. Redirect applications. Point‑in‑time recovery possible.

Answer 73

RPO = max acceptable data loss (time). RTO = max acceptable downtime. Design HA/DR to meet both.

Answer 74

Shared storage, multiple nodes. Instance fails over to another node. Protects at server level. Requires WSFC.

Answer 75

Tempdb is not replicated; each replica has its own. Ensure identical tempdb configuration for performance consistency after failover.

Answer 76

Snapshots can be created on secondary for reporting. Requires synchronization. Not supported on primary.

Answer 77

Enterprise Edition (or Standard for Basic AG), WSFC, identical server collation, full recovery model, and a domain (unless workgroup cluster in 2025).

Answer 78

Configure cluster with cross‑subnet failover, use RegisterAllProvidersIP=0 for listeners, adjust HostRecordTTL.

Answer 79

Supports AG without WSFC, using Kubernetes or Pacemaker for orchestration. Simplifies deployment on Linux and containers.

Answer 80

Use ALTER AVAILABILITY GROUP [MyAG] FAILOVER; in a job triggered by health detection scripts (e.g., DBATools).

Answer 81

A replica is a participating instance; a secondary is a replica that hosts copies and can be read‑only if configured.

Answer 82

Perform planned manual failover in a maintenance window. Use DR drills to validate RPO/RTO, document steps.

Answer 83

Configure backup preferences: primary only, secondary only, or prefer secondary. Use sys.fn_hadr_backup_is_preferred_replica.

Answer 84

Allows readable secondaries without high availability. No cluster required, but no automatic failover. Good for offloading reporting.

Answer 85

Azure SQL DB uses built‑in HA with 99.995% SLA. Active geo‑replication for DR. SQL Managed Instance uses auto‑failover groups.

Answer 86

Check wait stats for SOS_SCHEDULER_YIELD. Use Query Store to find high‑CPU queries. Tune indexes, remove scalar UDFs, consider Resource Governor.

Answer 87

Actual execution plan with STATISTICS IO/TIME, Query Store, DMVs (sys.dm_exec_query_stats), Extended Events, and sp_whoisactive.

Answer 88

Look for clustered index scans (SCAN), key lookups, high estimated vs actual rows, thick arrows (data flow). Identify the most expensive operator.

Answer 89

Execution plan shows missing index hints. DMVs: sys.dm_db_missing_index_details. Always review before implementing.

Answer 90

Seek navigates B‑tree directly for selective predicates; scan reads entire leaf level. Seek usually more efficient.

Answer 91

EXEC sp_query_store_force_plan @query_id = 10, @plan_id = 50;

After verifying the plan is better.

Answer 92

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE '%SLEEP%' ORDER BY wait_time_ms DESC;

Focus on PAGEIOLATCH, WRITELOG, CXPACKET, LCK_*.

Answer 93

Check PAGELATCH_* waits on tempdb. Ensure multiple data files, trace flag 1118 (if needed), and memory‑optimized tempdb metadata in 2025.

Answer 94

Plan compiled for atypical parameter values. Fixes: OPTION (RECOMPILE), OPTIMIZE FOR UNKNOWN, local variables, or PSP in 2025.

Answer 95

CREATE EVENT SESSION [HighCPU] ON SERVER ADD EVENT sqlserver.rpc_completed (WHERE sqlserver.cpu_time > 5000000) ADD TARGET ring_buffer;

Answer 96

Limits CPU/memory per workload group. Classify connections by application, login, etc. Prevents a single query from consuming all resources.

Answer 97

SELECT TOP 10 total_logical_reads/execution_count AS avg_reads, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY avg_reads DESC;

Answer 98

Use Ola Hallengren scripts. Reorganize 5‑30% fragmentation, rebuild >30%. Update statistics after. Use online operations.

Answer 99

Use sp_who2, sys.dm_exec_requests with blocking_session_id. Deadlock graphs via Extended Events or trace flag 1222.

Answer 100

Memory‑optimized tables and natively compiled stored procedures for high‑speed OLTP. Requires separate filegroup. Reduces latch contention.

Answer 101

sys.dm_os_process_memory, sys.dm_os_memory_clerks. Page life expectancy (< 300s) indicates memory pressure.

Answer 102

Buffer pool caches data pages; plan cache stores execution plans. Flushing them causes recompilations and physical reads.

Answer 103

Provide a workload trace; DTA recommends indexes/statistics. Always evaluate impact, don't blindly apply.

Answer 104

Use SQL Server Profiler (deprecated) or Extended Events to capture RPC/SP statements. Replay with Distributed Replay or SQL Workload Tools.

Answer 105

Store data column‑wise, great for large aggregations (DW). Clustered columnstore offers high compression and batch mode processing.

Answer 106

EXEC sp_query_store_set_hints @query_id = 10, @value = N'OPTION (HASH JOIN)';

Override plan without code changes.

Answer 107

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id, null, null, 'LIMITED');

Rebuild/reorganize based on thresholds.

Answer 108

Generates a new plan for each execution, ideal for queries with highly variable parameters that cause bad parameter sniffing. Trade‑off: increased CPU for compilation.

Answer 109

Combine built‑in tools (Query Store, DMVs), third‑party (SolarWinds, Redgate), and custom scripts collecting to a central monitoring database with alerts.

Answer 110

Key counters: SQLServer:Buffer Manager\Page life expectancy, SQLServer:Memory Manager\Target Server Memory, Processor\% Processor Time, PhysicalDisk\Avg. Disk sec/Read.

Answer 111

Full: point‑in‑time recovery, all operations logged. Simple: no log backups, only full/differential. Bulk‑logged: minimally logs bulk ops. Use Full for production.

Answer 112

BACKUP DATABASE [MyDB] TO DISK = 'D:\Backups\MyDB_Full.bak' WITH COMPRESSION, CHECKSUM, INIT;

Answer 113

Full: entire database. Differential: changes since last full. Log: all transactions since last log backup. Combine for point‑in‑time recovery.

Answer 114

RESTORE DATABASE [MyDB] FROM DISK = 'Full.bak' WITH NORECOVERY;
RESTORE LOG [MyDB] FROM DISK = 'Log1.trn' WITH NORECOVERY;
RESTORE LOG [MyDB] FROM DISK = 'Log2.trn' WITH STOPAT = '2025-06-24 10:30:00', RECOVERY;

Answer 115

BACKUP DATABASE [MyDB] TO URL = 'https://myaccount.blob.core.windows.net/backups/MyDB.bak' WITH CREDENTIAL = 'AzureCred', COMPRESSION;

Answer 116

Splits backup across multiple files for parallelism and to overcome file size limits. TO DISK = 'file1,file2'.

Answer 117

RESTORE VERIFYONLY FROM DISK = 'MyDB.bak';

Use CHECKSUM during backup for additional protection.

Answer 118

Backup of the active transaction log after a disaster, before restore. Ensures minimal data loss. BACKUP LOG ... WITH NORECOVERY.

Answer 119

Use Ola Hallengren's maintenance scripts or SQL Agent jobs. Full weekly, differential daily, log every 15‑30 min. Cleanup old files.

Answer 120

Does not affect the log chain or differential base. Used for ad‑hoc backups without disrupting the regular schedule.

Answer 121

RESTORE DATABASE [NewDB] FROM DISK = 'backup.bak' WITH MOVE 'DataFile' TO 'D:\Data\NewDB.mdf', MOVE 'LogFile' TO 'E:\Log\NewDB.ldf';

Answer 122

BACKUP DATABASE [MyDB] TO VIRTUAL_DEVICE = 'AzureSnapshot' WITH SNAPSHOT, METADATA_ONLY;

Near‑instant restore.

Answer 123

Analysis: identifies log records. Redo: applies committed changes. Undo: rolls back uncommitted. Fast recovery in 2025 reduces undo time.

Answer 124

Ensure regular log backups in Full recovery. Monitor log size, avoid autogrow churn. Set appropriate size and growth increments.

Answer 125

Adds a checksum to each page in the backup, allowing verification during restore. Detects corruption. Use WITH CHECKSUM.

Answer 126

BACKUP DATABASE [MyDB] TO URL = 's3://mybucket/backup.bak' WITH CREDENTIAL = 'S3Cred', FORMAT, COMPRESSION;

Answer 127

CREATE CERTIFICATE BackupEncryptCert ...;
BACKUP DATABASE [MyDB] TO DISK = '...' WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert);

Answer 128

Media set is a collection of backup devices. Backup set contains data from a single backup operation. Families allow mirrored backups.

Answer 129

RESTORE DATABASE [MyDB] PAGE = '1:57' FROM DISK = 'log.trn' WITH NORECOVERY;

Useful for corrupted pages without full restore.

Answer 130

Store backups for required time, possibly in immutable storage (Azure Blob with legal hold). Use lifecycle management to auto‑delete.

Answer 131

Set of stored procedures for index optimize, database integrity check, and backup. Highly configurable and widely used. IndexOptimize, DatabaseIntegrityCheck, etc.

Answer 132

EXEC msdb.dbo.sp_add_job ...; EXEC msdb.dbo.sp_add_jobstep ...; EXEC msdb.dbo.sp_add_schedule ...;

Or use SSMS GUI.

Answer 133

Right‑click Maintenance Plans > New. Drag tasks (Back Up, Rebuild Index). Set schedules. Under the hood uses Integration Services.

Answer 134

T‑SQL runs SQL commands; PowerShell allows running scripts and accessing OS functions. PowerShell steps can use SQLPS module.

Answer 135

EXEC dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'REORGANIZE', @FragmentationHigh = 'REBUILD', @UpdateStatistics = 'ALL', @MaxDOP = 4, @Online = 1;

Answer 136

Enable auto update statistics (default). Use Ola's IndexOptimize with @UpdateStatistics = 'ALL'. Or custom script using sys.dm_db_stats_properties.

Answer 137

EXEC master.dbo.xp_delete_file 0, 'D:\Backups', 'bak', '2025-06-01T00:00:00';

Or use PowerShell step.

Answer 138

Commands: Backup-DbaDatabase, Restore-DbaDatabase, Test-DbaLastBackup. Automate tasks across many servers easily.

Answer 139

Read‑only point‑in‑time copy. Useful before large updates; can revert via restore from snapshot. Minimal space overhead.

Answer 140

Create new partition, switch oldest partition out to archive table, merge empty partition. Automate with stored procedure called by Agent.

Answer 141

Register multiple servers in SSMS > Central Management Servers. Run multi‑server queries and apply policies.

Answer 142

Define rules (e.g., naming conventions, recovery model) and evaluate against servers. Can prevent violations or just report.

Answer 143

Start-Process -FilePath "CU.exe" -ArgumentList "/quiet /IAcceptSQLServerLicenseTerms" -Wait

Answer 144

Create a job that restores the latest production backup onto a dev server, with REPLACE, and then masks/scrubs sensitive data.

Answer 145

In SQL Agent, configure notifications on job failure to email an operator. Or use msdb.dbo.sp_send_dbmail in the final step.

Answer 146

Hybrid Runbook Worker can run PowerShell scripts against on‑prem SQL. Integrate with Azure Monitor for alerts.

Answer 147

Agent supports Kubernetes jobs, Logic Apps integration, managed identities for Azure. Script task steps can use PowerShell 7.

Answer 148

Use DBCC CHECKDB with MAXDOP or run multiple checks on different databases simultaneously via multiple Agent jobs.

Answer 149

Archives current error log and starts a new one. Helps avoid large logs. Schedule weekly.

Answer 150

Invoke-DbaDiagnosticQuery -SqlInstance localhost | Export-DbaDiagnosticQuery -Path C:\reports

Answer 151

Azure SQL Database (PaaS), Azure SQL Managed Instance (near‑100% compatibility), and SQL Server on Azure VM (IaaS).

Answer 152

Built‑in HA, automatic backups, scaling (vCore/DTU), no direct OS/file access. TDE enabled by default. Some T‑SQL differences.

Answer 153

Use Azure Database Migration Service or log replay service. Backup to URL and restore. Or use transactional replication.

Answer 154

Replicates database from SQL Server 2019+ to Managed Instance in real time, enabling hybrid DR and near‑zero downtime migration.

Answer 155

Set Azure AD admin in portal. Create users CREATE USER [user@domain] FROM EXTERNAL PROVIDER. Use Active Directory Interactive connection.

Answer 156

Separates compute, log, and storage. Enables very large databases (up to 100TB) with fast scaling and nearly instantaneous backups.

Answer 157

Create a secondary database in another region. Active geo‑replication allows up to 4 readable secondaries. Failover via portal or T‑SQL.

Answer 158

Group of databases that fail over together across regions. Provides a single endpoint, handles replication and connection routing.

Answer 159

Use Query Performance Insight, Azure Monitor metrics, Intelligent Insights, and Extended Events. Also DMVs like sys.dm_db_resource_stats.

Answer 160

Azure can automatically force good plans, create/drop indexes based on workload analysis. Turn on in portal.

Answer 161

Group databases with varying usage patterns to share resources cost‑effectively. Define pool size and per‑db min/max.

Answer 162

Configure server‑level firewall IP rules. For private access, use VNet service endpoints or Private Link.

Answer 163

Stores the TDE protector key. Enables Bring Your Own Key (BYOK). Managed Instance also supports Managed HSM.

Answer 164

Define desired state (server, DB, firewall rules) in code. Deploy via CI/CD pipeline for consistent environments.

Answer 165

CREATE EXTERNAL DATA SOURCE blob_ds WITH (LOCATION = 'wasbs://...');
CREATE EXTERNAL TABLE ... ;

Answer 166

DTU: bundled measure of CPU, IO, memory. vCore: independent scaling of compute and storage. vCore offers Azure Hybrid Benefit.

Answer 167

Create external data source pointing to another Azure SQL DB. Create external tables. Queries can join across shards.

Answer 168

Automatic backups: full weekly, differential 12‑24h, log every 5‑10 min. Retention 7‑35 days. Long‑term retention (LTR) for up to 10 years.

Answer 169

Provisioned as a database option in Azure SQL. Provides blockchain‑like tamper‑evident storage. Useful for compliance.

Answer 170

Use Azure SQL Managed Instance link or transactional replication. Set up bi‑directional sync, cutover after data catches up.

Answer 171

Cross‑platform tool with extensions for backup, performance dashboards, and Notebooks. Especially useful for Linux and cloud.

Answer 172

Deploys Azure SQL Managed Instance on any Kubernetes cluster. Provides automated updates, backup, and monitoring via Azure portal.

Answer 173

Fully functional SQL Agent, similar to on‑prem. Can run T‑SQL, PowerShell, SSIS packages. Managed via portal.

Answer 174

Create alert rules on metrics like dtu_consumption_percent, storage_percent. Send to email, webhook, or Logic Apps.

Answer 175

Backup on‑prem to URL (Azure Blob) or S3. Use Azure Backup for SQL Server on VMs for integrated management.

Answer 176

Replicates tables to Synapse Analytics in near real‑time, using change feed. Enables analytics without ETL.

Answer 177

Define policies (e.g., enforce TDE, audit) and assign to Azure Arc–enabled SQL instances. Compliance results in Azure portal.

Answer 178

Initiate a planned failover of a geo‑replicated database or auto‑failover group. Validate application connectivity and data integrity.

Answer 179

Azure AD auth, TDE with BYOK, Advanced Threat Protection, vulnerability assessment, dynamic data masking, and row‑level security.

Answer 180

Deeper integration with Azure Arc, serverless computing, AI‑driven tuning, and seamless hybrid operations. SQL Server will be fully managed across environments.

Answer 181

Check error log, verify file paths, ensure disk space, look for corruption. Emergency mode repair with ALTER DATABASE ... SET EMERGENCY and DBCC CHECKDB.

Answer 182

Check current waits, look at execution plan, compare with previous plans in Query Store. Recompile if needed, check statistics, look for blocking.

Answer 183

Add multiple tempdb data files, enable trace flag 1118 (older versions), ensure equal sizing, and in 2025 enable memory‑optimized tempdb metadata.

Answer 184

Review error log, run DBCC CHECKDB with repair options if necessary (last resort). Restore from backup if repair not possible.

Answer 185

Capture deadlock graphs via trace flag 1222 or Extended Events. Analyze locked resources, fix by adding indexes or reordering transactions.

Answer 186

Transaction log out of space. Caused by long‑running transaction, log backups not taken, or large operations. Fix: backup log, increase size, shrink (temporarily).

Answer 187

Check sys.dm_hadr_database_replica_states for synchronization health. Look for network latency, suspended movement, or disk space. Resume data movement if suspended.

Answer 188

Restore the page from a good backup if available. Otherwise, use DBCC PAGE to examine, possibly use DBCC WRITEPAGE (extreme).

Answer 189

Increase max server memory if appropriate, identify memory‑intensive queries, free plan cache, or use Resource Governor.

Answer 190

Assess the damage, decide on restoration (tail‑log backup if possible), restore full/diff/logs, bring databases online, test, and communicate.

Answer 191

Check for distributed transactions, ensure MSDTC service is running, configure firewall, and monitor with sys.dm_tran_active_transactions.

Answer 192

Analyze error log, Windows event logs, memory dumps. Look for stack dumps. Use SQL Server Setup to repair if needed.

Answer 193

Check error log, ensure database files accessible, run ALTER DATABASE ... SET EMERGENCY, then DBCC CHECKDB. Restore if necessary.

Answer 194

Verify server is running, port accessible (telnet, test‑netconnection), check firewall, SQL Browser for named instances, and login permissions.

Answer 195

Too many VLFs can slow recovery and log operations. Fix by growing log in appropriate increments to reduce VLF count. DBCC LOGINFO to check.

Answer 196

Use sys.dm_io_virtual_file_stats to see read/write stall. Check physical disk counters. Move to faster storage if needed.

Answer 197

Pre‑size data and log files to avoid growth during peak. Monitor growth events with default trace or Extended Events. Set growth increments to reasonable MB.

Answer 198

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

Kill the head blocker after verifying. Long term: add indexes, tune queries, use snapshot isolation.

Answer 199

sqlcmd -S admin:server\instance -U sa -P ...

Used when server is unresponsive. Limited resources.

Answer 200

Document common issues: CPU, memory, IO, blocking, AG sync, backup failures. Include step‑by‑step diagnostic queries and escalation paths.

Answer 201

Build WSFC, enable AG feature, create endpoints, prepare databases, create AG with replicas, join secondaries, add listener. Test failover.

Answer 202

Encrypt database, backup cert and key, restore on different server, decrypt with cert. Validate data.

Answer 203

$splat = @{ SqlInstance = 'ProdServer'; Path = '\\backupshare\'; Type = 'Full'; CompressBackup = $true }
Backup-DbaDatabase @splat

Answer 204

Use sys.dm_os_performance_counters, Query Store, wait stats. Schedule data collection, visualize trends.

Answer 205

Install Azure Arc agent, connect SQL Server, create Azure Policy assignments (e.g., require TDE). Review compliance.

Answer 206

Restore full, differential, log backups with STOPAT. Extract data and re‑insert into production.

Answer 207

Corrupt a page with hex editor (test only!), take tail‑log backup, restore page from earlier log backup.

Answer 208

Identify regressed query, find plan with lower avg duration, force it. Compare before/after execution times.

Answer 209

Create predicate function, security policy. Test with different user contexts. Ensure no data leakage.

Answer 210

Invoke-DbaDiagnosticQuery -SqlInstance 'server1','server2' | Out-File -FilePath report.txt

FreeLearning365

SQL Server DBA Ultimate Interview Guide – 200+ Expert Questions & Answers | FreeLearning365

⚙️ SQL Server DBA Ultimate Interview Guide

🔹 1. SQL Server 2025 Architecture & New Features (1–20)

🔹 2. Installation, Configuration & Upgrades (21–40)

🔹 3. Security & Compliance (41–60)

🔹 4. High Availability & Disaster Recovery (61–85)

🔹 5. Performance Tuning & Monitoring (86–110)

🔹 6. Backup & Restore Strategies (111–130)

🔹 7. Automation & Maintenance (131–150)

🔹 8. Cloud Integration & Azure SQL (151–180)

🔹 9. Troubleshooting & Advanced Scenarios (181–200)

🔹 10. Bonus Labs & Real‑World DBA Challenges (201–210)

Post a Comment

0 Comments

Contact Us

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Mobile App

Categories

Free Learning 365

Random Posts

Social Media

Recent in Courses

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Menu Footer Widget

FreeLearning365

SQL Server DBA Ultimate Interview Guide – 200+ Expert Questions & Answers | FreeLearning365

🔹 1. SQL Server 2025 Architecture & New Features (1–20)

🔹 2. Installation, Configuration & Upgrades (21–40)

🔹 3. Security & Compliance (41–60)

🔹 4. High Availability & Disaster Recovery (61–85)

🔹 5. Performance Tuning & Monitoring (86–110)

🔹 6. Backup & Restore Strategies (111–130)

🔹 7. Automation & Maintenance (131–150)

🔹 8. Cloud Integration & Azure SQL (151–180)

🔹 9. Troubleshooting & Advanced Scenarios (181–200)

🔹 10. Bonus Labs & Real‑World DBA Challenges (201–210)

Post a Comment

0 Comments

Contact Us

Social Plugin

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Mobile App

Categories

Free Learning 365

Random Posts

Social Media

Recent in Courses

Popular Posts

The Complete ERP Mastery Series: From Basics to SAP S/4HANA Expert | FreeLearning365

English for Bangladeshis: The Deshi Dose – The Ultimate Course Outline

English Alchemy: Transform Your Grammar from Confusion to Confidence (A Bangladesh-Centric Guide) - Part1

Menu Footer Widget