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