Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

70-462 MCSA Certification > 70-462 Study Notes > Flashcards

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What must you use to insure parallel imports do not block each other?

A

Table Locks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Only perform a bulk import on tables using a table lock if the table does not have a ____.

A

Index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Using a page lock ensures import operations will not block each other (True or False)

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which option is used to set the Thread Pool?

A

MAX WORKER THREADS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which T-SQL must be executed when using advanced configuration options.

A

sp_configure ‘SHOW ADVANCED OPTIONS’,1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which parallelism option determines the threshold at which a query is considered a long running query?

A

COST THRESHOLD FOR PARALLELISM

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Which Dynamic Management View returns information about tasks that are waiting for resources?

A

sys.dm_os_waiting_tasks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which Dynamic Management View returns information about active transactions?

A

sys.dm_tran_active_snapshot_database_transactions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Which property is used to limit the maximum size of a filegroup?

A

AUTOGROWTH property

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What technology allows more than one physical server to be available for processing a single application?

A

Clustering

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

_____ allows you to perform queries against DNS servers in order to ensure that DNS is operating appropriately.

A

NSLOOKUP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

____ provides a simple interface that is used to check whether a remote host is live on the network.

A

PING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

____ is used to test each connection along the path to a destination.

A

TraceRT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

SQL Server Profiler

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Alias

A

An Alternative name for a SQL Server Object that can be used to simplify object access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What technology is Microsoft’s implementation of web-based enterprise management and may be used to monitor system health for SQL Server systems?

A

Windows Management Instrumentation (WMI)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What SQL Server tool is used to configure the SQL Server services?

A

SQL Server Configuration Manager

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What SQL Server tool can be used to capture the SQL requests coming into SQL Server and save then to a trace file?

A

The SQL Server Profiler

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What tool can you use to build complex SQL Server Integration Services packages?

A

SQL Server Data Tools (SSDT)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Is the SQL Server Management Studio installed separately for each instance or once for all instances?

A

SSMS is a server-level object and is installed once for all instances.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What are the four System Databases?

A

Master DB, MSDB, Model DB, and tempdb

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What are the five most commonly configured database properties?

A 
Autogrowth
Recovery model
Compatibility level
Auto shrink
Restrict access
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What are the three types of Recovery models?

A

Simple
Bulk-logged
Full

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What are the three options for the Restrict Access database property?

A

MULTI_USER
SINGLE_USER
RESTRICTED_USER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Which Restrict Access option allows only administrator and Database Owner access to the database when set?

A

RESTRICTED_USER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What must you create to specify the file which a table should be stored in a multifile database?

A

multiple filegroups

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What RAID level provides stripe sets for performance gains but does not provide for parity-based fault tolerance?

A

RAID 0 provides striping

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What level of RAID provides mirroring for fault tolerance?

A

RAID 1 provides mirroring

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What recovery model will only minimally log a BCP operation?

A

Bulk-logged recovery

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What recovery model does not retain the transactions long term in the transaction log for any type of transaction?

A

Simple

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What can you create to generate a point-in-time capture of the data in a database?

A

A database Snapshot

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

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?

A

tempdb database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

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?

A

Place the log file on the E: drive.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What is the primary benefit of using multiple files to store a database in a single filegroup?

A

Data striping across the files for increased performance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

filegroup

A

A logical collection of one or more files used to store database table data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

RAID

A

Redundant Array of Independent Disks (RAID) is used to provide improved performance or storage fault tolerance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What index types are supported in SQL Server 2012?

A 
Clustered
Nonclustered
Spatial
Partitioned
XML
Filtered
Columnstore
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Covering Index

A

A Special multicolumn index used to cover frequently executed queries.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

What three methods can be used to defragment indexes?

A

Dropping and re-creating the index
Rebuild the index
Reorganize the index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

At what percentage of fragmentation does Microsoft recommend reorganizing an index?

A

5 - 30 % fragmentation. Above 30%, the index should be rebuilt.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

How is a table stored when no clustered index exists on the table?

A

As a heap

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

You have enabled compression for a table that has nonclustered indexes. Will the nonclustered indexes automatically be compressed?

A

No. Each nonclustered index must be enabled for compression individually.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What is a Nonclustered Index?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

What is a View?

A

A stored SELECT statement that is used to simplify access to table data. They may also improve performance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

What is XML?

A

The Extensible Markup Language. A portable data description language supported by SQL Server.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

What are the four primary steps to creating a SSIS package?

A
  • Define Connection Managers.
  • Define the Data ETL Objects
  • Testing
  • Save and Execute
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

To configure a SSIS project to run in 32-bit mode, which configuration property option must be set to False?

A

Run64BitRuntime option.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

What are the six security protection levels that can be used with SSIS packages to protect sensitive data?

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

What is the TDE Architecture hierarchy?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

What are the steps for implementing TDE?

A
  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;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

What system procedure would you use to evaluate the benefits of compression on a given object?

A

sp_estimate_data_compression

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

Which protection level is available only when saving packages in a SQL Server?

A

Rely on Server Storage protection level.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

What can you use to create a plan for the backup and maintenance of a database from within SSMS?

A

Maintenance Plan Wizard.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Where are the full-text catalogs used by full-text indexing stored in SQL Server?

A

They are stored inside the SQL Server database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

When implementing row compression for int data types, how is the data stored?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

Cube

A

Multi-dimensional objects within OLAP systems that allow you to view a data value from three or more dimensions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

Data mart

A

A small data warehouse, typically created for a department or a group within a department.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

Full-text index

A

A special index that allows for faster searches against text data columns. Search features provided include word forms and plurality.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

Transparent Data Encryption (TDE)

A

Allows for the encryption of data stored in SQL Server databases.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

What are the types available for creating SQL Server Job Step Tasks?

A 
ActiveX Script
Operating System
Powershell
Replication
SQL Server Analysis Services
SQL Server Intergration Services Packlage
T-SQL Commands
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

You want to ensure that a single table exists in a database at a remote location. What SQL Server 2012 component should you employ?

A

Replication

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

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

A SQL Server job

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

What does the Maintenance Plan Wizard create to implement a database maintenance plan?

A

An SSIS package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

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?

A

NET SEND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

What kind of alert should you create for watch for table integrity problems in a SQL Server Database>

A

SQL Event alert

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

What function within SSMS allows you to enable WMI monitoring from within a graphical interface?

A

SQL Agent Alerts

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

Other than WMI monitoring, what other events can a SQL Agent Alert monitor>

A

Performance events and SQL Server events.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

What windows Server tool includes the System Monitor control and allows for the capture of data collector sets?

A

The Performance and Reliability Monitor.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

Replication

A

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
Q

Each server instance participating in an availability group must have a ________ ________ ________.

A

database mirroring endpoint

82
Q

What are the two availability modes for AlwaysOn?

A

Synchronous-Commit

Asynchronous-Commit

83
Q

No data is lost, in this failover process because it performs intentionally and with forethought so that replicas are first synchronized.

A

planned manual failover

84
Q

Forced failover is used

A

When the primary replica experiences failure and the secondary is not synchronized. Can be used with either Synchronous and Asynchronous operational modes.

85
Q

What are the failover modes?

A

Automatic Failover
Planned Manual Failover
Forced Failover

86
Q

Hardware consideration for AlwaysOn should include…

A

Highly Available Servers (Redundant Components, Error-Correcting Components, Redundant Servers)
Highly Available Storage (Internal/External RAID, SAN)
Highly Available Networks

87
Q

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?

A

AleaysOn Failover Clustering Instance

88
Q

AlwaysOn

A

The new high-availability solution in SQL Server 2012 that supports clustering and database availability groups.

89
Q

availability group

A

A collection of databases that are replicated and maintained across multiple SQL Server instances.

90
Q

A single server that participates in a cluster

A

cluster node

91
Q

Failover Management choices

A

Active/Passive or Active/Active

92
Q

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.

A

Clustering

93
Q

Database Mirroring

A

allows a database to exist and be automatically maintained on two SQL Server 2012 instances at the same time.

94
Q

What does Log Shipping redundancy provide?

A

allows a database to be duplicated on a second instance with high levels of latency (due to delays between updates).

95
Q

Database Snapshot

A

Do not typically involve a second instances but are created on the local instances to provide point-in-time access to data.

96
Q

Database Mirroring

A

A transaction-level concept that provides a mechanism for duplicating databases across servers.

97
Q

Database Mirroring components

A

Principal
Mirror
Witness (Optional)

98
Q

Houses the active database that is accessed by the users.

A

Principal server

99
Q

houses a copy of the database but is not accessed by users or applications.

A

Mirror server

100
Q

An optional server used to provide automatic failover

A

Witness server

101
Q

What are the steps for implementing Database mirroring?

A

.1 Use full recovery model for database participating in mirroring.

  1. Backup database on principle instance
  2. Recover mirror instance with NORECOVERY option.
  3. Make sure other database objects are transferred to the mirror instance.
102
Q

What are the three phases of the Log-Shipping?

A

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
Q

What T-SQL command is used to revert an entire database to a database snapshot?

A

RESTORE DATABASE

104
Q

What level of RAID provides stripe sets with parity for fault tolerance?

A

RAID 5 provides stripe sets with parity.

105
Q

What SQL Server high-availability feature allows a database to be duplicated onto a second instance of SQL Server and updated transactionally?

A

Database mirroring

106
Q

When considering RAID 0 and RAID 1, which version of RAID provides better IO throughput in most scenarios?

A

RAID 0 provides better throughput with disk striping.

107
Q

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?

A

Log shipping

108
Q

What is required to implement automatic failover from the primary server to the mirror server with database mirroring?

A

A witness server.

109
Q

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?

A

No, because SQL Server 2005 does not support filestreams.

110
Q

You have a database with several filestream data columns. Can you mirror this database?

A

No, because filestreams are not supported for mirrored databases.

111
Q

What is Database mirroring?

A

A technology that allows a duplicate(mirror) copy of a database to be maintained on a spearate instance of SQL Server.

112
Q

What is a Database snapshot?

A

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
Q

What are the key roles involved in the replication process?

A 
Publisher
Subscriber
Distributor
Publication
Article
114
Q

Three main replication types:

A

Transactional
Snapshot
Merge

115
Q

Transactional Replication

A

Provides for lower latency and faster replication of changes. Useful when data source processes a large number of transactions.

116
Q

Snapshot replication

A

Used when higher latency is acceptable or changes happen less frequently at the publisher.

117
Q

Merge replication

A

Used when data changes should be allowed and retained at both the publisher and the subscriber.

118
Q

Replication Models:

A

Local Distributor Model
Remote Distributor Model
Single Subscriber
Mulitple Subscriber

119
Q

You need to perform an ETL task. What SQL Server component are you most likely to use?

A

SSIS is used for ETL task creation and execution.

120
Q

What type of replication will allow subscribers to change the data and synchronize those changes with the publisher?

A

Merge replication

121
Q

What T-SQL command can be used to insert a large amount of text from a text file into a SQL Server table?

A

BULK INSERT

122
Q

A collection of tables published for a subscriber is called what in the replication topology?

123
Q

If you want to duplicate data onto several subscriber machines from a single publisher, what SQL Server high-availability solution should you use?

A

Replication

124
Q

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?

A

Transactional

125
Q

Latency is acceptable and you want to implement a replication model that periodically updates the subscribers. What kind of replication should you use?

126
Q

Other than the publisher and the subscriber, what server role is used in a SQL Server replication topology?

A

Distributor

127
Q

What two roles of the replication topology can exist within the same server instance?

A

Publisher and distributor

128
Q

BCP

A

Bulk Copy Program is a command-line application used to import and export data to or from a database.

129
Q

BULK INSERT

A

A Transact-SQL command that can be used to import data into SQL Server database tables from flat files.

130
Q

You can configure Database Mail system parameters using this stored procedure…

A

msdb.dbo.sysmail_configure_sp

131
Q

Stored procedure used to create Database Mail accounts?

A

msdb.dbo.sysmail_add_account_sp

132
Q

When using WSRM which CPU allocation method divides CPU bandwidth evenly across matched processes?

A

Equal Per Process

133
Q

What are the three minimum CPU allocation methods when using WSRM?

A

Equal Per Process
Equal Per User
Equal Per Session

134
Q

Which stored procedure do you run to cycle the error log?

A

sp_cycle_errlog

135
Q

Which stored procedure do you use to cycle the SQL Agent error log?

A

msdb.dbo.sp_cycle_agent_errorlog

136
Q

Which T-SQL statement is used to move an index?

A

CREATE INDEX statement with DROP_EXISTING=ON option

137
Q

Which compression attribute is inherited when merging two partitions each having their own compression method?

A

The compression attribute of the destination partition.

138
Q

Which Editions of SQL Server support data compression?

A

SQL Server 2012 Enterprise and Developer editions only

139
Q

What function defines how the rows of an index or table map to specific partitions based on the values of their partitioning columns?

A

Partition Function

140
Q

What’s the command for backing up the server certificate and private key and storing them in the default folder MSSQL\DATA ?

A

BACKUP CERTIFICATE TO FILE = ‘’ WITH PRIVATE KEY (
FILE = ‘’ , ENCRYPTION BY PASSWORD = ‘’
);

141
Q

The column of an index or table that a partition function uses to partition the index or table is called?

A

Partitioning column

142
Q

Maps the partitions in a partition function to a collection of filegroups?

A

Partition scheme

143
Q

An index that uses the same partition scheme as the table where it belongs is called?

A

an Aligned Index

144
Q

An index that is partitioned away from the table which it belongs is called?

A

a Nonaligned index

145
Q

Which Database command can you use to monitor the amount of log space used?

A

DBCC SQLPERF(LOGSPACE)

146
Q

Which tool included with SQL Server 2012 installation media enables you to identify issues that will potentially block an upgrade?

A

Upgrade Advisor

147
Q

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?

A

Distributed Replay Utility

148
Q

Steps prior to upgrading to SQL Server 2012?

A
  • 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
Q

Difference between Upgrading and Migrating?

A
  • 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
Q

Which stored procedure can be used to alter the Full-Text Upgrade option?

A

sp_fulltext_service

151
Q

Which stored procedure is used to detach a database?

A

sp_detach_db

152
Q

What T-SQL statement do you use to attach a database?

A

CREATE DATABASE statement with FOR ATTACH option

153
Q

Which Catalog view contains information such as login creation date, modification date, and login type?

A

sys.server_principles

154
Q

Which catalog view provides information on SQL Server-Authenticated logins only?

A

sys.sql_logins

155
Q

Which stored procedure provides a list of fixed server roles?

A

sp_helpsrvrole

156
Q

Which stored procedure provides a list of fixed server roles and their membership?

A

sp_helpsrvrolemember

157
Q

Which stored procedure lists permissions for fixed server roles?

A

IS_SRVROLEMEMBER

158
Q

Which system view provides information about role members displayed as role and member id?

A

sys.server_role_members

159
Q

What’s the T-SQL command you can use to check for orphaned users?

A

sp_change_users_login @Action = ‘Report’;

160
Q

Which stored procedure can be used to link an orphaned user with a SQL login?

A

sp_change_users_login

161
Q

Which stored procedures are used to add and delete database users to fixed database roles?

A

sp_addrolemember - to add members

sp_droprolemember - to drop members

162
Q

What type of roles allow you to create custom database-level permissions?

A

Flexible Database-Level Roles

163
Q

Members of this role can perform any configuration or maintenance activity on the database as well as dropping the database.

164
Q

role members can modify role membership, manage role permissions, and can escalate their own privilege.

A

db_securityadmin

165
Q

Role members can add or remove database access for windows logins, Windows groups, and SQL Server logins.

A

db_accessadmin

166
Q

Role members can backup the database

A

db_backupoperator

167
Q

Role members can run any DDL command on the database

A

db_ddladmin

168
Q

Role members can add, delete, and change data in all user tables

A

db_datawriter

169
Q

Role members can read all data stored in user tables

170
Q

Role members are blocked from adding, modifying, or deleting any data stored in user tables

A

db_denydatawriter

171
Q

Role Members are blocked from reading any data stored in user tables

A

db_denydatareader

172
Q

Enables you to use roles with a custom set of permissions?

A

Flexible database roles

173
Q

Account Lockout Duration

A

The length of time an account is locked out

174
Q

Account Lockout Threshold

A

The number of failed logins that must occur to trigger a lock-out

175
Q

Reset Account Lockout Counter After

A

The duration in which the failed logins must occur to trigger a lockout

176
Q

Which system catalog view would you use to determine whether a login is disabled?

A

sys.server_principals

177
Q

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?

A

sys.asymmetric_keys

178
Q

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?

A

sys.certificates

179
Q

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.

A

sys.key_encryptions

180
Q

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?

A

sys.symmetric_keys

181
Q

What information does sys.database_mirroring_endpoints provide?

A

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
Q

What information is provided by sys.endpoints

A

Information on each endpoint created on the instance. Includes information n the endpoint protocol, payload type, and endpoint state.

183
Q

What information is provided by the sys.http_endpoints view

A

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
Q

What information does the sys.service_broker_endpoints view provide?

A

Information on service broker endpoints, including authentication configuration, encryption algorithm, and the identifier of any certificate used for authentication.

185
Q

What information does the catalog view sys.tcp_endpoints provide?

A

Information about the TCP endpoints in the system, including port number, IP address, and whether the port is dynamic.

186
Q

Which system catalog view provides information about permissions at the database level?

A

sys.database_permissions

187
Q

Which catalog view provides information about principle type, including authentication type (instance, database, or windows) and creation date?

A

sys.database_principals

188
Q

Which catalog view provides information about role membership at the database level?

A

sys.database_role_members

189
Q

Which catalog view provides information on database master key passwords, including the credentials in which the master key password is associated?

A

sys.master_key_passwords

190
Q

Which dynamic management view can you query to view information about the last successful and unsuccessful login times of SQL Logins?

A

sys.dm_exec_sessions

191
Q

Which System stored procedure can you use to retrieve or update the current status of a database mirror?

A

sp_dbmmonitorresults

192
Q

What are the steps for implementing Database Mail?

A
  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
Q

Which isolation level would you use to minimize locking contention by allowing other transactions to modify data being read by the current transaction?

A

READ UNCOMMITTED - The least restrictive isolation level

194
Q

Which isolation does not allow statements to read data that has been modified by other transactions that are uncommitted (preventing dirty reads.)

A

READ COMMITTED - Is also the default 2102 SQL Server transaction isolation level.

195
Q

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?

A

REPEATABLE READ - uses shared locks on all data read by each statement until the transaction completes.

196
Q

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.

A

SERIALIZABLE - the most restrictive isolation level

197
Q

Which fixed server role can modify endpoint settings?

A

serveradmin

198
Q

Which stored procedure would you query if looking for details on a database

199
Q

Which catalog view would you query to determine the operating mode of a mirroring session?

A

sys.database_mirroring

70-462 MCSA Certification (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions