Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Want to know features? Want to lear more about experience? Study . Gat a success with an absolute guarantee to pass Microsoft 70-765 (Provisioning SQL Databases (beta)) test on your first attempt.
Also have 70-765 free dumps questions for you:
NEW QUESTION 1
You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
Answer: C
Explanation: BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database
NEW QUESTION 2
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that the backup size is as small as possible. Which backup should you perform every two hours?
Answer: J
Explanation: Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log-backups-sql-server
NEW QUESTION 3
You administer a Microsoft SQL Server 2014 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve
performance.
What should you do?
Answer: C
Explanation: The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server.
NEW QUESTION 4
You plan to migrate a database To Microsoft Azure SQL Database. The database requires 500 gigabytes (GB) of storage.
The database must support 50 concurrent logins. You must minimize the cost associated with hosting the database.
You need to create the database. Which pricing tier should you use?
Answer: D
Explanation: For a database size of 500 GB the Premium tier is required. Both P1 and P2 are adequate. P1 is preferred as it is cheaper.
Note:
NEW QUESTION 5
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?
Answer: D
Explanation: To compare a time with date we must use >= and > operators, and not the = operator.
NEW QUESTION 6
You have a database named DB1. You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?
Answer: C
Explanation:
REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
NEW QUESTION 7
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. What should you do?
Answer: B
Explanation: To set up a readable secondary replica, you first create an availability group. Then you add replicas. You can choose either Yes or Read-intent only options.
References: http://msdn.microsoft.com/en-us/library/jj542414.aspx
NEW QUESTION 8
You administer two instances of Microsoft SQL Server 2014. You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?
Answer: D
Explanation: The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
References: https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx
NEW QUESTION 9
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?
Answer: D
Explanation: Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.
References:
http://msdn.microsoft.com/en-us/library/ms188246.aspx
NEW QUESTION 10
You have Microsoft SQL Server on a DS-series Microsoft Azure virtual machine. The virtual machine has 28 GB of memory.
You discover the following performance statistics on the server:
The average Page life expectancy is 30.
The server has excessive PAGELATCH_IO waits.
You need to decrease the PAGELATCH_IO waits. What should you do?
Answer: ACD
NEW QUESTION 11
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 write/sec’ parameter. Does this meet the goal?
Answer: B
NEW QUESTION 12
You are the database administrator in your company. You plan to create 10 identical environments that use SQL Server 2021 as a database engine. Each environment has the following custom requirements:
Three user databases must be preinstalled.
The tempdb database must contain eight data files that are 1024 MB each.
Trace flag 2371 must be turned at the instance level.
The solution must meet the following requirements:
The instance must be preconfigured.
No other database features are required in the future.
The solution must use the minimum administrative effort.
You need to prepare the environments. What should you do?
Answer: D
Explanation: You should create a virtual machine template by using a complete instance of SQL Server 2021. You use the sysprep tool to prepare a complete instance of SQL Server 2021. By using a complete instance, SQL Server, the network, and the users are all created, and the system cannot be reconfigured during the installation process.
NEW QUESTION 13
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do?
Answer: A
NEW QUESTION 14
You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database. You have the following requirements:
Maximum database size of 500 GB
A point-in-time-restore of 35 days
Maximum database transaction units (DTUs) of 500
You need to choose the correct service tier and performance level. Which service tier should you choose?
Answer: B
Explanation: You should choose Premium P4. The Premium tier is the highest Azure SQL Database tier offered. This tier is used for databases and application that require the highest level of performance and recovery. The P4 level supports a maximum of 500 DTUs, a maximum database size of 500 GB, and a point-in-time-restore to anypoint in the last 35 days.
NEW QUESTION 15
You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?
Answer: G
Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-
NEW QUESTION 16
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 a GS- Series VM and store the tempdb database on attached Premium storage. Does this meet the goal?
Answer: B
Explanation: For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan
NEW QUESTION 17
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.
Your company has several Microsoft Azure SQL Database instances.
Data encryption should be allowed to be implemented by the client applications that access the data. Encryption keys should not be made available to the database engine.
You need to configure the database. What should you implement?
Answer: A
Explanation: Using encryption during transit with Azure File Shares
Azure File Storage supports HTTPS when using the REST API, but is more commonly used as an SMB file share attached to a VM.
HTTPS is a transport-level security protocol.
NEW QUESTION 18
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 19
You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?
Answer: B
Explanation: To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-
100% Valid and Newest Version 70-765 Questions & Answers shared by prep-labs.com, Get Full Dumps HERE: https://www.prep-labs.com/dumps/70-765/ (New 209 Q&As)