Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
It is more faster and easier to pass the by using . Immediate access to the and find the same core area with professionally verified answers, then PASS your exam with a high score now.
Online Microsoft 70-765 free dumps demo Below:
NEW QUESTION 1
You plan to install Microsoft SQL Server 2014 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?
Answer: B
NEW QUESTION 2
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
A network administrator upgrades the hardware firewalls on the network. You need to verify whether data migration still runs successfully.
Which stored procedure should you run?
Answer: B
Explanation: The Sys_sp_rda_test_connection cmdlet tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-rda-test-connection-tr
NEW QUESTION 3
You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following:
One instance of SQL Server 2021 Enterprise
10 databases
500 stored procedures
You have a database named Database1 that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications. You plan to remove data from the procedure cache on Database1.
You have the following requirements:
Changes to Database1 must not affect other databases that are hosted on Server1
Changes to Database1 must not affect the performance of queries that are stored in other databases.
The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible. What should you do?
Answer: B
Explanation: You should run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the
context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the instance of SQL Server 2021.
NEW QUESTION 4
You use Microsoft SQL Server 2014 to develop a database application. You need to create an object that meets the following requirements:
Which object should you use?
Answer: D
Explanation: Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.
References:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-datab
NEW QUESTION 5
You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSOSQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.
Answer: CD
Explanation: How To Enable Instant File Initialization References:
http://msdn.microsoft.com/en-us/library/ms175935.aspx
NEW QUESTION 6
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 a GS-series virtual machine (VM) in Microsoft Azure. You plan to deploy Microsoft SQL Server.
You need to deploy a 30 megabyte (MB) database that requires 100 IOPS to be guaranteed while minimizing costs.
Which storage option should you use?
Answer: A
Explanation: Premium Storage Disks Limits
When you provision a disk against a Premium Storage account, how much input/output operations per second (IOPS) and throughput (bandwidth) it can get depends on the size of the disk. Currently, there are three types of Premium Storage disks: P10, P20, and P30. Each one has specific limits for IOPS and throughput as specified in the following table:
References:https://docs.microsoft.com/en-us/azure/storage/storage-premium-storage
NEW QUESTION 7
You are the database administrator for your company. Your company has one main office and two branch offices. You plan to create three databases named DB1, DB2, and DB3 that will be hosted on one Azure SQL Database server. You have the following requirements:
The main office must be able to connect to all three databases.
The branch offices must be able to connect to DB2 and DB3.
The branch offices must not be able to access DB1.
You need to configure transparent data encryption (TDE) for DB1. Which two actions should you perform? Each correct answer presents part of the solution.
Answer: BC
Explanation: You should connect to DB1. To encrypt DB1, you connect directly to DB1. When you connect to DB1. You use your dbmanager or administrative credentials.
You should run ALTER DATABASE DB1 SET ENCRYPTION ON.
You use the ALTER DATABASE DB1 SET ENCRYPTION ON statement to encrypt the database. This is the statement that turns on TDE for Azure SQL Database.
NEW QUESTION 8
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?
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 9
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:
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-a
NEW QUESTION 10
You use Microsoft Azure Resource Manager to deploy two new Microsoft SQL Server instances in an Azure virtual machine (VM). VM has 28 gigabytes (GB) of memory. The instances are named Instance1 and Instance2, respectively.
The various databases on the instances have the following characteristics:
You run the following Transact-SQL statements:
You need to configure each SQL Server instance to correctly allocate memory. What should you do?
Answer: D
NEW QUESTION 11
You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs
owned by UserA
To which role should you add UserA?
Answer: C
Explanation: SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. Members can create local jobs.
References:https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
NEW QUESTION 12
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 use the always Encrypted wizard to encrypt all possible for the tables in the primary instance. Does the solution meet the goal?
Answer: B
Explanation: Always Encrypted does not support geo replication. Transparent Data Encryption (TDE) would provide a solution.
Note: Use the Always Encrypted Wizard to help protect sensitive data stored in a SQL Server database. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/
http://blog.pragmaticworks.com/sql-server-2021-data-masking-and-always-encrypted
NEW QUESTION 13
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 have Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1. You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
You need to reduce the WRITE_LOG wait time. Solution: Move the transaction logs to a faster disk. Does this meet the goal?
Answer: A
Explanation: Section: Deploy and migrate applications
In SQL Server, if we have a transactional based system and find a high WRITELOG wait type this is a performance bottleneck and can cause the transaction log file to grow rapidly and frequently.
It is being recommended to SQL server users that they must archive the log files on a separate disk for getting better performance.
References: https://atdhebuja.wordpress.com/2021/06/20/resolving-sql-server-transaction-log-waits/
NEW QUESTION 14
You have an on-premises database.
You plan to migrate the database to Microsoft SQL Server on a Microsoft Azure virtual machine.
You move the database files to Azure.
You need to attach the database files to the SQL Server instance on the virtual machine. The solution must ensure that you can run file snapshot backups.
How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Answer:
Explanation: References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-sql-server-transact-sql
NEW QUESTION 15
You administer a Microsoft SQL Server 2014 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?
Answer: B
Explanation: To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.
The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.
References:
http://technet.microsoft.com/en-us/library/ms191545.aspx
NEW QUESTION 16
User report that a query takes a long time to execute. The query has the following wait statistics.
Which resource causes the issue?
Answer: B
Explanation: PAGEIOLATCH Wait time and WaitCount are both high.
One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions
The Microsoft definition of this wait type is:
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
References: https://www.sqlshack.com/handling-excessive-sql-server-pageiolatch_sh-wait-types/
NEW QUESTION 17
You administer a Microsoft SQL Server 2014 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error. Which server configuration property should you set?
Answer: B
Explanation: Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.
The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.
Note: The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread.
The system, resource, and query process components are used for failure detection. The io_subsytem and events components are used for diagnostic purposes only.
References:https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure-failurecondition
NEW QUESTION 18
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You suspect that the current SQL Server indexes cause queries to execute slowly.
You need to identify which indexes must be created to reduce the query execution time.
Which three dynamic management views should you use? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
Answer: BEG
Explanation: The missing indexes feature consists of the following components:
A set of dynamic management objects that can be queried to return information about missing indexes.
The Missing Indexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.
Dynamic Management Objects
After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.
sys.dm_db_missing_index_group_stats
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers ofall missing indexes that are contained in that group.
sys.dm_db_missing_index_details
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns
Returns information about the database table columns that are missing an index. References: https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx
NEW QUESTION 19
You plan to install a Microsoft SQL Server 2014 instance.
The instance will support a database that has the following requirements: Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met. Which feature should you use?
Answer: B
Explanation: FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server
Recommend!! Get the Full 70-765 dumps in VCE and PDF From Dumpscollection, Welcome to Download: http://www.dumpscollection.net/dumps/70-765/ (New 209 Q&As Version)