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 –
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:
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.
There are four types of triggers and they are:
Various tools available for performance tuning are:
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.