Standing on the shoulders of giants is how one customer described studying with the CertkingdomPDF 70-765 bank: Microsoft Provisioning SQL Databases knowledge assembled by specialists, refined by regular research, and delivered with service that treats your success as its own job.
Microsoft 70-765 Exam Overview:
| Certification Vendor: | Microsoft |
|---|---|
| Exam Name: | Provisioning SQL Databases |
| Exam Number: | 70-765 |
| Related Certifications: | MCSA: SQL 2016 Database Administration MCSA: Microsoft Dynamics 365 for Operations |
| Passing Score: | 700 (on a scale of 1-1000) |
| Exam Price: | $165 USD |
| Real Exam Qty: | 40-60 |
| Exam Duration: | 120 minutes |
| Available Languages: | English |
| Exam Format: | Scenario-based, Multiple choice, Performance-based |
| Certificate Validity Period: | Retired January 31, 2021; MCSA credentials valid for 2 years |
| Recommended Training: | Microsoft Learn Self-Paced Modules Microsoft Official Course 20765: Provisioning SQL Databases |
| Exam Registration: | Pearson VUE Registration Microsoft Learn Exam Page |
| Sample Questions: |  |
| Exam Way: | Online proctored or in-person at Pearson VUE test centers (before retirement) |
| Pre Condition: | No mandatory prerequisites; recommended experience with SQL Server administration, Azure services, and cloud deployment models |
| Official Syllabus URL: | https://learn.microsoft.com/en-us/credentials/certifications/exams/70-765/ |
Microsoft 70-765 Exam Syllabus Topics:
| Section | Weight | Objectives |
|---|---|---|
| Topic 1: Manage Storage | 30-35 | - Optimize storage performance
|
| Topic 2: Implement SQL in Azure | 40-45 | - Plan and deploy SQL Server on Azure Virtual Machines
|
| Topic 3: Manage Databases and Instances | 30-35 | - Monitor and optimize performance
|
Microsoft Provisioning SQL Databases Questions Answered by CertkingdomPDF
- Manage Databases and Instances (30-35)
- Manage Storage (30-35)
- Implement SQL in Azure (40-45)
Microsoft Provisioning SQL Databases Sample Questions:
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 set the size of the log files for the tempdb database on SRV1.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:
Correct Answer:
Explanation:
The ALTER DATABASE with MODIFY FILE command can make a file size bigger (but not smaller).
Example:
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
Note: MODIFY FILE
Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tdeprotected-database-to-another-sql-server
You plan to deploy two new Microsoft Azure SQL Database instances. Once instance will support a data entry application. The other instance will support the company's business intelligence efforts. The databases will be accessed by mobile applications from public IP addresses.
You need to ensure that the database instances meet the following requirements:
The database administration team must receive alerts for any suspicious activity in the data entry database, including potential SQL injection attacks.
Executives around the world must have access to the business intelligence application.
Sensitive data must never be transmitted. Sensitive data must not be stored in plain text in the database.
In the table below, identify the feature that you must implement for each database.
NOTE: Make only one selection in each column. Each correct selection is work one point.
Correct Answer:
Explanation:
Data entry: Threat Detection
SQL Threat Detection provides a new layer of security, which enables customers to detect and respond to potential threats as they occur by providing security alerts on anomalous activities. Users receive an alert upon suspicious database activities, potential vulnerabilities, and SQL injection attacks, as well as anomalous database access patterns.
Business intelligence: Dynamic Data Masking
Dynamic data masking limits (DDM) sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
You have a database named db1 and an application named App1. App1 queries DB1.
You discover that a non-clustered index in obi is corrupt.
You need to repair the index- App1 must be able to query osi while the index is being repaired.
What should you do?
- A. Drop and recreate the index.
- B. Run checkdb repair database and specify the repair_rebuild parameter.
- C. Restore DB1 from a backup.
- D. Run checkdb repair database and specify the allon_oata_loss parameter.
Correct Answer: B 🗳️
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?
- A. An Extended Event session
- B. A Policy
- C. A Server Audit Specification
- D. An Alert
- E. A Database Audit Specification
- F. A SQL Profiler Trace
- G. A Resource Pool
Correct Answer: F 🗳️
Explanation: Only visible for CertkingdomPDF members. You can sign-up / login (it's free).
You have the databases configured as shown in the following table.
Which two databases can use the Stretch Database feature? Each correct answer presents a complete solution. NOTE; Each correct selection is worth one point.
- A. DB2
- B. DB4
- C. DB1
- D. DB3
Correct Answer: C,D 🗳️
Explanation: Only visible for CertkingdomPDF members. You can sign-up / login (it's free).
York 
Julian 
Atwood 
Simona 
Booth 
Todd 
Lyndon 
Elvis 
Beatrice 
Ella 
Jonathan 
Tobias 
Jason 
Marshall 
Gene 
Horace 
Sabina 
Chester 
Adair 
