What are the interview questions for SQL Server DBA?
SQL Server DBA Interview questions and answers
- Tell me something about yourself and your experience in SQL Server?
- Brief me about your database environment like the number of instances, databases, biggest database size, etc.?
- Explain your backup and database maintenance strategy for your biggest critical OLTP database?
How does log shipping work in SQL Server?
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.
How do I use SQL Server service pack log shipping?
Apply Service pack on the secondary server first and then the primary. And you do not need to stop log shipping. When you apply the service pack on the secondary server, the log shipping from the primary to the secondary will be suspended until the update is finished.
How do I monitor log shipping in SQL Server?
To run the Transaction Log Shipping Status report, execute the following steps:
- Connect to the primary, secondary, or monitor server using Object Explorer in SQL Server Management Studio.
- Right click the SQL Server instance; click Reports.
- Click Standard Reports.
- Click Transaction Log Shipping Status.
How do I prepare for a DBA interview?
Database Administrator Interview Questions
- What types of databases do you work with?
- Do you have experience with on-premises databases, cloud databases or both?
- Can you explain what ODBC is?
- What is the highest number of database servers you have worked with?
- How would you handle data loss during a database migration?
How do you trace the traffic hitting a SQL Server?
How do you trace the traffic hitting a SQL Server? Example: “You use SQL profiler to trace the traffic on the SQL Server instance. To narrow down the transactions that are captured, you can use a filter. The trace files can be searched, saved or even replayed to help with troubleshooting.”
What are the two modes in log shipping?
There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:
- Standby mode – the database is available for querying and users can access it, but in read-only mode.
- Restore mode – the database is not accessible.
Can we take full backup in log shipping?
Thank you Manu Kapoor. You can take diffs or full backups on your Primary server and it doesn’t affect the LSN and therefore does not interfere with the log shipping process as long as the logs are restored in order.
How do you failover in log shipping?
To fail over to a secondary database: Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server. Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).
What is TUF file in log shipping?
TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
How do I view SQL trace logs?
To open the trace file:
- Open SQL Profiler, Start > Programs > Microsoft SQL Server > Profiler.
- Select File > Open >Trace File.
- Navigate to the directory where the trace file was stored and open it.
How do I Analyse a .trace file?
Find the target events.
- Open the trace file or table, and expand the node of the desired event class; for example, Deadlock Chain.
- Search through the trace data until you find the events for which you are looking (use the Find command on the Edit menu of SQL Server Profiler to help you find values in the trace).
How do I repair log shipping?
You will need to initialize log shipping by restoring the full database backup from the primary to the secondary. Once you re-initialize log shipping by performing a full backup of the primary database, copying the backup to the secondary and then restoring the secondary database, log shipping will get re-synced again.
How do you break shipping logs?
Once jobs are running properly we have to perform breaking of logshipping. Again go to properties of the database in PRIMARY SERVER. In Transaction Log shipping uncheck the check box which states enable transaction log shipping. This will disconnect the server.
What if TUF file is deleted?
TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode. This file contains information on all the modifications performed at the time backup is taken. If this file id deleted, you have to reconfigure the LogShipping as the secondary server.
What are DBA challenges?
Management of larger systems Databases are being managed on-premises and cloud which are continuously growing. So, it has become a challenge for a DBA to look after the health of their database systems and resolve issues related to them with their ever-increasing size.
How do I export SQL Server logs?
Within the Log File Viewer select “Current” and any “Archive” entries which cover the dates that logs are needed for. 4. Then click [Export] to save the logs to a file.
How many columns are there in trace file?
2.1 OLD TRACE FILE This type of the trace file has about 21 columns that records each event occurs through simulation time such as event type, event time, and packet number, etc.
What are the top 10 SQL server log shipping interview questions?
Below are the Top 10 SQL Server Log Shipping Interview Questions: 1. Can you be able to technically explain the log shipping process? 2. Have you ever observed the location of log shipping related SQL Agent jobs?
How difficult is log shipping in SQL Server 7?
The amount of data loss, if any, is also dependent on why your production server failed. Implementing log shipping is not technically difficult. Almost any DBA with several months or more of SQL Server 7 experience can successfully implement it.
Can we take full backup of the log shipped database?
Yes it’s possible. We can take full backup of log shipped database and this won’t affect the log shipping. What editions of SQL Server is log shipping available in? Can we take full backup of the log shipped database in secondary server?
Is there any job opportunities for dBa in SQL Server?
There are a lot of opportunities from many reputed companies in the world. According to research on SQL Server, DBA has a market share of about 46%. So, You still have the opportunity to move ahead in your career in SQL Server DBA.