SQL Server Final

It's best to manage SQL Server from a workstation running SSMS

True

The full recovery model provides complete transaction logging

True

You can install SQL Server on Linux

True

You can use AlwaysOn Availability Groups without failover cluster instances.

True

What are the three types of pages within an index?

root node leaf intermediate

When it comes to maintaining database file sizes, the default autogrowth size is:

1MB

How many bytes does the int data type take up?

4 bytes

What kind of data structure improves the speed of data retrieval?

index

What are the core components of replication?

One or more articles combined into a publication

The SQL Server machine learning features allow for______________________and_______________integration.

R, Python

When log shipping, you can use the restore feature with:

Restore NoRecovery Standby

When clustering it's best:

To have an odd set of nodes

What SQL dialect does Microsoft SQL Server use?

Transact-SQL

Every SQL Server database has at least 2 types of files:

Transaction Log Data

The uniqueidentifier datatype is often used as a Primary Key

True

Database access is made via principals named:

Users

The following are valid types of login authentication options:

a login that is mapped directly to a certificate or to an asymmetric key Azure AD accounts SQL Server authentication Windows

If you define a column as an identity column,

a number is generated for that column whenever a row is added to the table

To retrieve or update the data in a database, the client sends a ________________ to the database.

query

Which type of database has multiple tables with parent/child relationships and a child that can have more than one parent?

relational database

The DMV that shows the number of times each index within the database has been accessed as well as when the index was last accessed is:

sys.dm_db_index_usage_stats

A relational database consists of one or more what?

tables

RTO stands for:

Recovery Time Objective

What is the difference between a clustered and nonclustered index? Select 2

In a non-clustered index, the leaf level pages contain row locators (pointers) to the actual data, unlike a clustered index where a leaf level page contains the actual data Clustered as physically ordered, non-clustered created to make queries more efficient

Routine maintenance tasks include:

Integrity checks Transaction Log backups Backing up system and user databases Updating statistics Reorganize Indexes

What is a virtual table consisting of different columns from one or more tables?

view

What type(s) of data type is spatial for?

Geographic Geometric

When creating a database from scratch, the default data filegroup is:

MDF

Log truncation

Marks the VLF as inactive Makes the transaction log file smaller Allows the the VLF to be reused

For authentication SQL Server supports two modes:_______________________&___________________________

Mixed mode & Windows mode

RPO stands for

Recovery Point Objective

The History cleanup task in the maintenance plan wizard:

Removes older maintenance plan records Deletes older rows in MSDB tables that contain backup and restore history Prunes the SQL Server Agent logs file

Which tool can you use to enable AlwaysOn Availability Groups on a SQL Server 2012 instance?

SQL Server Configuration Manager

The following are part of SQL Server Data Tools (Select all that apply)

SQL Server Integration Services SQL Server Upgrade Wizard

Which of the following would be considered Infrastructure as a service?

SQL Server running on an Azure VM

Fischer enterprises needs to implement a replication architecture that maintains a reporting server while also ensuring that any changes to the report server do not get sent back to the publisher. Which replication option would be the best solution?

Transactional replication

Compression can be by row and by page

True

Data collector sets can be configured to capture performance data over time

True

The difference between Standard storage and Premium storage is the type of drives used

True

The maintenance plan designer allows you to build tasks step by step that include subplans as additional tasks.

True

The simple recovery model will not allow transaction log backups

True

VHSs that are managed by you are considered unmanaged disks

True

When installing and configuring features, you can install additional features using the SQL Server setup menu

True

You work as a database administrator for FischerCo Importers, which has an online transactional database used for human resources, sales, and customer relationship management. Management wants you to encrypt the [CreditCard], [PassportNumber] and [SocialSecurityNumber] fields in the customer table. They are concerned that contractor database administrators will be able to access the sensitive information. Unauthorized users should not be able to guess information about encrypted values by examining patterns in the encrypted column. The encrypted fields should be able to be used ins search arguments. What encryption strategy should you use?

Use Always Encrypted with deterministic encryption

You work as a database administrator for FischerCo Importers, which has an online transactionaldatabase used for human resources, sales, and customer relationship management. Management wants you to configure an auditing solution that will track data changesmade to a number of tables within the database. The solution should be able to showthe data before and after any modification. What technique should you use?

Use DML triggers.

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.

dialect/variant

What common data type is used to store decimal numbers such as 3.14 and 7.07?

float

To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.

foreign key

A_______________________backup is required before you can create a differential or transaction log backup.

full

The four types of backups are:

full filegroup transaction log differential

The SA account:

is a common vector for brute-force attacks to compromise a SQL Server can be used for all administrative access.

What uniquely identifies each row in a table?

primary key

When connecting to a named SQL server instance the format of:

servername\instancename

Use_________________ for a more flexible, scalable way to manage local storage.

storage spaces

This is typically modeled after a real-world entity, such as an invoice or a vendor.

table

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

What allows a SQL database to be accessed by multiple users at the same time?

database server

Which of the following types of statements isn't an action query?

Select

The three methods of replication are:

Snapshot Transactional Merge

The two roles that a database can have within a replication architecture are:

Subscriber Publisher

How many clustered indexes can you have for a table?

1

The designated port for the default instance is:

1433

LSN stands for:

Log Sequence Number

A RAID 1 is a:

mirrored array

SQL Server deals with _____________extents

64KB

Regarding on-prem provisioning, the SQL server installation should be put on_________________ to minimize SQL Server's footprint.

A different volume as the OS

You are a DBA of Amazon Whoot! The orders database is crucial to company operations and is set to the Full recovery model. You are running fullbackups daily at 1 A.M., differential backups every four hours beginning at 5 A.M., and transaction log backups every five minutes. If the Orders database were to becomedamaged and go off-line, what is the first step in the restore process?

Back up the transaction log with the NO_TRUNCATE option

The following statement : BACKUP DATATBASE [dbName] TO [backup device] WITH INIT;

Backs up a database and overwrites an existing backup device

In the____________recovery model, the transaction log is still used; however, for certain types of actions; minimal logging is performed.

Bulk-logged

You work as a database administrator for FischerCo Importers, which has an online transactional database used for human resources, sales, and customer relationship management. Management does not want staff to see the sales targets and salaries of other staff in the company. This sensitive information is kept in a number of columns in different tables. What encryption strategy should you use?

Configure row-level security

You work as a database administrator for FischerCo Importers, which has an online transactional database that used for human resources, sales and customer relationship management. Research Query Execution Plans to find a solution to the following Scenario. Management wants you to configure a monitoring solution that will track query execution plans and server-level wait stats so that your junior database administrators can troubleshoot performance and identify query execution times historically for a year. What technology should you use?

Data Collector and Management Data Warehouse

The following is true regarding MAXDOP

Default setting is 0 It's value is used to select the maximum number of logical processors to run a parallel query

Which SQL Server edition would be best for a testing environment?

Developer

SQL Server authentication is made via principals named:

Logins

You work as a database administrator for FischerCo Importers, which has an online transactional database that used for human resources, sales and customer relationship management. Research Query Statistic Updates to find the answer to the following scenario. Users are complaining about poor query performance for queries that query the [Sales_ History] table. The table is 400GB in size. It is not partitioned. After performing a root cuase analysis you have determined that the poor query performance is due to outdated statistics. You need to update the statistics in the least possible time for the table. What statistic update option should you use?

SAMPLE

The best storage solution to allow easier management across multiple networked systems would be:

SAN

The standard interface for server drives are:

SAS SATA

Which of the following is true regarding Table Partitioning?

You can split a table into groups of rows based on a column to improve R/W performance By partition switching you can instantly switch data into and out of a table.

You work as a database administrator for FischerCo Importers, which has an online transactional database that used for human resources, sales and customer relationship management. Research DMVs to find the proper DMV for this scenario. You need to analyze which queries are consuming the most memory by their query plans. What DMV should you query?

[sys].[dm_exec_cached_plans]

When a column in a table is defined, what determines the kind of data it can store?

a data type

Insert, Update, and Delete statements can be referred to as ________________ queries.

action

The processing that's done by the DBMS is typically referred to as

back-end processing

The working set includes ( Select all that apply)

buffer pool procedure cache

What is the set of rules that determine how data is sorted and compared?

collation

The interface between an application program and the DBMS is usually provided by the

data access API

A view is a SELECT statement that is stored with the ________________.

database

What can you use to combine data from two or more tables into a single result set?

join

When you back up the transaction log, the log is______________by default

truncated

When installing SQL Server the management tools are automatically installed

False

What type of structure does SQL Server use to construct an index?

B Tree

A table can have multiple primary keys.

False

The following is true regarding transaction logs:

Can allow you to recover every transaction in the database provided the LSN chain has not been broken should be backed up immediately following a database file storage failure must be applied in sequence during a restoration

Resource governor uses_____________________________to limit the CPU and memory allocated to or used by a connection or group of users. Select all that apply.

Classification functions Resource pools Workload groups

You will configure an AlwaysOn Availability Group to support automatic failover from the primary replica to any available secondary replica. Which of the following availability modes should you configure for the replicas in this availability group? (Each correct answer forms part of a complete solution. Choose all that apply.)

Configure the secondary replica to use the synchronous-commit availability mode. Configure the primary replica to use the synchronous-commit availability mode.

Which of the following is not a SQL DML statement?

CreateTable

Which of the following are included when you run DBCC CHECK DB?

DBCC CHECKCATALOG DBCC CHECKALLOC DBCC CHECKTABLE

What is a collection of programs that enables you to enter, organize, and select data from a database?

DBMS

What kind of statements are USE, CREATE, ALTER, and DROP?

DDL

How can you detect and log corrupt pages?

Execute ALTER DATABASE <database name> SET PAGE_VERIFY CHECKSUM

"Smart setup" automatically creates a user account for your instance

False

A built-in way in SQL Server Management Studio to write extendedevents session data directly to a SQL Server table, is to use the Watch Live Data interface.

False

Database Mail is an executable that uses SNMP & Service Broker for event notification

False

Disaster Recovery (DR) is the same as High Availability (HA)

False

In-place upgrades to SQL Server 2017 are recommended for SQL server 2005

False

SQL Server Management Studio can be installed on MacOS and Linux operating system environments.

False

SQL Server Management Studio is installed by default when you install SQL Server 2017.

False

SQL Server Reporting Services is installed automatically when you install SQL Server 2017.

False

SQL Server is lightweight it needs very little memory, and works well with operating system power saving features.

False

TempBD should have multiple transaction log files.

False

The email settings in Reporting Services is for administrators to be notified when backups complete

False

Turning on LPIM allows sharing of Memory with the windows operating system

False

When installing a SQL server VM in Azure you should set the installation volume to D:/

False

When setting up email notifications for SQL Server Agent Jobs, you should always include all severity levels

False

You should always have antivirus scanning your volumes for viruses

False

Which volume partitioning type should be used for very large Databases?

GPT

The default mode for SQL Server 2017 analysis services is:

Tabular