Sql server dba interview Questions and Goldengate

 

-------------------------------------------------------------------------------------

1.    Explain Sql architecture briefly.
2.    What is .wrk file & .tuf file.
3.    How to do patching.
4.    How to failover in Always On / Mirroring / Logshipping.
5.    How many jobs available in Logshipping and what are they?
6.    How the data moves in logshipping from primary server to secondary.
7.    How to configure logshipping
8.    Microsoft will support for which versions.
9.    Have you raised any MS cases?
10. What is snapshot is SQL.
11. What is split backup.
12. What is copy only backup.
13. What is latency logshipping.
14. How much is latency in logshipping.
15. Can you give drawback of each in HA techniques.
16. What is synchronous and asynchronous in SQL.
17. How you will suggest license process to application owners.
18. What Backup strategy followed in organization
19. What are the different types backups
20. What are the diff. types of backups
21. Difference between Full backup & Differential backups.
22. Explain about differential backup.
23. If one data center suddenly stopped due to some issue, then how failover the failover happens without data loss.
24. How to change the service account in SQL. (In SSMS / Configuration manager).
25. What are the default ports available in SQL server.
26. How & where to change the ports.
27. What are the different protocols we have (TCP/IP, Named Pipes & Shared Memory).
28. What are different types of recovery models (Full, Simple & Bulklogged).
29. Explain difference between Simple and Full recovery model. When we suggest simple recovery model to user.
30. Why we cannot take log backups in Simple recovery model.
31. What are the different types of Migrations(in-place & Side by Side).
32. What are the commands to check and fix the orphan users.
33. What is the default collation in SQL Server.
34. What is FILL Factor and what is the fill factor purpose?
35. DMV’s to check fragmentation / Can you tell me the DMV’s to fix index fragmentation level?
36. How toadd Data Base to always on.
37. What is auto seeding in always on?
38. What is MAXDOP (Maximum Degree of Parallelism).
39. What are the different Wait Types.
40. How to fix if log file full. Will shrinking what will happen to Log file –while shrinking log data will delete / truncate.

===========================================================

 Installation, configuration, and administration of MS SQL Server 2005/2008/2008R2/2012/2014/2016/2017 till 2022.
 Daily monitoring and maintenance of production, testing, and development environments.
 Scheduled database backups and recovery operations, including Backup, Restore, and Recovery.
 Perform DB refresh activities from production to lower environments.
 Troubleshoot database issues, including blocking, disk/drive space issues, and monitoring database error logs, log file sizes, and free space in data drives.
 Performing restore activities from Production to Test/Dev environment.
 Experience in using Performance Monitor, SQL Profiler and optimizing the queries
 Perform Server level tuning, performance monitoring, Index Tuning, Query Tuning.
 Good experience in using DBCC and DMV’s commands.
 Configuring and maintaining all types of Database Backups and Recovery operations.
 Creating logins and providing proper permissions to them.
 Providing day-to-day Support and Maintenance for SQL Server databases.
 Attending the CAB / FSC/ RITM review calls for critical activities
 Identifying and Resolving Blocking and Deadlock issues by using System Stored Procedures.
 Resolving Space Management issues and Data and log file growth space issues.
 Knowledge on creating the basic SSIS packages and deploying them and configure as jobs.
 Currently handling more than 3000 databases and 1500 Servers including PROD, TEST & DEV etc.
 I have knowledge on ServiceNow ticketing tool.
 Upgrade databases from SQL Server 2008/2008R2 and 2012 to SQL Server 2014/2016/2017/2019.
 Experience in upgrading SQL Server software to new versions and applying service packs and patches.
 Good experience in DB migration from lower to higher versions.
 Experience in migrating database from on-premises to azure SQL database.
 Good experience on SQL Server migrations.
 Configuring, monitoring and troubleshooting the Disaster recovery activities.
 Performing the failover and failback activates.
 Experience in Server level and database level collation change
 Investigating and Troubleshooting Database corruption issues.

 Oracle Goldendale Interview Questions & Answers (Classic vs Microservices) — What Real DBAs Must Know


1️⃣ What’s the fundamental difference between Classic Architecture and Microservices Architecture (MA)?
Answer:
Classic uses monolithic processes (Manager, Extract, Pump, Replicat) controlled via GGSCI.
MA breaks these into independent REST-enabled services (Administration, Distribution, Receiver, Performance, Metrics). It improves security isolation, observability, and CI/CD automation.

2️⃣ How does Extract operate in Classic vs MA?
Answer:
Classic: Extract reads redo/archives via trails; parameters stored in text files.
MA: Extract is configured via Administration Service; parameters stored in a wallet-backed config directory. Validation and version control become native.

3️⃣ What replaces Data Pump in Microservices?
Answer:
Distribution Service replaces the Pump. It pushes trail files over HTTPS to Receiver Service, enabling firewall-friendly replication, load balancing, and multi-target fan-out.

4️⃣ Does Replicat work differently in MA?
Answer:
Yes. In MA, Replicat runs as lightweight isolated services, each with its own secure checkpoint repository. Deploying multiple parallel Replicats becomes simpler and avoids OS-level conflicts.

5️⃣ How do you troubleshoot lag in Classic vs MA?
Answer:
Classic: lag extract, lag replicat, manual I/O and log analysis.
MA: Performance Metrics Service provides per-step breakdown (read, parse, apply). REST endpoints expose lag for external dashboards.

6️⃣ How is Security improved in MA?
Answer:
MA enforces TLS by design, removes plain-text passwords via Credential Store, supports reverse proxies, and prevents manual file editing—hugely reducing operational risk.

7️⃣ Can Classic and MA coexist?
Answer:
Yes. GoldenGate supports hybrid mode. Trails from Classic can flow into MA using Distribution/Receiver, enabling gradual modernization without rearchitecting.

8️⃣ What is the biggest operational advantage of MA?
Answer:
Automation. REST APIs allow deploying, monitoring, scaling, and patching Extract/Replicat without touching GGSCI. This is critical for DevOps, CI/CD, and container-based deployments.

9️⃣ What are common migration steps from Classic to MA?
Ans:
Export Classic parameter files
Set up MA deployment + secure wallet
Recreate Extract/Replicat configs
Redirect trails via Distribution/Receiver
Validate + cutover with minimal downtime

🔟 What is the role of the Administration Server in MA?
Ans:
The Administration Server is the control plane for GoldenGate Microservices. It manages configuration metadata, parameter validation, process lifecycle (start/stop), logging, credential storage, and REST security policies. In Classic, all of this was scattered across flat files and the Manager process—MA centralizes and standardizes it.