Skip to main content
Our Tech Ideas

SQL Server DBA interview question from !nf0$y$

  1. What are the prerequisites for Transactional replication?
    • Prerequisites include ensuring that both the Publisher and Subscriber databases are using the Full Recovery Model, proper network connectivity between the Publisher and Subscriber, appropriate permissions for the replication process, and no conflicts with existing objects.
  2. I have a very large existing set of tables being replicated (transactional) between two servers over a dedicated VPN connection. I need to add a new table. Reloading all the tables is not an option. How can this be achieved?
    • You can achieve this by using the “sp_addarticle” stored procedure to add the new table to the existing replication setup without reloading all tables. This allows for minimal disruption to the replication process.
  3. Have you worked on Backup failure reports?
    • Yes, I have experience in monitoring backup jobs and generating reports to identify and address backup failures promptly. This involves setting up alerting mechanisms and regularly reviewing backup logs for any errors or failures.
  4. What are the prerequisites for a database refresh activity?
    • Prerequisites typically include ensuring that there is sufficient disk space, proper planning to minimize downtime, taking backups of the existing databases, verifying the integrity of the backups, and coordinating with stakeholders to avoid any data loss or disruptions.
  5. What tools are you using for monitoring, ticketing, and performance-related tasks?
    • I have experience using tools such as SQL Server Management Studio (SSMS) for monitoring and management tasks, ticketing systems like ServiceNow or Jira for issue tracking, and performance monitoring tools such as SQL Server Profiler, Extended Events, and Performance Monitor.
  6. How do you address CPU and memory utilization issues?
    • I address CPU and memory utilization issues by identifying resource-intensive queries, optimizing indexes and query execution plans, adjusting server configurations such as memory allocation and parallelism settings, and implementing workload management strategies to prioritize critical tasks.
  7. SQL Server Upgradation and Migration: Have you been involved in such projects?
    • Yes, I have experience in planning and executing SQL Server upgrades and migrations. This involves assessing compatibility issues, performing test migrations, creating rollback plans, and coordinating with stakeholders to ensure a smooth transition with minimal downtime.
  8. Can we downgrade the SQL Server edition, like from Enterprise to Developer edition?
    • Downgrading SQL Server editions is not supported by Microsoft. However, you can achieve similar functionality by installing the desired edition side-by-side and migrating databases and objects accordingly.
  9. Do you have experience with rebalancing a cluster?
    • Yes, I have experience in rebalancing SQL Server clusters by redistributing resources, adding or removing cluster nodes, and optimizing failover configurations to ensure high availability and performance.
  10. What are the prerequisites for SQL Cluster installation?
    • Prerequisites include configuring shared storage, setting up a failover cluster, ensuring network connectivity, preparing domain accounts with appropriate permissions, and verifying hardware and software requirements.
  11. Can we take backups at the Analysis level?
    • Yes, backups at the Analysis Services (SSAS) level can be taken using SQL Server Management Studio (SSMS) or through scripting with SQL Server Agent jobs. This allows for data recovery and disaster preparedness in SSAS environments.
  12. Have you managed permissions for reporting services?
    • Yes, I have experience in managing permissions for SQL Server Reporting Services (SSRS) by configuring role-based security, granting access to reports and folders, and ensuring compliance with data privacy regulations.
  13. What are the main differences between Always On and Cluster?
    • Always On Availability Groups provide high availability and disaster recovery at the database level, whereas failover clustering provides high availability at the instance level. Always On allows for multiple readable secondary replicas and automatic failover, whereas failover clustering requires shared storage and manual failover.
  14. Have you worked on Backup and Restore operations?
    • Yes, I have extensive experience in performing backup and restore operations for SQL Server databases. This includes full, differential, and transaction log backups, as well as point-in-time recovery and testing backup integrity and reliability.