Understanding SQL Server DE

The core and most important service in the SQL Server technology set is the SQL Server Data Engine (DE). This service has the three most important responsibilities aside of storing data:

• Handling recovery: This responsibility means that after any sudden as well as planned breakdown of the service or database, the service will recover every database to its last consistent state without any undone transactions
• Handling transactions: Transaction is mentioned as a single unit of work and SQL Server DE has to guarantee that transactions will be durable and isolated one from another and correctly finished on COMMIT or ROLLBACK
• Handling security: SQL Server DE resolves every request for authentication or authorization and decides if the user or application is known (authenticated) and if the user or application has permission for certain actions (authorization)

SQL Server does not provide its capabilities to end users only, but it's necessary to keep in mind that SQL Server DE serves as a base service not just for users, but also for almost every other service in the SQL Server technology set and it sets the next important points:

• Every BI service such as analysis services or reporting services are actually clients of SQL Server DE.
• Some services like machine learning services can be installed within or independently of SQL Server DE.
• SQL Server agent (not seen in the previous figure) plays an exceptional role in the SQL Server ecosystem. This service exists as an inpisible part of every SQL Server DE.

Why do we need this information? It's one of the crucial moments when planning SQL Server installation. For example, analysis services are heavily resource consuming and their deployment along with SQL Server DE could lead to big performance problems and user disappointment with responses on their requests.