SQL Server is an RDBMS developed by Microsoft. It has two core components – database engine and SQLOS. The database engine has a relation engine that supports processing queries, and managing database files, pages, indexes, etc. And SQLOS is nothing but a SQL Operating system.

There are two authentication modes –

• Windows Mode
•  Mixed Mode

Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page

 The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model. It also tells SQL server which backup is possible in a particular selected recovery model.

There are three types of recovery models:

• Full
• Simple
• Bulk-Logged

 Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.

• COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
• Example –
  Select COALESCE(empno, empname, salary) from employee;

There are four types of triggers and they are:

• Insert
•  Delete
•  Update
• Instead of

 Various tools available for performance tuning are:

• Dynamic Management Views
• SQL Server Profiler
• Server Side Traces
• Windows Performance monitor.
• Query Plans
• Tuning advisor

SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis

 Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.