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

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

Q3. 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. Which Transact-SQL statement should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

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

Q5. You administer a SQL Server 2012 database instance. 

You need to configure the SQL Server Database Engine service on a failover cluster. 

Which user account should you use? 

A. A domain user 

B. The BUILTIN\SYSTEM account 

C. A local user with Run as Service permissions 

D. TheSQLBrowser account 

View Answer

Q6. You administer a Microsoft SQL Server 2012 database named Orders. Orders contains a table named OrderShip that is defined as follows: 

Performance on this procedure is slow. 

You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources. 

Which Transact-SQL statement should you use? 

A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL 

B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL 

C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode) 

D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL 

View Answer

Q7. 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? 

A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE 

B. ALTER INDEX ALL ON AccountTransaction REBUILD 

C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD 

D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING 

View Answer

Q8. You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

View Answer

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

View Answer

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

At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database. 

You also need to ensure that data loss is minimal. 

What should you do? 

A. Perform a partial restore. 

B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. 

C. Restore the latest full backup. 

D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. 

E. Perform a page restore. 

F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. 

G. Perform a point-in-time restore. 

H. Restore the latest full backup. Then, restore the latest differential backup. 

View Answer