[Free] 2019(Nov) EnsurePass Microsoft 70-764 Dumps with VCE and PDF 101-110

Get Full Version of the Exam
http://www.EnsurePass.com/70-764.html

Question No.101

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 collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository.

You need to examine disk usage, query statistics, and server activity without building custom counters.

What should you use?

  1. Activity Monitor

  2. Sp_who3 stored procedure

  3. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)

  4. SQL Server Data Collector

  5. SQL Server Data Tools (SSDT)

  6. SQL Server Configuration Manager

Correct Answer: D

Explanation:

The data collector is a core component of the data collection platform for SQL Server 2017 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data

Question No.102

HOTSPOT

You are configuring log shipping for a Microsoft SQL Server database named salesOrders. You run the following Transact-SQL script:

image

You need to determine the changes that the script has on the environment. How does the script affect the environment?

To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.103

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this

series.

You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

image

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

image

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.

Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.

You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

All databases use the full recovery model. All backups are written to the network location

\\SQLBackup\. A separate process copies backups to an offsite location. You should minimize

both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

image

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.

Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.

The wait statistics monitoring requirements for the instances are described in the following table.

image

You need to reduce the amount of time it takes to backup OperationsMain. What should you do?

  1. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.

  2. Modify the backup script to use the keyword SKIP in the WITH statement

  3. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.

  4. Modify the full database backups script to stripe the backup across multiple backup files.

Correct Answer: D

Explanation:

One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains

only the primary filegroup.

From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql- server

Question No.104

You administer a single server that contains a Microsoft SQL Server 2016 default instance on which several production databases have been deployed.

You plan to install a new ticketing application that requires the deployment of a database on the server.

The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the ticketing application cannot access other production databases.

What should you do?

  1. Use the SQL Server default instance and enable Contained Databases.

  2. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.

  3. Install a new named SQL Server instance on the server.

  4. Install a new default SQL Server instance on the server.

Correct Answer: C

Explanation:

SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the default instance. All others must be named instances. A computer can run multiple instances of SQL Server concurrently, and each instance runs independently of other instances.

References:

https://msdn.microsoft.com/en-us/library/ms143531(v=SQL.105).aspx

Question No.105

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 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 section, you will NOT be able to return to it As a result these questions will not appear in the review screen.

You have a database named DB1 that is 640 GB and is updated frequently.

You enabled log shipping for DB1 and configure backup and restore to occur every 30 minutes. You discover that the disks on the data server are almost full.

You need to reduce the amount of disk space used by the log shipping process.

Solution: You increase the frequency of the transaction log backups to every 10 minutes. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Question No.106

DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

image

You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

image

Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.

Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.

You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

All databases use the full recovery model. All backups are written to the network location

\\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

image

Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.

Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.

The wait statistics monitoring requirements for the instances are described in the following table.

image

You need to analyze the wait type and statistics for specific instanced in the environment.

Which object should you use to gather information about each instance?

To answer, drag the appropriate objects to the correct instances. Each object 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.

image

Correct Answer:

image

Question No.107

You have a database named DB1.

You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.

Occasionally, the identifying value must be reset to its initial value. You need to design a mechanism to hold the identifying values for the stored procedure to use.

What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

  1. Create a sequence object that holds the next value in the sequence.Retrieve the next value by using the stored procedure.Reset the value by using an ALTER SEQUENCE statement as needed.

  2. Create a sequence object that holds the next value in the sequence.Retrieve the next value by using the stored procedure.Increment the sequence object to the next value by using an ALTER SEQUENCE statement.Reset the value as needed by using a different ALTER SEQUENCE statement.

  3. Create a fourth table that holds the next value in the sequence.At the end each transaction, update the value by using the stored procedure.Reset the value as needed by using an UPDATE statement.

  4. Create an identity column in each of the three tables.Use the same seed and the same increment for each table.Insert new rows into the tables by using the stored procedure.Use the DBCC CHECKIDENT command to reset the columns as needed.

Correct Answer: A

Explanation:

an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.

ALTER SEQUENCE Includes argument: RESTART [ WITH lt;constantgt; ]

The next value that will be returned by the sequence object. If provided, the RESTART WITH value must be an integer that is less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. If the WITH value is omitted, the sequence numbering restarts based on the original CREATE SEQUENCE options.

CREATE SEQUENCE

Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.

The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

Question No.108

You have four databases that are accessed by using an Online Transaction Processing (OLTP) application.

The databases are stored on a server named SQL1 that has SQL Server 2014 installed. You plan to deploy an additional server that has SQL Server 2014 installed. You need to design a high- availability solution for the databases that meets the following requirements:

image

If SQL1 fails, the databases must be available.

image

Users must be able to run reports against a secondary copy of the databases.

What should you include in the design? More than one answer choice may achieve the goal. Select the BEST answer.

  1. AlwaysOn availability groups

  2. Database mirroring

  3. Log shipping

  4. Failover Clustering

Correct Answer: A

Explanation:

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together.

Question No.109

Overview

You are a database administrator for a company named Litware, Inc.

Litware is a book publishing house. Litware has a main office and a branch office.

You are designing the database infrastructure to support a new web-based application that is being developed.

The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

App1_DB will remain in production.

Requirements Planned Changes

You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

All database files will be stored in a highly available SAN. Database1 will contain two tables named Orders and OrderDetails.

Database1 will also contain a stored procedure named usp_UpdateOrderDetails.

The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.

The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.

Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.

Inventory will contain over 100 GB of data.

The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

The column that is used as the primary key will use the identity property.

Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

Business Requirements

You have the following requirements:

image

Costs for new licenses must be minimized.

image

image

Private information that is accessed by Application must be stored in a secure format. Development effort must be minimized whenever possible.

image

The storage requirements for databases must be minimized.

image

image

System administrators must be able to run real-time reports on disk usage. The databases must be available if the SQL Server service fails.

image

Database administrators must receive a detailed report that contains allocation errors and data corruption.

image

Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

You must encrypt the backup files to meet regulatory compliance requirements.

The encryption strategy must minimize changes to the databases and to the applications. You need to recommend a solution to improve the performance of usp.UpdateInventory.

The solution must minimize the amount of development effort. What should you include in the recommendation?

  1. A table variable

  2. A common table expression

  3. A subquery

  4. A cursor

Correct Answer: A

Explanation:

Scenario: Database2 will contain a stored procedure named usp_UpdateInventory. Usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies.

A table variable can be very useful to store temporary data and return the data in the table format. Example:

The following example uses a self-join to find the products that are supplied by more than one vendor. Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query.

This is an example of the self-join Transact-SQL statement:

image

Question No.110

You administer a Microsoft SQL Server 2016 database that has multiple tables in the Sales schema.

Some users must be prevented from deleting records in any of the tables in the Sales schema.

You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort.

What should you do?

  1. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

  2. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

  3. Deny Delete permissions on each table in the Sales schema for each user.

  4. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Correct Answer: A

Get Full Version of the Exam
70-764 Dumps
70-764 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>