SQL DBA Interview Questions 3008: What is SQL Agent and what would it be used for? What is SQL Agent and what would it be used for?
In this article, I will be taking you through a short trip to help you get ready for your next SQL database administrators job interview if you are a practicing SQL already. Also, it tells you why SQL administration is such a lovely thing to do, thus making SQL a good thing to know. Furthermore, telling Microsoft developers that despite all the great language toolings and framework provisions, having good knowledge of transact SQL is good and adds an extra layer of architectural understanding to any application you come across with, be it in the area of updating an existing application or creating a new one. If you are not into information technology, I will try my best to make you love a Librarian work. Yes, that was not a mistake(Keep that at the back of your mind). I will ensure this article is consumable by you and not going all technical about the description of a very technical field. Follow me as I try to look into the expected questions when applying for a database administrator’s role. And why I think the discussed questions are important for the role you are applying for. And also, talk about how you should go about answering them. Join me as I discuss the above topic
What Is SQL?
SQL is a technical term abbreviation that stands in full for Structured Query Language. It is a standard and accepted language for manipulating RDBMS (Relational Database Management System). Started in Oracle Lab by Oracle’s researchers Raymond Boyce and Donald Chamberlin, There are a couple of them now, but the very popular ones are the MSSQL and the MYSQL. MSSQL was developed by Microsoft, while Oracle developed MySql. Just in case you need clarification, let me jump back to introducing the reason for this topic, DATABASE. What is a database? According to Microsoft, A database is a set of tools for collecting and managing data.
Imagine a big warehouse used by High School A for the storage of their students’ information. It is managed by a man who makes sure the shelf is well maintained to avoid breakage and records damage, and arranges the records and shelves alphabetically or according to their subject matter. Then, make sure the records don’t get missed or manipulated negatively. The warehouse has shelves for record-keeping, ladders, so the storekeeper should access the records and a duster to keep the shelves dust-free. We can collectively call that warehouse a Database. Then call the storekeeper a DBA (Database Administrator). School A is trying to go from a traditional record-keeping system to using RDBMS, and these interview questions will be thrown during their interview section with A Database Administrator. I will go directly into the questions and then try to answer them.
- What is DBCC?
Answer: DBCC, which rightly stands for Database Console Command. And it is grouped into groups of commands: the Database maintenance command, Database Information command, Database Validation command, and the Database Miscellaneous command.
Database Maintenance Command
Just like the routine dusting and checkups by the Librarian aimed to keep the data in order, like dusting the shelves and trying to make more space for incoming records, A database has maintenance tasks or operations to run. An example will be Shrinking, clearing of cache, etc. According to (https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files/, “Shrinking a SQL Server database is a process in which unused space that was previously in use at some point is being removed from the database.” The next expected question is; When is the best time to use the shrink console command? But did I mention that this command is almost defunct and rarely used? Except for there are cases when it becomes the only valid solution for the problems of space management. So the proper times to go the shrink way are;
- When there has just been a data archiving job.
- After dropping large numbers of database tables
- After there are changes in datatypes of most of the database’s tables and object
Database Information Command
The bulk pain in data management is keeping up with fast, ever-changing data. This is where this category of command comes in. It gives the dba the power to command up vital information needed for his tasks quickly. An example of such information will be the Log information of a database.
Database Validation Command
These are commands used for the checking of a database’s integrity and consistency. This is very important, as the biggest worry of a DBA is Data Safety. So this command set comes in handy for quick checks and validations.
SQL DBA Interview Questions
These sets of commands do not fit into any of the above-mentioned 3 categories. They do nothing manipulative or very technical to the database. An example of such a command is the Help, Buffer, and show statistics commands.
- How many files can a database container, and what are the types of files allowed within a database, And how many of those files can exist in a database?
Answer: Just like a traditional file keeping method, a record keeper must know the capacity of his book store, although this is approximate when talking about flat files. But in the case of a Relational Database System, the numbers of files are fixed and only changes as general industry standards approve. And the number of files is 32,767 files.
There are two major files in a database viz; the primary data file and secondary data files. The primary data files; are files with the MDF file extensions. There are the main data stores, and there can be just one of such files in a database. On the other hand, We have the NDF or secondary data files. There can be more of this file type in a database. And mostly used for data replication and also for extending the MDF file capacity.
- What is SQL Agent and what would it be used for?
Answer: SQL Agent is an inbuilt medium for scheduling tasks. This would be similar to a Liberian having an apprentice and then delegating some routine jobs to him. Similarly, SQL Agents are given “Jobs” to execute, and these jobs are done at scheduled intervals. An example of such a job would be a routine backup.
- Explain your experience as SQL DBA.
Answer: As a practicing Database Administrator, I have worked in large organizations, having various data challenges and using different distros of the database. Ranging from Postgres, MSSQL, MYSQL, etc. From my past experiences, pressure has always been on the horizon, for the fact that you are sitting on the most important aspect of any company’s assets, namely, its data. To keep the job smooth and a little stress-free, DevOps for Databases are used. I try to keep to best practices and make sure that I don’t just leave the job for those under me, or even an AGENT. I try my best to double-check and even revalidate the data against an already built-in stored procedure. Thus always keeping my neck above waters, although it seems to be due to steady swimming.
From my experiences, I can say that no two firms have the same data challenge. I can also say that you will always be on a hot seat. Just make sure that you have enough coolant. Remember that there will always be problems. Wake up every morning with the mentality that you are the boss and problems are nothing too much to handle.
- Have you had to work with product designers or backend developers?
Answer: Yes, this is mostly the case. Few of my best colleagues are backend developers, and my best friends are too. I am always needed in the brainstorming section because backend developers know the need for normalization. We all truly see the database for what it is; The foundation of the application. And coming from the background of transact SQL, they all trust my skills in programming. And they all trust that things will be done in their right order.
We fight a few times when they make mistakes that touch the integrity of my data. Like not validating inputs while they take it. Or when they don’t insert into certain important fields. Working directly with backend developers is fun, and to be truthful, my work has always been smoother that way because most of what causes panic are from their end, and controlling them means controlling most events.
- What major difference have you noticed in SQL versions. And how did you handle them?
Answer: It would be hard to notice differences in versions, although there are always there. We DBAs’always have our ways around things. The subtle differences have been on the UI, especially when it comes to data migration. Some versions don’t take files from some versions. Especially when a higher version is trying to give files to a smaller version(backward compatibility). The best way around this is to use the console.
This one time, I had little time, and I was to move data to an MSSQL on godaddy.com. The SQL version I was using was MSSQL 2008, and Godaddy just upgraded theirs to 2012 or 2014 at a time. I could not connect to the remote server from my system. This was annoying as there would be no time to download a new SQL Server. Doing everything from the console(cmd) was the only option, unplanned and sweating for me.
- Shortly, explain DCL
Answer: DCL means in full; Data Control Language. Imagine the large tone of information laying on school A’s record store. Nobody should access records of fees, personal student bios, events, teachers records, etc., information of this value. So flat-file keepers would use access cards and access keys, while some would have volts with special locks on them.
Similarly, The Data Control Language acts as a security measure to check who and who has access to which data on which schema on the database. Examples of such language are the Grant, Revoke, Deny structured statements.
- Discuss the importance of recovery models
Answer: Recovery models are database properties created to minimize data loss and control how data is stored. Data safety can not be over-stressed, and data loss should not even be heard of. Data recovery models help create databases that store data according to the recovery model’s set properties. Every database has 3 recovery model options viz;
Full recovery models, Bulk-Logged recovery models, and Simple recovery models.
- Discuss briefly the different types of replication
Answer: There are 3 types of replication in SQL, namely, Snapshot replication, Merge replication, and Transactional replication. These replications work differently according to the type of problems you are trying to solve. If you need a near-real-time feed of your database, you will go with the Transactional replication. If it is in the case of weekly summary, you would likely opt for Snapshot Replication. If it is in a case of a distributed system, where some systems would likely be offline and then sync later, Merge Replication is the best solution.
- Database mirroring has 2 operating modes, what is the difference between them?
Answer: The High-safety mode is slower because it takes time to ensure that the mirrored database records data simultaneously or almost at the same time as the principal database. There is a lower risk of data loss in this model. On the other hand, the High-performance mode puts the principal database first, making sure it does not wait for the mirrored database. There is bound to be data loss in this mode if there is any pressure on the mirror database. But there is the great speed in data input, thereby leading to good and speedy data interaction.