Microsoft 70-765 Free Practice Questions

NEW QUESTION 1
You are a database developer of a Microsoft SQL Server 2014 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID,CustomerID));

Answer: D

NEW QUESTION 2
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. What should you create?

• A. An Alert
• B. A Resource Pool
• C. An Extended Event session
• D. A Server Audit Specification
• E. A SQL Profiler Trace
• F. A Database Audit Specification
• G. A Policy
• H. A Data Collector Set

Answer: F

Explanation: To audit users in a role use a Database Audit Specification.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql

NEW QUESTION 3
You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:
You need to grant users access according to the following requirements: What should you do?

• A. Create a database role called Employees.Add CORPEmployees to the db_datareader rol
• B. Add all company employees except HR administrators to the Employees rol
• C. Deny SELECT access to the SalaryDetails table to the Employees role.
• D. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader rol
• E. Add all HR administrators to the HRAdmins rol
• F. Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.
• G. Create two database roles: Employees and HRAdmin
• H. Add all company employees to the Employees role.Add HR administrators to the HRAdmins rol
• I. Grant SELECT access to all tables except SalaryDetails to the Employees rol
• J. Grant SELECT access to the SalaryDetails table to the HRAdmins rol
• K. Deny SELECT access to the SalaryDetails table to the Employees role.
• L. Create a database role called Employees.Add all HR administrators to the db_datareader rol
• M. Add all company employees to the Employees rol
• N. Grant SELECT access to all tables except the SalaryDetails table to the Employees rol
• O. Deny SELECT access to the SalaryDetails table to the Employees role.

Answer: D

Explanation: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
References: https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

NEW QUESTION 4
DRAG DROP
You create a login named BIAppUser. The login must be able to access the Reporting database.
You need to grant access to the BIAppUser login in the database.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation: Box 1: Reporting
The user is to be created in the Reporting database.
Box 2: CREATE USER
Box 3: FOR LOGIN [BIAppUser]
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
References: https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure- database/

NEW QUESTION 5
Settings Value VM size D3
Storage Location Drive E Storage type Standard Tempdb location Drive C
The workload on this instance has of the tembdb load.
You need to maximize the performance of the tempdb database.
Solution: You use an AB compute-intensive instance and store the tempdb database in Standard storage. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 6
You have Microsoft SQL Server on a Microsoft Azure Virtual machine that has a 4-TB database.
You plan to configure daily backups for the database. A single full backup will be approximately 1.5 TB of compressed data.
You need to ensure that the last backups are retained. Where should you store the daily backups?

• A. Local storage
• B. Page blob storage
• C. Virtual disks
• D. Block blob storage.

Answer: D

Explanation: When backing up to Microsoft Azure blob storage, SQL Server 2021 supports backing up to multiple blobs to enable backing up large databases, up to a maximum of 12.8 TB. This is done through Block Blobs.
References:

NEW QUESTION 7
You plan to deploy a Microsoft SQL Server database that will use FILESTREAM. The database will store 4 TB of FILESTREAM data on a single Windows partition.
You need to configure the hard disk that will support the FILESTREAM data. The solution must provide the fastest read and write access to the data.
How should you configure the disk? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: File System: NTFS
8.3 filename support: Disabled Indexing: Disabled
NTFS is required.
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage.
Check that search indexing is not enabled on FILESTREAM volumes, under the Volume Properties window, unchecking the “Allow files on this drive to have contents indexed in addition to file properties” box.
References:
https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestreamimplementations/

NEW QUESTION 8
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases. Which powershell cmdlet should you run?

• A. New-AzureSqlJob
• B. New-AzureWebsiteJob
• C. New-AzureBatchJob
• D. New-ScheduledJobOption
• E. New-JobTrigger

Answer: A

Explanation: The New-AzureSqlJob cmdlet, in the ElasticDatabaseJobs module, creates a job definition to be used for subsequent job runs.
References:
https://docs.microsoft.com/en-us/powershell/module/elasticdatabasejobs/new-azuresqljob?view=azureelasticdbj

NEW QUESTION 9
You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit.
(Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query. What should you do?

• A. Use a FORCESCAN hint in the query.
• B. Add a clustered index on SalesOrderId in SalesOrderHeader.
• C. Use a FORCESEEK hint in the query.
• D. Update statistics on SalesOrderId on both tables.

Answer: D

Explanation: New statistics would be useful.
The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
http://msdn.microsoft.com/en-us/library/ms187348.aspx

NEW QUESTION 10
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account.
You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes.
Solution: You run the Get-Counter cmdlet and specify the –counter ‘physicaldisk:disk Transfers/sec’ parameter.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 11
HOTSPOT
You need to ensure that a user named Admin2 can manage logins.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer Area

Answer:

Explanation: Step 1: CREATE LOGIN
First you need to create a login for SQL Azure, it's syntax is as follows: CREATE LOGIN username WITH password='password';
Step 2, CREATE USER Step 3: LOGIN
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin; Step 4: loginmanager
Members of the loginmanager role can create new logins in the master database.
References:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/ https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

NEW QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the PARAMETER_SNIFFING option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: PARAMETER_SNIFFING = { ON | OFF | PRIMARY} enables or disables parameter sniffing. This is equivalent to Trace Flag 4136.
SQL server uses a process called parameter sniffing when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 13
You have an on-premises SQL Server database named DB1 that contains a table named TB1. TB1 is stretched to Microsoft Azure.
A catastrophic hardware failure occurs on the on-premises SQL server.
You deploy a new on-premises server and restore all databases to the new server. You need to resume Stretch Database operations to Azure.
Which statements should you execute?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation: Use ALTER TABLE, not CREATE TABLE.
When you enable Stretch for a table by specifying ON, you also have to specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration.
Syntax:
<stretch_configuration> ::=
{
SET ( REMOTE_DATA_ARCHIVE
{
= ON ( <table_stretch_options> )
| = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
| ( <table_stretch_options> [, ...n] )
}
)
}
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2021

NEW QUESTION 14
You have on-premises database server.
The database files for a user database are configured as shown in the following table:

You plan to migrate the on-premises database server to Microsoft SQL Server on a Microsoft Azure virtual machine.
You need to provision storage for the virtual machine to meet the following requirements: Support the same configurations as the on-premises database server.
Provide a Service Level Agreement (SLA) for performance. Minimize costs.
Which type of storage should you provision for each file type? To answer, drag the appropriate storage types to the correct file types. Each storage type may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads. Production workloads should use Premium Storage.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowssql-performanc

NEW QUESTION 15
Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30 stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button). Exhibit:

You need to restore the Reporting database to SRV2. What should you do? To answer, drag the appropriate options to the correct locations. Each option may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Answer:

Explanation: Step 2: Create: server certificate
Recreate the server certificate by using the original server certificate backup file.
Note: The password must be the same as the password that was used when the backup was created. Step 3: Restore: Reporting database .mdf file.
-- Attach the database that is being moved.
-- The path of the database files must be the location where you have stored the database files. Example:
CREATE DATABASE [CustRecords] ON
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA
CustRecords.mdf' ),
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATA
CustRecords_log.LDF' ) FOR ATTACH ;
GO
From scenario: The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database and copy it to the new server.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-a

NEW QUESTION 16
You administer a Microsoft SQL Server 2021 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

• A. Disable FILESTREAM on the Database.
• B. Enable FILESTREAM on the Server Instance.
• C. Configure the Database for Partial Containment.
• D. Create a non-empty FILESTREAM file group.
• E. Enable Contained Databases on the Server Instance.
• F. Set the FILESTREAM directory name on the Database.

Answer: BDF

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

NEW QUESTION 17
You have a Microsoft SQL Server instance that has a database named DB1. DB1 has data files on drive E and transaction logs on drive L.
You perform full backups of DB1 daily and transaction log backups hourly. Drive E fails and is replaced.
You need to recover DB1 and prevent any data loss.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

Answer:

Explanation: Section: Deploy and migrate applications Step 1: Perform a tail-log backup.
A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.
Step 2: Restore a full backup.
Backups must be restored in the order in which they were created. Before you can restore a particular transaction log backup, you must first restore the following previous backups without rolling back uncommitted transactions, that is WITH NORECOVERY:
The full database backup and the last differential backup, if any, taken before the particular transaction log backup.
Step 3: Restore the log backups.
Log backups must be applied in the sequence in which they were created, without any gaps in the log chain. Step 4: Restore the tail-log backups.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-transaction-log-backup-sqlser https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

NEW QUESTION 18
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 Basic database pool that has 1,600 eDTUs. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: We need the use of a Standard database pool.

NEW QUESTION 19
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.
What should you create?

• A. An Alert
• B. A Resource Pool
• C. An Extended Event session
• D. A Server Audit Specification
• E. A SQL Profiler Trace
• F. A Database Audit Specification
• G. A Policy
• H. A Data Collector Set

Answer: C

Explanation: SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
Extended Events is a light weight performance monitoring system that uses very few performance resources. A SQL Server Extended Events session is created in the SQL Server process hosting the Extended Events
engine.
References:https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events