Microsoft 70-765 Dumps 2021
Master the content and be ready for exam day success quickly with this . We guarantee it!We make it a reality and give you real in our Microsoft 70-765 braindumps. Latest 100% VALID at below page. You can use our Microsoft 70-765 braindumps and pass your exam.
Free 70-765 Demo Online For Microsoft Certifitcation:
NEW QUESTION 1
You administer a Microsoft SQL Server 2014 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.
- A. Disable FILESTREAM on the Database.
- B. Enable FILESTREAM on the Server Instance.
- C. Configure the Database for Partial Containment.
- D. Create a non-empty FILESTREAM file group.
- E. Enable Contained Databases on the Server Instance.
- F. Set the FILESTREAM directory name on the Database.
Explanation: B: FileTables extend the capabilities of the FILESTREAM feature of SQL Server. Therefore you have to enable FILESTREAM for file I/O access at the Windows level and on the instance of SQL Server before you can create and use FileTables.
D: Before you can create FileTables in a database, the database must have a FILESTREAM filegroup. F: Specifying a Directory for FileTables at the Database Level
When you enable non-transactional access to files at the database level, you can optionally provide a directory name at the same time by using the DIRECTORY_NAME option. If you do not provide a directory name when you enable non-transactional access, then you have to provide it later before you can create FileTables in the database.
NEW QUESTION 2
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?
- A. Configure the application as data-tiered.
- B. Open port 1433 on the Windows firewall on the server.
- C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
- D. Start the SQL Server Browser Service.
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:
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
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?
- A. Run DBCC FREEPROCCACHE.
- B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in thecontext of Database 1.
- C. Run DBCC DROPCLEANBUFFERS.
- D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.
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 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 minimum amount of data is lost. Which recovery model should the database use?
- A. NORECOVERY
- B. FULL
- C. NO_CHECKSUM
- D. CHECKSUM
- E. Differential
- F. BULK_LOGGED
- G. STANDBY
- H. RESTART
- I. SKIP
- J. Transaction log
- K. DBO ONLY
- L. COPY_ONLY
- M. SIMPLE
- N. CONTINUE AFTER ERROR
Explanation: The full recovery model requires log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
NEW QUESTION 5
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 configure the Resource Governor to set the MAXDOP parameter to 0 for all queries against the database.
Does the solution meet the goal?
- A. Yes
- B. No
Explanation: SQL Server will consider parallel execution plans for queries, index data definition language (DDL) operations, and static and keyset-driven cursor population.
You can override the max degree of parallelism value in queries by specifying the MAXDOP query hint in the query statement.
NEW QUESTION 6
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?
- A. use SQL Profiler to trace all queries that are processing on the serve
- B. Filter queries that have a Duration value of more than 1,000.
- C. Use sp_configure to set a value for blocked process threshol
- D. Create an extended event session.
- E. Use the Job Activity monitor to review all processes that are actively runnin
- F. Review the Job History to find out the duration of each step.
- G. Run the sp_who command from a query window.
- H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
NEW QUESTION 7
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. Each VM has a dedicated disk for backups.
You need to backup a database to the local disk on a VM. The backup must be replicated to another region.
Which storage option should you use?
- A. Premium P10 disk storage
- B. Premium P20 diskstorage
- 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
Explanation: Note: SQL Database automatically creates a database backups and uses Azure read- access geo-redundant storage (RA-GRS) to provide geo-redundancy. These backups are created automatically and at no additional charge. You don't need to do anything to make them happen. Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion.
NEW QUESTION 8
You administer a Microsoft SQL Server 2014 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key',
ENCRYPTION BY PASSWORD = 'MyPassword1!'); CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
- A. ALTER DATABASE Master SET ENCRYPTION OFF;
- B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:TDE_Certificate.cer'WITH PRIVATE KEY (FILE = 'D:TDE_Certificate.key',DECRYPTION BY PASSWORD = 'MyPassword1!');
- C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders;CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTIONBY SERVER CERTIFICATE TDE_Certificate;
- D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:TDE_Certificate.cer';
Explanation: The CREATE CERTIFICATE command adds a certificate to a database in SQL Server. Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files. Code
USE AdventureWorks2012; CREATE CERTIFICATE Shipping11
FROM FILE = 'c:ShippingCertsShipping11.cer'
WITH PRIVATE KEY (FILE = 'c:ShippingCertsShipping11.pvk', DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
NEW QUESTION 9
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.
Each order is tracked by using one of the following statuses:
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
- A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDat
- B. Update rows as the order status changes.
- C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Statu
- D. Insert new rows into the table as the order status changes.
- E. Implement change data capture on the Sales.Orders table.
- F. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate.Update the value of each column from null to the appropriate date as the order status changes.
Explanation: This stores only the minimal information required.
NEW QUESTION 10
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?
- A. Move user databases to a local disk.
- B. Expand the tempdb data and log files
- C. Modify application code to use table variables
- D. Move the tempdb files to a local disk
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.
NEW QUESTION 11
You manage an on-premises Microsoft SQL server that has a database named DB1. An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records. You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Explanation: Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.
NEW QUESTION 12
Your company has several Microsoft Azure SQL Database instances used within an elastic pool. You need to obtain a list of databases in the pool.
How should you complete the commands? To answer, drag the appropriate segments to the correct targets. Each 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.
NOTE: Each correct selection is worth one point.
NEW QUESTION 13
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?
- A. Run DBCC CHECKDB (‘db1’, REPAIR_ALLOW_DATA_LOSS).
- B. Run DBCC CHECKDB (‘db1’, REPAIR_FAST).
- C. Delete the transaction logs and restart the Microsoft SQL Server instance.
- D. Run DBCC CHECKDB (‘db1’, REPAIR_REBUILD).
- E. Restore the database from a backup.
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 14
You have Microsoft SQL server on a Microsoft Azure virtual machine. The virtual machine has 200 GB of data.
User report a slow response time when querying the database.
You need to identify whether the storage subsystem causes the performance issue. Which performance monitor counter should you view?
- A. Data sec/Write
- B. Avg.disk Read Queue Length
- C. % Disk Read Time
- D. Disk sec/Read
NEW QUESTION 15
You administer a single server that contains a Microsoft SQL Server 2014 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases. What should you do?
- A. Use the SQL Server default instance and configure an affinity mask.
- B. Install a new named SQL Server instance on the server.
- C. Use the SQL Server default instance and enable Contained Databases.
- D. Install a new default SQL Server instance on the server.
NEW QUESTION 16
You administer a Microsoft SQL Server 2014 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?
- A. Execute sp_configure 'max log size', 2G.
- B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
- C. In SQL Server Management Studio, right-click the instance and select Database Setting
- D. Set the maximum size of the file for the transaction log.
- E. in SQL Server Management Studio, right-click the database, select Properties, and then click Files.Open the Transaction log Autogrowth window and set the maximum size of the file.
Explanation: You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file
To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option. However, there is no SET LOGFILE subcommand.
NEW QUESTION 17
You need to open the firewall ports for use with SQL Server environment. In table below, identify the firewall port that you must use for each service.
NOTE: Make only one selection in each column.
Explanation: Report Server: 80
By default, the report server listens for HTTP requests on port 80.
NEW QUESTION 18
You manage an on-premises, multi-tier application that has the following configuration:
Two SQL Server 2012 databases named SQL1 and SQL2
Two application servers named AppServer1 and AppServer2 that run IIS You plan to move your application to Azure.
You need to ensure that during an Azure update cycle or a hardware failure, the application remains available.
Which two deployment configurations should you implement? Each correct answer presents part of the solution.
- A. Deploy AppServer1 and AppServer2 in a single availability set.
- B. Deploy all servers in a single availability set.
- C. Deploy SQL1 and AppServer1 in a single availability set.
- D. Deploy SQL2 and AppServer2 in a single availability set.
- E. Deploy SQL1 and SQL2 in a single availability set.
Explanation: You should deploy AppServerl and AppServer2 in a single availability set. You should deploy SQL1 and SQL2 in a single availability set.
Note: Using availability sets allows you to build in redundancy for your Azure services. By grouping related virtual machines and services (tiers) into an availability set (in this case, deploying both of your databases into an availability set), you ensure that if there is a planned or unplanned outage, your services will remain available. At the most basic level, virtual machines in an availability set are put into a different fault domain and update domain. An update domain allows virtual machines to have updates installed and then the virtual machines are rebooted together.
If you have two virtual machines in an availability set, each in its own update domain, a rebooting of one server does not bring down all of the servers in a given tier. A fault domain operates in the same manner, so if there is a physical problem with a server, rack, network, or other service, both machines are separated, and services will continue.
NEW QUESTION 19
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.
- A. sys.dm_db_index_physical_stats
- B. sys.dm_db_missing_index_group_stats
- C. sys.indexes
- D. sys.dm_db_index_usage_stats
- E. sys.dm_db_missing_index_groups
- F. sys.dm_db_index_operational_stats
- G. sys.dm_db_missing_index_details
- H. sys.sysindexkeys
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.
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
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.
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.
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
100% Valid and Newest Version 70-765 Questions & Answers shared by Certifytools, Get Full Dumps HERE: https://www.certifytools.com/70-765-exam.html (New 209 Q&As)