SQL Server 2014 introduced In-Memory OLTP tables and many of you know the benefits you get with it. This feature allows you to create table in memory either as durable or non-durable and it is only for tables. Do we create in-memory databases? Can we create in-memory databases? Here is this week question based on it?
Which SQL Server component create in-memory databases that are visible for our implementation?
- SQL Server Engine
- SQL Server Analysis Services
- SQL Server Integration Services
- SQL Server Reporting Services
This question can be answered quickly if you have worked on Business Intelligence solutions. Yes, it is related to Analysis Services but for a specific type only. Analysis Services allows us to create two types of models; Multidimensional and Tabular. When working with a Tabular data model project, as you have experienced, it needs a workspace database which is created in Analysis Services tabular instance. This database is in-memory database that keep all data and metadata for the project while it is being developed. When the project is closed, it removes from the memory by default, however it can be controlled by setting Workspace Retention property.