70-462 Study Notes Flashcards
1
Q
Which system stored procedure is used to set the backup compression default for all databases on the server?
A
sp_configure
2
Q
What must you use to insure parallel imports do not block each other?
A
Table Locks
3
Q
Only perform a bulk import on tables using a table lock if the table does not have a ____.
A
Index
4
Q
Using a page lock ensures import operations will not block each other (True or False)
A
False
5
Q
Name a Pro and a Con to disabling constraints on a Table when setting Page Lock.
A
Pro - Imports will run quicker.
Con - May allow data to violate check constraints.
6
Q
Which option is used to set the Thread Pool?
A
MAX WORKER THREADS
7
Q
What value do you set MAX WORKER THREADS to in order to allow SQL Server to determine the size of the Thread Pool when the service is started.
A
MAX WORKER THREAD = 0
8
Q
Which T-SQL must be executed when using advanced configuration options.
A
sp_configure ‘SHOW ADVANCED OPTIONS’,1
9
Q
Which parallelism option determines the threshold at which a query is considered a long running query?
A
COST THRESHOLD FOR PARALLELISM
10
Q
The Max Degree of Parallelism option determines…
A
The maximum number of parallel execution plans that can be used for a single query.
11
Q
What two T-SQL commands can provide lock activity, including a list of currently active locks when data is retrieved?
A
sys.dm_tran_locks (dynamic management view)
sp_lock (system stored procedure)
12
Q
Which two SQL tools can provide information about lock activity over time but not as a snapshot of current activity?
A
SQL Trace and SQL Profiler
13
Q
Which Dynamic Management View returns information about tasks that are waiting for resources?
A
sys.dm_os_waiting_tasks
14
Q
Which Dynamic Management View returns information about active transactions?
A
sys.dm_tran_active_snapshot_database_transactions
15
Q
What T-SQL command using a DMV can you run to show the encryption key information for your current database?
A
SELECT * FROM sys.dm_database_encryption_keys
16
Q
What tool is used to analyze the performance of database workloads and provides recommendations to add, remove, or modify physical design structures including clustered indexes, nonclustered indexes, indexed views, and partitioning?
A
Database Engine Tuning Advisor
17
Q
Which property is used to limit the maximum size of a filegroup?
A
AUTOGROWTH property
18
Q
What are the 3 components of IT?
A
Client Solutions: These include desk-top computers, laptops or notebooks, portable devices, and even telephones in Voice over IP implementations.
Network Infrastructure Solutions: These include switches, routers, and network communications services( such as DNS, DHCP, authentication services, and so on).
Information Storage Solutions: These include databases, file servers, and networked storage such as Network Attached Storage (NAS) and storage area networks (SANs).
19
Q
What technology allows more than one physical server to be available for processing a single application?
A
Clustering
20
Q
Name 6 tasks you can perform with the SQL Server Configuraion Manager (SSCM):
A
- Stop and start the SQL Server services
- Change service login information
- Enable or disable network protocols
- Configure aliases on client computers
- Enable or disable protocol encryption for client connections
- Enable or disable filestream support for a SQL Server instance
21
Q
_____ allows you to perform queries against DNS servers in order to ensure that DNS is operating appropriately.
A
NSLOOKUP
22
Q
____ provides a simple interface that is used to check whether a remote host is live on the network.
A
PING
23
Q
____ is used to test each connection along the path to a destination.
A
TraceRT
24
Q
____ not only tests the devices along the path, but it generates reports to help you determine network communications problems such as latency and intermittent problems.
A
PathPING
25
SQL Server Profiler
An Application that is used to monitor the activity taking place on a SQL Server instance. Can be used to monitor T-SQL transactions and deadlocks.
26
Alias
An Alternative name for a SQL Server Object that can be used to simplify object access.
27
What technology is Microsoft's implementation of web-based enterprise management and may be used to monitor system health for SQL Server systems?
Windows Management Instrumentation (WMI)
28
What SQL Server tool is used to configure the SQL Server services?
SQL Server Configuration Manager
29
What SQL Server tool can be used to capture the SQL requests coming into SQL Server and save then to a trace file?
The SQL Server Profiler
30
What tool can you use to build complex SQL Server Integration Services packages?
SQL Server Data Tools (SSDT)
31
Is the SQL Server Management Studio installed separately for each instance or once for all instances?
SSMS is a server-level object and is installed once for all instances.
32
What are the four System Databases?
Master DB, MSDB, Model DB, and tempdb
33
What are the five most commonly configured database properties?
```
Autogrowth
Recovery model
Compatibility level
Auto shrink
Restrict access
```
34
What are the three types of Recovery models?
Simple
Bulk-logged
Full
35
What are the three options for the Restrict Access database property?
MULTI_USER
SINGLE_USER
RESTRICTED_USER
36
Which Restrict Access option allows only administrator and Database Owner access to the database when set?
RESTRICTED_USER
37
What must you create to specify the file which a table should be stored in a multifile database?
multiple filegroups
38
What RAID level provides stripe sets for performance gains but does not provide for parity-based fault tolerance?
RAID 0 provides striping
39
What level of RAID provides mirroring for fault tolerance?
RAID 1 provides mirroring
40
What recovery model will only minimally log a BCP operation?
Bulk-logged recovery
41
What recovery model does not retain the transactions long term in the transaction log for any type of transaction?
Simple
42
What can you create to generate a point-in-time capture of the data in a database?
A database Snapshot
43
What system database is used as a non-permanent location for data during large data volume operations when the server has insufficient memory for in-memory storage?
tempdb database
44
You must create a database on a SQL server instance. You can place the data files and log files on the E: or F: physical drive. Where should you place the log file if you place the data file on the F: drive?
Place the log file on the E: drive.
45
What is the primary benefit of using multiple files to store a database in a single filegroup?
Data striping across the files for increased performance.
46
filegroup
A logical collection of one or more files used to store database table data.
47
RAID
Redundant Array of Independent Disks (RAID) is used to provide improved performance or storage fault tolerance.
48
What index types are supported in SQL Server 2012?
```
Clustered
Nonclustered
Spatial
Partitioned
XML
Filtered
Columnstore
```
49
Covering Index
A Special multicolumn index used to cover frequently executed queries.
50
What three methods can be used to defragment indexes?
Dropping and re-creating the index
Rebuild the index
Reorganize the index
51
At what percentage of fragmentation does Microsoft recommend reorganizing an index?
5 - 30 % fragmentation. Above 30%, the index should be rebuilt.
52
How is a table stored when no clustered index exists on the table?
As a heap
53
You have enabled compression for a table that has nonclustered indexes. Will the nonclustered indexes automatically be compressed?
No. Each nonclustered index must be enabled for compression individually.
54
What is a Nonclustered Index?
An Index that is stored separately from the data table within the same database. Used to improve query results when non-key columns are used in WHERE clauses.
55
What is a View?
A stored SELECT statement that is used to simplify access to table data. They may also improve performance.
56
What is XML?
The Extensible Markup Language. A portable data description language supported by SQL Server.
57
What are the four primary steps to creating a SSIS package?
- Define Connection Managers.
- Define the Data ETL Objects
- Testing
- Save and Execute
58
To configure a SSIS project to run in 32-bit mode, which configuration property option must be set to False?
Run64BitRuntime option.
59
What are the six security protection levels that can be used with SSIS packages to protect sensitive data?
```
Do Not Save Sensitive
Encrypt All Data With Password
Encrypt Sensitive Data with Password
Encrypt All Data with User Key
Encrypt Sensitive Data with User Key
Rely On Server Storage
```
60
What is the TDE Architecture hierarchy?
The TDE is made up of the Service Master Key (SMK) which encrypts the Database Master Key (DMK) which encrypts the Database Encryption Key(DEK) which finally encrypts the data.
61
What are the steps for implementing TDE?
1. Use master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
2. CREATE CERTIFICATE SrvrCert WITH SUBJECT = 'Server Certificate';
3. Use ;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SrvrCert;
4. ALTER DATABASE SET ENCRYPTION ON;
62
What system procedure would you use to evaluate the benefits of compression on a given object?
sp_estimate_data_compression
63
Which protection level is available only when saving packages in a SQL Server?
Rely on Server Storage protection level.
64
What can you use to create a plan for the backup and maintenance of a database from within SSMS?
Maintenance Plan Wizard.
65
Where are the full-text catalogs used by full-text indexing stored in SQL Server?
They are stored inside the SQL Server database.
66
When implementing row compression for int data types, how is the data stored?
The space required to store the information is used in 1-byte increments. The least amount used will be 1 byte and the maximum amount will be 4 bytes, which is the standard size of an int column.
67
Cube
Multi-dimensional objects within OLAP systems that allow you to view a data value from three or more dimensions.
68
Data mart
A small data warehouse, typically created for a department or a group within a department.
69
Full-text index
A special index that allows for faster searches against text data columns. Search features provided include word forms and plurality.
70
Transparent Data Encryption (TDE)
Allows for the encryption of data stored in SQL Server databases.
71
What are the types available for creating SQL Server Job Step Tasks?
```
ActiveX Script
Operating System
Powershell
Replication
SQL Server Analysis Services
SQL Server Intergration Services Packlage
T-SQL Commands
```
72
You want to ensure that a single table exists in a database at a remote location. What SQL Server 2012 component should you employ?
Replication
73
You want to perform a DBCC check automatically against a database, and in the same task set you want to export data to a file. What do you need to create?
A SQL Server job
74
What does the Maintenance Plan Wizard create to implement a database maintenance plan?
An SSIS package
75
You want the operator named Fred to receive a pop-up message on his computer when a specific job completed. What notification option should you employ?
NET SEND
76
What kind of alert should you create for watch for table integrity problems in a SQL Server Database>
SQL Event alert
77
What function within SSMS allows you to enable WMI monitoring from within a graphical interface?
SQL Agent Alerts
78
Other than WMI monitoring, what other events can a SQL Agent Alert monitor>
Performance events and SQL Server events.
79
What windows Server tool includes the System Monitor control and allows for the capture of data collector sets?
The Performance and Reliability Monitor.
80
Replication
A SQL Server technology that allows data to be distributed automatically. The replication system includes publishers, distributors, and subscribers. Article are published and the articles include entire tables or portions of tables.
81
Each server instance participating in an availability group must have a ________ ________ ________.
database mirroring endpoint
82
What are the two availability modes for AlwaysOn?
Synchronous-Commit
| Asynchronous-Commit
83
No data is lost, in this failover process because it performs intentionally and with forethought so that replicas are first synchronized.
planned manual failover
84
Forced failover is used
When the primary replica experiences failure and the secondary is not synchronized. Can be used with either Synchronous and Asynchronous operational modes.
85
What are the failover modes?
Automatic Failover
Planned Manual Failover
Forced Failover
86
Hardware consideration for AlwaysOn should include...
Highly Available Servers (Redundant Components, Error-Correcting Components, Redundant Servers)
Highly Available Storage (Internal/External RAID, SAN)
Highly Available Networks
87
What high-availability technology requires two physical servers, is a Windows Server feature supported by SQL Server, and can provide automatic failover for an entire SQL Server instance?
AleaysOn Failover Clustering Instance
88
AlwaysOn
The new high-availability solution in SQL Server 2012 that supports clustering and database availability groups.
89
availability group
A collection of databases that are replicated and maintained across multiple SQL Server instances.
90
A single server that participates in a cluster
cluster node
91
Failover Management choices
Active/Passive or Active/Active
92
A method that allows multiple servers to act as one logical server on the network so that clients may access the logical server with high availability.
Clustering
93
Database Mirroring
allows a database to exist and be automatically maintained on two SQL Server 2012 instances at the same time.
94
What does Log Shipping redundancy provide?
allows a database to be duplicated on a second instance with high levels of latency (due to delays between updates).
95
Database Snapshot
Do not typically involve a second instances but are created on the local instances to provide point-in-time access to data.
96
Database Mirroring
A transaction-level concept that provides a mechanism for duplicating databases across servers.
97
Database Mirroring components
Principal
Mirror
Witness (Optional)
98
Houses the active database that is accessed by the users.
Principal server
99
houses a copy of the database but is not accessed by users or applications.
Mirror server
100
An optional server used to provide automatic failover
Witness server
101
What are the steps for implementing Database mirroring?
.1 Use full recovery model for database participating in mirroring.
2. Backup database on principle instance
3. Recover mirror instance with NORECOVERY option.
4. Make sure other database objects are transferred to the mirror instance.
102
What are the three phases of the Log-Shipping?
Phase 1: The transaction log is backed up on the primary server.
Phase 2: Copy log to the secondary server.
Phase 3: Restore log on secondary server.
103
What T-SQL command is used to revert an entire database to a database snapshot?
RESTORE DATABASE
104
What level of RAID provides stripe sets with parity for fault tolerance?
RAID 5 provides stripe sets with parity.
105
What SQL Server high-availability feature allows a database to be duplicated onto a second instance of SQL Server and updated transactionally?
Database mirroring
106
When considering RAID 0 and RAID 1, which version of RAID provides better IO throughput in most scenarios?
RAID 0 provides better throughput with disk striping.
107
What SQL Server high-availability feature uses backups and restores and is used to create a cold standby copy of a database on a separate instance of SQL Server?
Log shipping
108
What is required to implement automatic failover from the primary server to the mirror server with database mirroring?
A witness server.
109
You want to use log shipping to duplicate and maintain a database with filestreams from a SQL Server 2012 instance to a SQL Server 2005 instance. Can you do this?
No, because SQL Server 2005 does not support filestreams.
110
You have a database with several filestream data columns. Can you mirror this database?
No, because filestreams are not supported for mirrored databases.
111
What is Database mirroring?
A technology that allows a duplicate(mirror) copy of a database to be maintained on a spearate instance of SQL Server.
112
What is a Database snapshot?
A picture of the database at the point in time when the snapshot was taken. Mutiple snapshots can be take against a single database at the same time. The administrator can revert the database to a snapshot.
113
What are the key roles involved in the replication process?
```
Publisher
Subscriber
Distributor
Publication
Article
```
114
Three main replication types:
Transactional
Snapshot
Merge
115
Transactional Replication
Provides for lower latency and faster replication of changes. Useful when data source processes a large number of transactions.
116
Snapshot replication
Used when higher latency is acceptable or changes happen less frequently at the publisher.
117
Merge replication
Used when data changes should be allowed and retained at both the publisher and the subscriber.
118
Replication Models:
Local Distributor Model
Remote Distributor Model
Single Subscriber
Mulitple Subscriber
119
You need to perform an ETL task. What SQL Server component are you most likely to use?
SSIS is used for ETL task creation and execution.
120
What type of replication will allow subscribers to change the data and synchronize those changes with the publisher?
Merge replication
121
What T-SQL command can be used to insert a large amount of text from a text file into a SQL Server table?
BULK INSERT
122
A collection of tables published for a subscriber is called what in the replication topology?
Article
123
If you want to duplicate data onto several subscriber machines from a single publisher, what SQL Server high-availability solution should you use?
Replication
124
You want to implement a replication model that allows for updates to be sent to the subscribers as transactions occur. What model should you use?
Transactional
125
Latency is acceptable and you want to implement a replication model that periodically updates the subscribers. What kind of replication should you use?
Snapshots
126
Other than the publisher and the subscriber, what server role is used in a SQL Server replication topology?
Distributor
127
What two roles of the replication topology can exist within the same server instance?
Publisher and distributor
128
BCP
Bulk Copy Program is a command-line application used to import and export data to or from a database.
129
BULK INSERT
A Transact-SQL command that can be used to import data into SQL Server database tables from flat files.
130
You can configure Database Mail system parameters using this stored procedure...
msdb.dbo.sysmail_configure_sp
131
Stored procedure used to create Database Mail accounts?
msdb.dbo.sysmail_add_account_sp
132
When using WSRM which CPU allocation method divides CPU bandwidth evenly across matched processes?
Equal Per Process
133
What are the three minimum CPU allocation methods when using WSRM?
Equal Per Process
Equal Per User
Equal Per Session
134
Which stored procedure do you run to cycle the error log?
sp_cycle_errlog
135
Which stored procedure do you use to cycle the SQL Agent error log?
msdb.dbo.sp_cycle_agent_errorlog
136
Which T-SQL statement is used to move an index?
CREATE INDEX statement with DROP_EXISTING=ON option
137
Which compression attribute is inherited when merging two partitions each having their own compression method?
The compression attribute of the destination partition.
138
Which Editions of SQL Server support data compression?
SQL Server 2012 Enterprise and Developer editions only
139
What function defines how the rows of an index or table map to specific partitions based on the values of their partitioning columns?
Partition Function
140
What's the command for backing up the server certificate and private key and storing them in the default folder MSSQL\DATA ?
BACKUP CERTIFICATE TO FILE = '' WITH PRIVATE KEY (
FILE = '' , ENCRYPTION BY PASSWORD = ''
);
141
The column of an index or table that a partition function uses to partition the index or table is called?
Partitioning column
142
Maps the partitions in a partition function to a collection of filegroups?
Partition scheme
143
An index that uses the same partition scheme as the table where it belongs is called?
an Aligned Index
144
An index that is partitioned away from the table which it belongs is called?
a Nonaligned index
145
Which Database command can you use to monitor the amount of log space used?
DBCC SQLPERF(LOGSPACE)
146
Which tool included with SQL Server 2012 installation media enables you to identify issues that will potentially block an upgrade?
Upgrade Advisor
147
Which tool do you use to simulate mission-critical workloads, test server function before upgrading to SQL Server 2012 and can determine whether any incompatibilities might occur between an application and the SQL server?
Distributed Replay Utility
148
Steps prior to upgrading to SQL Server 2012?
- Ensure the databases are in consistent states by running appropriate DBCC commands.
- Verify that system databases are configured with AutoGrow enabled.
- Disable startup stored procedures and replication
- Ensure that enough space exists on the system volume and the volume that will host the program files for the upgrade
149
Difference between Upgrading and Migrating?
- Upgrading means the database stays on its original instance and all objects and files are upgraded in place.
- Migrating means a database is no longer on its original instance (Detached/Copied) and all objects and files are redone or relocated.
150
Which stored procedure can be used to alter the Full-Text Upgrade option?
sp_fulltext_service
151
Which stored procedure is used to detach a database?
sp_detach_db
152
What T-SQL statement do you use to attach a database?
CREATE DATABASE statement with FOR ATTACH option
153
Which Catalog view contains information such as login creation date, modification date, and login type?
sys.server_principles
154
Which catalog view provides information on SQL Server-Authenticated logins only?
sys.sql_logins
155
Which stored procedure provides a list of fixed server roles?
sp_helpsrvrole
156
Which stored procedure provides a list of fixed server roles and their membership?
sp_helpsrvrolemember
157
Which stored procedure lists permissions for fixed server roles?
IS_SRVROLEMEMBER
158
Which system view provides information about role members displayed as role and member id?
sys.server_role_members
159
What's the T-SQL command you can use to check for orphaned users?
sp_change_users_login @Action = 'Report';
160
Which stored procedure can be used to link an orphaned user with a SQL login?
sp_change_users_login
161
Which stored procedures are used to add and delete database users to fixed database roles?
sp_addrolemember - to add members
| sp_droprolemember - to drop members
162
What type of roles allow you to create custom database-level permissions?
Flexible Database-Level Roles
163
Members of this role can perform any configuration or maintenance activity on the database as well as dropping the database.
db_owner
164
role members can modify role membership, manage role permissions, and can escalate their own privilege.
db_securityadmin
165
Role members can add or remove database access for windows logins, Windows groups, and SQL Server logins.
db_accessadmin
166
Role members can backup the database
db_backupoperator
167
Role members can run any DDL command on the database
db_ddladmin
168
Role members can add, delete, and change data in all user tables
db_datawriter
169
Role members can read all data stored in user tables
db_reader
170
Role members are blocked from adding, modifying, or deleting any data stored in user tables
db_denydatawriter
171
Role Members are blocked from reading any data stored in user tables
db_denydatareader
172
Enables you to use roles with a custom set of permissions?
Flexible database roles
173
Account Lockout Duration
The length of time an account is locked out
174
Account Lockout Threshold
The number of failed logins that must occur to trigger a lock-out
175
Reset Account Lockout Counter After
The duration in which the failed logins must occur to trigger a lockout
176
Which system catalog view would you use to determine whether a login is disabled?
sys.server_principals
177
Which security catalog view provides information about each asymmetric key, including how the key is encrypted, how the private key is encrypted, the key length, and the algorithm used?
sys.asymmetric_keys
178
Which security catalog view provides information on each certificate stored in the database, including certificate name, how the private key is encrypted, certificate serial number, certificate login security identifier (SID), certificate expiration, and the date when the private key was last backed up?
sys.certificates
179
Which security catalog view provides information about each symmetric encryption specified when using the CREATE SYMMETRIC KEY statement with the ENCRYPTION BY parameter? Options include encryption by symmetric key, password, certificate, asymmetric key, and master key.
sys.key_encryptions
180
Which security catalog view provides information for every symmetric key that has been created by using the CREATE SYMMETRIC KEY statement, including the database principal who owns the key, key length, and key generation algorithm?
sys.symmetric_keys
181
What information does sys.database_mirroring_endpoints provide?
Information on endpoints used for database mirroring and AlwaysOn Availability Groups. Can be used to determine the connection authentication type and the properties of any certificate used to secure the endpoint.
182
What information is provided by sys.endpoints
Information on each endpoint created on the instance. Includes information n the endpoint protocol, payload type, and endpoint state.
183
What information is provided by the sys.http_endpoints view
Information on each endpoint that uses the HTTP protocol. Enables you to determine whether Secure Sockets Layer (SSL) is in use and the type of authentication method that has been configured for the endpoint.
184
What information does the sys.service_broker_endpoints view provide?
Information on service broker endpoints, including authentication configuration, encryption algorithm, and the identifier of any certificate used for authentication.
185
What information does the catalog view sys.tcp_endpoints provide?
Information about the TCP endpoints in the system, including port number, IP address, and whether the port is dynamic.
186
Which system catalog view provides information about permissions at the database level?
sys.database_permissions
187
Which catalog view provides information about principle type, including authentication type (instance, database, or windows) and creation date?
sys.database_principals
188
Which catalog view provides information about role membership at the database level?
sys.database_role_members
189
Which catalog view provides information on database master key passwords, including the credentials in which the master key password is associated?
sys.master_key_passwords
190
Which dynamic management view can you query to view information about the last successful and unsuccessful login times of SQL Logins?
sys.dm_exec_sessions
191
Which System stored procedure can you use to retrieve or update the current status of a database mirror?
sp_dbmmonitorresults
192
What are the steps for implementing Database Mail?
1. Enable Database Mail
2. Create a Database Mail account for the SQL Server Agent service account to use
3. Create a database Mail profile for the SQL Server Agent service account to use
4. Configure SQL Server Agent to use Database Mail
193
Which isolation level would you use to minimize locking contention by allowing other transactions to modify data being read by the current transaction?
READ UNCOMMITTED - The least restrictive isolation level
194
Which isolation does not allow statements to read data that has been modified by other transactions that are uncommitted (preventing dirty reads.)
READ COMMITTED - Is also the default 2102 SQL Server transaction isolation level.
195
Which transactional isolation level does not allow statements to read data that has been modified and uncommitted? And no transactions can alter data that has been read by the current transaction until that transaction completes?
REPEATABLE READ - uses shared locks on all data read by each statement until the transaction completes.
196
Which transaction level prevents statements from reading data that has been modified but not yet committed by other transactions. Transactions are not able to modify data that has been read by the current transaction until that transaction completes; other transactions are unable to insert rows into the range of rows being read by the current transaction.
SERIALIZABLE - the most restrictive isolation level
197
Which fixed server role can modify endpoint settings?
serveradmin
198
Which stored procedure would you query if looking for details on a database
sp_helpdb
199
Which catalog view would you query to determine the operating mode of a mirroring session?
sys.database_mirroring
