SQL Server Interview Questions and Answers for DBA Series 1

Go to the below url the main page:

http://www.sqlserverquest.com/p/sql-server-dba-interview-questions.html

What is Database?

Database is collection of tables which used to store a specific structured data which can be used by the application or user.

What are the system databases and what are their functions?

System database are used to store system information. There are five system databases each one having its own functionality.

1.      Master DB

2.      MSDB

3.      Model

4.      Resource

5.      Temp

Master Database: it stores all the system related information for an instance of SQL Server. It stores the metadata for the database which created in SQL Server Instances.

MSDB Database: it informs the information and activities related to SQL server agent.

Model Database: It is the template to create a new database in SQL server instance .if you have created some object in it will reflect in all database which were created after this until you won’t remove these objects from model database.

Resource Database: Resource database all the system objects views and procedures.

Temp Database: It is used to store temporary objects which create during the execution of query. SQL Server creates a free copy of temp dB whenever server starts. Backup operation is not allowed for the temp DB.

What are the files in SQL Server?

SQL Server stores the data in data files on hard disk.

What are different types of database files in SQL Server?

SQL has three types of database files:

Primary files: Stores the information of the database related to start-up, data and other files details. A database can have one primarily data base file and the file extension for this is .mdf.

Secondary files: Stores the user data. A database can have multiple secondary files which can help user data to spread across the different hard disk.

Transaction logs files: Transaction log files used to store the transactional log.

What are the different states of the database?

Different database states are as below mentioned.

What is a maintenance plan? A maintenance plan is a feature of SQL server that allows for common maintenance tasks to be Consolidated Into a single, scheduled job.  Tasks that can be performed include database backups, shrink operations, Integrity checks, statistic updates, index rebuilds, etc.

What are the system databases and what are their functions?

System database are used to store system information. There are five system databases each one having its own functionality.

1.      Master DB
2.      MSDB
3.      Model
4.      Resource
5.      Temp

Master Database: it stores all the system related information for an instance of SQL Server. It

Stores the metadata for the database which created in SQL Server Instances.
MSDB Database: it informs the information and activities related to SQL server agent.
Model Database: It is the template to create a new database in SQL server instance. if you
have created some object in it will reflect in all database which were created after this until you
won’t remove these objects from model database.
Resource Database: Resource database all the system objects views and procedures.
Temp Database: It is used to store temporary objects which create during the execution of
Query. SQL Server creates a free copy of temp dB whenever server starts. Backup operation is
Not allowed for the temp DB.

Why to normalize a database?

1. eliminates insert and update anomalies by eliminating redundant data
2. eliminates non functional dependencies between data
3. provides a flexible theoretical basis for modeling business processes
4. can improve performance, but not always
5. Helps enforce business rules with declarative referential integrity (DRI)

What is log shipping?
A way to maintain separate database by copying a transaction log from one database
to another. Generally used to maintain database to meet high availability requirements.




What are Simple, Full and Bulk Logged recovery modes?
Simple
The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.
Full
The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.
Bulk-Logged
The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.


What types of replication does SQL Server support?
Snapshot, Merge, and Transactional

What is SAVEPOINT?
A way to rollback a transaction to a particular point.
You create a savepoint using the SAVE TRANSACTION savepoint_name statement, and then
later execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the
savepoint instead of rolling back
to the start of a transaction.
Savepoints are useful where the cost of verifying any update is high, and the probability of
error is low so that the expectation of an error occurring is low.

How do you troubleshoot the failed JOB in SQL Server?

SQL Server agent shows the details of job in SQL Activity Monitor which in turns show the detailed of each failed job and the reason for failure.

What is the default Port No on SQL Server?

The default instance of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact are configured for dynamic ports. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

How many files can a Database contain in SQL Server? How many types of data files exists in SQL Server? How many of those files can exist for a single database?

What is DCL?

DCL or the Data Control Language provides you commands by which you can control access to your data. This includes creating users and granting and revoking permissions to/from them.

What is Replication? What the different types of Replication and why are they used?

Replication distributes data from the central database to one or more target databases, and merges changes from a target database into the central one. Both the source database and the destination database can be implemented as a SQL Server database or as any other data source, as long as an OLE DB provider is available to handle the nitty-gritty details of the particular data source you choose (such as ORACLE, Access, and so on).
The practical reasons to perform replication are to distribute workload and to
synchronize data among remote databases that you want to keep in sync. By maintaining identical data sets across multiple databases you can provide better performance.

Snapshot replication—this type of replication takes a snapshot of the
data in the Publisher database and replaces it with the entire data set of
one or more subscribers; subsequent replication again replaces the complete
data set in the subscriber database(s). Though notable for being virtually
foolproof in providing synchronous data sets, this type of replication
increases network traffic and, as intervals increase, data sets become less
synchronized.
Transactional replication—this type of replication is all about changes.
It propagates changes only to subscribers. It starts with an initial snapshot
Replication and then distributes selected transactions in the Publisher
database transaction log (marked for replication) to the target servers.
Snapshot replication is also regularly scheduled to ensure consistency of
the data. Its major advantages are more timely updates and much lighter
network traffic than you get with pure snapshot replication.
Merge replication—this type of replication allows subscribers who make
changes to their local copies of the data to merge these changes into the
source database. Merge replication is not transactional and relies on conflict
resolution to determine the precedence of the changes.

Can we Perform Backup Restore operation on TEMPDB?

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

What is stored in the mssqlsystemresource database?

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Where the SQL error Logs stores?

View the SQL Server error log by using SQL Server Management Studio or any text editor. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files

What is a Linked Server?

Linked Server is sql server to other remote server and executes SQL queries which can be distributed also.

What is the DAC connection

Microsoft SQL Server provides a dedicated administrator connection (DAC). The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted.

To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name. Object Explorer cannot connect using the DAC.