Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 61:

  You administer a database that has User A, B and C.

  User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.

  User C must be able to provide windows logins to the database

  To which role or roles should you add to UserA? Select all that apply.

  A. db_accessadmin
  B. db_ddladmin
  C. db_datawriter
  D. db_datareader
  E. db_owner
  B. db_ddladmin

  ---

  User A = db_ddladmin User B= db_datawriter, db_datareader User C = db_accessadmin

• Question 62:

  You administer all the deployments of Microsoft SQL Server 2012 in your company.

  You need to ensure that data changes are sent to a non-SQL Server database server in near real time.

  You also need to ensure that data on the primary server is unaffected.

  Which configuration should you use?

  A. SQL Server that includes an application database configured to perform transactional replication
  B. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
  C. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  D. SQL Server that includes an application database configured to perform snapshot replication
  E. 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
  F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  G. 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 the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
  A. SQL Server that includes an application database configured to perform transactional replication

  ---

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

• Question 63:

  A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year.

  Which Transact-SQL query should you use?

  

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

  ---

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

• Question 64:

  You administer a Microsoft SQL Server instance named SQL1 that hosts an OLTP database of 1 terabyte in size.

  The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.

  Backups are performed as shown in the following schedule:

  

  The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.

  The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.

  When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.

  You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time.

  What should you do on Saturday?

  A. Perform a differential backup at 08:59 hours.
  B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
  C. Create a database snapshot at 08:59 hours.
  D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
  E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours.
  F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours.
  C. Create a database snapshot at 08:59 hours.

• Question 65:

  You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  You need to complete the post-installation configuration. What should you do?

  A. Run the Configuration component in the Data Quality Client.
  B. Install ADOMD.NET.
  C. Run the Data Quality Server Installer.
  D. Make the data available for DQS operations.
  C. Run the Data Quality Server Installer.

  ---

  After you complete the SQL Server installation wizard, you must perform additional steps mentioned in this section to complete your Data Quality Server installation, configure it, and then use it.

  To complete the Data Quality Server installation, run the DQSInstaller.exe file.

  References: https://docs.microsoft.com/en-us/sql/data-quality-services/install-windows/install-data-quality-services?view=sql-server-2017#PostInstallationTasks

• Question 66:

  You are developing a SQL Server Integration Services (SSIS) package.

  You need to design a package to change a variable value during package execution by using the least amount of development effort.

  What should you use?

  A. Expression task
  B. Script task
  C. Execute SQL task
  D. Execute Process task
  E. Term Extraction transformation
  A. Expression task

  ---

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

• Question 67:

  You plan to migrate the database to Azure. You verify that all objects are valid for Azure SQL database. You need to ensure that users and logins are migrated to Azure. What should you do?

  A. Use the Copy Database wizard
  B. Use the Database Transfer wizard
  C. Use the SQL Management Studio to deploy the database to Azure
  D. Back up the database from the local server and restore it to Azure
  C. Use the SQL Management Studio to deploy the database to Azure

  ---

  Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.

  Steps:

  Create an empty Azure SQL database in the Azure portal (using a new or existing Azure SQL Database server)

  Create a server-level firewall in the Azure portal (if not previously created)

  Use the Data Migration Assistant (DMA) to import your SQL Server database into the empty Azure SQL database

  Use SQL Server Management Studio (SSMS) to change database properties.

  References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database

• Question 68:

  You administer two Microsoft SQL Server 2012 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 Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on Prodsrv1
  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 FULL CONTROL access for the domain account to the ReplData share on ProdSrv1
  C. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account
  D. Configure the Subscriber to use the Local System account
  A. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on Prodsrv1

• Question 69:

  Which of the following is required in order for a differential backup to be useful for recovery purposes?

  A. A full backup taken previous to the differential backup.
  B. Hard drive space that is 2 times the size of the database you want to restore.
  C. Less than 1000 database transactions an hour.
  D. A full backup taken after the differential backup.
  A. A full backup taken previous to the differential backup.

  ---

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

• Question 70:

  You administer a Microsoft SQL Server server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

  You plan to configure Instant File Initialization.

  You need to ensure that Data File Autogrow operations use Instant File Initialization.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Restart the SQL Server Agent Service.
  B. Disable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
  E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
  F. Enable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.

  ---

  How To Enable Instant File Initialization

  1.

  Open Local Security Policy and go to Local Policies --> User Rights Assignment.

  2.

  Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.

  3.

  Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.

  

  References:

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

  http://www.mssqltips.com/sqlservertip/2752/effect-of-instant-file-initialization-within-sql-server/