Interview with Mphasis

### **SQL Server Database Administration – Advanced Interview Questions** #### **1. How do you plan a migration for a critical enterprise database with a strict 4-hour outage window?** **Answer:** Migrating a critical database within a limited downtime window requires **careful planning and execution**. The following steps ensure a smooth transition: 1. **Define the Migration Strategy:** - Engage with **business stakeholders** to finalize the migration approach. - Prepare a **detailed migration plan** with target deadlines and responsibilities for each team. - Obtain approval from **business teams, support groups, and key stakeholders**. 2. **Minimizing Downtime:** - Consider efficient migration techniques such as: - **Log Shipping:** Pre-configure log shipping to sync data before cutover. - **Disk Snapshot Migration:** Detach and attach disks for faster recovery. - **Pre-stage Data Restoration:** Perform **FULL, DIFF, and TLOG restores** before the outage window. 3. **Performance Baseline & Validation:** - Capture **performance statistics** from the source system and compare them with the new environment post-migration. 4. **Post-Migration Best Practices:** - Upgrade **database compatibility** to leverage the latest SQL Server features. - Run **DBCC CHECKDB** to verify database integrity. - Perform **index rebuilds and update statistics** for optimized performance. - Resolve **orphaned users** to ensure seamless authentication. --- #### **2. What common issues have you encountered after database migration?** **Answer:** Post-migration challenges can arise due to compatibility changes, performance shifts, and security constraints. Some common issues include: - **Compatibility Issues:** - Legacy applications may require **Legacy Cardinality Estimation** to maintain query performance. - Orphaned users may need re-mapping. - **Performance Issues:** - Execution plans may change, requiring query tuning and statistics updates. - **SQL Server Reporting Services (SSRS):** - **Encryption key conflicts** after migration. - **SQL Server Integration Services (SSIS):** - SSIS Catalog migration challenges may arise, requiring package validation. --- #### **3. How do you configure MAXDOP and Cost Threshold for Parallelism?** **Answer:** - **MAXDOP (Maximum Degree of Parallelism):** Determines the maximum number of CPU cores SQL Server can use for query execution. It is typically set based on **CPU architecture** and **workload patterns**. - **Cost Threshold for Parallelism:** Defines the **query cost threshold** above which SQL Server will use parallelism. A default of **5** is often too low and needs to be adjusted based on workload analysis. --- #### **4. How does MAXDOP impact performance, and how do you determine the right value?** **Answer:** - Setting **MAXDOP too high** can lead to excessive **context switching and CPU contention**. - Setting **MAXDOP too low** may limit parallel query execution, increasing query runtime. - The ideal **MAXDOP value** is determined based on **server core count, NUMA configuration, and workload type** (OLTP vs. OLAP). --- #### **5. What are some common wait types you have encountered?** **Answer:** - **PAGEIOLATCH_SH / PAGEIOLATCH_EX:** Indicates slow disk I/O performance. - **CXPACKET:** Can signal **parallelism issues**, requiring MAXDOP tuning. - **LCK_M_S / LCK_M_X:** Lock contention issues due to blocking queries. - **ASYNC_NETWORK_IO:** Often due to slow application-side processing. --- #### **6. How do you troubleshoot a query that runs 20x slower on a higher SQL Server version after migration?** **Answer:** - Compare **execution plans** between old and new servers. - Update **statistics and indexes** to optimize performance. - Verify **query compatibility mode** to ensure it aligns with the new SQL version. - Enable **Legacy Cardinality Estimation** if necessary. - Check for **wait types** affecting the query execution. --- #### **7. Have you worked on SQL Server installations on Linux?** **Answer:** Yes, SQL Server is supported on Linux, and installation is done using package managers like **YUM, APT, or Zypper**. Tasks include configuring **mssql-server service, firewall rules, and storage optimizations**. --- #### **8. What is your expertise in PowerShell for SQL Server administration?** **Answer:** - Automating **SQL Server installations** and configurations. - Managing **backups, restores, and database health checks**. - Automating **SQL Agent job monitoring and alerting**. - Using **dbatools PowerShell module** for efficient SQL Server administration. --- #### **9. How do you efficiently install SQL Server when handling bulk installation requests?** **Answer:** - Use **PowerShell automation scripts** to streamline installations. - Pre-define **configuration.ini files** for unattended installations. - Deploy using **group policies or Ansible automation** for large environments. - Automate post-installation steps, such as **sp_configure settings, tempdb optimization, and security configurations**.