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

A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A. 

B. Use Node A to install SQL Server on Node C. 

C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

D. Use Cluster Administrator to add a new Resource Group to Node B. 

View Answer

Q2. You administer a SQL Server 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 and the Sales role is granted the Select permission on the Customers schema. 

You need to ensure that the Sales role, including UserA, is disallowed to select from the Regions table. 

Which Transact-SQL statement should you use? 

A. REVOKE SELECT ON Schema::Customers FROM UserA 

B. REVOKE SELECT ON Object::Regions FROM UserA 

C. EXEC sp_addrolemember 'Sales', 'UserA' 

D. DENY SELECT ON Schema::Customers FROM Sales 

E. EXEC sp_droprolemember 'Sales', 'UserA' 

F. REVOKE SELECT ON Schema::Customers FROM Sales 

G. DENY SELECT ON Object::Regions FROM UserA 

H. REVOKE SELECT ON Object::Regions FROM Sales 

I. DENY SELECT ON Schema::Customers FROM UserA 

J. DENY SELECT ON Object::Regions FROM Sales 

View Answer

Q3. You administer a Microsoft SQL Server environment. You purchase a new server and plan to migrate your database from SQL Server 2008 to SQL Server 2012. 

You want to evaluate to prepare for possible conflicts and issues that may arise during or after the migration. 

Which SQL Server tool should you use? 

A. Distributed Replay 

B. Migration Assistant 

C. Data Tools 

D. Upgrade Advisor 

View Answer

Q4. 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. 

A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin; 

B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs]; 

C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin; 

D. GRANT VIEW DEFINITION TO [SpecialDBARole]; 

E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin; 

F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole]; 

View Answer

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

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? 

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

B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = READ_ONLY, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = READ_ONLY, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = YES, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

View Answer

Q6. You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. 

The application is unable to connect to the database on the named instance. 

You need to ensure that the application can connect to the named instance. 

What should you do? 

A. Use the Data Quality Client to configure the application. 

B. Start the SQL Server Browser Service. 

C. Use the Master Data Services Configuration Manager to configure the application. 

D. Start the SQL Server Integration Services Service. 

View Answer

Q7. You administer a Microsoft SQL Server 2012. 

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? 

A. EXEC sp_who 60 

B. SELECT * FROM sys.dm_exec_sessions WHERE sessionjd = 60 

C. EXEC sp_helpdb 60 

D. DBCC INPUTBUFFER (60) 

View Answer

Q8. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. 

. The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

View Answer

Q9. You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. 

You have multiple queries that read data and produce reports from the database. 

You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. 

What should you do? 

A. Set the Availability Mode property of HA/Server02 to Asynchronous commit. 

B. Set the Readable Secondary property of HA/Server02 to Read-intent only. 

C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections. 

D. Set the Availability Mode property of HA/Server01 to Asynchronous commit. 

View Answer