ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam
Go to Exam

Exam 70-764 topic 1 question 33 discussion

Actual exam question from Microsoft's 70-764
Question #: 33
Topic #: 1
[All 70-764 Questions]

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK
INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location.
You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
✑ Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has
EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
✑ Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table.

You need to reduce the amount of time it takes to backup OperationsMain.
What should you do?

  • A. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.
  • B. Modify the backup script to use the keyword SKIP in the WITH statement
  • C. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
  • D. Modify the full database backups script to stripe the backup across multiple backup files.
Show Suggested Answer Hide Answer
Suggested Answer: D 🗳️
One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
by tomzus at Nov. 30, 2019, 1:28 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
huzein
4 years, 6 months ago
I also think that C is correct. Because the only info how backups are performed is with using COMPRESSION. So maybe the data files and filegroups are already getting backed up seperatly, but all with COMPRESSION, we don't know, because there is no info about that. There is no further detail about the backup process. That's why, because of the only info which we have, answer C is correct, performing backup without compression.
upvoted 1 times
Cococo
4 years, 5 months ago
What about this one? - "You should minimize both the time required to restore the databases and the space required to store backups. "
upvoted 1 times
huzein
4 years, 5 months ago
These are all hints. But the info does not say, that it is already taken into consideration for the backup process or not. To improve the backup process it is necessary to first know how backups are performed right now. And about that, we only have one info and that is with compression. Example: I cant optimize a query without knowing the query and the execution plan. Maybe D is correct, i dont know, but then the question is bad formulated in my opinion.
upvoted 1 times
...
...
Hoglet
4 years, 3 months ago
You’re more likely to be IO bound than CPU bound. The overhead in compressing is negligible and reduces the volume of data going over a network and to the disk. Not compressing is only useful with CPU bound system
upvoted 1 times
...
...
Slava_bcd81
4 years, 7 months ago
I think its C, D is possible, but "All backups are written to the network location \\SQLBackup\" and if its one HD there will be no profit from D
upvoted 1 times
Cococo
4 years, 5 months ago
"One of the filegroups is read_only should be as it only needs to be backup up once." + "The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size."
upvoted 1 times
...
Hoglet
4 years, 3 months ago
When using compression you limit the volume of data being written to disk, and going across the network. You are more likely to be disk IO bound than CPU bound, so going to non-compressed is likely to be a bad move
upvoted 1 times
...
...
jolsca
4 years, 7 months ago
Parallel writing of backup could really speed up the backup process. So this is the correct
upvoted 1 times
...
TheSwedishGuy
5 years, 3 months ago
Well, while that's true, the question wasn't to just backup the read/write filegroup, but instead the whole database, including read-only. Therefore, striping across many disks is the solution in this case.
upvoted 1 times
...
tomzus
5 years, 4 months ago
Striping a backup across multiple files is a different operation to backing up individual file groups. Both would give performance \ speed up the duration but the option to backup just the read\write filegroups wasn't presented as an option in the question
upvoted 3 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago