Exam 70-762 topic 1 question 155 discussion

Actual exam question from Microsoft's 70-762

Question #: 155
Topic #: 1

HOTSPOT -
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.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: Durable in-Memory OLTP -
Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with Always On Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.
Box 2: Always on Availability groups
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.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memory-oltp-databases https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

by JohnFan at March 24, 2020, 5:46 a.m.

Comments

Submit Cancel

strikersree

Highly Voted 4 years, 12 months ago

Yes, Non-Durable for transient data and Always on availability group. :)

upvoted 15 times

MML

4 years, 11 months ago

I agree

upvoted 4 times

...

amar111

Highly Voted 5 years, 1 month ago

it should be non durable . as the question says data is transient and can be rebuild when needed

upvoted 6 times

...

tfl

Most Recent 4 years, 5 months ago

It must be durable, as the question states users must be able to run a SELECT and retrieve data from any server at any time. If you use SCHEMA_ONLY, changes on the schema will be replicated, but not the data, as there's no logging. "Secondary replicas maintain the in-memory state of the durable memory-optimized tables. In the event of automatic or forced failover, the time to fail over to the new primary is comparable to disk-bases tables as no recovery is needed. Memory-optimized tables created as SCHEMA_ONLY are supported in this configuration. However, changes to these tables aren't logged so no data will exist in these tables on the secondary replica." https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memory-oltp-databases?view=sql-server-ver15

upvoted 2 times

...

kimalto452

4 years, 6 months ago

answer is correct, SEVERAL table have transient data not all

upvoted 2 times

...

Mikhail_174

4 years, 6 months ago

but they say "several", not "all" tables. may be answer about storage is correct?

upvoted 3 times

BabyBee

4 years, 5 months ago

It says "several" yes, but then it focuses on the transient data.

upvoted 1 times

...

JohnFan

5 years, 3 months ago

Durable. With this type, SQL Server guarantees full durability just as if the table were disk-based. If you do not specify the durability option explicitly when you create a memory-optimized table, it is durable by default. To explicitly define a durable table, use the SCHEMA_AND_DATA durability option. Non-durable. By choosing this type of durability, you instruct SQL Server to persist only the table schema, but not the data. This option is most appropriate for use cases in which data is transient, such as an application’s session state management, or ETL staging. SQL Server never writes a non-durable table’s data changes to the transaction log. To define a non-durable table, use the SCHEMA_ONLY durability option

upvoted 3 times

...