This article is in sequence with my last article where I have covered SQL Server interview questions and answers on SQL Server architecture, SQL Server administration, and SQL Server backup & recovery topics. I would recommend you read that article if you want to improve your interview skills on the given SQL Server topics. A detailed explanation has not been provided on any specific topics. If you want to read in detail visit all attached links in their respective questions.
All interview questions have been segregated between the below headings. Please suggest your feedback in the comment section if you want more articles on additional topics of interview questions.
- SQL Server Architecture
- SQL Server Administration
- Backup & Recovery
- Migration & upgrades
- Performance Tuning
- High Availability & Disaster Recovery (Always on availability groups, Log Shipping, SQL Server Replication, SQL Server Failover Cluster Instances)
I will cover the interview questions on the last 3 topics in this article.
Before going ahead with the below SQL Server Interview Questions & Answers, I would suggest you prepare the general interview questions topics like your past work experiences, your flexibility towards work and shifts, the reason behind looking for a change, your salary expectation, etc.
Migration & Upgrades
Q: Tell me high-level steps for SQL Server migration from one server to another.
A: SQL Server migration requires a lot of analysis and planning. You must divide all tasks into 3 parts.
- Pre-migration
- Migration
- Post-migration
The pre-migration phase includes analysis and planning about all aspects like whether our target system is hosted on new hardware or the same hardware from a compatibility and supportability standpoint. Make sure you have appropriate disk types and structures for your database files along with adequate space. Make sure the application has full connectivity to the target database systems. Gather the list of logins, agent jobs, and other dependent objects like linked server, any advanced feature is configured like replication, CDC, auditing, AOAG, etc.
Additionally, you need to finalize your migration approach in this phase whether you want to migrate using backup restore with an acceptable downtime or you want it using AOAG or log shipping or replication to minimize the downtime, etc. Finalize your migration approach considering the cutover time window. Ensure to create run books and documents which will be followed during the actual migration. Capture each step carefully and do not miss anything.
Once all planning is done then the Migration phase will start where we need to perform actual migration based on the decision, we have made in the pre-migration phase. Make sure to run a full backup before starting the actual migration to protect your data. Here you need to perform a cutover where you and the application team will work together to switch all workload to the new system. Make sure to shut down the application or disable all SQL Server logins during cutover to ensure there is no data mismatch.
If you are using backup restore, then take a log backup on the source database after applications shutdown and apply it to your target database with recovery mode. I assume you have already restored your database in no recovery mode along with creating all depended objects like login, linked server, jobs, etc., and waiting for cutover to apply only logs. If you are using AOAG then you need to synchronize both replicas and then failover to the target system and same with other options as well.
The Post-migration phase includes monitoring of systems and fixing the performance and user connection issues.
Q: How do you apply patches on a distributed availability group with minimum downtime?
A: Distributed availability groups are configured between two availability groups. Let’s assume we have a distributed availability group between 2 node replica availability groups AG1 and AG2. AG1 is configured as global having 2 replicas REPLICA1 and REPLICA2 whereas AG2 is configured as a forwarder having 2 replicas REPLICA3 and REPLICA4. If we have to patch this configuration then we will first patch secondary replicas of both availability groups like apply patches on REPLICA4 and reboot this server, then will patch REPLICA2 and reboot the server. Now both secondary replicas from both availability groups have been patched.
Next, we will failover AG2 from REPLICA3 to REPLICA4 and apply patches on REPLICA3 followed by a server reboot. Once this is done then do the same for primary replica REPLICA1, failover AG1 from REPLICA1 to REPLICA2 and apply the patches on REPLICA1 followed by a server reboot.
Now all replicas participating in the distributed availability group have been patched successfully. If you want to failback both availability groups to their original primary replicas, then before you can do that here. Failback AG2 from REPLICA4 to REPLICA3 and AG1 from REPLICA2 to REPLICA1. Now, you are done with the patching distributed availability group configuration. Also, make sure to run a full backup of all your databases before starting this activity.
Q: What are the points we need to take care of before upgrading our databases?
A: We must consider the below points if we are going to upgrade our databases from a lower version to the higher one.
- Make sure applications are compatible with our new target versions
- Target system where the upgraded database is hosted must be accessible to applications in case of side-by-side upgrade
- Make sure you have a full backup of your database in case something goes wrong you can revert them to the previous state
- Gather all details about dependencies of this database like linked server, Jobs, logins, reports, replication, auditing, etc.
- Make sure to capture all database related properties configuration and replicate same to the target system
Q: Can we upgrade a database running on SQL Server 2008 R2 instance to SQL Server 2019?
A: Yes, we can directly upgrade a database running on SQL Server 2008 R2 instance to SQL Server 2019 using the side-by-side upgrade approach. We need to take a backup of the source database that is running on SQL Server 2008 R2 and restore it on SQL Server 209 instance. Make sure to copy all related objects of this database.
Performance Tuning
Q: How do you troubleshoot a performance issue in SQL Server?
A: Performance issue troubleshooting requires a deeper analysis to get it fixed. There are numerous reasons for a database performance issue. It could be slow network issues, network packet drops, application issues, patching, poor code issue, poor database configurations, outdated statistics, or some resource-intensive transaction running on the database, etc. We need to start analyzing the behavior of database transactions and the nature of incidents to isolate the issue by excluding other possibilities and get the correct root cause. We should also track whether an ongoing performance issue is observed post executing any change request or applying any OS or DB patches. If all systems have received a specific change or patch, then you can decide to roll back that change or patch to restore the normal business processing.
Next, we can engage the application team for their analysis of whether they have applied any changes at the application or web layer that is resultant in poor response time. We will also look at database transactions whether there is any blocking, or any poor performing querying is running, or any resource-intensive transaction is eating most of the resources. Here we need to closely work with the development team or application team. Blocking issues can be addressed with the help of the application team whether to kill it or let it run based on the nature of transactions.
If any poor-performing query is identified, then we can gather its execution plan and see the scope of improvement by looking into it.
Parameter sniffing could be another aspect of query regression. We can analyze it using the Query store and fix it by recompiling the impacted object or performing update statistics on the impacted objects. We will gather SQL Server wait types to analyze what was the issue where is most of the resources were waiting. We can also exclude network-related possibilities by looking into network dashboards or engaging a network administrator to ensure everything is fine from their end.
Q: How to improve the performance of the tempdb database?
A: Tempdb database is very crucial to run all database transactions in a healthy manner that is why we must give importance to designing a tempdb configuration. Below there are some high-level points you must consider getting better tempdb performance.
- Tempdb database files must have adequate size and space to accommodate all database workloads
- Enable Instant file initialization to speed up data file autogrowth
- Place tempdb files on distinct drives with the same no of files as you have the number of CPU cores
- Set database file growth increment to the reasonable size to prevent too much auto grow operations
- Configure memory-optimized tempdb metadata for a system running with a high transaction workload
Q: What could be the reason behind having poor IO performance or we can say why IO takes a longer time to complete their operations?
A: Any long-running IO which is pending for 15 seconds will be logged in the SQL Server error log file. Long IO indicates pressure on the disk subsystem. There could be multiple reasons for this:
- Heavy SQL Server workload being performed on the disk subsystem
- Inappropriate disk type to handle exiting SQL Server workload
- Disk latencies because of other reasons like hardware, driver, or firmware issues
Q: What are T-SQL processing modes in SQL Server?
A: SQL Server process any T-SQL statement using either of below two processing modes.
- Row mode execution
- Batch mode execution
In row mode execution, SQL Server processes each row that is being accessed using the T-SQL statement. Row mode execution is very efficient for OLTP workload but it will not give adequate results if you have to process a large number of rows.
In batch mode execution, SQL Server processes multiple rows together in a batch. This mode of execution is very efficient for OLAP workload where a large amount of data needs to be processed.
Q: What do you understand if a specific transaction is showing in a sleeping state?
A: Sleeping state of a SQL Server transaction means work assigned under that transaction has been completed and the connection is waiting for the next command to receive from the client.
High Availability & Disaster Recovery Solutions
Q: What all options are available in SQL Server to achieve high availability and disaster recovery?
A: SQL Server offers various options to achieve high availability and disaster recovery. Below is the list of such features.
- Always on availability groups
- Always on failover cluster instances
- Log shipping
- Database Mirroring
- Replication
- Backup & Restore
Q: What is the difference between availability group and SQL Server replication?
A: Both features are designed for two different purposes.
- Replication is used to replicate data based on your selection of articles at the object level in the database whereas availability groups replicate data for a complete database or group of databases.
- We can control which table will be replicated to the secondary site in replication whereas we don’t have any control at table level in the availability group.
- An availability group supports failover modes whereas replication does not support any failover mode. We can easily switch between replicas in the availability group whereas switching is not possible in replication.
- We need a windows cluster configuration for availability groups whereas replication does not have any dependency on the windows cluster feature.
Q: What is the process to add a data file to a log shipped database?
A: The process of adding a data file to log shipped database is the same as a normal database if both servers (primary and standby) have the same directory structure and files are placed in the identical path. If database files are placed on distinct directory locations on both servers then we need to manually restore the log backup taken after adding the data file on the primary database WITH MOVE statement on the standby database to reinitiate the subsequent logs to apply smoothly.
Q: Can we change the availability mode of the existing Always on availability group configuration?
A: Yes, we can change availability mode using properties window of identified availability group or we can also do it using ALTER AVAILABILITY GROUP statement.
Q: Suppose you have 2 node failover cluster instances with shared storage to support high availability. Let’s assume your storage has become inaccessible then what would be the impact on SQL Server instances?
A: SQL Server databases will become inaccessible as we have only one set of database files hosted on shared storages in the failover cluster configuration.
Q: How do you patch SQL Server cluster instances?
A: We will follow the rolling upgrade approach to apply patches on cluster instances.
- First, make sure to run a full backup of all databases
- Ensure all roles are online from the active node (Node1).
- Apply patches on passive node (Node2)
- Reboot the server (Node2)
- Failover active node (Node1) to newly patched passive node (Node2) to make it active node
- Apply patches on new passive node (Node1) now which was earlier active node
- Reboot the server (Node1)
- Failback SQL Server to the original active node (Node1)
Conclusion
This article has explained SQL Server DBA interview questions and answers on migration & upgrades, performance tuning, and high availability & disaster recovery topics. Please share your feedback in the comment section, also you can let us know if you are looking for interview questions on any specific SQL Server topics.
Last modified: November 01, 2022
