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?

Reveal Solution Hide Solution

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?

Reveal Solution Hide Solution

You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.

An additional node named Node C has been added to the existing cluster.

You need to ensure that the SQL Server instance can use all nodes of the cluster.

What should you do?

Reveal Solution Hide Solution

You administer a Microsoft SQL Server 2012 database.

The database contains a customer table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the customer table.

What should you do?

Reveal Solution Hide Solution

You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.

You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.

You need to ensure that AgentPortalUser can send email messages.

What should you do?

Reveal Solution Hide Solution

DRAG DROP

You administer a Microsoft SQL Server database.

You need to convert the database to a contained database. You also need to ensure that all users are converted to contained users.

Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Reveal Solution Hide Solution

Correct Answer:

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.

Reveal Solution Hide Solution

DRAG DROP

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

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

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

Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Reveal Solution Hide Solution

Correct Answer:

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.

You also need to prevent reading queries from blocking writing queries.

Which isolation level should you use?

Reveal Solution Hide Solution

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

– A data file of 2 terabytes is located on a dedicated LUN (drive D).

– A transaction log of 10 GB is located on a dedicated LUN (drive E).

– Drive D has 1 terabyte of free disk space.

– Drive E has 5 GB of free disk space.

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours.

Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands.

Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours.

Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You implement log shipping of the financial database to another SQL Server 2012 instance. You decide to failover to this secondary database.

You need to ensure that all transactions will be replicated to the secondary database.

Which backup option should you use?

Reveal Solution Hide Solution