SQL Administrator

This question tests the technical knowledge of your applicant and is directed at people you're hiring for mid-level positions. Look for an answer that covers the basic concept and goes into some detail about its role in a database environment. What to look for:

☛ Thorough understanding of this acronym

☛ Good communication

☛ Use case examples

Row compression and Page compression.

Many types of database infrastructure exist, so you want to confirm that the applicant has the background that you're looking for. Look for people who mention the specific database versions they're familiar with, as well as a brief explanation of their experience level with each. What to look for:

☛ Detailed information

☛ Years of experience

☛ Brief overview of duties

A Database Management System, or DBMS, is essentially the application that handles the heavy lifting between you (the user), and the raw data. The database itself is just that — the database; it cannot alter its own data any more than the average person can re-arrange their genetic code. The DBMS is what you are talking to when you are asking the questions. It is what looks at your question, thinks about it for a while, goes to the database, picks up the data, hands it back to you, and asks you to come again.

This service acts as a listener for the incoming requests for Microsoft SQL Server resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list of servers, locating and connecting to the correct server.

This listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.

Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.

Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using

SELECT * FROM sys.dm_exec_requests where blocked <> 0

SELECT * FROM master..sysprocesses where blocked <> 0

Deadlock: When something happens as follows: Error 1205

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

No, you cannot have a Distributor on a previous version than the Publisher.

DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

SQL Server Agent is the primary scheduling engine in SQL Server. This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to Backup, Restore, Log Shipping, SSIS Packages etc.

Many organizations are moving from a fully on-premises infrastructure to the cloud. You can discover which environment your candidate works the best in. If your organization intends on changing away from your current configuration, you can find out whether your interviewee can support your long-term goals. What to look for:

☛ Strong understanding of infrastructure differences

☛ Flexibility

☛ Willingness to learn

The answer would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

Connect using DAC via CMD or SSMS

☛ Connect via CMD

☛ SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster

☛ Once you connect to the master database run the diagnostic quires to find the problem

☛ Correct the issue and restart the server

A query in normal terms is a question, simple enough. It is the statement that is talking to the database in order to Create, Read, Update or Delete (CRUD) data. While many times a query is an actual question asking for an answer, it can also be the statement to modify, insert, or remove data in the database as well.

In-place upgrade and Side-by-Side Upgrade.