70-462

Microsoft 70-462

Administering Microsoft SQL Server 2012 Databases Version: 30.0

Microsoft 70-462 Exam QUESTION NO: 1 You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution. You are tasked with designing a scale-out and high-availability SQL Server 2012 Online Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances. Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed. What should you include in your solution? A. You should include two servers configured in an Active-Active SQL Server 2012 Cluster. B. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to a secondary database. C. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster. D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group. E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group. Answer: B Explanation:

QUESTION NO: 2 You have been hired as a Database Consultant by ABC.com to design a database solution for a new application. You are tasked with designing a high-availability database solution that uses SQL Server 2012 to host a primary database. The solution should maintain a near real-time copy of the data on a second non-Microsoft database. What should you include in your solution? A. You should include a primary database with scheduled log shipping to the secondary database configured. B. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster. C. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to the secondary database. D. You should include two servers in an Asynchronous-Commit Availability Mode Availability "Pass Any Exam. Any Time." - www.actualtests.com

2

Microsoft 70-462 Exam Group. E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group. Answer: C Explanation:

QUESTION NO: 3 You work as the Senior Database Administrator (DBA) at ABC.com. The company has a main office and 10 branch offices. Each branch office contains a single database server running Microsoft SQL Server 2012. The main office has multiple clustered servers running Microsoft SQL Server 2012. Your role includes the management of the entire Microsoft SQL Server 2012 infrastructure. The company runs a custom application that stores data in a large Microsoft SQL Server 2012 database. The primary database is hosted in the main office. Each branch office SQL Server hosts a copy of the database. You need to configure a solution that will replicate the entire primary database from the main office SQL Server every weekend. What should you include in your solution? A. Transactional Replication. B. Log Shipping. C. Snapshot Replication. D. SQL Server Availability Group. Answer: C Explanation:

QUESTION NO: 4 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Microsoft SQL Server 2012. All databases are hosted on a SAN (Storage Area Network). You need to design a database solution for a new application. You are tasked with designing a high-availability database solution. The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution? "Pass Any Exam. Any Time." - www.actualtests.com

3

Microsoft 70-462 Exam A. You should include two servers configured as a failover cluster. B. You should include two servers and database mirroring. C. You should include two servers and log shipping. D. You should include two servers configure as a SQL Server Availability Group. Answer: A Explanation:

QUESTION NO: 5 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers. A full backup of all databases is taken every night at midnight. A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log backup is taken every 15 minutes for databases configured with the Full Recovery Model. One application named ABCApp1 stores data in a database named ABCApp1DB. ABCApp1DB is configured with the Simple Recovery Model. ABCApp1DB fails at 3:25am. You discover that the last differential backup of ABCApp1DB failed. You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss. Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers). A. Restore the latest full backup. B. Restore the latest differential backup. C. Restore the latest log backup. D. Restore each differential backup taken since the last full backup. E. Restore each log backup since the last full backup. Answer: A Explanation:

QUESTION NO: 6

"Pass Any Exam. Any Time." - www.actualtests.com

4

Microsoft 70-462 Exam You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers. A full backup of all databases is taken every night at midnight. A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log backup is taken every 15 minutes for databases configured with the Full Recovery Model. One application named ABCApp1 stores data in a database named ABCApp1DB. ABCApp1DB is configured with the Simple Recovery Model. You are informed at 10:00am that a number of pages in ABCApp1DB display an error when attempts are made to access the pages. You discover that the pages are corrupt. You need to restore ABCApp1DB from backup as quickly as possible and minimize data loss. Which of the following steps should you perform to restore ABCApp1DB? (Choose one or more answers). A. Restore the latest full backup. B. Restore the latest differential backup. C. Restore the latest log backup. D. Restore each differential backup taken since the last full backup. E. Restore each log backup since the last full backup. F. Restore the pages from backup. Answer: A,B Explanation:

QUESTION NO: 7 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers. A full backup of all databases is taken every night at midnight. A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log "Pass Any Exam. Any Time." - www.actualtests.com

5

Microsoft 70-462 Exam backup is taken every 15 minutes for databases configured with the Full Recovery Model. One application named ABCApp2 stores data in a database named ABCApp2DB. ABCApp2DB is configured with the Bulk-Logged Recovery Model. You are informed at 10:00am that a number of pages in ABCApp2DB display an error when attempts are made to access the pages. You discover that the pages are corrupt. You need to restore ABCApp2DB from backup as quickly as possible and minimize data loss. Which of the following steps should you perform to restore ABCApp2DB? (Choose one or more answers). A. Restore the latest full backup. B. Restore the latest differential backup. C. Restore the latest log backup. D. Restore each differential backup taken since the last full backup. E. Restore each log backup since the last full backup. F. Restore the pages from backup. Answer: F Explanation:

QUESTION NO: 8 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers. A full backup of all databases is taken every night at midnight. A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log backup is taken every 15 minutes for databases configured with the Full Recovery Model. One application named ABCApp3 stores data in a database named ABCApp3DB. ABCApp3DB is configured with the Simple Recovery Model. The server that hosts ABCApp3DB suffers a catastrophic hardware failure at 2:30pm.


6

Microsoft 70-462 Exam You need to restore ABCApp3DB from backup to another server running Microsoft SQL Server 2012 as quickly as possible and minimize data loss. Which of the following steps should you perform to restore ABCApp3DB? (Choose one or more answers). A. Restore the latest full backup. B. Restore the latest differential backup. C. Restore the latest log backup. D. Restore each differential backup taken since the last full backup. E. Restore each log backup since the last full backup. F. Restore the pages from backup. Answer: A,B Explanation:

QUESTION NO: 9 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses several custom applications that store data in databases on the Microsoft SQL Server 2012 servers. A full backup of all databases is taken every night at midnight. A differential backup of all databases is taken on the hour every hour starting at 3am until the last backup at 11pm. A log backup is taken every 15 minutes for databases configured with the Full Recovery Model. One application named ABCApp4 stores data in a database named ABCApp4DB. ABCApp4DB is configured with the Full Recovery Model. The server that hosts ABCApp4DB suffers a catastrophic hardware failure at 3:25pm. You need to restore ABCApp4DB from backup to another server running Microsoft SQL Server 2012 as quickly as possible and minimize data loss. Which of the following steps should you perform to restore ABCApp4DB? (Choose one or more answers). A. Restore the latest full backup. B. Restore the latest differential backup. "Pass Any Exam. Any Time." - www.actualtests.com

7

Microsoft 70-462 Exam C. Restore the latest log backup. D. Restore each differential backup taken since the last full backup. E. Restore each log backup since the last full backup. F. Restore the pages from backup. Answer: A,B,C Explanation:

QUESTION NO: 10 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The company has a main office in Atlanta and branch offices in New York and Washington. Each office is configured as an Active Directory site in the ABC.com domain. Each branch office has a server running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has multiple servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has three company departments. All departments are connected by a fast Ethernet network. Each department uses an application that stores data in a database on a SQL server in that department. Data is written by each department to their local database. The data written to each database needs to be replicated in near real-time to the databases in the other departments. Which replication method should you use? A. Scheduled Log shipping. B. Transactional replication. C. Merge replication. D. Snapshot replication. E. Peer-to-Peer replication. Answer: E Explanation:

QUESTION NO: 11 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The company has a main office in Atlanta and branch offices in New York and Washington. Each office is configured as an Active "Pass Any Exam. Any Time." - www.actualtests.com

8

Microsoft 70-462 Exam Directory site in the ABC.com domain. Each branch office has a server running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has multiple servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. A database in the main office needs to be replicated to the two branch offices once a week. Most of the data in the main office copy of the database is changed during the week. Branch office users do not make changes to the database. Which replication method should you use? A. Scheduled Log shipping. B. Transactional replication. C. Merge replication. D. Snapshot replication. E. Peer-to-Peer replication. Answer: D Explanation:

QUESTION NO: 12 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The company has a main office in Atlanta and branch offices in New York and Washington. Each office is configured as an Active Directory site in the ABC.com domain. Each branch office has a server running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has multiple servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office and the Washington branch office both maintain copies of named ProductsDB. Users in both offices make changes to the ProductsDB database. The changes need to be replicated to the other office three times per day. The Wide Area Network (WAN) connection between the two offices is unreliable. Which replication method should you use? A. Scheduled Log shipping. B. Transactional replication.


9

Microsoft 70-462 Exam C. Merge replication. D. Snapshot replication. E. Peer-to-Peer replication. Answer: C Explanation:

QUESTION NO: 13 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The company has a main office in Atlanta and branch offices in New York and Washington. Each office is configured as an Active Directory site in the ABC.com domain. Each branch office has a server running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has multiple servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has three company departments. All departments are connected by a fast Ethernet network. Each department uses an application that stores data in a database on a SQL server in that department. Only users in the Marketing department make changes to the database. The data written to the database needs to be replicated in near real-time to the databases in the other departments. Which replication method should you use? A. Scheduled Log shipping. B. Transactional replication. C. Merge replication. D. Snapshot replication. E. Peer-to-Peer replication. Answer: B Explanation:

QUESTION NO: 14 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The company has a main office in Atlanta and branch offices in New York and Washington. Each office is configured as an Active Directory site in the ABC.com domain.


10

Microsoft 70-462 Exam Each branch office has a server running Windows Server 2008 R2 and Microsoft SQL Server 2012. The main office has multiple servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. Each office runs an application that stores data in a database. The data is stored in tables that use the varchar (max) and XML data types. The databases are replicated to the other offices using merge replication. The replication between the offices sometimes fails. On investigation, you discover that the replication timed out. How can you improve the success rate of the merge replications? A. Configure the slow link detection setting in a group policy object (GPO). B. Change the replication type to transactional replication. C. Enable the slow link agent profile on the SQL Server merge agents. D. Configure the merge agents at each location to run simultaneously. Answer: C Explanation:

QUESTION NO: 15 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. Two of the SQL Servers are named ABC-SQL1 and ABC-SQL2. A database named App1DB runs on ABC-SQL1. You want to configure database mirroring to configure a mirror of App1DB on ABC-SQL2. You verify that the App1DB1 is configured with the Full Recovery Model You take a full backup of App1DB1. What should you do next? A. You should restore the App1DB1 database to ABC-SQL2 as App1DB1 with the Recovery option selected. B. You should restore the App1DB1 database to ABC-SQL2 as App1DB1 with the NoRecovery option selected. C. You should restore the App1DB1 database to ABC-SQL2 as App1DB1_Bak with the Recovery "Pass Any Exam. Any Time." - www.actualtests.com

11

Microsoft 70-462 Exam option selected. D. You should restore the App1DB1 database to ABC-SQL2 as App1DB1_Bak with the NoRecovery option selected. Answer: B Explanation:

QUESTION NO: 16 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. Two of the SQL Servers are named ABC-SQL1 and ABC-SQL2. A database named App1DB runs on ABC-SQL1. You want to configure database mirroring to configure a mirror of App1DB on ABC-SQL2. You verify that the App1DB1 is configured with the Full Recovery Model You take a full backup of App1DB1 and restore it to ABC-SQL2. You then take a transaction log backup of App1DB1. What should you do next? A. You should restore the transaction logs to ABC-SQL1 with the Recovery option selected. B. You should restore the transaction logs to ABC-SQL1 with the NoRecovery option selected. C. You should restore the transaction logs to ABC-SQL2 with the Recovery option selected. D. You should restore the transaction logs to ABC-SQL2 with the NoRecovery option selected. Answer: D Explanation:

QUESTION NO: 17 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. Three of the SQL Servers are named ABC-SQL1, ABC-SQL2 and ABC-SQL3 and are configured as a Windows Server Failover Clustering (WSFC) cluster. You want to configure a number of databases in an availability group for data redundancy.


12

Microsoft 70-462 Exam You create the availability group on ABC-SQL1 and name it AG1. You configure ABC-SQL1/HA as the primary replica and configure ABC-SQL2/HA and ABC-SQL3/HA as secondary replicas. You need to configure backups for the SQL servers. You want to ensure that no backups occur on the primary replica. Backups should occur on ABC-SQL2/HA when the server is available or on ABC-SQL3/HA if ABC-SQL2/HA is unavailable. Which three of the following options describe how you should configure the backups? (Choose three answers). A. Enter 10 as the backup priority for ABC-SQL2/HA. B. Enter 10 as the backup priority for ABC-SQL3/HA. C. Enter 20 as the backup priority for ABC-SQL2/HA. D. Enter 20 as the backup priority for ABC-SQL3/HA. E. Configure the Secondary Only backup preference option for AG1. F. Configure the Prefer Secondary backup preference option for AG1. Answer: B,C,E Explanation:

QUESTION NO: 18 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A server named ABC-SQL1 is configured with several scheduled SQL Server Agent jobs that are configured to run overnight. One morning you discover that a number of the scheduled jobs failed. You examine the logs to troubleshoot the issue. However you discover that the logs contain only truncated error messages. You need to view the full error messages to troubleshoot the failed jobs. How can you configure the logging of full error messages? A. By increasing the Application log maximum size. B. By configuring output files. C. By changing the output log directory. D. By disabling Event Forwarding. Answer: B Explanation:


13

Microsoft 70-462 Exam QUESTION NO: 19 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running different versions of Microsoft SQL Server. You create two databases named DB1 and DB2. You need to configure the compatibility level for DB1 to be compatible with SQL Server 2008. You need to configure the compatibility level for DB2 to be compatible with SQL Server 2012. How should you configure the databases? (Choose two answers). A. You should configure the compatibility level of DB1 to 90. B. You should configure the compatibility level of DB1 to 100. C. You should configure the compatibility level of DB1 to 110. D. You should configure the compatibility level of DB2 to 90. E. You should configure the compatibility level of DB2 to 100. F. You should configure the compatibility level of DB2 to 110. Answer: B,F Explanation:

QUESTION NO: 20 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A server named ABC-SSIS1 runs Microsoft SQL Server 2012 server and has SQL Server Integration Services (SSIS) installed. You plan to deploy a project containing several packages to ABC-SSIS1. You want to be able to manage the packages, run the packages, and configure runtime values for the packages by using environments on ABC-SSIS1. How should you configure ABC-SSIS1? A. By installing MDS (Master Data Services). B. By installing Reporting Services. C. By configuring an SSISDB Catalog. D. By disabling DQS (Data Quality Services) Cleansing transformation.


14

Microsoft 70-462 Exam Answer: C Explanation:

QUESTION NO: 21 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. You create an email distribution group named [email protected]. You add database administrators and other members of the IT team to the [email protected] distribution group. You want to configure a SQL server to send an email to [email protected] when database transaction logs reach a certain size. You enable Database Mail then create an operator with the [email protected] email address. What should you do next? A. You should enable Event Forwarding. B. You should create a SQL Server Agent Alert. C. You should configure SQL Mail. D. You should configure Output Files. Answer: B Explanation:

QUESTION NO: 22 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. You plan to deploy an application that stores data in a database. The database will be named AppDB1. You install a second instance of Microsoft SQL Server 2012 on ABC-SQL1 named Apps. The Apps instance will be used to host application databases.


15

Microsoft 70-462 Exam You install the application but discover that it is unable to connect to the ABC-SQL1\Apps instance. How can you enable the Apps instance to be located by the application? A. By starting the Computer Browser service. B. By restarting the default SQL Server instance. C. By installing SQL Server Integration Services. D. By starting the SQL Server Browser service. Answer: C Explanation:

QUESTION NO: 23 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. Mia works as an administrator in the Marketing department. John works as the Sales Manager. Mia creates a table named Customers in a database named DB1. John creates a table named Sales in the DB1 database. You create a stored procedure named CustomerSales that queries the data in the Customers and Sales tables. You need to grant a user named Tess the minimum permissions to use the CustomerSales stored procedure. Which permissions should you assign to Tess? A. You should assign the Create View permission on the tables and the Execute permission on the stored procedure. B. You should assign Select permission on the tables and the Create View permission on the stored procedure. C. You should assign Select permission on the tables and the Execute permission on the stored procedure. D. You should assign Execute permission on the tables and the Create View permission on the stored procedure. E. You should assign Execute permission on the tables and the Select permission on the stored procedure. F. You should assign the Create View permission on the tables and the Select permission on the "Pass Any Exam. Any Time." - www.actualtests.com

16

Microsoft 70-462 Exam stored procedure. Answer: C Explanation:

QUESTION NO: 24 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database named SalesDB has a table named Customers. You create a stored procedure named Customers.NoRecentOrders. The stored procedure is configured to delete customers from the database that have not placed an order in the last 12 months. You need to grant a user named Tess the minimum permissions to use the CustomerSales stored procedure. Which of the following Transact-SQL statements would give Tess the required permissions? A. GRANT SELECT ON Customers.NoRecentOrders TO Tess. B. GRANT DELETE ON Customers.NoRecentOrders TO Tess. C. GRANT EXECUTE ON Customers.NoRecentOrders TO Tess. D. GRANT SELECT ON SalesDB.Customers TO Tess. E. GRANT DELETE ON SalesDB.Customers TO Tess. F. GRANT EXECUTE ON SalesDB.Customers TO Tess. Answer: C Explanation:

QUESTION NO: 25 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. All servers are members of a single Active Directory Domain Services (AD DS) domain named ABC.com. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. You plan to create a contained database on ABC-SQL1. You need to create a user account for the contained database. Your configuration must ensure that you can move the contained "Pass Any Exam. Any Time." - www.actualtests.com

17

Microsoft 70-462 Exam database to other database servers in the domain without the need to configure additional user accounts or permissions. What should you do? A. You should create a domain user account. B. You should local user account on ABC-SQL1. C. You should create a SQL user without login. D. You should create a contained user. Answer: D Explanation:

QUESTION NO: 26 You work as a Database Administrator (DBA) at ABC.com. The ABC.com network is configured as a single Active Directory Domain Services (AD DS) domain. The network includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. A database named Corporate contains a schema named Marketing. The Marketing schema contains several tables. The tables are accessed by all users in the Marketing department. You need to prevent the junior Marketing users from deleting any records in any of the Marketing schema tables. You need a solution for grouping the junior Marketing users for administration and configuring the required permissions for them. Your solution must minimize administrative effort. Which two of the following steps should your solution include? (Choose two). A. Create an Active Directory group containing the junior Marketing users. B. Create custom database role containing the junior Marketing users. C. Configure the permissions on the Marketing schema to deny the Delete permission to the Active Directory Group. D. Configure the permissions on the Marketing schema to deny the Delete permission to the custom database role. E. Configure the permissions on each table in the Marketing schema to deny the Delete permission to the Active Directory Group. F. Configure the permissions on each table in the Marketing schema to deny the Delete permission to the custom database role. Answer: B,D "Pass Any Exam. Any Time." - www.actualtests.com

18

Microsoft 70-462 Exam Explanation:

QUESTION NO: 27 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. All databases on ABCSQL1 have the TRUSTWORTHY Database Property set to ON. A junior Database Administrator (DBA) named Mia needs to view database-level information for a database named DB1. You create a stored procedure that uses Dynamic Management Views (DMVs) to return the required information and grant Mia permission to run the stored procedure. You need to give Mia the minimum permissions for the stored procedure to return the required information. Which permission should you grant Mia? A. The Sysadmin role on ABC-SQL1. B. The VIEW SERVER STATE permission on ABC-SQL1. C. The DB_OWNER role on DB1. D. The VIEW DATABASE STATE permission DB1. Answer: D Explanation:

QUESTION NO: 28 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. All databases on ABCSQL1 have the TRUSTWORTHY Database Property set to ON. A Support Technician named Tess needs to view server-level implementation-specific state data on ABC-SQL1. You create a stored procedure that uses Dynamic Management Views (DMVs) to return the required information and grant Tess permission to run the stored procedure.


19

Microsoft 70-462 Exam You need to give Tess the minimum permissions for the stored procedure to return the required information. Which permission should you grant Tess? A. The Sysadmin role on ABC-SQL1. B. The VIEW SERVER STATE permission on ABC-SQL1. C. The Sysadmin role on all databases on ABC-SQL1. D. The VIEW DATABASE STATE permission on all databases on ABC-SQL1. Answer: B Explanation:

QUESTION NO: 29 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. ABC-SQL1 hosts a database named CorpDB. You want to know if another DBA has added any logins to any fixed server roles on ABC-SQL1. Which server-level audit action group will return the required information? A. SERVER_PERMISSION_CHANGE_GROUP B. SERVER_ROLE_MEMBER_CHANGE_GROUP C. SERVER_LOCAL_CHANGE_GROUP D. SERVER_OPERATION_GROUP E. DATABASE_ROLE_MEMBER_CHANGE_GROUP Answer: B Explanation:

QUESTION NO: 30 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. "Pass Any Exam. Any Time." - www.actualtests.com

20

Microsoft 70-462 Exam A database server named ABC-SQL1 runs Microsoft SQL Server 2012. ABC-SQL1 hosts a database named CorpDB. You want to know if any users have been created, altered, or dropped from the CorpDB database. Which server-level audit action group will return the required information? A. SERVER_PERMISSION_CHANGE_GROUP B. DATABASE_PERMISSION_CHANGE_GROUP C. DATABASE_PRINCIPAL_CHANGE_GROUP D. DATABASE_OPERATION_GROUP E. DATABASE_ROLE_MEMBER_CHANGE_GROUP Answer: C Explanation:

QUESTION NO: 31 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012. ABC-SQL1 hosts a database named CorpDB. You want to know if a login is added to or removed from a database role for the CorpDB database. Which server-level audit action group will return the required information? A. DATABASE_OBJECT_PERMISSION_CHANGE_GROUP B. DATABASE_PERMISSION_CHANGE_GROUP C. DATABASE_PRINCIPAL_CHANGE_GROUP D. DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP E. DATABASE_ROLE_MEMBER_CHANGE_GROUP Answer: E Explanation:

QUESTION NO: 32 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. "Pass Any Exam. Any Time." - www.actualtests.com

21

Microsoft 70-462 Exam A database named SalesDB runs on a server named ABC-SQL1. You want to end a session with a Server Process ID of 55. Which Transact-SQL statement should you run to end the session without affecting other connections to the server? A. END 55 B. KILL 55 C. STOP 55 D. DELETE 55 Answer: B Explanation:

QUESTION NO: 33 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. You receive reports of poor performance on a server named ABC-SQL1. You suspect that heavy disk I/O could be causing the problem. Which two of the following could you use to determine the current disk load? (Choose two) A. Task Manager. B. Resource Monitor. C. SQL Server Error Log. D. Performance Monitor. E. Disk Management. Answer: B,D Explanation:

QUESTION NO: 34 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012.


22

Microsoft 70-462 Exam You receive reports of problems accessing a database named DB1 a server named ABC-SQL1. You suspect that a blocked process may be causing the issue. Which of the following could you use to determine if a process is being blocked? A. Performance Monitor. B. sys.dm_exec_trigger_stats C. sys.dm_exec_requests DMV D. SQL Server Error Log. E. Task Manager. Answer: C Explanation:

QUESTION NO: 35 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. You receive reports of problems accessing a database named DB1 a server named ABC-SQL1. You want to check the logical and physical integrity of all the objects in the DB1 database. Which of the following Transact-SQL commands could you use to check the integrity of the database? A. DBCC CHECKDB B. DBCC CHECKIDENT C. DBCC CLEANTABLE D. DBCC CHECKALLOC E. DBCC CHECKTABLE Answer: A Explanation:

QUESTION NO: 36 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses an application that stores data in a database on one of the Microsoft SQL "Pass Any Exam. Any Time." - www.actualtests.com

23

Microsoft 70-462 Exam Server 2012 servers. When using the application, an error pops up. You look in the Windows Application Event Log and the Windows System Event Log for information about the error. You are unable to find the required information so you look in the SQL Server log. However, you are still unable to find information to troubleshoot the error. How can you retrieve information about the error to troubleshoot the cause of the problem? A. Run the SQL Server Profiler. B. Use the sqlos.wait_info event to create an Extended Events (XEvents) session. C. Run the SQL Server Query Analyzer. D. Use the sqlserver.error_reported event to create an Extended Events (XEvents) session. Answer: D Explanation:

QUESTION NO: 37 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses an application that stores data in an OLTP database on a Microsoft SQL Server 2012 server named ABC-SQL1. The load on the database is high during office hours. You receive reports from users that the application is running slowly. You suspect that database performance may be the cause of the problem. You want to see if the server is taking longer than 1 second to process any queries to the application database and list the queries. Which of the following should you use to monitor the queries for a period of time? A. The DBCC TRACEON Transact-SQL command with the 1222 flag. B. The DBCC TRACEOFF Transact-SQL command. C. The DBCC TRACESTATUS Transact-SQL command with the 1222 flag. D. The DBCC UPDATESTATUS Transact-SQL command. Answer: A Explanation:


24

Microsoft 70-462 Exam QUESTION NO: 38 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses an application that stores data in a database on one of the Microsoft SQL Server 2012 servers. You receive reports from users that the application is running slowly. You need to troubleshoot the performance issue. Which of the following should you use to capture and replay trace activity? A. System Monitor. B. SQL Profiler. C. Extended Events. D. Resource Monitor. Answer: B Explanation:

QUESTION NO: 39 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. The company uses an application that stores data in a database on one of the Microsoft SQL Server 2012 servers. You receive reports from users that the application is running slowly. You need to troubleshoot the performance issue. You suspect high page latch or page I/O latch to be causing the performance issues. Which of the following should you use to view page latch information? A. System Monitor. B. SQL Profiler. C. Extended Events. D. Resource Monitor. "Pass Any Exam. Any Time." - www.actualtests.com

25

Microsoft 70-462 Exam Answer: C Explanation:

QUESTION NO: 40 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database named AppDB runs on a server named ABC-SQL1. All databases on ABC-SQL1 are configured to store their database files on the D: drive and their transaction log files on the E: drive. The E: drive sometimes runs low on space. You want to limit the maximum size of the transaction log files for the database. How can you limit the size of a transaction log file? A. By using the SET LOGFILE option in the ALTER DATABASE command. B. By configuring a maximum size in the Transaction log Autogrowth window. C. By configuring a maximum size in the Database Settings window of the SQL Server Properties. D. By configuring a maximum size in the Memory settings window of the SQL Server Properties. Answer: B Explanation:

QUESTION NO: 41 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. You install a new server named ABC-SQL5. You configure MSSQLSERVER service to run as a domain user account named TESTING\SQLSVCAcc. You want to enable Instant File Initialization to improve performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW operations on ABC-SQL5. Which two of the following steps should you perform to enable Instant File Initialization?


26

Microsoft 70-462 Exam A. Restart the SQL Server Service. B. Assign the Perform Volume Maintenance Tasks right to TESTING\SQLSVCAcc. C. Add TESTING\SQLSVCAcc to the local Administrators group ABC-SQL5. D. Enable snapshot isolation. E. Disable the SQL Server VSS Writer service. F. Restart the SQL Server Integration Services Service. Answer: A,B Explanation:

QUESTION NO: 42 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. Two SQL servers named ABC-CL1a and ABC-CL1b run Microsoft SQL Server 2012 and are configured as an Active/Passive failover cluster. ABC-CL1a is currently the active node in the cluster. You want to add a third server named ABC-CL1c to the cluster and enable it to be used by the SQL Server instance installed on ABC-CL1a and ABC-CL1b. Which two of the following steps should you perform to install SQL Server? (Choose two) A. Run SQL Server Setup.exe on ABC-CL1a. B. Run SQL Server Setup.exe on ABC-CL1b. C. Run SQL Server Setup.exe on ABC-CL1c. D. Select the New SQL Server stand-alone installation option. E. Select the Add Node to SQL Server Failover Cluster option. Answer: C,E Explanation:

QUESTION NO: 43 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012.


27

Microsoft 70-462 Exam The databases are hosted on a hard drive array configured as drive D: in the operating system. The tempdb database is hosted on a separate hard drive configured as drive T:. You shutdown ABC-SQL1 for maintenance. When you attempt to start ABC-SQL1, the hard drive configured as drive T: fails. SQL Server fails to start. How can you start SQL Server? A. By starting SQL server with the –M option. B. By starting SQL server with the –G option. C. By starting SQL server with the –F option. D. By starting SQL server with the –C option. Answer: C Explanation:

QUESTION NO: 44 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A database server named ABC-SQL1 runs Microsoft SQL Server 2012 and hosts several databases on the default SQL Server instance. You have been asked to deploy an application named App1. App1 requires a SQL Server database named App1DB. The documentation for App1 states that it needs a SQL Server login with Sysadmin permissions. To comply with company security policy, the SQL Server login for App1 must not be able to access other databases on ABC-SQL1. How should you configure App1DB1? A. You should create App1DB1 as a contained database. B. You should remove the Sysadmin role from the other databases. C. You should create App1DB1 on a new named SQL Server instance. D. You should enable snapshot isolation. Answer: C


28

Microsoft 70-462 Exam Explanation:

QUESTION NO: 45 You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Windows Server 2008 R2 and Microsoft SQL Server 2012. One of the servers is a high-end, enterprise-level SQL Server named ABC-SQL6 that is running on a server with 32 CPU cores. The server hosts a highly active OLTP database. You need to performance tune ABC-SQL6 to improve the performance of the database. You want to configure SQL Server to use 24 out of the 32 CPUs available by allocating 8 CPUs to SQL Server disk IO and 16 CPUs to all other SQL Server processing. What should you do? A. You should configure I/O affinity mask. B. You should configure processor affinity mask. C. You should configure process priority. D. You should configure SQL Server priority. Answer: A Explanation:

QUESTION NO: 46 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. Two SQL servers named ABC-CL1a and ABC-CL1b run Microsoft SQL Server 2012 and are configured as an Active/Passive failover cluster. The cluster hosts a large database named CorpDB. The database stores 3 Terabytes of data. The data file and transaction log file are stored on a SAN (Storage Area Network). A full backup of CorpDB starts every Friday at 11pm and runs over the weekend.


29

Microsoft 70-462 Exam A full backup fails one weekend due to an invalid checksum. How can you ensure the full backup continues to run if corrupted data is detected? A. By running the backup with the NORECOVERY option. B. By running the backup with the NO_CHECKSUM option. C. By running the backup with the CHECKSUM option. D. By running the backup with the CONTINUE_AFTER_ERROR option. E. By running the backup with the SKIP option. Answer: D Explanation:

QUESTION NO: 47 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. Two SQL servers named ABC-CL1a and ABC-CL1b run Microsoft SQL Server 2012 and are configured as an Active/Passive failover cluster. The cluster hosts a large database named CorpDB. The database stores 3 Terabytes of data. The data file and transaction log file are stored on a SAN (Storage Area Network). A full backup of CorpDB starts every Friday at 11pm and runs over the weekend. Further backups take place every three hours during the week. You need to minimize the amount to data lost in the event of a hardware failure. Which recovery model would minimize the amount of data lost? A. SIMPLE B. CHECKSUM C. FULL D. TRANSACTION LOG E. BULK LOGGED Answer: C Explanation: "Pass Any Exam. Any Time." - www.actualtests.com

30

Microsoft 70-462 Exam

QUESTION NO: 48 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. Two SQL servers named ABC-CL1a and ABC-CL1b run Microsoft SQL Server 2012 and are configured as an Active/Passive failover cluster. The cluster hosts a large database named CorpDB. The database stores 3 Terabytes of data. The data file and transaction log file are stored on a SAN (Storage Area Network). A full backup of CorpDB starts every Friday at 11pm and runs over the weekend. You need to configure further backups to run every three hours during the week. You need to minimize the size of the 3-hourly backups. Which of the following options would minimize the size of the 3-hourly backups? A. NO RECOVERY B. SIMPLE C. COPY_ONLY D. FULL E. TRANSACTION LOG F. DIFFERENTIAL Answer: E Explanation:

QUESTION NO: 49 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A server named ABC-SQL1 hosts a 2 Terabyte database named CorpDB.


31

Microsoft 70-462 Exam The CorpDB database has a file named CorpDB.mdf in the PRIMARY file group. The database also has secondary database files named Data1.ndf, Data2.ndf and Data3.ndf. The three secondary database files are in a filegroup named DATA. One day the database stops working. You discover that CorpDB.mdf, Data1.ndf and Data3.ndf all contain corrupted data. What is the quickest way to recover the database? A. Restore the data from a Transaction Log backup. B. Restore the files from a file backup. C. Restore the data from a differential backup. D. Restore the data from a full backup. Answer: D Explanation:

QUESTION NO: 50 Your role as a Database Administrator (DBA) at ABC.com includes the management of the Microsoft SQL Server 2012 database server infrastructure. A server named ABC-SQL1 hosts several databases. You want to optimize the performance of ABC-SQL1. You want to examine physical design structures in your databases including clustered indexes, nonclustered indexes, indexed views, and partitioning to see where performance can be improved. Which tool should you use? A. SQL Server Query Analyzer. B. Database Engine Tuning Advisor. C. Index Tuning Wizard. D. sys.dm_db_index_usage_stats Dynamic Management View. Answer: B Explanation:


32

Microsoft 70-462 Exam


33

70-462

Recommend Documents