SQL Administrator Interview Questions & Answers

Download PDF

There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available.

SQL Writer does not support:

☛ Log backups
☛ File and filegroup backup
☛ Page restore

☛ T-SQL; Create Database command.
☛ Using Management Studio
☛ Restoring a database backup
☛ Copy Database wizard

Replication is a feature in SQL Server that helps us publish database objects and data and copy (replicate) it to one or more destinations. It is often considered as one of the High-Availability options. One of the advantages of Replication is that it can be configured on databases which are in simple recovery model.

The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

A Database Administrator needs to know what a database is before they can administer it, right? At its most basic, a database is a collection of tables, structured in such a way that it can be navigated like you would any sort of table. If you remember in math class, you may have had a number of tables that allowed you to quickly find a value if you multiplied an x and y value together — or in this case, what it would be if you were looking for a particular row and column value.

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created/rebuilt.

SQL Writer supports:

☛ Full database backup and restore including full-text catalogs
☛ Differential backup and restore
☛ Restore with move
☛ Copy-only backup
☛ Auto-recovery of database snapshot

default Port No on which SQL Server listens at 1433

The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

You get an idea of how large of a data center the database administrator has worked with. If you're a large organization looking for a DBA for critical systems, the applicant with small business experience may not work out well. What to look for:

☛ Size of company
☛ Number of servers
☛ Type of server environment

This service is used by the full-text search feature of SQL Server. It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.

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 some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.

Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Heartbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk, and MSDTC Disk.

Structured Query Language is the basic way of asking a database server to talk to you. Whether that is in the context of asking it a question, giving it answers to questions it is asking you, or updating answers that have already been stored in the database. The art of asking the right question is critical to getting back the right data you need, which is incredibly valuable when dealing with databases, as it is very easy to receive far more data than you know what to do with, or nothing at all.

This is a generic question often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed.

SELECT in the terms of an SQL query triggers a question to the database. It looks across the specified table(s), finds the data you are looking for and then presents it to the user for consideration. Depending on the query, this can be an awful lot of data, so again, asking the right question is critical.

Setting an autogrowth in multiples of MB is a better option than setting autogrowth in percentage (%).

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.

The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to the desired value there and click OK to save the changes.
The other option of viewing and changing this value is using

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

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.

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.