SQL Server Finals

A package configuration file can be generated from with SQL Server Data Tools for each environment a package will run in.

True

AES is a block cipher that translates fixed-size blocks of unencrypted data into corresponding fixed-sized blocks of encrypted data.

True

Configuring a linked server which enables a data source to be queried directly from within SQL Server works well for static data sources.

True

Encryption does not prevent data loss, but it can lessen the impact of data loss.

True

Indexes prevent the database engine from having to scan every record in the database.

True

SQL Server 2012 can back up database files to physical disk media or to tape.

True

Security principals can be either users or groups

True

The BULK INSERT statement inserts records from an external data file source into a SQL Server table.

True

The bcp utility can be executed to insert records from an external data file source into a SQL Server table.

True

Transaction logs are required in the event that a database needs to be restored back to a specific point in time.

True

Which environments must use SQL Server authentication because Windows authentication cannot be used?

Web applications

Which of the following is NOT a problem that may result from allowing uncontrolled access to the database?

concurrency control

Which of the following is a database object that physically stores all columns that are needed to query a single table?

covering index

Which of the following is NOT a method to leverage the bulk copy interface?

create a SQL Server Agent job

Which of the following would you use if you wanted to collect performance data on several resources over a period of time for analysis later?

data collector set

What kind of task should be added to an SSIS package if you want to download a data file from an FTP site?

data preperation

Which of the following is true about the factors that should be considered when evaluating database business requirements and the associated risk assessments?

databases that have a high change rate require more frequent backups

Which encryption algorithm is a block cipher that uses a 128-bit block cipher with varying key sizes?

AES

Which command takes corrective action on a fragmented index?

ALTER INDEX REORGANIZE

Which of the following is true about packages created with SQL Server Data Tools?

projects can contain multiple Integration Services packages

Which component of the SQL Server database engine builds a physical execution plan from a SQL query?

query optimizer

Which of the following is true about indexes and index fragmentation?

records should be stored on the disk contiguously

Which of the following is a suggested threshold for performing index fragmentation maintenance?

reorganize if the logical fragmentation is between 5 and 30 percent

Which term is best described as a way to group together users for the purposes of managing database permissions?

role

Which default account is enabled when SQL Server authentication is enabled using mixed mode?

sa

Which of the following is true about query design?

subqueries can degrade performance

What should you reduce in order to limit the number of security vulnerabilities in a system?

surface area

Which action does a user perform that requires the user to provide credentials?

system logon

In which risk category would an incompatibility between SQL Server and Windows be considered?

system software failure

Which of the following is true about asymmetric encryption algorithms?

the performance is slower than with symmetric algorithms

Which of the following is true about recovery objectives?

the recovery point objective is expressed as a time frame

Which of the following best defines a trace?

the result set of database events captured by SQL Server Profiler

Which of the following is NOT true about roles?

the scope of a role is confined to each table in a database

Which of the following is true about database access control?

to access a database the SQL Server login must be mapped to a database user

What type of component should you use if you need to convert numeric data to string data as the data flows between the source and destination?

transformation

Aside from extracting and loading data, the process of data integration involves which of the following?

transforming data

Which of the following may be found in a database table that performs a secondary set of operations when data is inserted, deleted, or updated?

trigger

Which of the following is a guideline to follow that can help you avoid blocking locks and deadlocks?

use clustered indexes for frequently accessed tables

Which category of risk has the highest probability of occurrence?

user error

Which of the following is NOT an action against a database object that requires a permission?

Logging in to SQL Server

Which counter indicates the number of requests that are waiting to perform a disk read or write operation?

Logical Disk: Avg. Disk Queue Length

Which of the following is a method to secure client/server connections that uses certificates for identification?

SSL

What SQL command do you use when restoring transaction logs if you want to do a point-in-time restore?

STOPAT

Which of the following is the correct syntax for using the BULK INSERT statement?

BULK INSERT <Database>.<Schema>.<Table> FROM '<DataFilePath>' WITH ( <Options> )

Which of the following is a legacy data integration tool with a graphical designer that had a limited range of functionality?

DTS

What SQL function should you use to encrypt an individual data item in a column that lets you choose the encryption algorithm?

EncryptByKey

After you devise a backup plan, you should do a risk assessment.

False

All server-level principals have access to the SQL database.

False

Data loss caused by environmental events has the greatest possibility of occurrence.

False

IPSec uses certificates for identity verification.

False

If a request is made to use a resource that is busy, the request is dropped and must be re-issued.

False

ODBC is an API that is designed to facilitate the interactions between a client application and a database management system, but it is platform-specific.

False

Only one user or client application at a time may attempt to query a database.

False

The % Processor Time performance counter displays a count of the number of processes that are waiting for the processor.

False

The AwlaysOn capabilities of SQL Server 2012 effectively replace the need to perform traditional backups.

False

The logical model is subjected to normalization, which can eliminate data redundancy.

False

Which backup scheme reduces the amount of downtime to perform a restore but creates additional IT management and physical resource overhead?

Full database backup plus differential database backup plus multiple transaction log backups

Which method of securing client/server connections used by SQL Server 2012 requires implementing a policy on the server and client computers?

IPSec

Which counter measures the data being transferred between the physical disk and memory?

Memory: Pages/sec

Which of the following is an authentication mode supported by SQL Server 2012?

Mixed authentication mode

Which Windows tool can provide the most detailed analysis of physical resource performance?

Performance Monitor

Which of the following SQL commands should you perform in a database recovery operation right before you check the logical and physical integrity of the database?

RESTORE DATABASE <DatabaseName> WITH RECOVERY;

Which type of task should you add to an SSIS package if you need to perform operations on schema objects?

SQL Server

Which basic data integration tool provides step-by-step instructions but has a limited range of data transformation capabilities?

SQL Server Import and Export Wizard

Which of the following is NOT a commonly used type of backup plan?

Single transaction log backup plus simple recovery model

Which of the following are the basic building blocks of an SSIS package?

Task

Which of the following is NOT a method that can be used to execute an Integration Services package?

Windows Task Scheduler

Which of the following is NOT a type of security principal that may request access to a SQL server instance?

Windows contact

Which of the following is true about backup sets?

a backup device references a physical location where backup sets are stored

Which of the following is a valid consideration when implementing physical database design?

all relationships should be explicitly defined in the database

Which type of encryption uses public and private keys?

asymmetric

If you experience database corruption, what should you do before performing a restore operation?

backup the transaction log tail

Which of the following is true about database backup types?

backups can be performed when a database is offline

Which of the following can be used for bidirectional data transfers and is a stand-alone application that is run from a command prompt?

bcp utility

Which predefined server-level role allows a user to create or alter any database?

dbcreator

Which situation occurs when two transactions each have a lock on a resource that the other transaction needs in order to complete?

deadlock

Which of the following is another term for the layered security model?

defense-in-depth

How are full and differential database backups used?

differential backups are usually performed more often than full backups

Which problem occurs when a concurrent process reads data that was not committed to the database?

dirty read

What should be used if you want an email alert to be sent to you if an error occurs in the SSIS package?

event handler

What should you use when running the bcp utility if you want to import only certain columns and run the command interactively?

format file

Which recovery model causes all changes to the database to be written to the transaction log?

full recovery model

Which of the following is true about indexes and index design?

indexes should be created on columns used by WHERE and BY clauses

What is the drawback to using a linked server for integrating an external data source?

it doesn't work well for dynamic data sources

What happens to a request for a resource if the resource is busy?

it is put in a queue

Which of the following is true about the Database Engine Tuning Advisor?

it quantifies the expected performance gains for each recommendation

Which of the following is a reason that Windows authentication is considered more secure than combining Windows and SQL Server authentication.

multiple sets of credentials are not required

Which of the following is NOT a factor that influences the speed and efficiency with which information is retrieved or modified in a database management system?

permissions

Which of the following is true about database permissions?

permissions can be granted on a schema

Which of the following is NOT among the three primary factors that should be considered for each risk scenario identified?

personnel involved in the scenario

Which of the following is a reason that transaction logs must be backed up frequently?

when the log is backed up, it is truncated

Which type of SSIS package task permits the package to interact with other applications?

workflow