Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

CNTS
Database Administration
Chapter 1
Planning and Installing SQL Server 2012

Lesson 1 Planning Your Installation

1
...
512 MB
Correct AnswerB
...
2 GB
D
...
8 GB

2
...
)
Correct AnswerA
...
SQL Server 2012 (x64) Web edition
C
...
SQL Server 2012 (x64) Standard edition

3
...
)
Correct AnswerA
...
SQL Server Replication
Correct AnswerC
...
Reporting Services

4
...
)
A
...
Windows Server 2008 R2 SP1 (x64) Standard edition
Correct AnswerC
...
Windows Server 2003 R2 (x64) Enterprise edition

5
...
Which of the following tools
would you use to accomplish this goal?
A
...
SQLIOSim
C
...
chkdsk
...
You are planning on deploying a server that will be dedicated for ETL (Extraction,
Transformation, and Loading) processes
...
Which of the following features must you
install on the ETL server in addition to SSIS to accomplish this goal?
Correct AnswerA
...
SQL Server Reporting Services

1

CNTS
C
...
Client Tools SDK

2
...
Which of the following features must you install if
you want to ensure that you can run packages in 32-bit mode?
A
...
Data Quality Client
Correct AnswerC
...
Client Tools Backwards Compatibility

3
...
You
have installed SQL Server Analysis Services in the default location
...
C:\Program Files\Microsoft SQL Server\MSSQL11
...
exe
Correct AnswerB
...
MSSQLSERVER\OLAP\bin
\msmdsrv
...
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr
...
C:\Program Files\Microsoft SQL Server\MSRS11
...
exe

4
...
Which of the following tools can you use to accomplish
this goal?
A
...
SQL Server Configuration Manager
Correct AnswerC
...
SQL Server Installation Center

5
...
Which of the following files will you generate by using SQL Server Setup to
accomplish this goal?
A
...
xml
B
...
ini
C
...
xml
Correct AnswerD
...
ini
...
You have recently removed a SQL 2012 Database Engine instance from a computer
running the Windows Server 2008 R2 operating system
...
You want to ensure that the default instance
can use all processors available to the host
...
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
B
...
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
D
...
You have run the following command on a SQL Server 2012 default instance:
EXEC sys
...
Choose two
...
RECONFIGURE;
GO
B
...
sp_configure 'min server memory', 1024;
GO
C
...
sp_configure 'min server memory', 4096;
GO
Correct AnswerD
...
sp_configure 'max server memory', 4096;
GO

3
...
Which of the following would you configure to accomplish this goal?
A
...
Maximum server memory
C
...
I/O affinity

4
...
Which of the following system databases do you modify to
accomplish this goal?
A
...
model
C
...
tempdb

5
...
sp_monitor
B
...
sp_rename
D
...


Lesson 2Managing SQL Server Instances

1
...
From which of the following system
databases must you run the sp_cycle_agent_errorlog stored procedure to accomplish
this goal?
A
...
msdb
C
...
tempdb

2
...
Resource Governor
B
...
Processor affinity
D
...
What is the maximum number of SQL Server 2012 Enterprise edition Database Engine
instances that you can deploy on a non-clustered server running Windows Server 2008
R2 Enterprise edition?
A
...
25
Correct AnswerC
...
100

4
...
You have assigned
the first resource pool a minimum CPU and memory value of 20%
...
You want to
assign maximum CPU and memory values to the third resource pool
...
30%
Correct AnswerB
...
70%
D
...
A server that has four processors has three SQL Server 2012 Database Engine instances
installed
...
Resource Governor
Correct AnswerB
...
Processor affinity
D
...


Chapter 3
Configuring SQL Server 2012 Components

Lesson 1 Configuring Additional SQL Server Components

1
...
SQL Server Management Studio
B
...
SQL Server Data Tools
Correct AnsweD
...
exe)

2
...
SQL Server Management Studio
Correct AnsweB
...
SQL Server Configuration Manager
D
...
What is the maximum number of full-text indexes that you can configure for a partitioned
table?
Correct AnsweA
...
32
C
...
2,048

4
...
Choose two
...
Edit the properties of the SQL Server service in SQL Server Configuration Manager
...
Edit the properties of the Analysis Services service in SQL Server Configuration
Manager
...
Run sp_configure filestream_access_level, 2
...
Run sp_configure filestream_access_level, 0
...
Which statement would you use to add a filegroup to an existing database?
Correct AnsweA
...
CREATE DATABASE
C
...
CREATE TABLE

2
...
Columns include Student_Name, DOB, Telephone, Email, Street_Address,
Town, State, and Zip Code
...
Given this
information, which of the following statements will provide the greatest reduction in
the amount of space required to store data for the STUDENTS table?
A
...
ALTER TABLE STUDENTS REBUILD WITH (DATA_COMPRESSION=PAGE)
C
...
ALTER INDEX IDX1 ON STUDENTS REBUILD PARTITION ALL WITH
(DATA_COMPRESSION=PAGE)

3
...
Choose three
...
Create a master encryption key
...
Create a certificate
...
Create a database encryption key
...
Enable page-level compression
...
Which command would you run if you wanted to check the physical and logical integrity
of all objects within a specific database?
A
...
DBCC CHECKDB
C
...
DBCC SHRINKDATABASE
...
Which of the following operating systems can you use as the host operating system for
a SQL Server 2012 multi-subnet failover cluster instance?
A
...
Windows Server 2008 Enterprise edition
Correct AnswerC
...
Windows Vista Ultimate edition

2
...
Move-ClusterGroup
B
...
Move-ClusteredSharedVolume
D
...
The primary node of a four-node SQL Server failover cluster instance fails due to a
hardware failure
...
Which of the following
steps should you take first to remedy this situation?
Correct AnswerA
...

B
...

C
...

D
...


4
...
Server SYD-B has control of the shared disk resources
...
Which of the following
steps must you take to accomplish this goal? (Each correct answer forms part of a
complete solution
...
)
Correct AnswerA
...

Correct AnswerB
...

C
...

Correct AnswerD
...


Lesson 2 Always On Availability Groups

1
...
SQL Server Management Studio
B
...
SQL Server Configuration Manager
D
...
Which Windows PowerShell cmdlet can you use to perform manual availability
group failover?
Correct AnswerA
...
New-SqlHadrEndpoint
C
...
Enable-SqlAlwaysOn

3
...
New-SqlAvailabilityGroupListener
B
...
Enable-SqlAlwaysOn
Correct AnswerD
...
You will configure an AlwaysOn Availability Group to support automatic failover from
the primary replica to any available secondary replica
...
Choose all that apply
...
Configure the primary replica to use the asynchronous-commit availability mode
...
Configure the primary replica to use the synchronous-commit availability mode
...
Configure the secondary replica to use the asynchronous-commit availability
mode
...
Configure the secondary replica to use the synchronous-commit availability mode
...
What does the System Processor Queue Length counter measure?
Correct AnswerA
...
The number of SQL Server requests waiting for a processor
C
...
The amount of time that a processor is in use

2
...
The total number of pages on all free lists
B
...
The number of physical database page reads that are issued per second
Correct AnswerD
...
What does the Physical Disk:Avg
...
The number of system requests, on average, that are waiting for disk access
B
...
The number of physical database page reads that are issued per second
D
...


Lesson 2Working with SQL Server Profiler

1
...
Query performance
declines every Monday morning from 9 A
...
to 10 A
...
Which tools can you
use to diagnose the cause of the performance problems? (Choose all that apply
...
Extended Events Profiler
B
...
Resource Governor
Correct AnswerD
...
Which trace event would you use to determine when a T-SQL statement has
completed?
A
...
SQL:StmtStarting

CNTS
Correct AnswerC
...
RPC:Completed

3
...
Choose two
...
Lock:DeadLock
Correct AnswerB
...
Process:DeadLock
D
...


Lesson 3Monitoring SQL Server

1
...
sys
...
sys
...
sys
...
sys
...
Which dynamic management object can be used to identify the names of any tables
where the index is missing?
A
...
dm_db_missing_index_columns
B
...
dm_db_missing_index_groups
C
...
dm_db_missing_index_group_stats
Correct AnswerD
...
dm_db_missing_index_details
...
You must centralize your SQL Server capacity management tasks and evaluate the
performance against a known baseline by implementing a solution that incurs minimal
cost and requires the least effort to configure and maintain
...
Install a SQL Server 2012 instance and collect data from the dynamic management
objects
...
Install a SQL Server 2012 instance and implement a management data warehouse
...
Configure Windows Performance Monitor
...
Implement Microsoft System Center Operations Manager
...
You need to grant a user access to the management data warehouse reports
...
mdw_admin
Correct AnswerB
...
dc_operator
D
...
What does the Server Activity report include? (Each answer presents a complete solution
...
)
Correct AnswerA
...
Trends and details of disk and file usagedc_proxy
C
...
SQL Server waits and SQL Server activity
...
Which counters would you use to determine whether CPU usage rates for SQL Server

CNTS
are within normal ranges? (Each correct answer presents a complete solution
...
)
Correct AnswerA
...
Processor: % User Time
C
...
SQL Server: Buffer Manager: Page writes/sec

2
...
The percentage of time that the disk is busy with read/write activity
B
...
The number of pages used to store compiled queries
D
...
By using Windows Performance Monitor, you have identified a large number of threads
waiting for processor time
...
Add RAM
...
Add spindles to the storage subsystem
...
Disable Hyper-Threading
...
Install a faster processor
...
Which index type organizes the data in the table in the logical order of the key?
A
...
Spatial index
Correct AnswerC
...
Non-clustered index

2
...
One index key and 900 bytes
B
...
16 index keys and 900 bytes
D
...
What are two situations in which you should consider adding filtered indexes to a
table?
Correct AnswerA
...
When a column contains many duplicate values
Correct AnswerC
...
When you want to support XML queries

4
...
sys
...
sys
...
sys
...
sys
...
When you examine a query execution plan, you notice that for a given operator the
actual number of rows and estimated number of rows returned are very different
...
Execute the sp_updatestats system stored procedure, specifying 20 for the value

CNTS
of the @resample parameter
...
Execute the UPDATE STATISTICS command on the table in question, specifying the
WITH SAMPLE 20 PERCENT option
...
Execute the UPDATE STATISTICS command on the table in question, specifying the
WITH FULLSCAN option
...
Execute the UDPATE STATISTICS command on the table in question, specifying the
WITH NORECOMPUTE option
...
What is the default transaction isolation level in SQL Server 2012?
A
...
Read committed
C
...
Snapshot

2
...
sys
...
sys
...
sys
...
sys
...
You suspect that blocking is occurring
...
Examine the blocking_session_id of sys
...

Correct AnswerB
...
dm_exec_requests
...
Examine the Procesess pane of Activity Monitor
...
Examine the blocked_process column of the system_health Extended Event
Session
...
You have determined that blocking is occurring due to an index rebuild operation
...
You need to remove the blocking and must be able to
give an estimate of the time remaining until the blocking is resolved
...
KILL 57 WITH STATUSONLY;
B
...
ROLLBACK TRANSACTON
D
...


Chapter 11
SQL Server Agent, Backup, and Restore

Lesson 1Managing SQL Server Agent

1
...
These users can create jobs
...
The SQL Server service
on SQL-A uses a domain account named syd-sql-a-agnt
...
Backup Operators

CNTS
B
...
Performance Monitor Users
Correct AnswerD
...
Rooslan needs to be able to create SQL Server Agent jobs and to manage the schedules
of jobs that he has created
...
To which of
the following roles should you add Rooslan’s account?
Correct AnswerA
...
SQLAgentReaderRole
C
...
sysadmin

3
...
You have created the job
...
Alert
B
...
Maintenance plan
D
...
You must automate the process of running a Windows PowerShell script followed by
an Integration Services package and then a system stored procedure
...
M
...
Operator
B
...
Job
D
...


Lesson 2Configuring and Maintaining a Backup Strategy

1
...
M
...
You want to perform a full backup of the database at 1 P
...
without
affecting the existing restore sequence
...
Full backup
B
...
Differential backup
Correct AnswerD
...
You perform a full backup daily at 1 A
...
You perform transaction log backups every
30 minutes
...

Which of the following backups should you perform?
A
...
Transaction log backup
Correct AnswerC
...
Copy backup

3
...
master
B
...
tempdb
D
...
Which backup preference should you set for an AlwaysOn availability group if you

CNTS
want backups to occur on secondary replicas but which enables backups to occur on
the primary replica if that replica is the only one online?
Correct AnswerA
...
Secondary Only
C
...
Any Replica
...
A database has become corrupted
...
Which of the following restore
options should you use?
A
...
RESTORE WITH NORECOVERY
Correct AnswerC
...
RESTORE WITH KEEP_REPLICATION

2
...
Which is the final step in this process?
Correct AnswerA
...

B
...

C
...

D
...


3
...
M
...
M
...
M
...
Database files and transaction log files are
stored on the same volume
...
M
...
Which of the following backups will you use when performing a
restore operation? (Each correct answer presents part of a complete solution
...
)
Correct AnswerA
...
M
...
10 A
...
differential backup
C
...
30 A
...
transaction log backup
D
...
30 A
...
transaction log backup

4
...
exe command?
(Each correct answer presents a complete solution
...
)
Correct AnswerA
...
model
C
...
master
...
Muhammad Musleh™



---