Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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:
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?
A. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
B. Perform a point-in-time restore. C Restore the latest full backup.
C. 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.
D. Restore the latest full backup. Then, restore the latest differential backup.
E. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
F. Perform a page restore.
G. Perform a partial restore.
Answer: C
Q2. 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?
A. . 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.
B. . Set the backup preference of HaContoso to Secondary only.
. Set the backup priority of Server02/HA to 20.
. Set the backup priority of Server03/HA to 10.
C. . 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.
D. . 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.
Answer: B
Q3. 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
Answer: B
Q4. You administer a Microsoft SQL Server 2012 database instance.
You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database.
You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database.
What should you do?
A. Use the Copy Database wizard.
B. Back up the database from the local server and restore it to Windows Azure SQL Database.
C. Use the Database Transfer wizard.
D. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database.
Answer: D
Q5. You plan to install a Microsoft SQL Server 2012 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met.
Which feature should you use?
A. Excel Services
B. FILESTREAM
C. SQL Server Integration Services (SSIS)
D. OpenXML
Answer: B
Q6. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server. You also need to be able to add indexes to the secondary database.
Which configuration should you use?
A. . Two servers configured in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
B. . 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
C. . Two servers configured in the same data center
. A primary server configured to perform log-shipping every 10 minutes
. A backup server configured as a warm standby
D. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F. . SQL Server that includes an application database configured to perform transactional replication
G. . SQL Server that includes an application database configured to perform snapshot replication
H. . Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
Answer: F
Q7. You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. Execute sp_configure 'max log size', 2G.
B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
Answer: D
Q8. 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. A. Configure the database to use the SIMPLE recovery model.
B. Create a DIFFERENTIAL database backup every 4 hours.
C. Create a LOG backup every 5 minutes.
D. Configure the database to use the FULL recovery model.
E. Create a FULL database backup every 24 hours.
F. Create a DIFFERENTIAL database backup every 24 hours.
Answer: BCDE
Q9. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.
You need to configure the SQL Server environment to support these packages.
What should you do?
A. Create SSIS configuration files for the packages.
B. Create an Integration Services catalog.
C. Install Data Quality Services.
D. Install Master Data services.
Answer: B
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.
Answer: H
Q11. 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. The BUILTIN\LocalService account
B. A domain user
C. A local administrative user
D. The BUILTIN\NetworkService account
Answer: B
Q12. 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.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
Answer: BDF
Q13. 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));
Answer: D
Q14. You plan to install Microsoft SQL Server 2012 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?
A. Express Edition with Tools
B. Standard Edition
C. Web Edition
D. Express Edition with Advanced Services
Answer: B
Q15. 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?
A. Differential
B. Transaction Log
C. FULL
D. SIMPLE
E. SKIP
F. RESTART
G. STANDBY
H. CHECKSUM
I. DBO_ONLY
J. COPY_ONLY
K. NORECOVERY
L. NO_CHECKSUM
M. CONTINUE_AFTER_ERROR
N. BULK_LOGGED
Answer: K