Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 1:

  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...MODIFY FILE command along with the name and maxsize parameters.
  C. Use the ALTER DATABASE...MODIFY FILEGROUP command along with the name and maxsize parameters.
  D. Use the ALTER DATABASE...AUTO_SHRINK command along with the On parameter.
  B. Use the ALTER DATABASE...MODIFY FILE command along with the name and maxsize parameters.

  ---

  MODIFY FILE specifies the file that should be modified. The file could be a log file.

  MAXSIZE { max_size| UNLIMITED } specifies the maximum file size to which the file can grow.

  References: https://technet.microsoft.com/en-us/library/bb522469(v=sql.105).aspx

• Question 2:

  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.

  You administer all the deployments of Microsoft SQL Server 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 SQL Server Availability Group configured in Synchronous-Commit Availability Mode 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
  H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

• Question 3:

  You administer a SQL Server database instance.

  You need to configure the SQL Server Database Engine service on a failover cluster.

  Which user account should you use?

  A. The BUILTIN\LocalService account
  B. A domain user
  C. A local administrative user
  D. The BUILTIN\NetworkService account
  B. A domain user

  ---

  

  References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2017

• Question 4:

  DRAG DROP

  You administer a database named SalesDb that has users named UserA, UserB, and UserC. You need to ensure that the following requirements are met:

  * UserA must be able to provide Windows login access to the database.

  * UserB must be able to select, update, delete and insert data to the database tables.

  * UserC must be able to create new tables and stores procedures

  You need to achieve this goal by granting only the minimum permissions required.

  To which fixed database role or roles should you add the users?

  Select and Place:

  

  

• Question 5:

  You install SQL Server on a new server named SQL1 and create several databases.

  Users report that they fail to connect to any of the databases on SQL1.

  You discover that the TCP connections are disabled for the database engine on SQL1.

  You need to ensure that the users can connect to the databases on SQL1.

  What should you use?

  A. the SQL Server Installation Center
  B. the sp_configure stored procedure
  C. SQL Server Configuration Manager
  D. the ODBC Data Source Administrator
  C. SQL Server Configuration Manager

  ---

  To enable the TCP/IP network protocol

  1.

  Start SQL Server Configuration Manager. Click Start, point to All Programs, and click Microsoft SQL Server. Click Configuration Tools, and then click SQL Server Configuration Manager.

  2.

  In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  3.

  In the console pane, click Protocols for .

  4.

  In the details pane, right-click TCP/IP, and then click Enable.

  5.

  In the console pane, click SQL Server Services.

  6.

  In the details pane, right-click SQL Server (), and then click Restart, to stop and restart the SQL Server service. References: https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx

• Question 6:

  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.
  B. Install a new named SQL Server instance on the server.

• Question 7:

  You have a large partitioned fact table in a data warehouse. The table is stored as a clustered index.

  You need to modify the indexing solution to minimize the amount of disk space required to store the table.

  What should you do?

  A. Enable row compression for the clustered index.
  B. Enable page compression for the clustered index.
  C. Implement a nonclustered columnstore index.
  D. Implement a clustered columnstore index.
  D. Implement a clustered columnstore index.

  ---

  Use a clustered columnstore index for large data warehouse tables.

  The clustered columnstore index is more than an index, it is the primary table storage. It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. Clustered

  columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.

  Incorrect:

  Not C: A nonclustered columnstore index and a clustered columnstore index function the same. The difference is a nonclustered index is a secondary index created on a rowstore table, whereas a clustered columnstore index is the primary

  storage for the entire table.

  The nonclustered index contains a copy of part or all of the rows and columns in the underlying table. The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

  A nonclustered columnstore index enables real-time operational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance

• Question 8:

  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
  D. SQL Server Data Tools

  ---

  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 9:

  Which of the following transaction safety setting in SQL Server 2012 mirroring forces the mirror to be synchronized with the primary at all times?

  A. SAFETY FULL
  B. SAFETY OFF
  C. MIRROR SYNC
  D. MIRROR 100
  A. SAFETY FULL

  ---

  References: https://msdn.microsoft.com/en-us/library/ms189852%28v=sql.110%29.aspx

• Question 10:

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

  You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

  What should you create?

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

  ---

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