In this article, we will discuss a number of questions that you may be asked when applying to a junior SQL Server database administrator (DBA) position.
Note: To learn more about Performance tuning as a DBA skill, please read this article: Why SQL Performance Tuning is the Most Important Database Management Skill to Have
Q1: Working as a SQL Server database administrator, A SQL Server developer asked you to allow him to view the code definition of the stored procedures in one database. Which permission should you grant him?
View definition.
Q2: During your absence, you ask one of your colleagues to terminate a specific session if it takes more than one hour. To which Fixed Server-Level role should you add his account to perform that task?
Processadmin
Q3: A SQL development engineer claims that each time he tries to run a BULK INSERT statement, the query failed with permission related error. To which Fixed Server-Level role should you add that engineer to allow him to execute the BULK INSERT statement?
Bulkadmin
Q4: While trying to assign tasks to the new database administration team engineers, you ask one of the engineers to run a DBCC CHECKDB command to check the consistency of a user database. What server scoped permissions should you grants to that engineer in order to perform that task?
Alter Server State
Q5: A request received from the development team to grant permission for a specific service account that will create a new database in the SQL Server instance at the first application launch. Which permission should be granted to the service account to achieve that?
DBCreator
Q6: As a SQL Server database administrator, you are planning to perform the multiple tasks with the minimum effort performed by your team. These tasks involve scheduling a backup plan for all user databases, checking the databases consistency once a week, and maintaining the indexes during the weekend. How could you achieve that?
Creating Maintenance plans.
Q7: After creating a list of linked servers, you need to leave and ask one of your colleagues to test and reconfigure these linked servers in case there is anything incorrect. Which permission should you grant the engineer to perform that task?
setupadmin
Q8: You are managing the permission of the new database administration team. To which fixed server role should you add the SQL Server database administrators in order to be able to perform any action on the server?
sysadmin
Q9: You asked one of your colleagues from the database administration team to execute a script that checks the blocking chain by querying a number of dynamic management objects. Which server scoped permissions should you grant that engineer to be able to execute this script?
View Server State
Q10: Working as a SQL Server database administrator, you are planning to track and audit all logon operations at your SQL Server. Which type of triggers can you take advantage of to achieve that?
LOGON trigger
Note: To learn more about trends that impact Database Administration, please read this article: 5 DBMS Trends Impacting Database Administration
Q11: Which tool can you use to check the performance-related information about CPU times, logins and Disk I/O on your SQL Server instance?
Activity Monitor from the SQL Server Management Studio.
Q12: As a SQL Server database administrator you are responsible for securing your SQL Server environment. Part of this task, you need to choose the security protocol that will be used to manage the security policies such as strong passwords, account locking, and password expiration. Which authentication protocol will you use?
Kerberos Authentication protocol.
Q13: The managed codes are .NET codes that are used to write any complex procedure or function that cannot be performed using the T-SQL language. After building that code, which permission should be granted to that assembly in order to allow the code to access local and network resources, registry and environment variables?
External_Access
Q14: During the backup job configuration, it is recommended to ensure that the backup file is valid and will be successfully restored without any errors. Which backup option should be used to achieve that?
WITH CHECKSUM
Q15: Working as SQL Server database administrator, you are planning to recover a user database to the Tuesday 7:15 AM point in time, before the database becomes corrupted.
The full backup is taken every Friday.
The Differential backup is taken every day at 12 AM.
The Transaction Log backup is taken every one hour. And the last log backup file has taken at 7 AM on Tuesday.
How could you restore that database?
- Take a tail-log backup to cover the changes on Tuesday between 7 AM and 7:15 AM
- Restore the Full backup using the NORECOVERY option
- Restore the differential backup taken on Tuesday using the NORECOVERY option
- Restore the Transaction Log backups starting from the file taken Tuesday 12 AM till 7 AM and the tail log backup file, where all files will be restored with NORECOVERY except for the last file that will be restored with RECOVERY
Q16: You are working as a SQL Server database administrator on a company. After restarting one of the SQL Servers, the SQL service cannot be brought online as the driver where the tempdb database is stored fails. How could you start the SQL Server service?
Start SQL Server in minimal configuration mode
Q17: You are a SQL Server database administrator in a company. One of your tasks is adding another disk drive to your FCI for the SQL Server cluster site.
Which windows tool should you use to perform that?
Failover Cluster Manager
Q18: Assume that you have a data warehouse database that contains 15 large database data files. Which Backup operation should you use to take a backup from that database, taking into consideration that they are writing to 3 database data files only?
File Backup operation
Q19: While trying to take backup for a database, you need to ensure that all backup sets will be overwritten. Which backup option should you use to achieve that?
WITH INIT
Q20: You are using the Always Encrypted technique for encryption of your critical data and prevent the SQL Server database administrators from viewing it. Always Encrypted supports both Deterministic and Randomized encryption types. Which encryption option is more secure?
Randomized Encryption
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021