Microsoft 70-765 Dumps 2021

NEW QUESTION 1
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space.
The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has 30 containers. You create a VHD in each container. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References: https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/

NEW QUESTION 2
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 3
You administer a Microsoft SQL Server 2014 environment. One of the SQL Server 2014 instances contains a database named Sales.
You plan to migrate Sales to Windows Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set database containment to AZURE.
• B. Enable server property contained database authentication.
• C. Disable server property cross db ownership chaining.
• D. Set database containment to PARTIAL.
• E. Disable server property contained database authentication.
• F. database containment to FULL.

Answer: BD

Explanation: A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
B: In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database.
SQL Database and SQL Data Warehouse support Azure Active Directory identities as contained database users.
D: The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

NEW QUESTION 4
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure.
You need to resume Stretch Database operations.
Which two stored procedures should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. sys.sp_rda_reconcile_batch
• B. sys.sp_rda_reconcile_indexes
• C. sys.sp_rda_reauthorize_db
• D. sys.sp_rda_reconcile_columns
• E. sys.sp_rda_set_rpo_duration

Answer: CD

Explanation: sys.sp_rda_reauthorize_db restores the authenticated connection between a local database enabled for Stretch and the remote database.
If you have accidentally deleted columns from the remote table, run sp_rda_reconcile_columns to add columns to the remote table that exist in the Stretch-enabled SQL Server table but not in the remote table.

NEW QUESTION 5
You plan to deploy an AlwaysOn failover cluster in Microsoft Azure. The cluster has a Service Level Agreement (SLA) that requires an uptime of at least 99.95 percent.
You need to ensure that the cluster meets the SLA.
Which cmdlet should you run before you deploy the virtual machine?

• A. New-AzureRmAvailabilitySet
• B. New-AzureRmLoadBalancer
• C. New-AzureRmSqlDatabaseSecondary
• D. New-AzureRmSqlElasticPool
• E. New-AzureRmVM
• F. New-AzureRmSqlServer
• G. New-AzureRmSqlDatabaseCopy
• H. New-AzureRmSqlServerCommunicationLink

Answer: B

Explanation: On Azure virtual machines, a SQL Server Availability Group requires a load balancer. The load balancer holds the IP address for the Availability Group listener. The New-AzureRmLoadBalancer cmdlet creates an Azure load balancer.
References:

NEW QUESTION 6
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine.
You must enable Always Encrypted for columns in a database. You need to configure the key store provider.
What should you do?

• A. Use the Randomized encryption type
• B. Modify the connection string for applications.
• C. Auto-generate a column master key.
• D. Use the Azure Key Vault.

Answer: D

Explanation: There are two high-level categories of key stores to consider - Local Key Stores, and Centralized Key Stores.
Centralized Key Stores - serve applications on multiple computers. An example of a centralized key store is Azure Key Vault.
Local Key Stores References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-storecolumn-master-ke

NEW QUESTION 7
A company has an on-premises Microsoft SQL Server environment with a SQL-Server named SQL01. You need to create a local sysadmin account on SQL01 named Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: B: First we create a login with the CREATE LOGIN command. E: Then we add it to the sysadmin role.
1. To add a member to a fixed server role
2. In Object Explorer, connect to an instance of Database Engine.
3. On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. ALTER SERVER ROLE diskadmin ADD [DomainJuan] ;
GO
G: Finally we add a database user for the login we created.
References: https://technet.microsoft.com/en-us/library/aa337562(v=sql.110).aspx

NEW QUESTION 8
You are building the database platform for a multi-tenant application. The application will have one database per tenant and will have at least 30 tenants. Each tenant will have a separate resource group for billing purposes.
The application will require at least 10 GB of clustered columnstore indexes for each database.
You need to implement the database platform for the application. The solution must minimize costs. What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: The Standard tier service allows for 1TB of data. Here 30 x 10 GB, 0.3 TB, is required.

NEW QUESTION 9
HOTSPOT
You use Resource Manager to deploy a new Microsoft SQL Server instance in a Microsoft Azure virtual machine (VM) that uses Premium storage. The combined initial size of the SQL Server user database files is expected to be over 200 gigabytes (GB). You must maximize performance for the database files and the log file.
You add the following additional drive volumes to the VM:

You have the following requirements:
You need to deploy the SQL instance.
In the table below, identify the drive where you must store each SQL Server file type. NOTE: Make only one selection in each column. Each correct selection is worth one point.

Answer:

Explanation: Enable read caching on the disk(s) hosting the data files and TempDB.
Do not enable caching on disk(s) hosting the log file. Host caching is not used for log files.

Topic 2, Manage databases and instances

NEW QUESTION 10
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 have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User. All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete. The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You create a snapshot of the database. You configure all report queries to use the database snapshot.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Use a Resource Governor instead.
References:https://msdn.microsoft.com/en-us/library/bb933866.aspx

Topic 4, automobile parts Case Study 1Background
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.
Exhibit

NEW QUESTION 11
You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?

• A. min memory per query to 4 GB
• B. index create memory to 16 GB
• C. max worker threads to 1
• D. max server memory to 16 GB

Answer: B

Explanation: The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-indexcreate-memory-ser

NEW QUESTION 12
You develop a Microsoft SQL Server 2014 database that contains a heap named OrdersHistorical. You write the following Transact-SQL query:
INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

• A. HOLDLOCK
• B. ROWLOCK
• C. XLOCK
• D. UPDLOCK
• E. TABLOCK

Answer: E

Explanation: When importing data into a heap by using the INSERT INTO SELECT <columns> FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.
References:https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

NEW QUESTION 13
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in a development environment.
You need to provide storage to the environment that minimizes costs. Which storage option should you use?

• A. Premium P10 disk storage
• B. Premium P20 disk storage
• C. Premium P30 disk storage
• D. Standard locally redundant disk storage
• E. Standard geo-redundant disk storage
• F. Standard zone redundant blob storage
• G. Standard locally redundant blob storage
• H. Standard geo-redundant blob storage

Answer: D

NEW QUESTION 14
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112)=CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Explanation: To compare a time with date we must use >= and > operators, and not the = operator.

NEW QUESTION 15
You plan to migrate a Microsoft sql server instance between physical servers. You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the model database.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: The model database does not handle alerts and jobs. It is used as the template for all databases created on an instance of SQL Server.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2021

NEW QUESTION 16
A company has an on-premises Microsoft SQL Server 2014 environment. The company has a main office in Seattle, and remote offices in Amsterdam and Tokyo. You plan to deploy a Microsoft Azure SQL Database instance to support a new application. You expect to have 100 users from each office.
In the past, users at remote sites reported issues when they used applications hosted at the Seattle office.
You need to optimize performance for users running reports while minimizing costs. What should you do?

• A. Implement an elastic pool.
• B. Implement a standard database with readable secondaries in Asia and Europe, and then migrate the application.
• C. Implement replication from an on-premises SQL Server database to the Azure SQL Database instance.
• D. Deploy a database from the Premium service tier.

Answer: B

Explanation: References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-transact-sql#add-secondary-database

NEW QUESTION 17
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 18
You administer a Microsoft SQL Server 2014 instance that has several SQL Server Agent jobs configured. When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required
detail.
The following error message is an example error message:
"The job failed. The Job was invoked by User CONTOSOServiceAccount. The last step to run was step 1 (Subplan_1)."
You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained. What should you do?

• A. Configure output files.
• B. Expand agent logging to include information from all events.
• C. Disable the Limit size of job history log feature.
• D. Configure event forwarding.

Answer: B

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms175488.aspx

NEW QUESTION 19
Database DB1 must use two CPU cores.
Queries that were running on database DB2 prior to migration do not complete. You need to configure the databases.
In the table below, identify the parameter that must be configured for each databases. Select one option for DB1, and one option for DB2. Select one option for each column.

Answer:

Explanation: Query_optimizer_hotfixes DB1: MAXDOP
You can use the max degree of parallelism (MAXDOP) option to limit the number of processors to use in parallel plan execution.
DB2: LEGACY_CARDINALITY_ESTIMATION
The CE (Cardinality Estimation) predicts how many rows your query will likely return. The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.
Legacy CE: For a SQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the ALTER DATABASE SCOPED CONFIGURATION.
Example:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; GO