The industry uses SQL Server extensively for database management requirements. To make the preparation process much more successful, this article provides the top 20 SQL Server DBA interview questions and answers.
SQL Server DBA Interview Questions and Answers for Freshers
1. List a few of the primary authentication methods that SQL Server is compatible with.
Windows authentication and mixed mode are the two primary authentication methods for SQL Server.
- Windows authentication is the default authentication option that allows users to access the system after it has already authenticated them.
- For Windows and SQL Server authentication, database administrators can use mixed mode authentication.
2. Explain the procedures or activities that take place at checkpoints.
The system writes the pages from the buffer cache to disk and checkpoints truncate the transaction log to the beginning of the oldest open transaction. They might also clarify that the cache improves SQL server speed by keeping track of the transactions that engineers commit.
3. Explain DBCC.
Database console commands, or DBCCs, are crucial for preserving the accuracy of data in databases. You can use it to finish jobs like clearing caches, keeping database space, recording transactions, and ensuring that the data in the database is accurate.
4. List the following four SQL Server high-availability solutions.
SQL servers provide four high-availability options:
- Replication
- Log Shipping
- Database Mirroring
- Failover Clustering
5. Define transaction replication.
Transaction replication is a technique wherein the system duplicates the changes made while monitoring transactions as they occur.
6. What is snapshot replication?
Database managers can distribute a data snapshot to any subscriber with snapshot replication, which is great if there isn’t much data that needs to be replicated.
7. Explain merge replication.
Merge replication is a popular replication technique used in client-server scenarios where the server resembles a repository. It incorporates a snapshot for replication seeding and tracks changes.
8. Which of the three DCL commands are you capable of using?
In data-control language (DCL), we have three commands at our disposal: GRANT, REVOKE, and DENY.
9. Identify the four SQL Server database creation methods.
The four methods listed below are what senior-level SQL Server database administrators should be aware of when creating databases on a SQL server:
- Apply T-SQL.
- Utilize SQL Server Management Studio.
- Finish restoring a database backup.
- Make a database wizard copy.
10. How can the traffic that reaches a SQL server be tracked?
The SQL Server tool that allows you to track traffic on the SQL Server instance is called SQL Profiler. Traces can be filtered to reduce the number of transactions that are recorded and the trace’s overhead. To aid in troubleshooting, the trace files can be searched, stored, and even replayed.
11. How do you utilize SQL Agent?
The SQL Server work scheduling system is called SQL Agent. It is possible to plan jobs to execute at a given time or in response to a particular event. Tasks can also be completed as needed. The most common use case for SQL Agent is scheduling administrative tasks like backups.
12. In what ways can you manage the number of spaces on your index pages?
Your indexes’ fill factor is configurable. To re-index, SQL Server needs to know how much free space to leave in the index pages. Because there is room for expansion built into the index, there are fewer page splits (where SQL Server has to copy rows from one index page to another to create a place for an added row), which is a performance gain.
13. Which kinds of indexes does SQL Server support?
“Clustered and Non-Clustered Indexes” are the two categories of indexes. Other index kinds that can be mentioned are spatial, XML, unique, and filtered indexes.
SQL Server DBA Interview Questions and Answers for Experienced
14. Which topologies are available for configuring replication?
Replication can be set up in any architecture as long as the overall complexity and workload are taken into consideration. Any of the following could be it:
- On the same SQL instance, Publisher, Distributor, and Subscriber.
- Subscriber on one instance of SQL, Publisher and Distributor on another.
- For each unique SQL instance, Publisher, Distributor, and Subscriber are involved.
15. What does the clustering setup of Active-Passive and Active-Active mean?
- One cluster node is always active in an Active-Passive cluster because it is a failover cluster set up that way.
- The other node, referred to as the passive node, is constantly online but inactive.
It waits for the active node to fail so that it may take over the SQL Server services and become the active node, turning the former active node into a passive node.
- When two cluster nodes are configured to be active at the same time as a failover cluster, this is known as an active-active cluster.
- It means that when one of the nodes fails, both instances of SQL Server continue to run on that single node until the failed node is brought back up.
It happens after resolving the issue that caused the node failure. Once back on its assigned node, the instance is then failed over.
16. How can a cluster administrator be opened?
You can access the Cluster Administrator console as follows:
Start -> All Programs -> Administrative Tools -> Cluster Administrator, or by typing CluAdmin (case insensitive) into Start -> Run.
17. What distinguishes the two database mirroring working modes from one another?
There are two operating modes for database mirroring: high-performance and high-safety.
In High-Safety Mode, transactions are committed simultaneously on both servers to guarantee consistency, but there may be a temporal lag. This ensures that the principal and mirrored databases are in a synchronized state.
By not waiting for the transactions to be committed by the mirrored database, High-Performance Mode makes sure that the principal database operates more quickly. If the mirror server is under a lot of pressure, there is a small risk of data loss, and the mirror database may not update as quickly as the principal database.
18. Explain transparent data encryption.
Transparent Data Encryption (TDE), which was first introduced in SQL Server 2008, is a method that uses encryption to prevent unwanted access to SQL Server database files. Additionally, TDE can safeguard the instance’s database backups.
19. How can SQL Server 2000 be upgraded to SQL Server 2008?
The safest method would be to upgrade SQL Server 2000 to SQL Server 2008 side by side.
We have two options for doing this: attaching and detaching the database files, or using backup and restore. But since the former is a safer method, it is advised to employ it. The following are the steps to follow:
Launch Microsoft’s Upgrade Analysis tool. Before addressing any concerns brought up there, identify the DTS packages.
- Rebuild the SSIS and DTS packages.
- Write all SQL Agent scripts.
- Script out all security measures
- On the new machine, run the security script.
- Utilize restore on the newly installed system.
- Run DBCC to validate the databases.
- Update every statistic by hand
- Execute the SQL Agent script.
- Make a backup of the systems and verify it by restoring it to a different system, if possible.
20. What is the process for updating a data file’s physical file name?
The actual database file name must be changed in multiple steps. These kinds of tasks should only be completed when necessary, not regularly. These are the general procedures for altering a database’s physical file name.
- Set the database OFFLINE
- Navigate to the folder containing your database files, and rename each file using the naming scheme you’ve chosen.
- Change the names of the files in the system catalog by executing the ALTER DATABASE statement for each file independently.
USE master
GO
ALTER DATABASE SQLSHACK MODIFY FILE (Name=’SQLSHACK_Data1′
FILENAME=’F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSHACK_Renamed.mdf’)
GO
- Launch the ONLINE database
Conclusion
These SQL Server DBA interview questions and answers will help you ace the interviews easily. If you are looking for the opportunity to start your DB career, join us for the best SQL Server DBA training in Chennai.
