2018 July New Micorsoft 70-462 Exam Dumps with PDF and VCE Free Updated Today! Following are some new 70-462 Real Exam Questions:
QUESTION 299
You are the senior database administrator for a Microsoft SQL Server environment that uses Microsoft Azure SQL Database.
You need to grant a junior database administrator only the permissions required to create new logins.
Which Microsoft Azure SQL Database role should you assign the junior database administrator?
A. setupadmin
B. loginmanager
C. securityadmin
D. serveradmin
Answer: B
Explanation:
Members of the login manager role can create new logins in the master database.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
QUESTION 300
You need to install SQL Server on a server. The server must meet the following requirements:
– Include SQL Server Integration Services (SSIS)
– Be able to run 32-bit SSIS legacy packages
You add the Integration Services shared feature to the installation.
Which other shared feature should you add to the installation?
A. Client Tools Connectivity
B. Master Data Services (MDS)
C. Management Tools – Complete
D. SQL Server Data Tools
Answer: D
Explanation:
SQL Server Data Tools (SSDT) provides project templates and design surfaces for building SQL Server content types — relational databases, Analysis Services models, Reporting Services reports, and Integration Services packages.
SSDT is backwards compatible, so you can always use the newest SSDT to design and deploy databases, models, reports, and packages that run on older versions of SQL Server.
References: https://docs.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-2017
QUESTION 301
Drag and Drop Question
Your company has a data warehouse that contains all of the sales data for your company. Several business applications read data from the data warehouse.
You plan to deploy an indexing strategy for a 2-TB table named FactSales contains a clustered index.
You need to recommend a solution for index maintenance that meets the following requirements:
– Maintenance must be performed only when index fragmentation reaches 50 percent.
– Maintenance must minimize the impact on the database reads from the business applications.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: Partition the table
Step 2: Create a partition function
To migrate SQL Server partition definitions to SQL Data Warehouse simply:
Eliminate the SQL Server partition scheme.
Add the partition function definition to your CREATE TABLE.
Step 3: Create a partition scheme
Partitioning requires a partition function and a partition scheme.
Step 4: Rebuild the clustered index
Rebuild the clustered index onto new partition function/scheme.
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition
https://blog.sqlrx.com/2017/02/24/sql-2016-partitioning-an-existing-table-with-a-columnstore-index/
QUESTION 302
Drag and Drop Question
You administer a Microsoft SQL Server instance that contains a database of confidential data.
You need to enable Transparent Data Encryption.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
The steps to setup TDE are:
Step 1: Create a master key
We must first create the master key. It must be created in the master database.
Example:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<UseStrongPasswordHere>’; go
Step 2: Create or obtain a certificate protected by the master key Once the master key is created, we will go ahead and create the actual certificate in the master database, not the user database.
Example:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’; go
USE AdventureWorks2012;
GO
Step 3: Create a database encryption key and protect it by the certificate Now, we must utilize our USE command to switch to the database, the user database, that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database.
Example:
USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
Step 4: Set the database to use encryption
Example:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO
References: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent- data-encryption
QUESTION 303
Hotspot Question
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 has 32 processor cores and 64 GB of RAM.
SQL1 hosts a database used for a public-facing marketing website. SQL1 performs Online Transaction Processing (OLTP) operations only.
Several of the queries that run on SQL1 use the 32 processor cores and complete in 30 ms.
You need to reduce the number of queries that use multiple cores. The solution must also reduce the number of processor cores used by the queries that require multiple processor cores.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.
Answer:
Explanation:
Cost Threshold for Parallelism
The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and is not a unit of time. The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.
Max Degree of Parallelism
When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for- parallelism-server-configuration-option
QUESTION 304
Drag and Drop Question
You administer two Microsoft SQL Server database servers named ProdSrv1 and ProdSrv2. Each server has a database named Orders.
You need to configure transactional replication from the OrderSummary table in the Orders database on ProdSrv1 to the OrderSummary table in the Orders database on ProdSrv2.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: Configure ProdSrv1 as a Distributor.
Step 2: Configure ProdSrv1 as a Publisher.
Step 3: Configure a publication of the Order-Summary table on ProdSrv1.
Step 4: Create a subscription of the publication in the Orders database on ProdSrv1.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and- distribution
QUESTION 305
Drag and Drop Question
You have a server that has SQL Server 2016 installed.
You need to configure native SQL Server backups for a database named Customers. The solution must use backup encryption.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct orders.
Answer:
Explanation:
The following are prerequisites for encrypting a backup:
1. Create a Database Master Key for the master database:
2. Create a certificate or asymmetric Key to use for backup encryption.
3. You can encrypt a backup when creating the backup of a database in any of the following dialog boxes:
Back Up Database (Backup Options Page) On the Backup Options page, you can select Encryption, and specify the encryption algorithm and the certificate or asymmetric key to use for the encryption. Using Maintenance Plan Wizard When you select a backup task, on the Options tab of the Define Backup ()Task page, you can select Backup Encryption, and specify the encryption algorithm and the certificate or key to use for the encryption.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption
QUESTION 306
Drag and Drop Question
You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes.
You install another server that has SQL Server installed.
Which three additional actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: Add a new availability replica in asynchronous mode. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: 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.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client’s connection strings must specify the application intent as “read-only.” That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
!!!RECOMMEND!!!
1.|2018 Latest 70-462 Exam Dumps (PDF & VCE) 313Q&As Download:
https://www.braindump2go.com/70-462.html
2.|2018 Latest 70-462 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/1CzCDfoHpyjc9sRJ9CnfrmejAQqzb8KVQ?usp=sharing
3.|2018 Latest 70-462 Study Guide Video: