Always on Availability groups
The ability to use both columnstore and nonclustered indexes in memory-optimized tables makes it much easier to support both OLTP and analytics workloads in the same database.
However, sometimes analytics queries require considerable CPU, IO, and memory resources that might have an adverse impact on OLTP performance.
If you need to support both OLTP and analytics workloads, consider an Always On configuration to offload analytics workloads to a readable secondary.
The In-Memory OLTP feature built into SQL Server 2016 adds a new memory-optimized relational data management engine and a native stored procedure compiler to the platform that you can use to run transactional workloads with higher concurrency.
A memory optimized table is a highly optimized data structure that SQL Server uses to store data completely in memory without paging to disk. It uses hash and nonclustered ordered indexes to support faster data access from memory than traditional B-tree indexes. SQL Server maintains a copy of the data on disk to guarantee transaction durability and to reload tables during database recovery.
The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise.
An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. An availability group fails over at the level of an availability replica.
Example:
You are designing a data warehouse that will be clustered across four servers. Several of the tables in the data warehouse contain transient data that you can rebuild as needed. The schema for the data changes periodically. You must minimize the work required to make schema changes and deploy those changes across the server farm.
Administrators must be able to make the following schema changes: ✑ Adding columns to tables ✑ Removing columns from tables ✑ Adding tables ✑ Removing tables
Changes to the transient data are done as singleton operations. You must make the data available on all the servers in the server farm. Data movement between servers must occur in real time or near real time.
You must be able to run SELECT statements against the data from any server at any time. You need to configure the environment. Which technologies should you implement? To answer, select the appropriate technologies in the answer area.
Answer:
Box 1: Durable in-Memory OLTP
Box 2: Always on Availability groups