In this article, we will discuss a number of questions that you may be asked when applying to a senior SQL Server database administrator 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: You are working as an Azure database administrator in an international company. You are administrating an Azure SQL Database instance. Do you need to enable the Transparent Data Encryption feature to encrypt the database at rest?
No need to enable the Transparent Data Encryption feature on Azure SQL Database as it is enabled by default.
Q2: Could we store our database files in the temporary disks of the Azure Virtual Machine that hosts the SQL Server instance?
When provisioning an Azure Virtual Machine to host a SQL Server instance, it comes with two default disks; the OS disk and a Temporary disk that is used to host the Pagefile.sys file. We cannot store the database files in the Temporary disk as data is not persisted on that disk.
Q3: You are working as a database administrator in an international company. You are administrating a SQL Server database that contains critical data stored in specific columns. What should you use in order to encrypt the critical data stored in these columns taking into consideration that the database administrators should not be able to view the critical data?
You should use the Always Encrypted feature, by setting the column master key, the column encryption key then encrypts the required columns.
Q4: You are working as an Azure database administrator in an international company. Azure SQL Database allows you to create up to 128 firewall rules at the server level and at the database level. What should you use to create the server-level firewall rules and the database-level firewall rules?
Server-level firewall rules can be created using the Azure Portal and the T-SQL commands, where the database-level firewall rules can be created using T-SQL commands.
Q5: You are working as an Azure database administrator in an international company. After provisioning an Azure SQL database instance. If you try to create the first server-level firewall rule using the T-SQL commands, will it work with you?
It will not work. You need to create the first server-level firewall rule using the Azure Portal by adding the current client IP to be authorized to connect to the Azure SQL database instance from your machine using SQL Server Management Studio, then you can add other server-level and database-level firewall rules using the T-SQL commands.
Q6: You are working as a database administrator in an international company. You are administrating a SQL Server Always on Failover Cluster instance and you are planning to configure when a failover or restart is performed for the FCI. Which property should you set to achieve that?
The FailureConditionLevel property can be used to set the condition to perform a failover or restart for the FCI instance.
Q7: After encrypting the columns that contain critical data, you plan to increase the security by masking these columns using the Dynamic Data Masking masks. Will this achieve the security enhancement goal?
This will not work as the Dynamic Data Masking feature does not apply to the columns that are encrypted with the Always Encrypted feature.
Q8: You are working as a database administrator in an international company. You are trying to enhance the performance of the application that is connecting to the SQL Server instance by controlling the parallelism behavior of the queries. This can be achieved by tuning the Max Degree of Parallelism and Cost Threshold for Parallelism server-level configurations. What are the two cases in which you will not consider tuning the Cost Threshold for Parallelism?
When the Maximum Degree of Parallelism option is set to 1 or the number of logical processors available in the SQL Server is only one. In this case, the queries will always run in a single thread.
Q9: You are administrating a SQL Server database in which you need to resolve an issue with the queries that become very slow after upgrading the SQL Server instance from SQL Server 2012 to SQL Server 2017. Which database level option should you configure to fix that issue?
The LEGACY_CARDINALITY_ESTIMATION database level option should be disabled, to set the cardinality estimation model of the database back to the SQL Server 2012.
Q10: You are working as a database administrator in an international company. You are administrating a large database table that is used to store the book’s information in the library. Although you are storing the library books information in a VARCHAR column with maximum varchar size equal to 50, you still need to minimize the amount of disk space used to store the table’s data as the table contains millions of records. Which compression method should you use?
We should use the page-level compression as the row-level compression will not affect the VARCHAR columns.
Note: To learn more about trends that impact Database Administration, please read this article: 5 DBMS Trends Impacting Database Administration
Q11: You are working as a database administrator in an international company. If a query that used to take a few seconds has been running for more than 30 minutes. What is the fastest method that can you use to check if that query is blocked by another session?
After getting the ID of the session that is running for a long time, you can easily execute the system stored procedure SP_Who2 providing that session number, then have a look at the BlkBy column from the result to see the ID of the session that is blocking that session.
Q12: Which system database is a read-only database that contains copies of all system objects?
Resource system database, that resides in the mssqlsystemresource.mdf file.
Q13: Which system database that is only created when the SQL Server instance is configured as a replication distributor?
The Distribution database, that stores replications transactions and metadata.
Q14: Which DBCC command should you use to check the consistency of the system tables?
DBCC CHECKCATALOG. The command that checks the catalog consistency.
Q15: After running the DBCC CHECKDB command on a database, it is found that a non-clustered index is corrupted. How could you repair the corrupted index issue?
By rebuilding the index using ALTER INDEX REBUILD or using the REPAIR_REBUILD option of the DBCC CHECKDB command.
Q16: You are working as a database administrator in an international company. You are administrating a SQL Server instance and you plan to create a baseline of the properties and recovery model for all user databases that are created under that SQL Server instance. How could you achieve that?
By applying the baselines to the Model system database that acts as a template for all newly created databases.
Q17: You are administrating a SQL Server instance and you need to execute a SQL Agent job and notify the system administrator when a fatal error occurs on that SQL Server instance. How could you achieve that?
This can be achieved by defining an Alert that is fired when the severity of the raised errors is 17 and higher.
Q18: You are working as a database administrator in an international company. You are administrating a SQL Server instance on which you need to write a message to the server security log when a fixed server role is modified. How could you achieve that?
You need to define a SQL Server Audit Specification with the Server Audit target to the server Security Log.
Q19: You are administrating a SQL Server database in which you are trying to overcome the current database storage limitation by stretching the table that you already enhanced using an indexed view and an index on the XML column. Will stretching the database to Azure achieves your goal?
No. This is because stretching the database to Microsoft Azure does not apply to the tables that have an indexed view or has a column with an XML data type.
Q20: You are working as a database administrator in an international company. You are administrating a SQL Server instance that serves multiple applications with no issue. In order to store data for another application that is not compatible with the current SQL Server version, you installed another named instance on that server. The application is not able to connect to the new SQL Server instance although the server is hosting another application that can connect to the default SQL Server instance. How could you fix this issue?
Start the SQL Browser service, as the default instance listening on the default TCP port 1433 and the named instance will listen on another TCP port.
You can also fix that issue by configuring the named instance to listen on a static TCP port and add an exception rule on the local server firewall to allow the application server to connect to the SQL Server on that TCP port.
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