Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
are updated and are verified by experts. Once you have completely prepared with our you will be ready for the real 70-765 exam without a problem. We have . PASSED First attempt! Here What I Did.
Online 70-765 free questions and answers of New Version:
NEW QUESTION 1
Note: This questions 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 deploy Microsoft SQL Server to a virtual machine in Azure. You distribute the database files and filegroups across multiple Azure storage disks.
You must be able to manage the databases as individual entities by using SQL Server Management Studio. All data in the databases must be stored encrypted. Backups must be encrypted by using the same key as the live copy of the database.
You need to secure the data. What should you implement?
Answer: C
Explanation: Transparent data encryption (TDE) encrypts your databases, associated backups, and transaction log files at rest without requiring changes to your applications.
TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. In SQL Database the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each SQL Database server.
References:https://msdn.microsoft.com/en-us/library/dn948096.aspx
Topic 3, Deploy and migrate applications
NEW QUESTION 2
You administer a Microsoft SQL Server 2014 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.
You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?
Answer: D
Explanation: The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks.
References:https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/
NEW QUESTION 3
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 Add-AzureRmMetricAlertRule cmdlet and specify the –MetricName ‘Network Out’ parameter.
Does this meet the goal?
Answer: B
NEW QUESTION 4
You have a database named DB1 that uses simple recovery mode.
Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup. There was no corruption when the last backup was complete.
You run the sys.columns catalog view and discover corrupt pages.
You need to recover the database. The solution must minimize data loss. What should you do?
Answer: B
Explanation: A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
RESTORE DATABASE WITH PAGE
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
NEW QUESTION 5
You are deploying a Microsoft SQL Server database that will support a mixed OLTP and OLAP workload. The target virtual machine has four CPUs.
You need to ensure that reports do not use all available system resources. What should you do?
Answer: C
Explanation: When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
NEW QUESTION 6
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 msdb database.
Does the solution meet the goal?
Answer: A
Explanation: 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 7
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You have two Windows accounts named serviceAccount1 and ServiceAccount2. The SQL Server Agent runs as ServiceAccount1.
You need to run SQL Server Agent job steps by using ServiceAccount2. Which cmdlet should you run first?
Answer: C
Explanation: The New-ADServiceAccount command creates a new Active Directory managed service account or group managed service account object.
NEW QUESTION 8
You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?
Answer: D
Explanation: The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References:
http://msdn.microsoft.com/en-us/library/ms189629.aspx
NEW QUESTION 9
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 an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs. Which storage option should you use?
Answer: A
Explanation: P10 has 500 IOPS per disk, which provides for more than 1 million IOPS per month.
Note: 3600*30* 500 is 54 million IOPS/month.
References: https://azure.microsoft.com/en-us/pricing/details/managed-disks/
NEW QUESTION 10
DRAG DROP
A new Azure Active Directory security principal named ReportUser@contoso.onmicrosoft.com should have access to select all current and future objects in the Reporting database. You should not grant the principal any other
permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
Which three 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.
Answer:
Explanation: Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad- authentication
NEW QUESTION 11
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?
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 12
DRAG DROP
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications and public websites. You plan to create a new security principal named User1.
The principal must have access to select all current and future objects in a database named Reporting. The activity and authentication of the database user must be limited to the Reporting database.
You need to create the new security principal.
Which three 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.
Answer:
Explanation: Step 1, Step 2:
First you need to create a login for SQL Azure, it's syntax is as follows: CREATE LOGIN username WITH password='password';
This command needs to run in master db. Only afterwards can you run commands to create a user in the database.
Step 3:
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; References:https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/
NEW QUESTION 13
You have Microsoft SQL Server on a Microsoft Azure virtual machine. The virtual machine has a database named DB1. DB1 contains a table named Table1 that has 4 billion rows.
Users report that a query using Table1 takes longer than expected to execute.
You review the execution plan for the query and discover that the expected number of returned rows is one, while the actual number of returned rows is 1 million.
You need to reduce the amount of time it takes for the query to execute. The solution must prevent additional performance issues from being introduced.
Hot Area:
Answer:
Explanation: When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan.
References:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statisticsoptions/
NEW QUESTION 14
You are the administrator of a Microsoft SQL Server 2014 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications
You need to dynamically limit resource consumption. What should you do?
Answer: A
Explanation: In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.
References:https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resou
NEW QUESTION 15
You have a Microsoft SQL Server 2014 named SRV2014 that has a single tempdb database file. The tempdb database file is eight gigabytes (GB) in size.
You install a SQL Server 2021 instance named SQL Server 2021 by using default settings. The new instance has eight logical processor cores.
You plan to migrate the databases from SRV2014 to SRV2021.
You need to configure the tempdb database on SRV2021. The solution must minimize the number of future tempdb autogrowth events.
What should you do?
Answer: B
Explanation: In an effort to simplify the tempdb configuration experience, SQL Server 2021 setup has been extended to configure various properties for tempdb for multi-processor environments.
1. A new tab dedicated to tempdb has been added to the Database Engine Configuration step of setup workflow.
2. Configuration options: Data Files
* Number offiles – this will default to the lower value of 8 or number of logical cores as detected by setup.
* Initial size – is specified in MB and applies to each tempdb data file. This makes it easier to configure all files of same size. Total initial size is the cumulative tempdb data file size (Number of files * Initial Size) that will be created.
* Autogrowth – is specified in MB (fixed growth is preferred as opposed to a non-linear percentage based growth) and applies to each file. The default value of 64MBwas chosen to cover one PFS interval.
Figure:
References:https://blogs.msdn.microsoft.com/psssql/2021/03/17/sql-2021-it-just-runs-faster-automatic-tempdb-configuration/
NEW QUESTION 16
You administer a Microsoft SQL Server 2014 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
What should you do?
Answer: C
Explanation: You would need to use either the SQL Server Management Studio or Transact-SQL.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate
NEW QUESTION 17
You administer a Microsoft SQL Server 2014 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account. What should you use?
Answer: D
Explanation: A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
NEW QUESTION 18
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 one container. You create multiple VHDs in the container. Does this meet the goal?
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 19
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?
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
Recommend!! Get the Full 70-765 dumps in VCE and PDF From Simply pass, Welcome to Download: https://www.simply-pass.com/Microsoft-exam/70-765-dumps.html (New 209 Q&As Version)