Engineering
Intelligent Solutions
for Modern Enterprises

SQL Server DBA

Master Interview & Exam Q&A — 57 Questions • 9 Domains

🔷 FUNDAMENTALS — 6 Questions

Q1 [Fundamentals] What is SQL Server and what are its three core purposes?

Answer: SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft (originally co-developed with Sybase). Its three core purposes are: (1) Store Data — persist data to disk reliably, (2) Process Data — execute queries, transactions, and computations, (3) Retrieve Data — return queried results to users and applications.

Key Terms: RDBMS, Store, Process, Retrieve, Microsoft, Sybase

Example: A bank application uses SQL Server to store customer account records, process debit/credit transactions in real-time, and retrieve account balances when a customer logs in.

Q2 [Fundamentals] What is the difference between DBMS and RDBMS? Who is the father of RDBMS?

Answer: DBMS (Database Management System) stores and manages data but does not enforce relationships between data. RDBMS (Relational DBMS) organizes data into related tables and enforces data integrity through relationships, primary keys, and foreign keys. Prof. EF Codd is recognized as the Father of RDBMS — he published the relational model in 1970.

Key Terms: DBMS, RDBMS, Prof. EF Codd, relational model, tables, relationships, primary key, foreign key

Example: A flat-file system (like a spreadsheet) is a DBMS. SQL Server, Oracle, and MySQL are RDBMS products because they support tables, relationships, and the relational model defined by Codd.

Q3 [Fundamentals] What is a SQL Server Instance? How many instances can be installed on one machine?

Answer: A SQL Server Instance is an independent installation of the SQL Server Database Engine on a machine — it has its own services, system databases, configuration, and port. Two types exist: (1) Default Instance — takes the computer name, only one allowed, connects using MachineName or IP only. (2) Named Instance — has a custom name, connects using MachineName\InstanceName. A single Windows machine supports up to 50 total instances: 1 Default + 49 Named.

Key Terms: Default Instance, Named Instance, 50 instances, MachineName\InstanceName, independent services

Example: If your server is named SQLPROD01, a default instance is reached at SQLPROD01. A named instance called FINANCE is reached at SQLPROD01\FINANCE. Both run simultaneously and independently.

Q4 [Fundamentals] What are the naming convention rules for a SQL Server Named Instance?

Answer: The naming rules are: (1) Must start with a letter (A-Z or a-z) — cannot start with a number or special character. (2) Maximum 16 characters in length. (3) Only two special characters allowed: dollar sign ($) and underscore (_). (4) Cannot contain spaces.

Key Terms: 16 characters, starts with letter, $ underscore allowed, naming convention

Example: Valid names: SQL2019$PROD, Finance_DB, HRApp. Invalid names: 2019SQL (starts with number), SQL-2019 (contains hyphen), SQL Server 2019 (contains space).

Q5 [Fundamentals] What is the SQL Server Build Number format and where is it stored?

Answer: SQL Server build numbers follow the format: MajorVersion.MinorVersion.ActualBuildNo.RevisionHistory. For example, SQL Server 2019 (RTM) is 15.0.2000.5 — Major=15 (SQL 2019), Minor=0, Build=2000, Revision=5. The build number is stored in the hidden Resource Database. You can also check it by running SELECT @@VERSION or SELECT SERVERPROPERTY('ProductVersion').

Key Terms: MajorVersion.MinorVersion.ActualBuildNo.RevisionHistory, Resource DB, @@VERSION, build number

Example: SELECT @@VERSION might return: Microsoft SQL Server 2019 (RTM-CU14) 15.0.4188.2. Here 15=SQL2019, CU14 means Cumulative Update 14 has been applied, 4188 is the actual build number.

Q6 [Fundamentals] What are the two types of Operating System environments and why does it matter for SQL Server?

Answer: Windows has two OS types: (1) Desktop OS — Windows 7, 8, 8.1, 10, 11 — designed for personal use, limited connections, cannot host SQL Server in production. (2) Server OS — Windows Server 2008, 2012, 2016, 2019, 2022 — designed for enterprise hosting, supports multiple simultaneous connections, required for production SQL Server. Each SQL Server version supports specific Server OS versions within its lifecycle of approximately -7 to +7 years from release.

Key Terms: Desktop OS, Server OS, lifecycle -7 to +7 years, production support

Example: SQL Server 2019 supports Windows Server 2012 through 2022. Installing SQL Server 2019 on Windows 10 (Desktop OS) is possible for development but not recommended or supported for production workloads.

⚙ INSTALLATION — 6 Questions

Q7 [Installation] What is the difference between Standalone and Cluster SQL Server installation?

Answer: Standalone installation deploys SQL Server on a single Windows machine — simple to set up, no high availability built in, suitable for development or non-critical applications. Cluster installation deploys SQL Server across two or more Windows nodes sharing a common storage — provides automatic failover if one node goes down, more complex to configure, used for high availability in production. In clustering, there is NO data synchronization (shared storage is used instead).

Key Terms: Standalone, Cluster, shared storage, automatic failover, single machine, multiple nodes

Example: A dev environment uses a standalone SQL Server on one VM. A production finance system uses a 2-node cluster so if Node1 fails, SQL Server automatically moves to Node2 within seconds with zero data loss (same shared disk).

Q8 [Installation] What is Collation in SQL Server and why is it important?

Answer: Collation is the setting that determines the language, character set, case sensitivity, and accent sensitivity rules for the SQL Server instance and databases. It affects how string data is stored, sorted, and compared. Setting the wrong collation can cause data sorting issues or application errors. The App Team specifies the collation in the Build Sheet before installation. Common collation: SQL_Latin1_General_CP1_CI_AS (CI=Case Insensitive, AS=Accent Sensitive).

Key Terms: Collation, language settings, case sensitivity (CI/CS), accent sensitivity (AI/AS), Build Sheet, SQL_Latin1_General_CP1_CI_AS

Example: If collation is Case Insensitive (CI), SELECT * FROM Users WHERE name='john' also returns rows with name='John' or 'JOHN'. If Case Sensitive (CS), it only returns exact match. Wrong collation in a multi-language app can cause characters like é, ü, ñ to sort or compare incorrectly.

Q9 [Installation] What is a Service Account in SQL Server and what are its two types?

Answer: A Service Account is a Windows account used to run SQL Server services (Database Engine, Agent, etc.). It must have Local Administrator rights on the server to read/write data to disk. Two types: (1) Default/Virtual Service Accounts — automatically created by SQL Server setup (e.g., NT SERVICE\MSSQLSERVER). Good for standalone servers, no password management needed. (2) Customized Service Accounts — manually created domain accounts (e.g., UIA\Sql_PRD_SVC_ACT). Required for domain environments, log shipping, AOAG, linked servers — any feature needing network access.

Key Terms: Service Account, Local Administrator, Virtual Service Account, Customized/Domain Service Account, read/write disk

Example: For an Always On AG setup across two servers, you need a domain service account like UIA\Sql_PRD_SVC_ACT so the SQL Server Engine on both nodes can authenticate to each other across the network. A virtual account cannot do this.

Q10 [Installation] What are Specific Features vs Shared Features during SQL Server installation?

Answer: Specific Features are installed per-instance and each instance gets its own copy: Database Engine (core SQL service), SQL Server Replication, Full-Text and Semantic Search, CEIP (Customer Experience Improvement Program). Shared Features are installed once on the machine and used by all instances: Client Tools Connectivity, Client Tools Backward Compatibility, Client Tools SDK, SQL Client Connectivity SDK, SSMS (SQL Server Management Studio).

Key Terms: Specific Features, Shared Features, Database Engine, Replication, Full Text, SSMS, per-instance, machine-wide

Example: On a machine with 2 named instances (FINANCE and HR), each gets its own Database Engine and Agent service. But SSMS is installed once and can connect to both instances from the same machine.

Q11 [Installation] After SQL Server installation, how many services are created and what is the formula?

Answer: Formula: 4N+2, where N = number of instances. Each instance creates 4 specific services: SQL Server Database Engine (MSSQLSERVER or MSSQL$InstanceName), SQL Server Agent (SQLServerAgent or SQLAgent$InstanceName), SQL Server Full Text (MSSQLFDLauncher), SQL Server CEIP. Two shared services are created once regardless of instance count: SQL Server Browser (helps clients find named instances on non-default ports) and SQL Server VSS Writer (integrates with Windows Volume Shadow Copy for backups).

Key Terms: 4N+2, Database Engine, SQL Server Agent, Full Text, CEIP, SQL Server Browser, VSS Writer

Example: Install 3 instances on one machine: 4×3 + 2 = 14 services. SQL Server Browser running on that machine allows clients to type ServerName\HR and be automatically directed to the correct port for the HR instance.

Q12 [Installation] What is SSMS and what are the key version facts every DBA must know?

Answer: SSMS (SQL Server Management Studio) is the primary GUI tool used to connect to, manage, configure, and query SQL Server instances. Key facts: (1) Introduced in SQL Server 2005. (2) Up to SQL Server 2014, SSMS was bundled inside the SQL Server setup file. (3) From SQL Server 2016 onwards, SSMS is a separate download from Microsoft. (4) If SSMS version is LOWER than the SQL Server version you connect to, higher-version features will NOT be visible in the GUI. Always use the latest SSMS version. (5) SSMS is a Shared Feature — installed once per machine.

Key Terms: SSMS, SQL Server Management Studio, SQL 2005, Shared Feature, separate download from 2016, version compatibility

Example: If you install SSMS 17 (for SQL 2017) and connect to a SQL Server 2019 instance, you will not see SQL 2019-specific features like Accelerated Database Recovery (ADR) in the SSMS GUI. Always download the latest SSMS.

📁 SYSTEM DBS — 6 Questions

Q13 [System DBs] Explain the Master database in detail — what does it store and what happens if it is lost?

Answer: Master is the most critical SQL Server system database (DB ID=1). It stores: (1) All server-level configuration settings (max memory, max connections, etc.), (2) Login and password information for all SQL Server logins, (3) Linked server definitions, (4) Data file and log file locations for ALL databases including other system databases, (5) Server startup procedures. If Master is corrupted or lost, SQL Server CANNOT start. To recover Master, you must rebuild it using the SQL Server setup media and then restore from the last Master backup.

Key Terms: Master, DB ID 1, server configuration, logins, linked servers, data file locations, brain of SQL Server, cannot start without it

Example: Think of Master as the address book of SQL Server. If you lose the address book, SQL Server cannot find any of its databases — it does not know where TempDB files are, where user databases are, or which logins are allowed to connect.

Q14 [System DBs] Explain TempDB — why is it called the most active database and what are its special properties?

Answer: TempDB (DB ID=2) is a shared workspace for all users and all databases on the SQL Server instance. It stores: (1) User-created temp objects (#temp tables, ##global temp tables, table variables), (2) Internal work tables used by SQL Server for sort operations, hash joins, spools, (3) Row versioning data (used by snapshot isolation, online index rebuilds, MARS). Special properties: (1) TempDB is RECREATED from scratch every time SQL Server restarts — all data is lost, (2) Cannot be backed up, (3) Only one TempDB per SQL Server instance (though it can have multiple data files for performance), (4) TempDB contention is a common performance bottleneck — DBA best practice is to create multiple TempDB data files (typically equal to number of CPU cores, up to 8).

Key Terms: TempDB, DB ID 2, recreated on restart, cannot backup, temp tables, sort operations, row versioning, multiple data files

Example: When you write SELECT * FROM Orders ORDER BY OrderDate, SQL Server may use TempDB to sort millions of rows before returning them. A session's #TempOrders table lives in TempDB and is automatically dropped when that session ends.

Q15 [System DBs] Explain the Model database and its relationship with TempDB. What happens if Model is corrupt?

Answer: Model (DB ID=3) is the template database. Every new user database created in SQL Server is built as a COPY of Model. Model's properties are inherited: database size, recovery model, database options, and any objects (tables, stored procedures) placed in Model appear in all future databases. Critical relationship with TempDB: TempDB is recreated from Model every time SQL Server starts. If Model is corrupt or inaccessible, SQL Server CANNOT recreate TempDB, and therefore SQL Server itself CANNOT start.

Key Terms: Model, DB ID 3, template, inherit properties, TempDB depends on Model, corrupt Model prevents startup

Example: If your company requires all new databases to start at 500MB with Full recovery model, place those settings in Model. Every new database will automatically start with those settings. Also, if your DBA puts a compliance audit table in Model, it will appear in every new database automatically.

Q16 [System DBs] What does MSDB store and when does SQL Server NOT need MSDB to start?

Answer: MSDB (DB ID=4) is the automation and history database. It stores: (1) SQL Server Agent Job definitions, schedules, and execution history, (2) Maintenance Plan configurations, (3) Backup and Restore history (every backup/restore is recorded here), (4) Log Shipping configuration (monitor tables), (5) SSIS package storage (if using MSDB deployment model), (6) Database Mail configuration and sent mail history, (7) Policy-Based Management data. Unlike Master, Model, and TempDB, MSDB is NOT required for SQL Server to start. SQL Server can come online without MSDB, but ALL automation (jobs, alerts, maintenance plans) will fail until MSDB is restored.

Key Terms: MSDB, DB ID 4, Jobs, Maintenance Plans, Backup/Restore history, Log Shipping, SSIS, Database Mail, optional for startup

Example: If your nightly backup job fails and MSDB is corrupt, SQL Server stays online and users can query databases normally — but no backups run, no alerts fire, and no maintenance jobs execute. The business data is safe but unprotected.

Q17 [System DBs] What is the Resource Database and why is it hidden?

Answer: The Resource Database (DB ID=32767) is a read-only, hidden system database that contains all the system objects (system stored procedures, system views, system functions) used by SQL Server. It is hidden to prevent accidental modification of system objects. Key facts: (1) Stores SQL Server version and build number information, (2) Responsible for patching and upgrading — when you apply a CU/SP, the Resource DB is updated, (3) Not visible in Object Explorer, (4) Files are mssqlsystemresource.mdf and mssqlsystemresource.ldf, located in the same directory as master.mdf, (5) DB ID is always 32767.

Key Terms: Resource DB, DB ID 32767, hidden, read-only, system objects, build number, patching, mssqlsystemresource.mdf

Example: When you run SELECT * FROM sys.objects, you are reading from the Resource Database through a metadata layer. When a CU is applied to SQL Server, the patching process replaces the Resource Database files with updated versions containing fixed/improved system objects.

Q18 [System DBs] What is the startup order of SQL Server system databases and why does the order matter?

Answer: Startup order: (1) Master — must start first; contains locations of all other databases, (2) Resource DB — loaded after Master; provides system objects needed by startup code, (3) Model — needed to create TempDB if TempDB files do not exist or need recreation, (4) TempDB — recreated from Model on every startup, (5) MSDB — started after core databases; loads automation subsystem, (6) User Databases — started last. Order matters because each database depends on the previous: without Master, nothing starts; without Model, TempDB cannot be created; without TempDB, SQL Server cannot operate (it needs TempDB for internal work).

Key Terms: Startup order, Master first, Resource, Model, TempDB, MSDB, User Databases, dependency chain

Example: If you manually delete TempDB data files and restart SQL Server, SQL Server will recreate TempDB files from scratch using Model's properties and the configured TempDB location stored in Master. This only works because the startup order ensures Master and Model are ready first.

🔩 DB INTERNALS — 4 Questions

Q19 [DB Internals] Explain the SQL Server Database Storage Architecture from top to bottom.

Answer: SQL Server storage hierarchy from top to bottom: Database → File Groups → Data Files → Extents → Pages. (1) Database: top-level container. (2) File Groups: logical grouping of data files; PRIMARY filegroup always exists. (3) Data Files: physical files — MDF (Master Data File, one per DB), NDF (secondary data files, multiple allowed, up to 32,767 total files per DB). (4) Log File: LDF — transaction log, outside filegroups, records all transactions. (5) Extent: group of 8 contiguous 8KB pages = 64KB, the basic allocation unit. (6) Page: smallest unit of I/O in SQL Server = 8KB, holds actual data rows.

Key Terms: Database, FileGroup, MDF, NDF, LDF, Extent, Page, 8KB page, 64KB extent, 32767 files, PRIMARY filegroup

Example: A database called SalesDB has one PRIMARY filegroup containing Sales.mdf and Sales_data2.ndf, plus Sales_log.ldf. When you insert a new order, SQL Server allocates an extent (64KB), then writes the row to a page (8KB) within that extent.

Q20 [DB Internals] What is an Extent? Explain Mixed Extent vs Uniform Extent.

Answer: An Extent is the basic allocation unit in SQL Server — a collection of 8 physically contiguous data pages, each 8KB, making an extent 64KB total. Two types: (1) Mixed Extent — shared among up to 8 different objects. When a new small table or index is created, SQL Server first uses mixed extents to avoid wasting space on small objects. (2) Uniform Extent — dedicated entirely to one object. Once an object grows beyond 8 pages (64KB), SQL Server switches to allocating full uniform extents for that object for better performance. IAM (Index Allocation Map) pages track which extents belong to which object.

Key Terms: Extent, 8 pages, 64KB, Mixed Extent (shared), Uniform Extent (dedicated), IAM page, allocation unit

Example: You create a new small lookup table States with only 5 rows. SQL Server puts its data pages in a Mixed Extent alongside pages from 3 other small tables. As States grows to thousands of rows, SQL Server allocates dedicated Uniform Extents for it exclusively.

Q21 [DB Internals] What is WAL (Write Ahead Logging) and Log File Hardening? Why are they critical?

Answer: WAL (Write Ahead Logging) is a fundamental SQL Server durability mechanism: every data modification (INSERT, UPDATE, DELETE) must be written to the Transaction Log File FIRST, before the actual data page is written to the data file. This is also called Log File Hardening. Why critical: (1) Durability — if SQL Server crashes after a transaction is committed, the log record exists to redo the transaction on recovery. (2) Rollback — uncommitted transactions can be undone using the log. (3) Recovery — on restart, SQL Server uses the log to roll forward committed transactions and roll back uncommitted ones (REDO/UNDO phases). A transaction is considered committed only when its log record is hardened (written) to disk.

Key Terms: WAL, Write Ahead Logging, Log File Hardening, Transaction Log, LDF, durability, REDO, UNDO, checkpoint, committed

Example: You run UPDATE Salary SET Amount=50000 WHERE EmpID=101. SQL Server: (1) Writes log record "UPDATE EmpID 101, old=40000, new=50000" to LDF file on disk first. (2) Marks the data page as dirty in the buffer pool. (3) Later, the checkpoint process writes the dirty data page to the MDF file. If power fails between steps 2 and 3, the log record still exists — SQL Server redoes the update on next startup.

Q22 [DB Internals] What is an LSN (Log Sequence Number) and why is it critical for backup/restore?

Answer: LSN (Log Sequence Number) is a unique, monotonically increasing 25-digit number assigned to every log record written to the Transaction Log. LSNs are critical because: (1) They define the exact order of all transactions in the database. (2) They establish the log backup chain — each log backup covers an LSN range, and the ranges must be continuous for point-in-time recovery. (3) During restore, SQL Server checks LSNs to ensure backup chain is unbroken. (4) In Always On and Log Shipping, LSNs are used to track how far behind the secondary is (redo queue). If any log backup is missing (gap in LSN range), the log chain is broken and you cannot recover to a point after the gap.

Key Terms: LSN, Log Sequence Number, monotonically increasing, log backup chain, point-in-time recovery, unbroken chain, redo queue

Example: Log Backup 1 covers LSN 1000–2000. Log Backup 2 covers LSN 2000–3000. Log Backup 3 covers LSN 4000–5000 (Gap! LSN 3000-4000 missing). To restore to 4:30 PM, you need all three backups — but the gap at LSN 3000-4000 means recovery stops at LSN 3000. The backup chain is broken.

🔨 PATCHING — 5 Questions

Q23 [Patching] Explain the different types of SQL Server updates (SP, CU, GDR, QFE, Hot Fix) with key differences.

Answer: (1) Service Pack (SP): Major update bundling all previous patches plus new fixes. Released ~annually. Available only up to SQL Server 2016 — Microsoft discontinued SPs after 2016. (2) Cumulative Update (CU): Released every 5-8 weeks for SQL Server 2017+. Bundles all fixes since last CU. Replaces SPs for modern SQL versions. (3) GDR (General Distribution Release): Security-only critical fixes. Released outside the normal CU cadence when an urgent security vulnerability is found. Does NOT include the latest CU fixes — only security patches. (4) QFE (Quick Fix Engineering): Targeted fix for a specific reported bug. Often requires Microsoft support engagement. (5) Hot Fix: An emergency fix for a critical production-impacting bug, sometimes delivered between CUs.

Key Terms: Service Pack (SP), Cumulative Update (CU), GDR (General Distribution Release), QFE (Quick Fix Engineering), Hot Fix, SQL 2016 last SP, CU every 5-8 weeks

Example: SQL 2019 patching path: Install SQL 2019 RTM (15.0.2000) → Apply CU14 (15.0.4188) → If a critical security CVE is found next week, Microsoft may release a GDR-based patch immediately without waiting for the next CU.

Q24 [Patching] What is a Change Request (CR) and CAB in the context of SQL Server patching?

Answer: A Change Request (CR) is a formal documented request to perform a change in a production environment. It is submitted through a ticketing tool (ServiceNow/SNOW, Jira, Remedy, Lotus Notes) before any patching begins. CAB (Change Advisory Board) is a committee that reviews and approves Change Requests before the change window. The CAB evaluates risk, impact, rollback plans, and timing. Patching without a CR and CAB approval is a compliance violation in enterprise environments. The CR includes: server name, patch version, change window, expected downtime, rollback plan, and stakeholder approvals.

Key Terms: Change Request (CR), CAB (Change Advisory Board), ServiceNow, Jira, Remedy, compliance, change window, approval, rollback plan

Example: DBA submits CR: "Patch SQLPROD01 from SQL 2019 CU10 to CU14 on Saturday 10PM-2AM EST. Rollback: restore VM snapshot. App owner: Mark Johnson (approved). Expected impact: 30 min downtime." CAB meets Thursday, approves the CR. Patching proceeds Saturday night.

Q25 [Patching] What are MSI and MSP files and why do they matter for patching and rollback?

Answer: MSI (Microsoft Software Installer) files are the original installation package files for SQL Server. MSP (Microsoft Patch) files are the incremental patch files applied on top of the MSI during updates. These files are critical because: (1) The patching process (applying updates) uses MSP files. (2) The uninstall/rollback process (removing patches) uses both MSI and MSP files. (3) If MSI or MSP files are missing from the Windows Installer cache (typically C:\Windows\Installer), BOTH patching AND patch removal will FAIL with error codes 1603 or 1706. DBAs must verify installer cache integrity before patching and never manually delete files from C:\Windows\Installer.

Key Terms: MSI (Microsoft Installer), MSP (Microsoft Patch), C:\Windows\Installer, error 1603 (registry/permissions), error 1706 (missing MSI/MSP), rollback failure

Example: A DBA applies CU12, which succeeds. Then tries to apply CU14 but it fails with error 1706. Investigation reveals the original SQL Server setup MSI file was deleted from C:\Windows\Installer to free disk space. Solution: copy the original ISO/setup files back and repair the installer cache before retrying.

Q26 [Patching] Explain the complete pre-patching communication process — emails and notifications required.

Answer: Pre-patching requires two rounds of communication: (1) ADVANCE NOTICE EMAIL (sent days before): Address to App Owner/DB Owner/Server Owner/Business Owner. Content: "We are patching ServerName hosting SQL Server 2019 CU X to CU Y on [Date/Time]. SQL Server will restart multiple times. Windows Server will be rebooted before and after. Requesting 2-3 hours downtime window. Please confirm approval and a suitable maintenance window." (2) INITIATION EMAIL (sent at start of patching): Address to App Owner, DBA Team, Alert Monitoring Team, Windows Team. Content: "We are NOW initiating patching on [ServerName]. Will update once complete. Alert Monitoring: please suppress alerts from [ServerName] from [StartTime] to [EndTime]." (3) COMPLETION EMAIL: Notify all that patching is complete and service is restored.

Key Terms: App Owner, DB Owner, Alert Monitoring, Windows Team, advance notice, initiation email, completion email, suppress alerts, downtime window

Q27 [Patching] What is In-Place Upgrade vs Side-by-Side Migration? When would you choose each?

Answer: In-Place Upgrade: The new SQL Server version is installed directly on the same machine, upgrading the existing installation. Pros: Simple, reuses same hardware, keeps same server name. Cons: No rollback possible after completion, cannot skip multiple major versions in one step (e.g., 2005 to 2022 requires two hops: 2005→2014→2022), complete downtime required, risky for critical systems. Side-by-Side Migration: A new server/instance is built with the newer SQL Server version, and databases are migrated over (backup/restore or detach/attach). Pros: Full rollback possible (old server remains), any version to any version in one step, downtime minimized, can test thoroughly before cutover. Cons: Requires additional hardware temporarily. Choose In-Place for less critical systems with tight budgets. Choose Side-by-Side for any production critical system.

Key Terms: In-Place Upgrade, Side-by-Side Migration, rollback, version hop, downtime, hardware, cutover, critical systems

Example: Scenario: SQL 2005 Standard on Windows 2008 needs to become SQL 2022 Enterprise. In-Place: 2005→2014 (reboot, test) → 2014→2022 (reboot, test) — 2 maintenance windows, no rollback. Side-by-Side: Build new SQL 2022 server, migrate all databases, test with app team, cutover DNS/connection strings in one window — old server kept for 2 weeks as rollback.

🔐 SECURITY — 5 Questions

Q28 [Security] Explain Windows Authentication vs SQL Authentication. Which is more secure and why?

Answer: Windows Authentication: SQL Server trusts the Windows/Active Directory identity. The user logs in to Windows once and connects to SQL Server using that Kerberos/NTLM token — no separate password for SQL Server. Called "Trusted Connection." SQL/Mixed Mode Authentication: SQL Server maintains its own username and password database (stored in Master DB). The application sends credentials directly to SQL Server. Mixed Mode allows BOTH Windows and SQL logins simultaneously. Windows Authentication is more secure because: (1) Passwords are managed by Active Directory with enterprise password policies (complexity, expiry, lockout). (2) No SQL Server passwords to steal from connection strings. (3) Kerberos provides mutual authentication. (4) Single Sign-On (SSO) possible. SQL logins in connection strings are a common security vulnerability.

Key Terms: Windows Authentication, SQL Authentication, Mixed Mode, Kerberos, NTLM, Active Directory, Trusted Connection, sa account, connection string security

Example: An application using Windows Auth has a service account UIA\AppServiceAct connecting to SQL Server. If the connection string is leaked, the attacker still needs valid Windows credentials to exploit it. With SQL Auth, leaking the connection string "Server=SQLPROD01;UID=sa;PWD=P@ss123" immediately gives full access.

Q29 [Security] What is the sysadmin role and why is it dangerous? What is the sa account?

Answer: sysadmin is the highest privilege Fixed Server Role in SQL Server — members can perform ANY operation on the SQL Server instance without restriction: create/drop databases, change server configuration, read any data, create logins, run OS commands via xp_cmdshell. It is dangerous because a compromised sysadmin account gives full control over all data on the server. Best practices: (1) Only DBAs should have sysadmin. (2) Application service accounts should NEVER be sysadmin. (3) The sa (System Administrator) account is the built-in SQL Server sysadmin login. It should be RENAMED and DISABLED in production — it is the first account attackers try. (4) Use Windows Authentication for admin access where possible.

Key Terms: sysadmin, highest privilege, sa account (disable and rename), xp_cmdshell, principle of least privilege, application account never sysadmin

Example: If an application service account AppSvc has sysadmin, an attacker exploiting an SQL injection vulnerability in the app now has full DBA-level access: they can read all tables, drop databases, create backdoor logins, and even run OS-level commands. Always use the minimum required permissions (principle of least privilege).

Q30 [Security] Explain the DENY permission — why does it override GRANT?

Answer: DENY explicitly blocks a permission and takes priority over GRANT regardless of how the GRANT was applied — even through role membership. SQL Server evaluates permissions with this priority order: DENY (wins always) → GRANT → No permission (implicit deny). This means: if a user is granted SELECT through a role, but has DENY on SELECT directly on their login, DENY wins and they CANNOT select. The only way to override DENY is to explicitly REVOKE the DENY (remove it) — you cannot "out-GRANT" a DENY. REVOKE simply removes a previously applied permission (either GRANT or DENY); it does not block or allow — it returns to the default state (implicit deny unless inherited from a role).

Key Terms: DENY, overrides GRANT, priority order, REVOKE (removes permission), implicit deny, role membership, explicit deny

Example: User JohnDoe is a member of role SalesTeam, which has GRANT SELECT on Orders table. But JohnDoe also has DENY SELECT on Orders directly. Result: JohnDoe CANNOT query Orders — DENY beats GRANT from role. To fix: REVOKE DENY SELECT ON Orders FROM JohnDoe — now the GRANT from SalesTeam role applies.

Q31 [Security] What is an Orphan User and how is it created? How do you resolve it?

Answer: An Orphan User is a database user that exists inside a database but has NO corresponding server-level login (the link between them — the SID — is broken or missing). This commonly happens during: (1) Side-by-side migration: databases are restored/attached to a new server, but the server-level logins were not migrated, (2) Server rename: the SIDs no longer match. Symptoms: user sees "Login failed" or "User is not associated with a trusted SQL Server connection" errors. Resolution: use sp_help_revlogin (a Microsoft script) to script out all logins WITH their original SID hashes from the source server, then run that script on the destination server to recreate the logins with matching SIDs. Alternatively use sp_change_users_login to remap.

Key Terms: Orphan User, SID mismatch, sp_help_revlogin, sp_change_users_login, migration, login vs user, SID (Security Identifier)

Example: You migrate SalesDB from OldServer to NewServer. The database has user JohnDoe with SID 0x1234. On NewServer, JohnDoe login exists but with SID 0x5678 (new server assigned new SID). JohnDoe gets "Login failed" when connecting to SalesDB. Fix: use sp_help_revlogin to script JohnDoe with SID 0x1234 from OldServer, run on NewServer — SIDs now match.

Q32 [Security] What is xp_logininfo and what is a Group Login in SQL Server?

Answer: A Group Login is a Windows Active Directory group added as a SQL Server login. All members of that AD group automatically inherit SQL Server permissions without needing individual SQL logins. This simplifies access management — add/remove users in AD, SQL access updates automatically. xp_logininfo is a system stored procedure used to get information about Windows logins/groups in SQL Server. When called with a group name and 'members' parameter, it lists all Windows users who are members of that group and have access through the group login.

Key Terms: Group Login, Windows AD group, xp_logininfo, xp_logininfo GroupName members, automatic inheritance, access management

Example: Your company adds all DBAs to AD group CORP\SQLDBAs. You create one SQL Server login for CORP\SQLDBAs with sysadmin role. All 10 DBAs automatically get sysadmin access. New DBA joins? IT adds them to the AD group — no SQL Server change needed. To verify members: EXEC xp_logininfo 'CORP\SQLDBAs', 'members'.

💾 BACKUPS — 9 Questions

Q33 [Backups] Explain Full Backup in detail — what does it back up, how long does it take, and what is DCM reset?

Answer: A Full Backup backs up the entire database: all data files (MDF+NDF) AND the active portion of the transaction log (to ensure consistency). Key facts: (1) Full Backup is MANDATORY for every database — no other backup type can be taken without at least one prior Full Backup. (2) A Full Backup can take a long time for large databases (hours for TB-sized DBs). (3) Backups are "performance killers" — they consume I/O, CPU, and network resources; schedule during low-activity windows. (4) Full Backup includes BOTH committed AND uncommitted data as of the backup — SQL Server uses the log portion to make it consistent. (5) DCM Reset: After a successful Full Backup, SQL Server resets (clears) the DCM (Differential Changed Map) pages, establishing a new baseline for differential backups.

Key Terms: Full Backup, mandatory, entire database, DCM reset (Differential Changed Map), performance impact, committed + uncommitted, log tail

Example: A 2TB database starts a Full Backup at 9 PM. A new order is inserted at 11 PM. The Full Backup (completing at midnight) includes that 11 PM order because it captures the log records that cover the backup window. At midnight, DCM pages are reset — all future differential backups will only track changes AFTER midnight.

Q34 [Backups] How does Differential Backup work? What is the DCM page?

Answer: Differential Backup backs up only the extents (64KB allocation units) that have changed since the LAST FULL BACKUP — NOT since the last differential. This is tracked using DCM (Differential Changed Map) pages — special pages inside the database that maintain a bitmap: each bit represents an extent, and the bit is set to 1 whenever that extent is modified after the last full backup. When a Differential Backup runs, it copies all extents where the DCM bit = 1. Key insight: only the MOST RECENT Differential Backup is needed for restore (along with the full backup) — older differentials are not needed because each differential contains ALL changes since the last full. Differentials grow larger over time until the next Full Backup resets DCM.

Key Terms: Differential Backup, DCM (Differential Changed Map), extents changed since last FULL, bitmap, most recent diff only needed, grows over time

Example: Sunday Full Backup resets DCM. Monday: 100 extents modified → Monday Diff backs up 100 extents. Tuesday: 150 more extents modified → Tuesday Diff backs up 250 extents total (all changes since Sunday). Restore scenario: use Sunday Full + Tuesday Diff only. Monday Diff is not needed.

Q35 [Backups] What is a Transaction Log Backup? Explain log chain, LSN, and what breaks the chain.

Answer: A Transaction Log Backup backs up only the active transaction log (LDF) since the last log backup. It records all transactions in LSN order. Requirements: database must be in Full or Bulk-Logged recovery model. Critical concept — LOG CHAIN: Log backups form a sequential chain where each backup's starting LSN = previous backup's ending LSN. ALL log backups in the chain are needed for point-in-time recovery — missing even one breaks the chain. Log Backup also TRUNCATES the log (frees inactive log space). Chain breakers: (1) Recovery model changed to Simple (auto-truncates, chain broken), (2) A manual log backup taken outside the regular job, (3) A log backup file is deleted or corrupted, (4) BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG run (deprecated but still seen).

Key Terms: Transaction Log Backup, log chain, LSN sequence, TRUNCATE log, point-in-time recovery, chain breakers, Full or Bulk-Logged model required

Example: Chain intact: Full(Sun 2AM) → Log1(Mon 2AM) → Log2(Mon 2:15AM) → Log3(Mon 2:30AM). Restore to Mon 2:28AM: apply Full, Log1, Log2, stop Log3 mid-way using STOPAT='2:28AM'. Chain broken: if Log2 is accidentally deleted — you can only recover to 2:15AM.

Q36 [Backups] What is Copy-Only Backup and when must you use it?

Answer: Copy-Only Backup is an out-of-band backup that does NOT affect the normal backup sequence. Key difference from regular Full Backup: a Copy-Only Full Backup does NOT reset the DCM pages, so the differential backup chain is completely unaffected. It copies the data exactly like a regular full backup but is "invisible" to the backup strategy. When to use: whenever someone requests a full backup OUTSIDE the regular schedule — for example, before a major release deployment, before migration testing, for a developer needing a copy of production data. If you took a regular Full Backup instead, it would reset DCM and break the differential strategy.

Key Terms: Copy-Only Backup, does NOT reset DCM, out-of-band, ad-hoc backup, regular schedule unaffected, developer copy, pre-deployment backup

Example: Your normal strategy: Sunday Full → Daily Diff → 15-min Logs. The dev team asks for a fresh copy of production on Wednesday. If you do a regular Full on Wednesday, Wednesday's diff will now only show changes since Wednesday (not Sunday), and you've confused your restore strategy. Instead, take a Copy-Only Full — devs get their data, Sunday's DCM baseline is untouched, Wednesday's Diff still reflects all changes since Sunday.

Q37 [Backups] What is a Tail Log Backup? Provide a step-by-step crash recovery scenario.

Answer: A Tail Log Backup is an emergency log backup taken when a database is damaged or inaccessible, using WITH NO_TRUNCATE to capture the tail of the log (transactions not yet in any backup). It is the FIRST step in disaster recovery before initiating restore. Syntax: BACKUP LOG [DBName] TO DISK='path\filename.trn' WITH NO_TRUNCATE, FORMAT. Step-by-step crash scenario: DB corrupts Friday 7:57 PM. Strategy: Sunday Full (3AM) + Daily Diff (11PM) + 15-min Logs. Steps: (1) IMMEDIATELY take Tail Log Backup (captures Friday 7:45-7:57 transactions). (2) Restore Sunday Full WITH NORECOVERY. (3) Restore Thursday's Diff backup WITH NORECOVERY. (4) Restore all 83 log backups (Thursday 11PM to Friday 7:45PM) WITH NORECOVERY. (5) Restore Tail Log Backup WITH RECOVERY. Database is now recovered to 7:57 PM.

Key Terms: Tail Log Backup, NO_TRUNCATE, emergency backup, crash recovery, first step before restore, captures recent transactions, disaster recovery

Example: If you skip the Tail Log Backup and go straight to restore, you lose all transactions from the last log backup (say 7:45 PM) to the crash (7:57 PM). In a banking system, that 12 minutes could be thousands of transactions.

Q38 [Backups] Explain Split/Striped Backup — why use it and what is the syntax?

Answer: Split/Striped Backup distributes a single database backup across multiple backup files simultaneously. SQL Server writes to all specified files in parallel. Benefits: (1) Faster backup completion (parallel I/O across multiple disks), (2) Better storage management (spread across multiple drives/paths), (3) Smaller individual file sizes (easier to manage/copy). All files together form one complete backup — ALL striped files must be present for restore. Syntax: BACKUP DATABASE [DBName] TO DISK='X:\path\part1.bak', DISK='Y:\path\part2.bak', DISK='Z:\path\part3.bak'

Key Terms: Striped Backup, Split Backup, parallel I/O, multiple disks, faster backup, all files needed for restore, DISK= multiple

Example: A 3TB database that normally takes 6 hours to back up to one disk can be split across 4 disks — each receiving ~750GB — completing in ~1.5 hours. This fits the 2AM-4AM maintenance window that the single-file backup cannot. Restore: RESTORE DATABASE [DBName] FROM DISK='X:\part1.bak', DISK='Y:\part2.bak', DISK='Z:\part3.bak'

Q39 [Backups] Explain the three Recovery Models in detail with their trade-offs.

Answer: (1) Full Recovery Model: Every transaction is fully logged with all details (108+ attributes per transaction). Log file grows continuously and must be managed via regular log backups (which also truncate the log). Enables Point-In-Time Recovery (PITR). Required for log backups, log shipping, mirroring, AOAG. Recommended for ALL production databases. (2) Bulk-Logged Recovery Model: Works like Full but bulk operations (BULK INSERT, SELECT INTO, index builds) are minimally logged — only extent changes recorded, not individual rows. Benefits: faster bulk loads, smaller log growth during bulk ops. PITR not possible during periods with bulk operations. Use temporarily for large data loads. (3) Simple Recovery Model: Log is automatically truncated at every checkpoint (approximately every minute). No log backup possible. No PITR. Suitable for development, test, or non-critical databases where some data loss is acceptable. Smallest log files.

Key Terms: Full (everything logged, PITR possible), Bulk-Logged (bulk ops minimally logged, PITR conditional), Simple (auto-truncate, no log backup, no PITR)

Example: Production OLTP database → Full (can recover to exact second of crash). ETL staging database loading 500M rows nightly → Bulk-Logged during load (log 10x smaller), switch back to Full after load. Dev sandbox → Simple (nobody cares if 1 hour of dev changes is lost on crash, and log never grows large).

Q40 [Backups] Explain the three Restore Recovery States: WITH RECOVERY, WITH NORECOVERY, and WITH STANDBY.

Answer: (1) WITH RECOVERY: Brings the database fully ONLINE (ReadWrite). Performs UNDO phase (rolls back uncommitted transactions). This is the FINAL restore step — no more backups can be applied after this. (2) WITH NORECOVERY: Leaves the database in RESTORING state — completely inaccessible. Does NOT perform UNDO. Allows additional backups (differential, log) to be applied afterwards. Use for all restore steps except the final one. (3) WITH STANDBY: Leaves the database in Read-Only state (partially accessible). Creates a TUF (Transaction Undo File) that temporarily stores rolled-back uncommitted transactions. Additional backups can still be applied — when next backup is applied, TUF is used to re-apply those transactions first. Used in Log Shipping secondary and for read-only reporting on secondaries.

Key Terms: WITH RECOVERY (final, ReadWrite, UNDO), WITH NORECOVERY (Restoring, more backups possible), WITH STANDBY (ReadOnly, TUF file, more backups possible)

Example: Restore chain: Full WITH NORECOVERY → Diff WITH NORECOVERY → Log1 WITH NORECOVERY → Log2 WITH NORECOVERY → Log3 WITH RECOVERY (final step — DB comes online). OR in Log Shipping: Full WITH NORECOVERY → Log1 WITH STANDBY → (users can query) → Log2 WITH STANDBY (users disconnected briefly, log applied, users reconnected).

Q41 [Backups] What is Pseudo Simple Recovery Model?

Answer: Pseudo Simple Recovery Model is a temporary transitional state that occurs when you change a database's recovery model but have NOT yet taken a Full Backup after the change. Until a Full Backup is taken, the database effectively BEHAVES like its OLD recovery model despite the setting being changed. This is because: the backup chain must be re-established with a new Full Backup before the new recovery model properties take effect. This is a commonly tested concept — the answer is always: "Change recovery model → IMMEDIATELY take a Full Backup to apply the new model."

Key Terms: Pseudo Simple, transitional state, recovery model change, Full Backup required to activate, old behavior until Full Backup taken

Example: Database ProductionDB is in Simple recovery model (no log backups running). You change it to Full recovery model at 9 AM. At 9:01 AM, you try to take a Log Backup — it succeeds but is considered a "pseudo" log backup that cannot be used in a chain until after a Full Backup. At 10 AM you take a Full Backup. Now from 10 AM onwards, proper log backups establish a valid chain for PITR.

🤖 AUTOMATION — 2 Questions

Q42 [Automation] What is SQL Server Agent and how does it differ from a Maintenance Plan?

Answer: SQL Server Agent is a Windows service (SQLServerAgent) responsible for all scheduled automation in SQL Server. It reads job definitions from MSDB and executes them. A JOB is a user-defined collection of steps (T-SQL, SSIS, PowerShell, OS command, etc.) with a schedule, notifications, and error handling. Fully customizable — a step can do anything. A MAINTENANCE PLAN is a pre-built set of common DBA tasks configured through a GUI wizard — it generates Jobs behind the scenes. Less flexible than hand-crafted jobs but easier for less experienced DBAs. Key difference: Maintenance Plans are GUI-based with limited options; Jobs can implement any custom logic. Both store their metadata and execution history in MSDB.

Key Terms: SQL Server Agent, SQLServerAgent service, Job (collection of steps), Maintenance Plan (GUI-generated jobs), MSDB stores metadata, schedule, notification

Example: Maintenance Plan: click "Backup Database Task," select databases, schedule. Done — but limited options. Job: Step 1 checks free disk space via PowerShell, Step 2 runs T-SQL backup with dynamic filename including date/timestamp, Step 3 sends email via sp_send_dbmail on failure, Step 4 deletes backups older than 7 days. Much more powerful.

Q43 [Automation] Explain the three types of SQL Server Alerts with threshold examples.

Answer: (1) SQL Server Event Alert: Triggers when a specific SQL Server error number occurs. Error messages have severity levels 1-25 — severities 19-25 indicate serious issues. Example: Alert on Error 823 (disk I/O error), Error 9002 (log file full), Error 1205 (deadlock victim). Configure the alert to send email via Database Mail and/or execute a job (like a cleanup script). (2) SQL Server Performance Condition Alert: Triggers when a SQL Server performance counter breaches a threshold. Common thresholds in practice: CPU Usage > 80%, Buffer Cache Hit Ratio < 90%, User Connections > 500, Log File Used (%) > 80%, Transactions/sec > threshold for blocking alerts. (3) WMI Event Alert: Triggers based on Windows Management Instrumentation (WMI) events — OS-level events like disk space running low, Windows services stopping, or security events.

Key Terms: Event Alert (error numbers), Performance Condition Alert (thresholds), WMI Event Alert (OS events), Database Mail, severity levels, Error 823, Error 9002

Example: Alert 1 (Event): Error 9002 (log full) triggers alert → sends email to DBA team → executes job to take emergency log backup. Alert 2 (Perf Condition): When "SQL Server:Databases - Log File(s) Used(%)" for ProductionDB > 85 → email DBA team. Alert 3 (WMI): Windows disk space < 10% on C: → page on-call DBA.

🔄 HA/DR — 8 Questions

Q44 [HA/DR] Explain Log Shipping architecture — all three jobs, their roles, and what each job does.

Answer: Log Shipping is a database-level DR feature using three SQL Server Agent jobs: (1) BACKUP JOB (runs on PRIMARY): Takes a transaction log backup of the primary database at scheduled intervals (e.g., every 15 minutes) and saves it to a shared network path accessible from the secondary server. (2) COPY JOB (runs on SECONDARY): Connects to the shared path, copies the log backup files to a local folder on the secondary server. The file temporarily has .WRK extension during copy — becomes .TRN when copy completes. (3) RESTORE JOB (runs on SECONDARY): Applies (restores) the copied .TRN log backup files to the secondary database using WITH NORECOVERY or WITH STANDBY. The MONITOR server (optional) tracks latency and alerts when jobs fall behind. The secondary database exists in a permanent restore state — never fully online unless failover occurs.

Key Terms: Backup Job (primary), Copy Job (secondary, .WRK file), Restore Job (secondary, WITH NORECOVERY or STANDBY), shared path, local folder, monitor server, 15-min intervals

Example: Primary DB: SalesDB on SQLPROD01. Backup Job runs every 15 min, writes to \\SQLPROD01\LogShipBackups\SalesDB_20250607073000.trn. Copy Job on SQLDR01 copies it to D:\LogShipLocal\SalesDB_20250607073000.wrk → .trn. Restore Job applies it to SalesDB in NORECOVERY state. Secondary is typically 15 minutes behind primary.

Q45 [HA/DR] What is the TUF file in Log Shipping? When is it created and what happens if it is deleted?

Answer: TUF (Transaction Undo File) is created when the secondary database is set to WITH STANDBY state during log shipping restore. When a log backup is restored WITH STANDBY, SQL Server must allow users to read the database (Read-Only). But uncommitted transactions in the log must be rolled back temporarily so users see a consistent view. These rolled-back transactions are stored in the TUF file (not permanently rolled back — they are needed when the next log backup arrives). TUF filename example: D:\Standby\SalesDB_Standby.tuf. When the next log backup is applied, SQL Server re-applies the TUF transactions first (rolls them forward), applies the new log backup, creates a new TUF. If TUF is deleted or corrupted, the restore chain is broken and Log Shipping must be fully RECONFIGURED from scratch (new full backup, new initialization).

Key Terms: TUF (Transaction Undo File), Standby mode, uncommitted transactions, rolled back temporarily, reconfigure if deleted, standby path

Example: Secondary SalesDB is in Standby. At 2:00 PM restore, 3 uncommitted transactions were in the log. SQL Server rolls them back into TUF file. Users query SalesDB Read-Only and see consistent data. At 2:15 PM, new log backup arrives: SQL Server reads TUF, re-applies those 3 transactions, applies new log records, creates new TUF for next cycle.

Q46 [HA/DR] What is the difference between Synchronous and Asynchronous commit in Mirroring/AOAG?

Answer: Synchronous Commit: A transaction is NOT considered committed on the primary until the log record is hardened (written to disk) on the secondary as well. Both primary and secondary acknowledge the commit together. Result: ZERO data loss (RPO=0). Tradeoff: higher latency for write transactions (application must wait for secondary to acknowledge). Used when secondary is geographically close (same datacenter, low latency). Asynchronous Commit: A transaction is committed on the primary immediately — the log is sent to the secondary in the background without waiting for acknowledgment. Result: potential data loss (RPO > 0 — data in transit is lost if primary fails before secondary receives it). Tradeoff: no latency impact on primary write performance. Used when secondary is geographically distant (cross-datacenter, high network latency).

Key Terms: Synchronous (zero data loss, RPO=0, both acknowledge), Asynchronous (potential data loss, RPO>0, background send, no latency), RPO, commit acknowledgment

Example: Bank HQ in New York, DR site in New Jersey (5ms latency) → Synchronous (zero data loss, 5ms extra per transaction acceptable). Bank HQ New York, DR site London (120ms latency) → Asynchronous (120ms extra per transaction is unacceptable; accept small data loss risk).

Q47 [HA/DR] Explain Replication — what are Articles, Publications, Publishers, Distributors, and Subscribers?

Answer: SQL Server Replication distributes data at the OBJECT level (not entire database). Key terminology: Article = the object being replicated (a table or stored procedure). Publication = a named collection of one or more articles from one database. Publisher = the SQL Server instance that hosts the publication and makes data available. Distributor = the intermediary SQL Server instance (can be the publisher itself) that stores replication metadata, history, and manages distribution queues in the distribution database. Subscriber = the SQL Server instance that receives the publication data. Architecture flow: Publisher pushes changes to Distributor → Distributor delivers to Subscriber(s). A publisher can have multiple publications; a publication can have multiple subscribers.

Key Terms: Article (table/proc), Publication (collection of articles), Publisher, Distributor (distribution DB), Subscriber, object-level replication

Example: SalesDB on SQLPROD01 (Publisher) creates a publication "OrdersReplication" containing Articles: Orders table and OrderDetails table. SQLREPORT01 (Subscriber) subscribes — it receives all new/updated order data. SQLDIST01 acts as Distributor, managing the queue. Reports run against SQLREPORT01 without impacting SalesDB on SQLPROD01.

Q48 [HA/DR] Compare the four types of SQL Server Replication with use cases.

Answer: (1) Snapshot Replication: Sends a complete snapshot of all replicated articles periodically. Simple to set up. No primary key required. Best for: small tables that change infrequently, reference/lookup data. Latency: minutes to hours. (2) Transactional Replication: Captures individual INSERT/UPDATE/DELETE changes from the log reader and delivers them near real-time. Requires PRIMARY KEY on each article. Best for: high-volume OLTP data needing near real-time copies for reporting. Low latency (seconds). (3) Merge Replication: Both publisher and subscriber can make changes — changes are merged and conflicts resolved using configurable rules. Best for: mobile/disconnected scenarios, branch office applications. Includes conflict detection and resolution. (4) Peer-to-Peer Replication: Every node is both publisher and subscriber — multi-master. Each node can receive writes. Best for: read scale-out with write distribution, geographically distributed OLTP.

Key Terms: Snapshot (periodic full copy), Transactional (near real-time, PRIMARY KEY required), Merge (bi-directional, conflict resolution), Peer-to-Peer (multi-master)

Example: Lookup table of Country codes → Snapshot (changes monthly, full snapshot fine). Real-time order reporting dashboard → Transactional. Field sales app on laptops that sync when online → Merge. 3 regional datacenters all accepting orders → Peer-to-Peer.

Q49 [HA/DR] Explain Windows Server Failover Clustering (WSFC) and SQL FCI architecture.

Answer: WSFC (Windows Server Failover Cluster) is the Windows-level clustering infrastructure that groups multiple servers (nodes) together with shared storage. When one node fails, Windows automatically moves resources (IP, storage, services) to a surviving node. SSFC/FCI (SQL Server Failover Cluster Instance) is SQL Server installed on top of WSFC — SQL Server uses the shared storage (SAN or iSCSI) for data files. Key concepts: (1) NO data synchronization — both nodes access the SAME physical disk. (2) Only ONE node is active at a time (Active/Passive). (3) Shared storage is a single point of failure (mitigated by SAN redundancy). (4) Heartbeat Network: private network between nodes used to detect node failures (also called private/heartbeat network). (5) Quorum: cluster voting mechanism — majority of votes (nodes + disk witness) must agree cluster is healthy. IP formula: 2N+1+Applications (N=nodes).

Key Terms: WSFC, FCI/SSFC, shared storage, no data sync, Active/Passive, heartbeat network, Quorum, VNN, 2N+1+Apps IP formula

Example: 2-node cluster: SQLNODE01 (active) and SQLNODE02 (passive) share a SAN disk. SQLNODE01 hosts SQL Server, users connect via VNN "SQLCLUSTER". SQLNODE01 fails — within 30 seconds, Windows moves SQL Server resources (IP, disk, SQL service) to SQLNODE02. Users reconnect to "SQLCLUSTER" — same data, same IP, new physical node.

Q50 [HA/DR] What is an Always On Availability Group Listener and how does it work?

Answer: An AG Listener is a virtual network resource (Virtual Network Name + Virtual IP address) that clients use to connect to an Availability Group without knowing which physical server is the current Primary. The Listener always routes connections to the current Primary replica for read-write operations (default). It can also route read-only connections to Secondary replicas using Read-Only Routing. When failover occurs — automatic or manual — the Listener IP moves from the old primary to the new primary within seconds. Application connection strings never change — they always point to the Listener name. Components: Listener DNS name (e.g., AOAG_LISTENER), Listener IP (e.g., 10.10.10.50), Listener Port (default 1433 or custom).

Key Terms: AG Listener, Virtual Network Name (VNN), Virtual IP, read-write routing, read-only routing, failover transparency, connection string unchanged

Example: App connection string: Server=SALES_LISTENER,1433;Database=SalesDB. SQLPRIMARY01 is primary. Failover to SQLPRIMARY02 occurs. Within 15-30 seconds, SALES_LISTENER IP moves to SQLPRIMARY02. Application reconnects to SALES_LISTENER — lands on SQLPRIMARY02 transparently. No config change needed in the application.

Q51 [HA/DR] What is a Quorum in Windows Failover Clustering and why is it needed?

Answer: Quorum is the clustering mechanism that prevents "split-brain" — the scenario where both nodes of a cluster think they are the active primary (which would cause data corruption from dual writes to shared storage). Quorum works by requiring a MAJORITY of votes to agree that the cluster is healthy before any node can host the SQL Server resources. Votes come from: cluster nodes themselves and a witness (disk witness or file share witness). Common configurations: (1) Node Majority: odd number of nodes — majority wins. (2) Node and Disk Witness: even number of nodes + 1 disk witness vote. (3) Node and File Share Witness: same but uses a file share instead of disk. A 2-node cluster needs a witness (disk or file share) — without it, if one node loses connectivity, it cannot determine if the other node is down or if it is isolated.

Key Terms: Quorum, split-brain, majority votes, disk witness, file share witness, Node Majority, Node+Disk Witness, 2-node needs witness

Example: 2-node cluster, no witness: both nodes lose network connectivity between them. Each node thinks the other is dead and tries to become active — both try to mount the shared disk (SPLIT BRAIN). With a disk witness: only the side that can access the quorum disk wins. Other side sees it cannot get majority votes (1 node + 0 witness < 2 votes needed) and shuts down gracefully.

⚡ PERFORMANCE — 6 Questions

Q52 [Performance] Explain Blocking in SQL Server — what causes it, how to find it, and how to resolve it.

Answer: Blocking occurs when Session A holds a lock on a resource and Session B needs the SAME resource — Session B waits (is blocked) until Session A commits or rolls back. Blocking is normal in a concurrent database — SHORT blocking is expected. LONG blocking (minutes) becomes a performance problem. Causes: long-running uncommitted transactions, missing indexes (forcing table scans that hold locks longer), large batch operations without transaction chunking. Detection: sp_who2 (BLKBY column), SELECT * FROM sys.sysprocesses WHERE blocked <> 0, sys.dm_exec_requests (blocking_session_id), sp_whoisactive. Head/Lead Blocker: the session at the TOP of the blocking chain — killing it resolves all downstream blocked sessions. Resolution: (1) Kill the head blocker (KILL SPID), (2) Tune the query to complete faster, (3) Add appropriate indexes, (4) Use READ_COMMITTED_SNAPSHOT isolation level to reduce lock contention.

Key Terms: Blocking, lock contention, sp_who2, sys.sysprocesses WHERE blocked<>0, sys.dm_exec_requests, sp_whoisactive, Head Blocker, KILL SPID, long-running transactions

Example: SELECT @@spid returns 55. Session 55 starts a transaction: BEGIN TRAN; UPDATE Orders SET Status='Shipped' — but the developer forgot to run COMMIT. 20 minutes later, session 75 tries to UPDATE the same Orders row and waits. Then sessions 80, 85, 90 also wait. Run sp_who2: BLKBY column shows 55 for sessions 75, 80, 85, 90. Session 55 is the Head Blocker. KILL 55 resolves all blocking.

Q53 [Performance] Explain Deadlocks — what they are, how SQL Server detects and resolves them, and how to prevent them.

Answer: A Deadlock is a circular blocking situation where Session A is waiting for Session B, and Session B is waiting for Session A — neither can proceed. SQL Server's lock monitor checks for deadlocks every 5 seconds. When detected: SQL Server selects one session as the DEADLOCK VICTIM (the one with the lower priority, or by default the one that is cheapest to rollback) and kills it with error 1205. The victim's transaction is rolled back, releasing its locks, allowing the other session to proceed. Deadlock dump (.xdl file) is generated in the SQL Server ERRORLOG. Detection tools: SQL Profiler (Deadlock Graph event), Extended Events (xml_deadlock_report), Trace flags 1222 (recommended, XML format) or 1202 (text format). Prevention: (1) Access tables in consistent order across all transactions, (2) Keep transactions short, (3) Use appropriate indexes to minimize lock duration, (4) Consider NOLOCK hints carefully (risk: dirty reads).

Key Terms: Deadlock, circular blocking, error 1205, deadlock victim, 5-second check, trace flag 1222/1202, deadlock dump, deadlock graph, consistent table access order

Example: Session A: BEGIN TRAN; UPDATE Orders SET... (locks Orders); then tries UPDATE Customers SET... (needs Customers lock). Session B: BEGIN TRAN; UPDATE Customers SET... (locks Customers); then tries UPDATE Orders SET... (needs Orders lock). Circular wait → Deadlock! SQL Server kills Session A (error 1205). Session B completes. Fix: ensure all code updates Orders BEFORE Customers consistently.

Q54 [Performance] Explain MaxDOP and CTP settings — what they do, recommended values, and when to change them.

Answer: CTP (Cost Threshold for Parallelism): SQL Server assigns a cost estimate (in seconds) to every query execution plan. If the estimated cost EXCEEDS the CTP value, SQL Server considers using parallelism (multiple CPU cores). Default CTP is 5. Recommended: 40-50 for OLTP systems (prevents trivial queries from going parallel unnecessarily). Too low = too many parallel queries = CXPACKET waits. MaxDOP (Maximum Degree of Parallelism): When parallelism is triggered, MaxDOP controls the MAXIMUM number of CPU cores SQL Server can use for a single query. Default is 0 (use all cores) — never recommended for production. Recommended: for servers with <8 cores, set MaxDOP = number of cores. For >8 cores with NUMA, set MaxDOP = 8. For OLTP: MaxDOP = 1 to 4 is common. Both settings: dynamic — no restart required. Scope: server-level (sp_configure), database-level (ALTER DATABASE SCOPED CONFIGURATION), or query-level (OPTION (MAXDOP N)).

Key Terms: CTP (Cost Threshold for Parallelism, default 5), MaxDOP (default 0=all cores), CXPACKET wait, parallel query, dynamic setting, no restart, sp_configure, NUMA

Example: 8-core server: MaxDOP=4, CTP=40. A SELECT joining 3 large tables has cost=85 (>40, triggers parallel). SQL Server uses 4 CPU cores to run it faster. A simple SELECT * FROM Lookup WHERE ID=5 has cost=0.001 (<40, serial). Single core used. Perfect balance.

Q55 [Performance] Explain Index Fragmentation in detail — what causes it, how to measure it, and when to Rebuild vs Reorganize.

Answer: Fragmentation occurs when data modifications (INSERT/UPDATE/DELETE) cause index pages to become partially empty or out of logical order on disk. Types: (1) Internal Fragmentation: pages are partially filled (too much free space per page) — caused by page splits when a row is inserted into a full page. (2) External Fragmentation: logical page order doesn't match physical order on disk — caused by page allocations in non-contiguous locations. Measurement: sys.dm_db_index_physical_stats — returns avg_fragmentation_in_percent for each index. Thresholds: < 5% = ignore. 5-30% = REORGANIZE (online, low impact, compacts pages in-place, does not rebuild statistics). > 30% = REBUILD (offline by default [WITH ONLINE=ON available in Enterprise], drops and recreates the index, updates statistics, allocates contiguous pages). REBUILD is more thorough but requires more resources and causes brief blocking (in offline mode).

Key Terms: Fragmentation, sys.dm_db_index_physical_stats, avg_fragmentation_in_percent, Internal/External fragmentation, page split, REBUILD (>30%, offline/online), REORGANIZE (5-30%, online), update statistics

Example: An Orders table has 10 million rows with daily inserts/deletes. After 3 months, sys.dm_db_index_physical_stats shows PK_Orders fragmentation = 45%. Action: ALTER INDEX PK_Orders ON Orders REBUILD — takes 5 minutes in a maintenance window. Next month fragmentation = 12% → ALTER INDEX PK_Orders ON Orders REORGANIZE (online, no maintenance window needed).

Q56 [Performance] What is Max Server Memory and why must it be configured? What happens if it is not set?

Answer: Max Server Memory is a SQL Server configuration setting that caps the maximum amount of RAM the SQL Server Buffer Pool can consume. If NOT configured (left at default 2,147,483,647 MB = unlimited), SQL Server will consume ALL available RAM on the machine, leaving nothing for: the Windows OS (which needs ~1-2GB), other services, and SQL Server's own non-buffer pool memory (thread stacks, CLR, linked server providers). This causes Windows to page memory to disk (page file thrashing), severely degrading performance and potentially causing OS instability. Recommended value: 80% of total physical RAM, leaving ~20% for OS and other processes. Setting is dynamic — no restart required. Configure via: sp_configure 'max server memory', [ValueInMB]; RECONFIGURE.

Key Terms: Max Server Memory, Buffer Pool, 80% of RAM, dynamic setting, no restart, paging/thrashing, OS needs RAM, sp_configure, RECONFIGURE

Example: Server has 64GB RAM. Default: SQL Server consumes 62GB, leaving OS only 2GB → OS starts paging → server becomes unresponsive. Correct: sp_configure 'max server memory', 52428 (80% of 64GB = 51.2GB → round to 52428MB). SQL Server uses up to 52GB, OS has 12GB → stable operation.

Q57