Microsoft 70-765 Provisioning SQL Databases Exam Practice Test

Page: 1 / 14
Total 267 questions
Question 1

You have a server named server1-contoso.database.windows.net that has a Microsoft Azure SQL database.

You need to create a group of Azure SQL databases that share the resources on server1-contoso.database.windows.net.

Which cmdlet should you run before you create the database?



Answer : D

The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database.

SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.


Question 2

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server hosts a database named Orders.

Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.

You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.

Which Transact-SQL command should you use?



Answer : C

Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it's able to just stop where it is (it doesn't have a giant operation to rollback).

References:https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/


Question 3

You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.

You need to implement real-time encryption of the database and all backups.

Solution: You password protect all azure SQL backups and enable azure active directory authentication for all azure SQL server instances.

Does the solution meet the goal?



Answer : B

Password protection does not encrypt the data.

Transparent Data Encryption (TDE) would provide a solution.

References:

https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/


Question 4

You administer a Microsoft SQL Server 2014 database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.

Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.

You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.

Which type of solution should you use?



Answer : A

Partitioning large tables or indexes can have manageability and performance benefits including:

You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes


Question 5

You administer a Microsoft SQL Server 2014 database. You want to make a full backup of the database to a file on disk.

In doing so, you need to output the progress of the backup.

Which backup option should you use?



Answer : A

STATS is a monitoring option of the BACKUP command.

STATS [ =percentage ]

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql


Question 6

You administer a Microsoft SQL Server 2016 instance.

You need to configure a new database to support FILETABLES.

What should you do? Choose all that apply.



Answer : B, D, F

References:

https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable


Question 7

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).

You load data to the database by using Azure Data Factory.

You need to reduce the amount of time it takes to load the data.

Solution: You move the database to a Standard elastic pool that has 800 eDTUs.

Does this meet the goal?



Answer : B


Page:    1 / 14   
Total 267 questions