Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 261:

  You administer a Microsoft Azure SQL Database instance named Inventory that contains a stored procedure named p_AddInventory.

  Users need to be able to SELECT from all tables in the database and execute the stored procedure.

  You need to grant only the necessary permissions.

  What should you do?

  A. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role.
  B. Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
  C. Add all users to the db_owner role.
  D. Add all users to the db_datawriter role. Add all users to the dbd_datareader role.
  A. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role.

  ---

  Executing as OWNER executes the procedure in the context of the procedure owner. If the procedure is created in a schema owned by dbo or the database owner, the procedure will execute with unrestricted permissions.

  Members of the db_datareader fixed database role can read all data from all user tables.

  References:

  https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/customizing-permissions-with-impersonation-in-sql-server

  https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

• Question 262:

  DRAG DROP

  You administer a Microsoft SQL Server clustered instance that has two nodes named Node 1 and Node 2.

  Node 1 fails and the cluster fails over to Node 2.

  You need to replace Node 1 and add it to the cluster.

  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.)

  Select and Place:

  

  

  ---

  Reference: http://technet.microsoft.com/en-us/library/ms181075.aspx

• Question 263:

  You administer a Microsoft SQL Server database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.

  Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.

  You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log.

  Which type of solution should you use?

  A. Extended events
  B. Columnstore index
  C. Partitioned tables
  D. Read committed snapshot
  C. Partitioned tables

• Question 264:

  You want to reproduce the same SQL Server 2012 installation configuration across five servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal?

  A. Configuration.xml
  B. Setup.ini
  C. Setup.xml
  D. ConfigurationFile.ini
  D. ConfigurationFile.ini

• Question 265:

  You are a database administrator for a Microsoft SQL Server database named AdventureWorks1.

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

  

  You need to implement an AlwaysOn Availability 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?

  

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

  ---

  How correctly configure AlwaysOn readable secondary replica:

  Add Application Intent in the connection string by adding: ;ApplicationIntent=ReadOnly

  ALLOW_CONNECTIONS property of the secondary replica is READ_ONLY.

  The READ_ONLY_ROUTING_URL for each replica is set. The TCP port is a port number that is used by the Database Engine of the SQL Server instance

  alteravailabilitygroupmyGroupName

  modifyreplicaon'myMachine1instanceName1'with

  (secondary_role(read_only_routing_url='tcp://myMachine1:1433'))

  References: https://msdn.microsoft.com/en-us/library/hh213002.aspx

  https://blogs.technet.microsoft.com/mdegre/2013/03/20/how-configure-correctly-alwayson-readable-secondary-replica-for-applicationintent/

• Question 266:

  You administer a Microsoft SQL Server database named Orders.

  Orders contain a table named OrderShip that is defined as follows:

  

  A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.

  Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

  

  Performance on this procedure is slow.

  You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.

  

  Which Transact-SQL statement should you use?

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

  ---

  Filtered statistics can improve query performance for queries that select from well-defined subsets of data. References: https://msdn.microsoft.com/en-us/library/ms190397.aspx

• Question 267:

  You administer two Microsoft SQL Server servers named ProdSrv1 and ProdSrv2.

  ProdSrv1 is configured as a Distributor.

  Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.

  You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.

  The distribution agent on ProdSrv2 regularly fails, displaying the following error message:

  "Cannot access the file. Operating system error code 5 (Access is denied.)."

  You need to configure the distribution agent by granting only the minimum required access to all accounts.

  What should you do?

  A. Configure the SQL Server service on ProdSrv2 to use the Local System account.
  B. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant EXECUTE permissions on xp_cmdshell to the Windows domain account.
  C. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.
  D. Grant EXECUTE permission on xp_cmdshell to the [NT SERVICE\SQLSERVERAGENT] account.
  C. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.

• Question 268:

  You have a server named SQL1 that has SQL Server installed. SQL1 hosts several applications.

  You plan to deploy a third-party application to SQL1. The application must have the following: A vendor-provided password for the sa account A vendor-provided user account that is a member of the sysadmin fixed server role A vendor-provided user account that is a member of the db_owner database role in the msdb database A vendor-provided user account that is a member of the db_owner fixed database role in the master database A vendor-provided user account that is a member of the SQLAgentOperatorRole fixed database role in the msdb database

  You need to identify the minimum number of SQL Server instances that you must add to SQL1 to support the application requirements. The solution must use the principle of least privilege.

  How many SQL Server instances should you add to SQL1?

  B. 1
  C. 2
  D. 3
  B. 1

  ---

  If there are any server role requirements it's a cue to me solution requires a dedicated SQL instance. I don't want any user of Database X having the ability to affect Database Y (other than through resource consumption which can be controlled via Resource Governor.)

  Reference: http://www.itprotoday.com/microsoft-sql-server/17-questions-every-sql-dba-should-ask-supporting-new-third-party-database

• Question 269:

  DRAG DROP

  You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.

  Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

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

• Question 270:

  Which of the following statements will set the maximum amount of system memory that is managed by the SQL Server Memory Manager to 4GB?

  A. sp_configure `max memory', 4;
  B. ALTER SYSTEM SET `max server memory'=4096;
  C. sp_configure `max server memory', 4;
  D. sp_configure `max server memory', 4096;
  D. sp_configure `max server memory', 4096;

  ---

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