[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 61-70

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

Question No.61

You administer a Microsoft SQL Server 2014 environment. One of the SQL Server 2014 instances contains a database named Sales.

You plan to migrate Sales to Windows Azure SQL Database. To do so, you need to implement a contained database.

What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  1. Set database containment to AZURE.

  2. Enable server property contained database authentication.

  3. Disable server property cross db ownership chaining.

  4. Set database containment to PARTIAL.

  5. Disable server property contained database authentication.

  6. database containment to FULL.

Correct Answer: BD

Explanation:

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

B: In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database. SQL Database and SQL Data Warehouse support Azure Active Directory identities as contained database users.

D: The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

Question No.62

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?

  1. Use the copy database wizard

  2. Use the Database Transfer wizard

  3. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database

  4. Backup the database from the local server and restore it to Windows Azure SQL Database

Correct 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

Question No.63

You have been hired as a Database Consultant by ABC.com to design a SQL Server 2014 database solution.

You are tasked with designing a scale-out and high-availability SQL Server 2014 Online Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.

Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2014 nodes. The data in both SQL Server nodes needs to be indexed.

What should you include in your solution?

  1. You should include a primary database with scheduled log shipping to the secondary database

    configured.

  2. You should include two servers configured in an Active-Passive SQL Server 2014 Cluster.

  3. You should include a primary SQL Server 2014 database that uses transactional replication to replicate data to the secondary database.

  4. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group.

  5. You should include two servers in a Synchronous-Commit Availability Mode Availability Group.

Correct Answer: C

Explanation:

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

References:

https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110)

Question No.64

You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.

You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs.

You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention.

Which configuration should you use?

  1. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

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

  3. Two servers configured in the same data centerA primary server configured to perform log- shipping every 10 minutes A backup server configured as a warm standby

  4. Two servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary

  5. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary

  6. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode

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

  8. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode

Correct Answer: H

Explanation:

Always On availability groups supports two availability modesasynchronous-commit mode and synchronous-commit mode

Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability- modes-always-on-availability-groups#SyncCommitAvMode

Question No.65

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 = #39;MyPassword1!#39;

CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = #39;TDE Certificate#39;; BACKUP CERTIFICATE TDE_Certificate TO FILE = #39;#39;d:\TDE_Certificate.cer#39; WITH PRIVATE KEY (FILE = #39;D:\TDE_Certificate.key#39;,

ENCRYPTION BY PASSWORD = #39;MyPassword1!#39;); 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?

  1. ALTER DATABASE Master SET ENCRYPTION OFF;

  2. CREATE CERTIFICATE TDE_Certificate FROM FILE = #39;d:\TDE_Certificate.cer#39;WITH PRIVATE KEY (FILE = #39;D:\TDE_Certificate.key#39;,DECRYPTION BY PASSWORD = #39;MyPassword1!#39;);

  3. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = #39;TDE Certificate#39;; USE Orders;CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

  4. CREATE CERTIFICATE TDE_Certificate FROM FILE = #39;d:\TDE_Certificate.cer#39;;

Correct Answer: B

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

Copy

USE AdventureWorks2012; CREATE CERTIFICATE Shipping11

FROM FILE = #39;c:\Shipping\Certs\Shipping11.cer#39;

WITH PRIVATE KEY (FILE = #39;c:\Shipping\Certs\Shipping11.pvk#39;, DECRYPTION BY PASSWORD = #39;sldkflk34et6gsS#v00#39;); GO

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql

Question No.66

You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012.

You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)

image

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:

Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.

Which Transact-SQL statement should you insert at line 06?

  1. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = #39;TCP://SecondaryServer:1433#39;) PRIMARY_ROLE (ALLOW_CONNECTIONS =

    READ_WRITE,READ_ONLY_ROUTING_LIST = NONE)

  2. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = #39;TCP://SecondaryServer:1433#39;)

  3. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = #39;TCP://SecondaryServer:1433#39;)

  4. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = YES,READ_ONLY_ROUTING_URL = #39;TCP://SecondaryServer:1433#39;)

Correct Answer: C

Explanation:

As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.

Question No.67

You are a database developer for an application hosted on a Microsoft SQL Server 2014 server. The database contains two tables that have the following definitions:

image

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.

Which Transact-SQL query do you use?

  1. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1

  2. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID

    = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk

    = 1

  3. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1

  4. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC

Correct Answer: A

Explanation:

Use descending (DESC) ordering.

To order by the number of orders we use ORDER BY COUNT(OrderAmount). Finally a WHERE close is needed: WHERE o.Rnk = 1

Question No.68

You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA.

You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server

agent jobs owned by UserA

To which role should you add UserA?

  1. DatabaseMailUserRole

  2. ServerGroupAdministratorGroup

  3. SQLAgentUserRole

  4. Securityadmin

Correct Answer: C

Explanation:

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

Members can create local jobs.

References:

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles

Question No.69

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?

  1. NORECOVERY

  2. FULL

  3. NO_CHECKSUM

  4. CHECKSUM

  5. Differential

  6. BULK_LOGGED

  7. STANDBY

  8. RESTART

  9. SKIP

  10. Transaction log

  11. DBO ONLY

  12. COPY_ONLY

  13. SIMPLE

  14. CONTINUE AFTER ERROR

Correct Answer: B

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.

Question No.70

You create a new Microsoft Azure subscription.

You need to create a group of Azure SQL databases that share resources. Which cmdlet should you run first?

  1. New-AzureRmAvailabilitySet

  2. New-AzureRmLoadBalancer

  3. New-AzureRmSqlDatabaseSecondary

  4. New-AzureRmSqlElasticPoolE. New-AzureRmVM

  5. New-AzureRmSqlServer

  6. New-AzureRmSqlDatabaseCopy

  7. New-AzureRmSqlServerCommunicationLink

Correct Answer: D

Explanation:

SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources (elastic Database Transaction Units (eDTUs)) at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

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

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.