SQL DBA Interview Questions
Below are the MS SQL DBA interview questions, we will add the questions in the future.
Daily routine or daily activities when you log in
The daily routine of a SQL database administrator (DBA) varies depending on the size and complexity of the organization’s database infrastructure. However, there are some general tasks that most DBAs perform daily.
1. Monitor server health and performance: DBAs start their day by checking the health and performance of the SQL Server they manage. This includes checking for errors in the event log, monitoring resource usage (CPU, memory, disk I/O), and reviewing performance metrics such as response times and throughput.
2. Verify backups: DBAs ensure that daily, weekly, and monthly backups of all databases are completed successfully and that the backups are stored securely. They also periodically test the restore process to ensure that they can restore the databases in case of data loss.
3. Analyze queries and troubleshoot performance issues: DBAs analyze query execution plans to identify and troubleshoot performance bottlenecks. They may also need to rewrite queries to improve performance or implement indexing strategies to improve query execution times.
4. Manage user permissions: DBAs manage user permissions to ensure that authorized users have access to the data they need while unauthorized users are restricted. This includes creating and managing users, assigning permissions to users and groups, and reviewing access logs.
5. Apply patches and updates: DBAs apply security patches and software updates to the SQL Server servers they manage to protect against vulnerabilities and ensure that the servers are running with the latest features and fixes.
6. Respond to user requests and incident reports: DBAs respond to user requests for data access, troubleshooting assistance, and performance optimization. They also investigate and resolve incidents such as database corruption, application failures, and security breaches.
7. Document database changes and procedures: DBAs document all changes they make to the database schema, configuration settings, and security procedures to maintain a clear record of the database infrastructure and facilitate communication with other team members.
8. Stay up-to-date with industry trends and new technologies: DBAs continuously educate themselves about new SQL Server features, best practices, and emerging technologies to ensure that they are equipped to manage the database infrastructure effectively.
What is a service broker in an SQL server?
Service Broker is a messaging and queuing mechanism in SQL Server that allows applications to communicate with each other asynchronously and reliably. It provides a framework for developing distributed applications that can be loosely coupled and highly scalable.
Key Features of Service Broker:
Asynchronous Communication: Applications can send messages to each other without having to wait for a response. This allows applications to be more responsive and efficient.
Reliable Delivery: Service Broker guarantees that messages are delivered once and only once to the intended recipient. This ensures that data is not lost or corrupted.
Transactional Support: The service Broker supports transactions, which means that a message is not delivered until all of the related operations have been completed successfully. This ensures data integrity.
Loose Coupling: Service Broker allows applications to communicate with each other without having to know the details of the other application’s implementation. This makes applications more flexible and reusable.
Scalability: The service Broker can handle a large number of concurrent message exchanges. This makes it suitable for applications that need to support a high volume of traffic.
Common Use Cases for Service Brokers:
Order Processing: Service Broker can be used to process orders from multiple applications. For example, an order processing application could send a message to a payment processing application to verify the customer’s credit card.
Workflow Management: Service Broker can be used to manage workflows that involve multiple steps. For example, an application could use a Service Broker to send messages to different applications to approve a purchase order.
Data Synchronization: Service Broker can be used to synchronize data between different applications. For example, an application could use a Service Broker to send messages to a data warehouse to update the warehouse with the latest data from a production database.
Overall, Service Broker is a powerful and versatile tool for developing distributed applications. It is a valuable addition to the SQL Server ecosystem and is well-suited for a wide range of applications. (SQL DBA Interview Questions)
Fixed roles, server level, and database level
In SQL Server, there are two types of fixed roles: server-level roles and database-level roles.
Server-level roles have permissions that apply to the entire SQL Server instance, while database-level roles have permissions that apply to a specific database.
Here is a table that summarizes the key differences between server-level roles and database-level roles:
| Feature | Server-level roles | Database-level roles |
|---|---|---|
| Scope of permissions | Entire SQL Server instance | Specific database |
| Created by | SQL Server | Database administrator |
| Managed by | SQL Server | Database administrator |
| Permissions | Can perform any action on the SQL Server instance | Can perform actions on objects within the database |
Read More: Difference between SQL Server Roles and Database Roles
What is a synonym in SQL server and how to create a synonym?
a synonym is a virtual object that provides an alternative name for an existing database object. Synonyms are primarily used to simplify data access and improve the readability of SQL queries. They act as aliases for existing objects, allowing users to refer to them using a more concise and meaningful name.
Synonyms offer several advantages, including:
- Improved Readability: Synonyms make SQL queries more readable and easier to understand, especially for complex queries involving multiple objects.
- Flexible Data Access: Synonyms allow users to access objects located in different databases or even on different servers, enhancing data accessibility.
- Security Enhancement: Synonyms can be used to restrict access to objects by granting permissions to the synonym instead of the underlying object, providing an additional layer of security.
- Logical Object Organization: Synonyms can be organized logically in a database schema, making it easier to manage and maintain references to objects.
Creating a synonym in SQL Server involves the following steps:
(SQL DBA Interview Questions)
- Establish Connection: Connect to the SQL Server instance using SQL Server Management Studio (SSMS) or the Transact-SQL (T-SQL) language.
- Specify Synonym Definition: Use the
CREATE SYNONYMstatement to define the synonym. The statement includes the following parameters:
synonym_name: The name you want to use for the synonym.object_name: The name of the object you want to reference.schema_name: The schema in which the object resides, if applicable.server_name: The name of the server where the object resides, if different from the current server.
- Granting Permissions: Grant the necessary permissions to the synonym to allow users to access the underlying object. For instance, if the synonym refers to a table, grant SELECT permissions to the synonym.
- Using Synonyms in Queries: Once created, synonyms can be used just like any other object in SQL queries. For example, you can refer to the synonym in SELECT, INSERT, UPDATE, and DELETE statements.
Here’s an example of creating a synonym for a table named ‘customers’ in the schema ‘sales’:
| SQL |
|---|
| CREATE SYNONYM my_customers FOR sales.customers; |
To use the synonym in a query, you would write:
| SQL |
|---|
| SELECT * FROM my_customers; |
Synonyms are a valuable tool in SQL Server that simplifies data access, enhances readability, and improves data security. They provide flexibility in data management and organization, making them an essential component of relational database management.
What is the default size of .MDF and .LDF in SQL server?
The default size of the .mdf (primary data) and .ldf (transaction log) files in SQL Server is 8 MB. However, this can be changed to a different size when creating a new database. The default setting is sufficient for small databases, but larger databases may require larger file sizes. You can also change the file size later by using the ALTER DATABASE statement. (SQL DBA Interview Questions)
Here is an example of how to change the file size of the .mdf file:
| SQL |
|---|
| ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyData, SIZE = 1024MB); |
This will change the size of the .mdf file to 1024 MB. The .ldf file will automatically be resized to the same size as the .mdf file.
Here are some factors to consider when choosing a file size:
- The amount of data you expect to store in the database
- The number of concurrent users who will be accessing the database
- The performance requirements of your applications
It is important to test your database with different file sizes to ensure that it is performing as expected.
How to migrate a 5 TB database with low downtime?
Migrating a 5 TB database with low downtime requires careful planning and execution. Here’s a comprehensive approach to minimize disruption to your applications and users:
1) Planning and Preparation
- Assessment and Preparation: Thoroughly assess the database’s structure, data distribution, and performance characteristics. Identify any potential bottlenecks or areas requiring optimization.
- Tool Selection: Choose appropriate migration tools that can handle the size and complexity of the database while ensuring data integrity and minimal downtime. Consider tools like SQL Server Data Tools (SSIS), Azure Data Factory, or third-party migration tools.
- Environment Setup: Set up the target environment, where the migrated database will reside. Ensure the target server has sufficient hardware resources, storage capacity, and network bandwidth to handle the database’s workload.
2) Data Migration
- Data Staging: Create a staging area on the target server to temporarily store the migrated data. This will allow for seamless switching and minimize downtime.
- Data Extract: Extract the data from the source database in a staging-friendly format, such as CSV or Parquet. This may involve using data masking or transformation techniques to protect sensitive information.
- Data Transformation: If necessary, transform the data in the staging area to conform to the target database schema and data types. This may involve data cleansing, data type conversion, or normalization.
- Data Load: Load the transformed data from the staging area into the target database. Use parallel loading techniques to optimize performance and reduce overall migration time. (SQL DBA Interview Questions)
3) Application and Configuration Updates
- Update Application Connections: Modify application connections to point to the new target database. This may require updating database URLs or configuration files in the applications.
- Change DNS Records (Optional): If migrating to a different server, update DNS records to point to the new database server’s address. This will ensure seamless access for applications and users.
- Verify and Test: Thoroughly test the migrated database to ensure data integrity, application compatibility, and performance. Address any issues identified during testing before rolling out the migration to production.
4) Cutover and Maintenance
- Gradual Cutover: Gradually shift application workloads from the source database to the target database. This may involve using load balancers or gradual application restarts to minimize downtime.
- Final Cutover: Once the target database has fully assumed the application load, mark the source database as read-only or offline. Gradually decommission the source database once all data and configurations are fully migrated.
- Post-Migration Maintenance: Monitor the performance and stability of the migrated database. Regularly back up the database and implement data protection measures to safeguard against data loss.
What are DMVs in the SQL server?
(SQL DBA Interview Questions)
Dynamic Management Views (DMVs) are special database objects in SQL Server that provide real-time information about the state of the SQL Server system. They are like snapshots of the server’s current health and performance, and they can be used to identify and troubleshoot problems, optimize performance, and monitor resource usage.
DMVs are a powerful tool for SQL Server administrators, and they are used by a variety of other applications, including monitoring tools, reporting tools, and backup solutions.
Here are some of the benefits of using DMVs:
- Real-time information: DMVs provide real-time information about the state of the SQL Server system. This means that you can see what is happening on the server right now, without having to wait for a scheduled report or performance monitor poll.
- Performance and scalability: DMVs are designed to be highly performant and scalable. This means that they can be used to monitor large and complex SQL Server systems without impacting performance.
- Security: DMVs are secure and can only be accessed by authorized users. This helps to protect sensitive data from unauthorized access.
Here are some examples of how DMVs can be used:
- Identifying performance bottlenecks: DMVs can be used to identify performance bottlenecks on the SQL Server system. This can help you to optimize performance and improve the response time of your applications.
- Troubleshooting problems: DMVs can be used to troubleshoot problems that are affecting the SQL Server system. This can help you to identify the root cause of the problem and resolve it quickly.
- Monitoring resource usage: DMVs can be used to monitor resource usage on the SQL Server system. This can help you to identify and prevent resource contention.
DMVs are a valuable tool for SQL Server administrators, and they can be used to improve the performance, reliability, and security of your SQL Server system.
(SQL DBA Interview Questions)
What is table seek and scan in the SQL server?
A table scan is a method of retrieving all rows from a table. It is the simplest way to retrieve data from a table, but it can be very inefficient, especially for large tables. This is because the table scan must read every row in the table, even if only a few rows are needed.
Table seek is a more efficient method of retrieving data from a table. It uses an index to locate the specific row or rows that you want to retrieve. This is much faster than a table scan because the index only needs to read the rows that are relevant to the query.
Here is a table that summarizes the differences between table scans and seeks:
| Method | Description | Advantages | Disadvantages |
|---|---|---|---|
| Table scan | Retrieves all rows from a table. | Simplest method. | Inefficient for large tables. |
| Table seek | Retrieves specific rows from a table using an index. | More efficient than a table scan. | Requires an index. |
Here is an example of how to perform a table scan and a table seek in SQL Server:
Table scan:
| SQL |
|---|
| SELECT * FROM Customers; |
Table seek:
| SQL |
|---|
| SELECT * FROM Customers WHERE CustomerId = 1; |
In this example, the first query will retrieve all rows from the Customers table. The second query will only retrieve the row where the CustomerId is 1.
In general, you should use a table seek whenever possible. This will improve the performance of your queries, especially for large tables.
Can we back up system databases?
(SQL DBA Interview Questions)
Yes, you can backup system databases, but it is generally not recommended. System databases contain essential information about your SQL Server instance, such as the database configuration and system state. If you corrupt or lose a system database, it can be very difficult or even impossible to restore your SQL Server instance.
Here are some reasons why you should not backup system databases:
- System databases are constantly changing. This means that any backup you take will quickly become outdated and unusable.
- System databases are very large. This means that backing them up can be time-consuming and resource-intensive.
- Restoring a system database from a backup can be very difficult. This is because system databases are complex and many things can go wrong during the restore process.
If you do need to back up a system database, it is important to do so only as a last resort and to take the following precautions:
- Take a differential backup instead of a full backup. This will only back up the changes that have been made to the database since the last backup.
- Test the backup before restoring it to production. This will help to ensure that the backup is valid and can be restored successfully.
Here are some alternative solutions to backing up system databases:
- Use SQL Server data replication to replicate the system database to another server. This will allow you to restore the database from the replicated copy if you need to.
- Use a database mirroring solution to mirror the system database to another server. This will allow you to failover to the mirrored copy of the database if the primary server fails.
If you do choose to back up a system database, be sure to consult the Microsoft documentation for specific instructions.
Read More: The Importance of Backups in SQL Server
Types of isolations in the SQL server
In SQL Server, isolation levels are a set of rules that govern how transactions interact with each other in a multi-user database environment. Isolation levels determine which data changes each transaction can see, and how those changes are applied to the database.
Types of isolation levels:
- Read uncommitted: This is the lowest isolation level. At this level, transactions can see incomplete or uncommitted changes made by other transactions. This can lead to dirty reads, where a transaction sees data that has not been committed and may be rolled back.
- Read committed: This isolation level prevents dirty reads, but it allows for non-repeatable reads. At this level, transactions can see committed changes made by other transactions, but those changes may be updated by other transactions before the first transaction re-reads the data. This can lead to a transaction seeing different data the second time it reads it.
- Repeatable reads: This isolation level prevents both dirty reads and non-repeatable reads. At this level, transactions can see committed changes made by other transactions, and those changes will remain consistent when the transaction re-reads the data. This is considered a good level of isolation for most applications.
- Serializable: This is the highest isolation level. At this level, transactions are completely isolated from each other. This means that no transaction can see any changes made by other transactions until those changes have been committed. Serializable isolation is the best level of isolation for applications that require very strict data integrity, but it can also be the most expensive in terms of performance. (SQL DBA Interview Questions)
The choice of isolation level depends on the specific needs of the application. Applications that require a high level of data integrity, such as banking applications, should use a higher isolation level, such as repeatable reads or serializable. Applications that are less sensitive to data integrity, such as web applications, can use a lower isolation level, such as read committed or even read uncommitted.
Here is a table that summarizes the different isolation levels:
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
|---|---|---|---|
| Read uncommitted | Yes | Yes | Yes |
| Read committed | No | Yes | Yes |
| Repeatable reads | No | No | No |
| Read Committed | No | No | No |
Current patch number
The current patch number for SQL Server depends on the specific version of SQL Server that you are running.
Here are the current patch numbers for the most recent versions of SQL Server:
- SQL Server 2022: 2022 CU5 (Build 2023.8.9)
- SQL Server 2019: CU22 (Build 15.0.5076.19)
- SQL Server 2017: CU24 (Build 14.0.7005.21)
- SQL Server 2016: CU21 (Build 13.0.7044.21)
- SQL Server 2014: CU56 (Build 12.0.6061.8)
You can always find the latest patch numbers for SQL Server on the Microsoft Download Center.
Can we backup tempdb in the MS SQL server?
(SQL DBA Interview Questions)
Yes, you can backup the tempdb database in SQL Server. However, it is not a recommended practice. Tempdb is a system-managed database that is automatically created and maintained by SQL Server. It is used to store temporary data and objects that are created by users and applications.
There are several reasons why you should avoid backing up tempdb:
- Tempdb is constantly changing: Tempdb is a volatile database that is constantly being updated and overwritten. Backing up a tempdb database will quickly become outdated and unusable.
- Tempdb is very large: Tempdb can grow to be very large, especially in environments with high concurrency. Backing up a large tempdb database can be time-consuming and resource-intensive.
- Restoring a tempdb database is difficult: Restoring a tempdb database is not as straightforward as restoring a regular database. There are several steps that must be taken to ensure that the restored tempdb database is compatible with the current SQL Server instance.
It is generally better to let SQL Server manage tempdb automatically. SQL Server will automatically create and maintain a fresh tempdb database whenever it is needed. This ensures that tempdb is always up-to-date and optimized for performance.
What are P-1 level issues you have faced?
P1 issues are the highest priority issues in the IT world. They are critical issues that can have a significant impact on the business if they are not resolved immediately. P1 issues require immediate attention from the most senior IT staff and may require the involvement of external vendors or consultants.
Here are some examples of P1 issues that I have encountered:
- Data corruption: Data corruption can cause severe problems, such as loss of data or inability to access data. It is important to take immediate action to restore the corrupted data or recover from the loss.
- Application outages: Application outages can prevent users from accessing critical systems and applications. They can also cause a loss of productivity and revenue. It is important to restore application services as quickly as possible.
- Security breaches: Security breaches can allow unauthorized users to access sensitive data. They can also damage the reputation of the organization and lead to legal liabilities. It is important to investigate security breaches and take steps to prevent them from happening again.
- Infrastructure failures: Infrastructure failures can cause major disruptions to the IT environment. They can also lead to data loss and downtime. It is important to identify and resolve infrastructure problems as quickly as possible.
- Performance bottlenecks: Performance bottlenecks can make it difficult to use IT systems and applications. They can also lead to frustration and dissatisfaction among users. It is important to identify and address performance bottlenecks to improve the overall performance of the IT environment. (SQL DBA Interview Questions)
When dealing with P1 issues, it is important to follow these steps:
- Acknowledge the issue: As soon as you are aware of a P1 issue, acknowledge it to the appropriate stakeholders.
- Prioritize the issue: Determine the severity of the issue and prioritize it accordingly.
- Investigate the cause: Identify the root cause of the issue to develop a solution.
- Implement a solution: Implement a solution to resolve the issue.
- Communicate the status: Keep stakeholders informed of the status of the issue.
- Prevent recurrence: Take steps to prevent the issue from happening again.
By following these steps, you can effectively manage P1 issues and ensure that your IT environment remains stable and reliable.
Have you worked on Powershell scripting?
Yes, I have worked on PowerShell scripting. I can:
- Write PowerShell scripts to automate tasks, such as managing files and folders, configuring systems, and interacting with web services.
- Use PowerShell modules to extend my capabilities and access functionality that is not available in the core PowerShell language.
- Troubleshoot PowerShell scripts and identify and fix errors.
- Document PowerShell scripts to make them easy for others to understand and use.
I can also help you get started with PowerShell scripting by providing examples of scripts and tutorials.
Database connectivity with Powershell
(SQL DBA Interview Questions)
Yes, you can connect to databases using PowerShell. The SqlServer module provides PowerShell cmdlets for connecting to SQL Server instances, managing databases and objects, executing Transact-SQL (T-SQL) statements, and retrieving data.
To connect to a SQL Server instance using PowerShell, you will need to install the SqlServer module. You can install the module from the PowerShell Gallery using the following command:
| PowerShell |
|---|
| Install-Module -Name SqlServer |
Once the module is installed, you can connect to a SQL Server instance using the Connect-PSSession cmdlet. For example, to connect to a SQL Server instance named “MyServer” with the credentials “username” and “password”, you would use the following command:
| PowerShell |
|---|
| Connect-PSSession -ComputerName MyServer -SqlServerInstanceName MyInstance -Credential (New-Object System.Management.Automation.PSCredential(“username”, (ConvertTo-SecureString “password” -AsPlainText -Force))) |
Once you are connected to a SQL Server instance, you can use the SqlServer module cmdlets to manage databases and objects, execute T-SQL statements, and retrieve data. For example, to retrieve all the tables in the “MyDatabase” database, you would use the following command:
| PowerShell |
|---|
| Get-SqlTable -ServerInstance MyServer -DatabaseName MyDatabase |
Here are some examples of PowerShell scripts that you can use to connect to databases: (SQL DBA Interview Questions)
- Connect to a SQL Server instance and retrieve all the tables in the “MyDatabase” database:
| PowerShell |
|---|
| Connect-PSSession -ComputerName MyServer -SqlServerInstanceName MyInstance -Credential (New-Object System.Management.Automation.PSCredential(“username”, (ConvertTo-SecureString “password” -AsPlainText -Force))) Get-SqlTable -ServerInstance MyServer -DatabaseName MyDatabase |
Connect to a SQL Server instance and create a new table:
| PowerShell |
|---|
| Connect-PSSession -ComputerName MyServer -SqlServerInstanceName MyInstance -Credential (New-Object System.Management.Automation.PSCredential(“username”, (ConvertTo-SecureString “password” -AsPlainText -Force))) New-SqlTable -ServerInstance MyServer -DatabaseName MyDatabase -TableName MyNewTable -Columns @{ColumnName = “Id” ; Datatype = “int” ; IsIdentity = $true; IsNullable = $false} |
Connect to a SQL Server instance and execute a T-SQL statement:
| PowerShell |
|---|
| Connect-PSSession -ComputerName MyServer -SqlServerInstanceName MyInstance -Credential (New-Object System.Management.Automation.PSCredential(“username”, (ConvertTo-SecureString “password” -AsPlainText -Force))) Invoke-Sqlcmd -ServerInstance MyServer -DatabaseName MyDatabase -Query “SELECT * FROM MyTable” |
These are just a few examples of the many ways that you can use PowerShell to connect to and manage databases. The SqlServer module provides a powerful and versatile set of tools that can be used to automate a wide range of database tasks. (SQL DBA Interview Questions)
- What is the role of DBA in migration?
- How do you resolve high CPU utilization in SQL servers?
- How do you resolve high Memory utilization in SQL servers?
- How to find long-running queries in the SQL servers?
- How to upgrade the SQL server?
- How to patch the SQL server?
- What are high availability solutions available in the SQL servers?
- Explain all high availability solutions.
- Difference between high availability solutions.
- How to plan a backup strategy?
- What is copy-only backup?
- Why do we take copy-only backups?
- How to resolve the tempdb full issue?
- How do synch logins in the always-on availability group?
- What is the execution plan in the SQL server?
- How to take backup of a database in multiple locations or distributed locations?
- What is the Basic Always on Availability Group?
- What is the Distributed Always on Availability Group?
- How to migrate Linked Server?
- What is TDE and how to configure it? (SQL DBA Interview Questions)
- How to configure Always on Availability Group?
- What is Stats and how does it helps to improve the MS SQL Server performance?
- What happens, when we update stats?
- What is the MAXDOP parameter?
- What are the pre-requisites for installing the MS SQL Server?
- Performance tuning activity
- How do change file locations of system databases?
- Default locations of system database files?
- What are RTO and RPO in MS SQL Server?
- What is MFA in MS SQL Server?
- How do recover logins in case MS SQL Server needs to recreate from the scratch?
- How do check deadlocks and resolve them?
- What is the execution plan?
- What is the cover index?
- On which columns create the non-clustered index?
- How to re-create the MS SQL Server using only database backups?
- What are pages in MS SQL Server? & types of pages in MS SQL Server?
- How to patch MS SQL Server in always on high availability configuration?
(SQL DBA Interview Questions)