[2021-New] Microsoft 70-764 Dumps With Update Exam Questions (1-10)

70-764 Dumps

70-764 Exam Questions - Online Test

70-764 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.

You need to prevent users from disabling server audits in Server01. What should you create?

A. an Alert

B. a Resource Pool

C. an Extended Event session

D. a Database Audit Specification

E. a SQL Profiler Trace

F. a Server Audit Specification

G. a Policy

Answer: G

Q2. You administer a single server that contains a Microsoft SQL Server 2012 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.

Answer: B

Explanation:

I would have gone with Contained Databases, but the application requires sysadmin permissions.

Q3. HOTSPOT

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.

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

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.

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.

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

You need to create the connection strings for the operations and reporting systems. In the table below, identify the option that must be specified in each connection string. NOTE: Make only one selection in each column.

Answer:

Explanation:

Reporting system: Connect to any current read-only replica instance

We configure Read-OnlyAccess on an Availability Replica. We select Read-intent only. Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

From Scenario: 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.

Operating system: Connect to the current primary replica SQL instance

By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.

From scenario: 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.

References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-access-on-an-availability-replica-sql-server

Q4. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

A data file of 2 terabytes is located on a dedicated LUN (drive D). A transaction log of 10 GB is located on a dedicated LUN (drive E). Drive D has 1 terabyte of free disk space.

Drive E has 5 GB of free disk space.

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?

A. BULK_LOGGED

B. NO_CHECKSUM

C. FULL

D. RESTART

E. CHECKSUM

F. STANDBY

G. DBO.ONLY

H. NORECOVERY

I. SIMPLE

J. Transaction log

K. Differential

L. CONTINUE_AFTER_ERROR

M. COPY_ONLY

N. SKIP

Answer: J

Explanation:

Verified answer as correct.

http://msdn.microsoft.com/en-us/library/ms186865.aspx

http://msdn.microsoft.com/en-us/library/ms191429.aspx

http://msdn.microsoft.com/en-us/library/ms179478.aspx

Q5. You have a database named DB1 that is configured to use the full recovery model. You have a full daily backup job that runs at 02:00. The job backs up data from DB1 to the file B:\DB1.bak.

You need to restore the DB1 database to the point in time of May 25, 2021 at 02:23 and ensure that the database is functional and starts to accept connections.

Which Transact-SQL statement should you run?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: B

Q6. You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.

Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement:

SELECT * FROM sys.database_query_store_options

You determine that the Query Store is in Read-Only mode.

You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?

A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)

B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)

C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)

Answer: C

Explanation:

Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.

By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

Avoid keeping historical data that you do not plan to use. This will reduce changes to read- only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]

SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

Q7. You administer a SQL Server 2012 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 and the Sales role is granted the Select permission on the Customers schema. You need to ensure that the Sales role, including UserA, is disallowed to select from the Regions table.

Which Transact-SQL statement should you use?

A. REVOKE SELECT ON Schema::Customers FROM UserA

B. REVOKE SELECT ON Object::Regions FROM UserA

C. EXEC sp_addrolemember 'Sales', 'UserA'

D. DENY SELECT ON Schema::Customers FROM Sales

E. EXEC sp_droprolemember 'Sales', 'UserA'

F. REVOKE SELECT ON Schema::Customers FROM Sales

G. DENY SELECT ON Object::Regions FROM UserA

H. REVOKE SELECT ON Object::Regions FROM Sales

I. DENY SELECT ON Schema::Customers FROM UserA

J. DENY SELECT ON Object::Regions FROM Sales

Answer: J

Explanation:

http://msdn.microsoft.com/en-us/library/ms188369.aspx http://msdn.microsoft.com/en-us/library/ms187750.aspx http://msdn.microsoft.com/en-us/library/ff848791.aspx

Q8. You administer all the deployments of Microsoft SQL Server 2012 in your company.

You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.

You also need to minimize the amount of storage used by the database. Which configuration should you use?

A. • Two servers configured in different data centers

• One server configured as an Active Secondary

B. • SQL Server that includes an application database configured to perform transactional replication

C. • Two servers configured in the same data center

• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

• One server configured as an Active Secondary

D. • Two servers configured in different data centers

• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

E. • Two servers configured in the same data center

• A primary server configured to perform log-shipping every 10 minutes

• A backup server configured as a warm standby

F. • Two servers configured on the same subnet

• SQL Server Availability Group configured in Synchronous-Commit Availability Mode

G. • SQL Server that includes an application database configured to perform snapshot replication

H. • Two servers configured in a Windows Failover Cluster in the same data center

• SQL Server configured as a clustered instance

Answer: H

Q9. You administer all the deployments of Microsoft SQL Server 2012 in your company.

You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server.

You also Need to be able to add indexes to the secondary database. Which configuration should you use?

A. • Two servers configured in different data centers

• SQL Server Availability Group configured in Synchronous-Commit Availability Mode

• One server configured as an Active Secondary

B. • Two servers configured in the same data center

• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

• One server configured as an Active Secondary

C. • Two servers configured in the same data center

• A primary server configured to perform log-shipping every 10 minutes

• A backup server configured as a warm standby

D. • Two servers configured in different data centers

• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode

E. • Two servers configured on the same subnet

• SQL Server Availability Group configured in Synchronous-Commit Availability Mode

F. • SQL Server that includes an application database configured to perform transactional replication

G. • SQL Server that includes an application database configured to perform snapshot replication

H. • Two servers configured in a Windows Failover Cluster in the same data center

• SQL Server configured as a clustered instance

Answer: F

Q10. HOTSPOT

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

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.

Answer:

Explanation:

Box 1: is

The dedicated backup file share is \\localhost\Backup

Box 2: does not run

The only thing with a name related to ADATM-SQL11 is the schedule name.

Box 3: 72 hours

4320 minutes equals 72 hours.

Note: @backup_retention_period= ] backup_retention_period

Isthe length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_period is int, with no default, and cannot be NULL.

Box 4: 15 minutes.

[ @freq_subday_type = ] freq_subday_type

Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.

Here it is 4, which means minutes.

[ @freq_subday_interval = ] freq_subday_interval

The number of freq_subday_type periods to occur between eachexecution of a job. freq_subday_intervalis int, with a default of 0. Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal

to 1.

Here it is 15.

References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-log-shipping-primary-database-transact-sql