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
