You administer a SQL Server 2008 instance. You plan to deploy a new database to the instance. The database will be subject to frequent inserts and updates. The database will have multiple schemas…

The correct answer is C. Create the database by using a single log file and multiple filegroups. Reasons to take database snapshots include: - Maintaining historical data for report generation. Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database…

Design an optimized database solution

Question

You administer a SQL Server 2008 instance. You plan to deploy a new database to the instance. The database will be subject to frequent inserts and updates. The database will have multiple schemas. One of the schemas will contain a large amount of read-only reference data. You need to design the physical database structure for optimal backup performance. What should you do?

Options

ACreate the database by using a single data file and a single log file.
BCreate the database by using a single data file and multiple log files.
CCreate the database by using a single log file and multiple filegroups.
DCreate the database by using a single log file and a filegroup that has multiple data files.

How the community answered

(24 responses)

A
4% (1)
B
4% (1)
C
79% (19)
D
13% (3)

Explanation

Reasons to take database snapshots include: - Maintaining historical data for report generation. Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter} for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance. - Using a mirror database that you are maintaining for availability purposes to offload reporting. Using database snapshots with database mirroring permits you to make the data on the mirror sewer accessible for reporting, Additionally, running queries on the mirror database can free up resources on the principal. For more information, see Database Mirroring and Database - Safeguarding data against administrative error. - Before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.

Topics

#filegroups#physical database design#backup performance#read-only filegroup

Community Discussion

No community discussion yet for this question.

Full 70-450 Practice