Essential SQL Administrator Interview Questions & Answers:

1. Tell us what are the recovery models for a database?

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

2. Do you know which types of backups are not supported by SQL Write Service?

SQL Writer does not support:

☛ Log backups
☛ File and filegroup backup
☛ Page restore

3. Tell us what are the different ways you can create Databases in SQL Server?

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

4. Tell us what is Replication?

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.

5. Do you know what are the different types of Indexes available in SQL Server?

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.

6. Explain me what is a Database?

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.

7. Explain me what is Fill Factor?

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.

8. Tell us what is SQL Server VSS Writer?

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

9. Please explain what is a Database Management System?

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.

10. Do you know what is the default Port No on which SQL Server listens?

default Port No on which SQL Server listens at 1433