Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 181:

  You administer a Microsoft SQL Server database named Contoso that contains a single user-defined database role namedBillingUsers.

  All objects in Contoso are in the dbo schema.

  You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers.

  Which Transact-SQL statement should you use?

  

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  D. OptionD

• Question 182:

  You are a database administrator for a Microsoft SQL Server instance.

  You need to ensure that data can be migrated from a production server to two reporting servers with minimal data latency. You also need to ensure that data on the reporting server is always accessible.

  A. Database Snapshot
  B. Log Shipping
  C. Availability Groups
  D. Change Data Capture
  C. Availability Groups

• Question 183:

  HOTSPOT You administer a Microsoft SQL Server 2012 instance. You need to configure an existing SQL Authenticated Login that meets the following requirements: Enforces password policy No password expiration enforcement Resets the default database to AdventureWorks database Which option or options should you choose in the Login Properties of the login for User1? (To answer, configure the appropriate option or options in the dialog box in the answer are A. )

  Hot Area:

  

  

• Question 184:

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

  A. Use the SQL Server default instance and enable Contained Databases.
  B. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.
  C. Install a new named SQL Server instance on the server.
  D. Install a new default SQL Server instance on the server.
  C. Install a new named SQL Server instance on the server.

• Question 185:

  You administer a Microsoft SQL Server 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 Settings. Set the maximum size of the file for the transaction log.
  D. 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.
  E. Use the ALTER DATABASE...AUTO_SHRINK command along with the On parameter.
  D. 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.

• Question 186:

  You administer a Microsoft SQL Server database server that has SQL Server Integration Services (SSIS) installed.

  You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

  You need to configure the SQL Server environment to support these packages.

  What should you do?

  A. Create SSIS configuration files for the packages.
  B. Create an Integration Services catalog.
  C. Install Data Quality Services.
  D. Install Master Data services.
  B. Create an Integration Services catalog.

  ---

  The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you've deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.

  The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.

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

• Question 187:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a table that has the following definition:

  

  You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.

  You need to ensure that the following requirements are met:

  The first row of the file contains the first row of data.

  Each record is of the same length.

  The date follows the U.S. date format.

  The file supports international characters.

  What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Check the answer in explanation.

  

• Question 188:

  Note: This question is part of a series of questions that use the same set of 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 administer a Microsoft SQL Server database named Contoso on a server named Server01.

  You need to be notified immediately when fatal errors occur on Server01. What should you create?

  A. An Alert
  B. A Server Audit Specification
  C. An Extended Event session
  D. A Resource Pool
  E. A Policy
  F. A SQL Profiler Trace
  G. A Database Audit Specification
  A. An Alert

  ---

  Reference: http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

• Question 189:

  You are implementing a SQL Server five-node failover cluster.

  You need to choose a quorum configuration. Which configuration should you use?

  A. Distributed file system (DFS)
  B. Cluster Shared Volume (CSV)
  C. Node and Disk Majority
  D. Node Majority
  D. Node Majority

  ---

  Node Majority is recommended for clusters with an odd number of nodes.

  Incorrect Answers:

  A, B: These are not quorum configuration choices.

  : Node and Disk Majority is recommended for clusters with an even number of nodes.

  References: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731739(v=ws.11)

• Question 190:

  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 minimum amount of data is lost.

  Which recovery model should the database use?

  A. FULL
  B. DBO_ONLY
  C. CONTINUE_AFTER_ERROR
  D. CHECKSUM
  E. NO_CHECKSUM
  F. SIMPLE
  G. Transaction log
  H. SKIP
  I. RESTART
  J. COPY_ONLY
  A. FULL

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server