SQL Server database administration.

You are the lead database administrator (DBA) of a Microsoft SQL Server environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server. You need to create a server role named SpecialDBARole that can perform the following functions: ✑ View all databases. ✑ View the server state. ✑ Assign GRANT, DENY, and REVOKE permissions on logins. You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary. Which three SQL statements should you use? Each correct answer presents part of the solution.

Correct Answer: CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;

You administer a Microsoft SQL Server 2012 instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met: ✑ No more than 5 minutes worth of transactions are lost. ✑ Data can be recovered by using the minimum amount of administrative effort. What should you do? Choose three.

Correct Answer: Configure the database to use the FULL recovery model.

You administer a Microsoft SQL Server database. You provide temporary securityadmin access to User1 to the database server. You need to know if User1 adds logins to securityadmin. Which server-level audit action group should you use?

Correct Answer: SERVER_PRINCIPAL_IMPERSONATION_GROUP

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?

Correct Answer: 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

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 a Microsoft SQL Server database named Contoso on a server named Server01. You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01. What should you create?

Correct Answer: A Database Audit Specification

Note: This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series. You administer a Microsoft SQL Server server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and backup schedule are configured as shown in the following table: The differential backup of the reporting database fails. Then, the reporting database fails at 14:00 hours. You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal. What should you do?

Correct Answer: Restore the latest full backup.

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 a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to prevent users from disabling server audits in Server01. What should you create?

Correct Answer: An Extended Event session

You administer a Microsoft SQL Server. A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

Correct Answer: SELECT * FROM sys.dm_exec_sessions WHERE session_id = 60

You administer a Microsoft SQL Server database. You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically. Which Transact-SQL statement should you use?

Correct Answer: ALTER AVAILABILITY GROUP haContosoDbs MODITY REPLICA ON Server01\Contoso01 WITH (AVAILABILITY _MODE=ASYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC)

You administer a Microsoft SQL Server instance. The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. The database is configured to use the FULL recovery model. You plan to define a backup strategy for the database. You need to ensure that the following requirements are met: ✑ No more than 5 minutes worth of transactions are lost. ✑ Data can be recovered by using the minimum amount of administrative effort. What should you do? Each correct answer presents part of the solution.

Correct Answer: Create a DIFFERENTIAL database backup every 4 hours.

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured. When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. The following error message is an example of the truncated error message: "Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution f

Correct Answer: Configure output files.

You manage a server that has SQL Server installed and hosts databases for five applications. Each application has a different login to the SQL Server instance. You need to create a TCP endpoint to be used only by a login named Application3. The solution must ensure that Application3 only uses the new endpoint. Which three statements should you execute? Each correct answer presents part of the solution.

Correct Answer: GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO Application3

You administer a Microsoft SQL Server database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables. You create a new user named UserC. You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC. Which permission or permissions should you assign to UserC? Each correct answer presents part of the solution.

Correct Answer: The Take Ownership permission on Customers

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?

Correct Answer: Client Tools Connectivity

You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance. You need to ensure that users can create subscriptions. What should you configure?

Correct Answer: SSRS Execution Account

You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance. You need to ensure that users can create subscriptions. What should you configure?

Correct Answer: processing options for the reports A

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 a SQL 2012 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. You need to remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. Which Transact-SQL statement should you use?

Correct Answer: REVOKE SELECT ON Object::Regions FROM UserA

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?

Correct Answer: Install a new default SQL Server instance on the server.

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You attempt to add the [Corpnet\User1] login to the database. However, you receive the following error message: "User already exists in current database." You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

Correct Answer: ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];

You administer a Microsoft SQL Server 2012 default instance. The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server. You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. Which three actions should you perform? Each correct answer presents part of the solution.

Correct Answer: Execute the Reconfigure command.

You maintain several databases on a 32-bit Microsoft SQL Server 2005 instance on a Windows Server 2008 R2 64-bit server. You need to migrate the databases to a 64-bit SQL Server 2012 instance on the same server. You also need to ensure that the new Transact-SQL functionality in SQL Server 2012 can be used in the database after the migration. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Correct Answer: Perform a side-by-side installation of a 64-bit SQL Server 2012 instance.

You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction. You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. Which Transact-SQL batch should you use?

Correct Answer: ALTER INDEX ALL ON AccountTransaction REBUILD

You administer a Microsoft Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting. Which Transact-SQL command should you use?

Correct Answer: BACKUP DATABASE Orders TO DISK = 'D:\Orders.bak' RESTORE DATABASE Orders_Reporting FROM DISK = 'D:\Orders.bak

SQL Server database administration.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

MCSA Administering Microsoft SQL Server 20122014 Databases

Administer SQL Server databases for reliable operations.

4.8★★★★★(362 ratings)

16,604 studentsIntermediate

Develop the skills to administer Microsoft SQL Server databases in production environments. The exam covers installation, maintenance, security, backup, restore, and performance management. It is a practical certification for professionals who keep databases secure and available. Strong database administration skills are essential for business continuity, application stability, and long-term platform health.

What you'll learn

Manage SQL instances- Configure security- Perform backups and restores- Monitor performance- Maintain database availability

70-462

Lectures1

Quizzes0

Duration30 week

Students16,604

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!