SQL 70-462

Question 1 of 22

1

Q10
You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability.
You also need to ensure that no data is lost.

Select one of the following:

ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)
ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)
ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

Explanation

Question 2 of 22

1

Q11-1
You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for THIS scenario.

Multiple databases on the same low-latency subnet must allow applications to write changes locally, and these changes must be replicated to all related databases.

Select one of the following:

Transaccional Replication
Peer-to-Peer Replication
Snapshot Replication
Merge Replication

Explanation

Question 3 of 22

1

Q11-2
You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for THIS scenario.

An order summary table is repopulated once a week. This table must be replicated to all databases.

Select one of the following:

Transaccional Replication
Peer-to-Peer Replication
Snapshot Replication
Merge Replication

Explanation

Question 4 of 22

1

Q11-3
You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for the scenario:

Field offices using unreliable connections keep a local copy of the product catalog and process orders locally. These orders must be periodically replicated to all other.

Select one of the following:

Transaccional Replication
Peer-to-Peer Replication
Snapshot Replication
Merge Replication

Explanation

Question 5 of 22

1

Q11-4
You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for THIS scenario.

Information in an order-tracking database must be replicated across a low-latency connection as changes occur to multiple reporting databases.

Select one of the following:

Transaccional Replication
Peer-to-Peer Replication
Snapshot Replication
Merge Replication

Explanation

Question 6 of 22

1

Q14
You create an availability group named HaContoso that has replicas named Server01/HA, Server02/HA, and Server03/HA. Currently, Server01l/HA is the primary replicA.
You need to ensure that the following requirements are met:
Backup operations occur on Server02/HA.
If Server02/HA is unavailable, backup operations occur on Server03/HA. Backup operations do not occur on Server01/HA.
How should you configure HaContoso?

Select one of the following:

Set the backup preference of HaContoso to Prefer Secondary.
Set the backup priority of Server02/HA to 20.
Set the backup priority of Server03/HA to 10.
Set the back up preference of HaContosoto Secondary only.
Set the backup priority of Server02/HA to 20.
Set the backup priority of Server03/HA to 10.
Set the backup preference of HaContoso to Secondary only.
Set the backup priority of Server02/HA to 10.
Set the backup priority of Server03/HA to 20.
Set the exclude replica of Server01/HA to true.
Set the backup priority of Server02/HA to 10.
Set the backup priority of Server03/HA to 20.

Explanation

Question 7 of 22

1

Q23
You administer a Microsoft SQL Server 2012 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? Choose all that apply.

Select one or more of the following:

The Select permission on Customers
The Execute permission on GetCustomerOrderInfo
The Take Ownership permission on Customers
The Control permission on GetCustomerOrderInfo
The T ake Ownership permission on Orders
The Select permission on Orders

Explanation

Question 8 of 22

1

Q26
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema.
You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?

Select one of the following:

Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
Deny Delete permissions on each table in the Sales schema for each user.
Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

Explanation

Question 9 of 22

1

Q27
You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 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 SQL statement or statements should you use? Choose all that apply.

Select one or more of the following:

CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
GRANT VIEW DEFINITION TO [SpecialDBARole];
CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
GRANT VIEW SERVER STATE, VIEW ANY DATA BASE TO [SpecialDBARole];

Explanation

Question 10 of 22

1

Q29
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Select one or more of the following:

Create a SQL Server login that has VIEW SERVER STATE permissions.
Create an application role and a secured password for the role.
Modify the stored procedure to include the EXECUTE AS OWNER statement.
Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
Create a SQL Server login that has VIEW SERVER STATE permissions.
Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
Grant the db_owner role on the database to User1.
Grant the sysadmin role on the database to User1.

Explanation

Question 11 of 22

1

Q33
You administer a Microsoft SQL Server 2012 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?

Select one of the following:

SERVER_STATE_CHANGE_GROUP
SERVER_PRINCIPAL_IMPERSONATION_GROUP
SUCCESSFUL_LOGIN_GROUP
SERVER_ROLE_MEMBER_CHANGE_GROUP

Explanation

Question 12 of 22

1

Q36
You administer a Microsoft SQL Server 2012 database.
Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message. What should you do?

Select one of the following:

Create an Extended Events session by using the sqlserver.error_reported event.
Create a SQL Profiler session to capture all ErrorLog and EventLog events.
Flag all stored procedures for recompilation by using sp_recompile.
Execute sp_who.

Explanation

Question 13 of 22

1

Q40
You administer a Microsoft SQL Server 2012 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.
What should you do? Choose all that apply.

Select one or more of the following:

Restart the SQL Server Agent Service.
Disable snap shot isolation.
Restart the SQL Server Service.
Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
Add the CONTOSO\SQLService account to the Server Operators fixed server role.
Enable snap shot isolation.

Explanation

Question 14 of 22

1

Q52
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 all that apply.

Select one or more of the following:

Configure the database to use the SIMPLE recovery model.
Create a DIFFERENTIAL database backup every 4 hours.
Create a LOG backup every 5 minutes.
Configure the database to use the FULL recovery model.
Create a FULL database backup every 24 hours.
Create a DIFFERENTIAL database backup every 24 hours.

Explanation

Question 15 of 22

1

Q73
You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

Select one or more of the following:

Disable FILESTREAM on the Database.
Enable FILESTREAM on the Server Instance.
Configure the Database for Partial Containment.
Create a non-empty FILESTREAM file group.
Enable Contained Databases on the Server Instance.
Set the FILESTREAM directory name on the Database.

Explanation

Question 16 of 22

1

Q86
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. What should you do? Choose all that apply.

Select one or more of the following:

Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
Execute the following Transact-SQL command: sp_configure 'remote admin connections',
Execute the Reconfigure command.
Execute the following Transact-SQL command: sp_configure 'remote access', 1
Restart the SQL Server Agent Service.
Enable in bound connections on TCP port 135 in the Windows Firewall on the server.

Explanation

Question 17 of 22

1

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

Select one or more of the following:

Perform a side-by-side installation of a 32-bit SQLServer 2012 instance.
Perform a side-by-side installation of a 64-bit SQLServer 2012 instance.
Perform an in-place upgrade to 64-bit SQL Server 2012.
Detach the database from the old instance and attach it to the new instance.
Change the compatibility level of the database.

Explanation

Question 18 of 22

1

Q109
You administer a Windows Azure SQL Database database 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?

Select one of the following:

Grant EXECUTE permission on p_AddInventory to all users. Grant VIEW DEFINITION to all users.
Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datawriter role.
Add all users to the db_owner role.
Grant EXECUTE permission on p_AddInventory to all users. Add all users to the db_datareader role.

Explanation

Question 19 of 22

1

Q111
You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server: CORP\Employees - All company employees CORP\HRAdmins - HR administrators only
HR Administrators are also company employees.
You need to grant users access according to the following requirements:
CORP\Employees should have SELECT access to the Employees table.
Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table. Logins are based only on Windows security groups.
What should you do?

Select one or more of the following:

Create a database role called Employees.
Add CORP\Employees to the db_datareader role.
Add all company employees except HR administrators to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.
Create a data base role called HRAdmins.
Add all company employees except HR administrators to the db_datareader role, Add all HR administrators to the HRAdmins role.
Grant SELECT access to the SalaryDetails table to the HRAdmins role.
Deny SELECT access to the SalaryDetails table to the db_datareader role.
Create two database roles: Employees and HRAdmins.
Add all company employees to the Employees role.
Add HR administrators to the HRAdmins role.
Grant SELECT access to all tables except SalaryDetails to the Employees role. Grant SELECT access to the SalaryDetails table to the HRAdmins role.
Deny SELECT access to the SalaryDetails table to the Employees role.
Create a database role called Employees.
Add all HR administrators to the db_datareader role.
Add all company employees to the Employees role.
Grant SELECT access to all tables except the SalaryDetails table to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.

Explanation

Question 20 of 22

1

Q119
You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)
(foto)
You need to implement an AlwaysOnAvailablity 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?

Select one of the following:

AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (
ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = NONE)
AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY, READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES, READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

Explanation

Question 21 of 22

1

Q127
You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales. You plan to migrate Sales to Windows Azure SQL Database.
To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Select one or more of the following:

Set database containment to AZURE.
Enable server property contained database authentication.
Disable server property cross db ownership chaining.
Set database containment to PARTIAL.
Disable server property contained database authentication.
Set database containment to FULL.

Explanation

Question 22 of 22

1

Q190
Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)

Select one or more of the following:

Database Engine Services
SQL Server Replication
Analysis Services
Reporting Services

	Created by Roberto López almost 8 years ago

Preguntas multi opción del examen.

Question 1 of 22

Q10 You administer a Microsoft SQL Server 2012 database. You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability. You also need to ensure that no data is lost.

Select one of the following:

Explanation

Question 2 of 22

Select one of the following:

Explanation

Question 3 of 22

Select one of the following:

Explanation

Question 4 of 22

Select one of the following:

Explanation

Question 5 of 22

Select one of the following:

Explanation

Question 6 of 22

Select one of the following:

Explanation

Question 7 of 22

Select one or more of the following:

Explanation

Question 8 of 22

Select one of the following:

Explanation

Question 9 of 22

Select one or more of the following:

Explanation

Question 10 of 22

Select one or more of the following:

Explanation

Question 11 of 22

Q33 You administer a Microsoft SQL Server 2012 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?

Select one of the following:

Explanation

Question 12 of 22

Select one of the following:

Explanation

Question 13 of 22

Select one or more of the following:

Explanation

Question 14 of 22

Select one or more of the following:

Explanation

Question 15 of 22

Q73 You administer a Microsoft SQL Server 2012 instance. You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

Select one or more of the following:

Explanation

Question 16 of 22

Select one or more of the following:

Explanation

Question 17 of 22

Select one or more of the following:

Explanation

Question 18 of 22

Q109 You administer a Windows Azure SQL Database database 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?

Select one of the following:

Explanation

Question 19 of 22

Select one or more of the following:

Explanation

Question 20 of 22

Select one of the following:

Explanation

Question 21 of 22

Select one or more of the following:

Explanation

Question 22 of 22

Q190 Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)

Select one or more of the following:

Explanation

Q10
You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability.
You also need to ensure that no data is lost.

Q33
You administer a Microsoft SQL Server 2012 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?

Q73
You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

Q109
You administer a Windows Azure SQL Database database 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?

Q190
Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)