Sql Server 2012 70-462 Summary

Front 1

All editions of SQL Server 2012 have a minimum RAM requirement of _______except the
Express editions, which have a minimum RAM requirement of _______.

Back 1

1 GB (1:1)

512 MB

Front 2

SQL Server 2012 Enterprise, Business Intelligence, and Web editions of SQL Server
2012 can be run only on _______ and _____ operating systems using SQL Server 2012 Datacenter, Enterprise, Standard, and Web editions. You can run the x86 version of these SQL Server 2012 editions on the x86 version
of ________, but not on the ____ versions.

Back 2

Windows Server 2008 R2 SP1 (1:1)

Windows Server 2008 SP2

Windows Server 2008 SP2

x64

Front 3

The x64 versions of SQL Server 2012 require an AMD Opteron, AMD Athlon 64, Intel
Xeon with Intel EM64T support, or Intel Pentium IV with EMT54 support with a minimum
processor speed of 1.4 GHz and a recommended speed of 2.0 GHz or faster. The
x86 versions of SQL Server 2012 require a Pentium III–compatible processor or newer
that has a minimum speed of _____ and a recommended speed of _____ or faster.

Back 3

1.0 GHz (1:1)
2.0 GHz

Front 4

The
x86 versions of SQL Server 2012 require a Pentium III–compatible processor or newer
that has a minimum speed of ____ and a recommended speed of _____ or faster.

Back 4

1.0 GHz (1:1)
2.0 GHz

Front 5

Scaling up involves adding better ______; scaling out involves adding more _______.

Back 5

hardware (1:1)
SQL instances

Front 6

Databases are configured to ______ by default. You can configure a database to
shrink automatically, but this is likely to lead to _______.

Back 6

autogrow (1:1)

index fragmentation

Front 7

Databases use ______ files (.mdf), _____ files (.ndf), and ________ files (.ldf).

Back 7

primary (1:1)
secondary
transaction log

Front 8

When SQL Server 2012 is deployed on Windows Server 2008 R2, it uses a _______
account, which is a locally managed account, by default. You can configure SQL Server
2012 to use an existing __________ account.

Back 8

virtual (1:1)
managed service

Front 9

SQL Server 2012 can be installed on __________ in the Server Core
configuration, although not all features are supported.

Back 9

Windows Server 2008 R2 SP1 (1:1)

Front 10

You can use _________ to benchmark the I/O subsystem prior to SQL Server 2012 deployment,
and you can use ______ to simulate read, write, checkpoint, backup, sort, and
read-ahead activities.

Back 10

SQLIO (1:1)
SQLIOSim

Front 11

You can configure instance-level settings such as fill factor, minimum memory use,
maximum memory use, processor, and I/O affinity through the ________ dialog
box.

Back 11

Server Properties

Front 12

You can use the ________ stored procedure to configure minimum server memory,
maximum server memory, fill factor, and I/O affinity.

Back 12

sp_configure

Front 13

You can configure processor affinity
by using sp_configure, although _____________ is the preferred
method.

Back 13

ALTER SERVER CONFIGURATION

Front 14

__________ ties an instance to specific processors rather than to all processors on
the host server.

Back 14

Processor affinity

Front 15

A _____________ assigns each instance a maximum amount of memory.

Back 15

maximum memory strategy

Front 16

A minimum memory strategy assigns each instance a minimum amount of memory and
allows instances to ____________

Back 16

use available extra free memory as necessary

Front 17

The _____________ database serves as a template for all new databases.

Back 17

model

Front 18

Use the ______________ statement to modify the model database.

Back 18

ALTER DATABASE

Front 19

_______ is required when transactions run across multiple servers. You should configure it as a cluster resource prior to installing the Database Engine on a cluster.

Back 19

MSDTC

Front 20

_____________ enables an instance to send email messages.

Back 20

Database Mail

Front 21

You can enable Database Mail by using the _________ stored procedure.

Back 21

sp_configure

Front 22

An Analysis Services instance in _______ and _______ mode supports OLAP databases.

Back 22

multidimensional (3:1)
data mining

Front 23

An Analysis Services instance in _____ mode supports the new tabular modeling feature.

Back 23

tabular (3:1)

Front 24

If you perform a file-only Reporting Services deployment, you must run the _______________

Back 24

Reporting Services Configuration Manager (3:1)

Front 25

__________ enables you to store BLOB objects in the file system.

Back 25

FILESTREAM (3:1)

Front 26

________ are special types of tables that enable you to store files and directories directly in the database. These files and directories can be accessed through the Windows file system.

Back 26

FileTables (3:1)

Front 27

Analysis Services and Reporting Services can be enhanced with ____________ integration

Back 27

SharePoint (3:1)

Front 28

You configure Integration Services security with the _________ Tool.

Back 28

DCOM Configuration (3:1)

Front 29

You can upgrade from ______, ______, and ________ if the appropriate service packs have been applied, you are upgrading within the same processor architecture, and you are attempting a supported edition upgrade path.

Back 29

SQL Server 2005 (4:1)
SQL Server 2008
SQL Server 2008 R2

Front 30

You can migrate a database from one instance to another instance by using the _______ method.

Back 30

detach and attach (4:1)

Front 31

You can’t _________ a database that is being mirrored or replicated or that has a snapshot.

Back 31

detach (4:1)

Front 32

You can copy a database to another instance by using the ________ Wizard or by ___________

Back 32

Copy Database (4:1)

backing up and then restoring the database.

Front 33

When copying a database, the advantage of using the ______________ is that it also enables you to migrate database metadata, such as logins, to the new instance.

Back 33

Copy Database Wizard (4:1)

Front 34

You can migrate SQL logins by using the ___________ function in SQL Server Management Studio.

Back 34

Generate A Script (4:1)

Front 35

SQL Server logins allow access at the ______ level. You can create SQL Server logins that are mapped to local or domain-based ____ accounts or to to local or domainbased ______ groups. You can also create SQL Server logins that are authenticated by SQL Server or that use _______ or ______ keys for authentication.

Back 35

instance (5:1)
user
security
certificate
asymmetric

Front 36

______ fixed server roles are associated with a SQL Server 2012 Database Engine instance.

Back 36

Nine (5:1)

Front 37

You cannot alter the permissions assigned to eight of the ______ roles, but you can modify the membership of these roles.

Back 37

fixed server (5:1)

Front 38

You can alter the ______ assigned to the public fixed server role, but you cannot modify the ______ of the public fixed server role.

Back 38

permissions (5:1)
membership

Front 39

A securable is an item for which you can assign ________. Securables can be contained within other _______.

Back 39

permissions (6:1)
securables

Front 40

A scope is a _______. Permissions assigned at the scope level are inherited by objects within that _______

Back 40

container (6:1)
scope

Front 41

The ________, _____, and ______ statements are used to manage permissions on objects.

Back 41

GRANT (6:1)
DENY
REVOKE

Front 42

Roles enable you to simplify the management of ________. Assign ______ to roles and then add principals to roles.

Back 42

permissions (6:1)
permissions

Front 43

Fixed ________ roles have fixed ______.

Back 43

database (6:1)
permissions

Front 44

Flexible _________ roles enable you to assign custom ________.

Back 44

database (6:1)
permissions

Front 45

Schemas enable you to collect objects to simplify the process of ____________.

Back 45

assigning permissions (6:1)

Front 46

A database must use the ______ model before it can be mirrored.

Back 46

full recovery (7:1)

Front 47

Only one ________ and one ______ instance can participate in a session

Back 47

principal (7:1)
mirror

Front 48

High-safety mode, also known as synchronous mode, commits transactions when they are ________ on both partners.

Back 48

synchronized (7:1)

Front 49

_________ mode has higher transaction latency.

Back 49

Synchronous (high-safety) (7:1)

Front 50

High-performance mode, also known as asynchronous mode, has lower transaction latency because transactions are committed before partner _________.

Back 50

synchronization (7:1)

Front 51

____________ mode can lead to data loss if the primary fails.

Back 51

Asynchronous (high-performance) (7:1)

Front 52

The ________, _____, and _______ instance must be running the same version of SQL Server when configuring a new mirroring session.

Back 52

principal (7:1)
mirror
witness

Front 53

If the SQL Server service is not associated with a domain-based account but a local account or virtual account, you must configure ______ authentication.

Back 53

certificate-based (7:1)

Front 54

Automatic failover is possible only if the mirroring session is in ______ mode with a ______.

Back 54

high-safety (7:1)
witness

Front 55

________ replication enables complete refreshes of all data rather than refreshing on an incremental basis.

Back 55

Snapshot (7:2)

Front 56

_________ replication is suitable when data changes infrequently and small volumes must be replicated.

Back 56

Snapshot (7:2)

Front 57

___________ replication is suitable when a subscriber database must be up to date with the publisher but does not need to update the publisher.

Back 57

Transactional (7:2)

Front 58

______ replication is suitable for distributed server applications when conflict is possible.

Back 58

Merge (7:2)

Front 59

_________ replication is a form of transactional replication by which nodes can read and write changes, but you must partition data to avoid conflicts.

Back 59

Peer-to-peer (7:2)

Front 60

You cannot use filtering with ___________ replication.

Back 60

peer-to-peer (7:2)

Front 61

You must use a ______ to publish snapshots if distribution and merge agents are running at the subscriber instance rather than at the publisher instance.

Back 61

shared folder (7:2)

Front 62

______________ enables you to monitor replication and configure replication alerts.

Back 62

Replication Monitor (7:2)

Front 63

SQL Server 2012 can subscribe to an Oracle publication and can function as a publisher to Oracle and IBM DB2 _______ and ______ replication.

Back 63

transactional (7:2)
snapshot

Front 64

SQL Server 2012 supports up to ____ instances on a single host and up to _____ instances on a failover cluster.

Back 64

50 (2:2)
25

Front 65

You must update ____ features associated with a SQL Server 2012 instance at the same time.

Back 65

all (2:2)

Front 66

_______ features must be updated to the most recent update.

Back 66

Shared (2:2)

Front 67

Run a software update with the _______ parameter to update a specific instance; run the update with the ________ parameter to update all instances.

Back 67

/InstanceName (2:2)

/AllInstances (2:2)

Front 68

_________ enables you to manage processing and memory resources within a Database Engine instance.

Back 68

Resource Governor (2:2)

Front 69

You cannot use _________ to manage Analysis Services, Integration Services, or Reporting Services.

Back 69

Resource Governor (2:2)

Front 70

_______ host workload groups. The _______ assigns particular sessions to workload groups.

Back 70

Resource pools (2:2)
categorizer

Front 71

You assign resources to a resource pool by specifying either a minimum or maximum value for both the ______ and ______ resources.

Back 71

processor (2:2)
memory

Front 72

_______________ enables you to manage and allocate processor and memory resources across instances.

Back 72

Windows System Resource Monitor (2:2)

Front 73

You can use the _________ stored procedure to cycle the SQL Server error log.

Back 73

sp_cycle_errorlog (2:2)

Front 74

You can use ____________ from the ______ system database to cycle the SQL Server Agent error log.

Back 74

sp_cycle_agent_errorlog (2:2)

msdb

Front 75

______________ are collections of database files that enable you to implement partitioning of tables and indexes.

Back 75

Filegroups (3:2)

Front 76

Configure the ____________ system database as a template when standardizing databases on an instance.

Back 76

model (3:2)

Front 77

______ databases are databases that have no dependencies on the Database Engine. This makes it easy to move databases between instances and to cloud-based deployments such as SQL Azure.

Back 77

Contained (3:2)

Front 78

______ compression modifies data types to reduce the amount of storage space used. ______compression uses dictionary compression techniques and provides greater space savings, but at the cost of CPU usage.

Back 78

Row-level (3:2)
Page-level

Front 79

_______ enables you to encrypt an entire database

Back 79

Transparent Data Encryption (TDE) (3:2)

Front 80

When using TDE, the database will remain encrypted even when ______.

Back 80

when backed up (3:2)

Front 81

Transaction log truncation depends on the configured ______ model.

Back 81

recovery (3:2)

Front 82

You can force a checkpoint by using the _____________ statement.

Back 82

CHECKPOINT (3:2)

Front 83

The volume that hosts the operating system needs a minimum of _____________ of available space to host the temporary SQL Server 2012 installation files.

Back 83

4 GB (1:2)

Front 84

If you do not enter a product key, SQL Server 2012 will be installed as an ______ edition.

Back 84

evaluation (1:2)

Front 85

You can install up to ______ instances of the Database Engine on a single server

Back 85

50 (1:2)

Front 86

You can install only one instance of _____ on a server.

Back 86

SSIS (1:2)

Front 87

You can add features to an existing installation by using ________, by using ______ from the command line, or by invoking SQL Server Setup through ________.

Back 87

Windows Explorer (1:2)
Setup.exe
SQL Server Installation Center

Front 88

You can remove features from an existing installation by using ______ from the command line or by using ________ in Control Panel.

Back 88

Setup.exe (1:2)

Programs And Features

Front 89

You must create ______ rules to enable remote connections to SQL Server 2012 features.

Back 89

firewall (1:2)

Front 90

You can verify connectivity to a remote instance of SQL Server 2012 features by using _____ or _______

Back 90

SQL Server Management Studio (1:2)

SQLCMD

Front 91

The ___ utility is a command-line utility you can use to export data from a database or to perform a bulk import of data into a preexisting table.

Back 91

bcp (4:2)

Front 92

The _________ Transact-SQL statement enables you to import data from a file into a preexisting table.

Back 92

BULK INSERT (4:2)

Front 93

You can use the _______ function to import data from an OLE DB data source.

Back 93

OPENROWSET(BULK) (4:2)

Front 94

You can use the _________ Wizard to migrate data to and from OLE DB providers.

Back 94

Integrations Services Import and Export (4:2)

Front 95

The _______________ wizard enables you to save an import or an export task as an Integration Services package, which you can execute later according to a schedule.

Back 95

Integrations Services Import and Export (4:2)

Front 96

The ______ statement enables you to create a new table on a database hosted on the current instance based on the results of a SELECT query.

Back 96

SELECT INTO (4:2)

Front 97

You use a format file when you are performing a bulk import of data from a file that uses a ____________

Back 97

fixed-length or fixed-width field. (4:2)

Front 98

In ________databases, database user accounts map to existing SQL logins.

Back 98

uncontained (5:2)

Front 99

In ___________ databases, database user accounts do not need to map to existing SQL logins.

Back 99

contained (5:2)

Front 100

______ users are users in uncontained databases in which there is no corresponding SQL Server login

Back 100

Orphaned (5:2)

Front 101

There are ______ fixed database-level roles. You cannot alter the _____ assigned to these roles.

Back 101

nine (5:2)

permissions

Front 102

You can create ______ database-level roles and assign custom permissions to these roles so you can be more specific with the assignment of permissions rather than using the more general fixed database-level roles.

Back 102

flexible (5:2)

Front 103

An _______ role is a special role used by an application to access a database.

Back 103

application (5:2)

Front 104

_______ roles are secured by passwords.

Back 104

Application (5:2)

Front 105

_________ items determine account lockout settings.

Back 105

Group Policy (6:2)

Front 106

The ______________ statement can unlock locked SQL Server–authenticated logins and force password changes.

Back 106

ALTER LOGIN Transact-SQL (6:2)

Front 107

Windows authentication mode ______ SQL Server authentication mode.

Back 107

disables (6:2)

Front 108

________________ mode uses both Windows authentication and SQL Server authentication.

Back 108

Mixed authentication (6:2)

Front 109

Verify the _____ of certificates when troubleshooting certificate-based security.

Back 109

expiry dates (6:2)

Front 110

Use the ________ catalog view to view certificate properties.

Back 110

sys.certificates (6:2)

Front 111

Use the ________ catalog view to view endpoint information

Back 111

sys.endpoints (6:2)

Front 112

Use the _________ catalog view to determine whether a principal is disabled

Back 112

sys.server_principals (6:2)

Front 113

sys view that provides information about permissions at the database level

sys view that provides information about principal type, including authentication type (instance, database, or Windows) and creation date


Back 113

sys.database_permissions (6:2)

sys.database_principals (6:2)

Front 114

sys view that provides information about role membership at the database

sys view that provides information on database master key passwords, including the credential with which the master key password is associated

Back 114

sys.database_role_members (6:2)

sys.master_key_passwords (6:2)

Front 115

An ______ database can serve as a publisher for data replicated to databases hosted on
SQL Server 2012.

Back 115

Oracle (7:2)

Front 116

SQL Server 2012 can serve as a publisher or distributor for data replicated to _____ and _____ databases.

Back 116

Oracle (7:2)
IBM DB2

Front 117

A Windows Server Failover Cluster must be created ______ to installing a failover cluster instance.

Back 117

prior (8:1)

Front 118

_________________ editions and ____________ editions can function as host operating systems for failover
cluster instances.

Back 118

Windows Server 2008 Enterprise and Datacenter (8:1)

Windows Server 2008 R2 Enterprise and Datacenter

Front 119

To install a failover cluster instance, first run ________ on all nodes and then run _____________ on the node that has control of the shared storage device.

Back 119

advanced cluster preparation (8:1)

advanced cluster completion

Front 120

__________ clusters have nodes on separate TCP/IP subnets.

Back 120

Multi-subnet failover (8:1)

Front 121

Use the _______________ console or the _____________ cmdlet to perform manual failover.

Back 121

Failover Cluster Manager (8:1)

Move-ClusterGroup PowerShell

Front 122

In the event of hardware failure, evict the ____ node from the cluster and then join it after it is _________ before reinstalling SQL Server.

Back 122

failed (8:1)

repaired

Front 123

The _____ feature is an alternative to database mirroring.

Back 123

AlwaysOn Availability Groups (8:2)

Front 124

Availability groups are supported in production on _____________ only.

Back 124

SQL Server 2012 Enterprise edition (8:2)

Front 125

An AlwaysOn availability group can have _____ primary and ________ secondary replicas.

Back 125

one (8:2)

four (8:2)

Front 126

You must create ________ either before or during the availability group creation process.

Back 126

mirroring endpoints (8:2)

Front 127

An availability group ______ can contain multiple databases

Back 127

replica (8:2)

Front 128

You can configure secondary replicas to be available to _______ queries

Back 128

read-only (8:2)

Front 129

Failover occurs on a ______ basis

Back 129

per-replica (8:2)

Front 130

________ mode involves higher transaction latency but allows manual and automatic failover.

Back 130

Synchronous-commit (8:2)

Front 131

______________ mode minimizes transaction latency, is suitable for geographically dispersed clusters, but only supports forced failover.

Back 131

Asynchronous-commit (8:2)

Front 132

You can perform availability group failover by using ______________, the ____________Transact-SQL statement, or the ______________ PowerShell cmdlet.

Back 132

SQL Server Management Studio (8:2)

ALTER AVAILABILITY GROUP

Switch-SqlAvailabilityGroup

Front 133

You can have only one listener per ___________ group.

Back 133

availability (8:2)

Front 134

_____ Monitor captures numeric statistics about hardware and software components.

Back 134

Performance (9:1)

Front 135

Counters are organized into a three-level hierarchy: ______, _______, and _________.

Back 135

counter object (9:1)

counter

counter instance

Front 136

A counter object must have at least one __________.

Back 136

counter (9:1)

Front 137

A ____________ can have zero or more instances.

Back 137

counter (9:1)

Front 138

You capture _________ with Performance Monitor to perform analysis.

Back 138

counter logs (9:1)

Front 139

Use Performance Monitor to capture metrics for ________ Server and ________ Server

Back 139

Windows (9:1)

SQL

Front 140

_________is the utility that enables you to interact graphically with the SQL Trace application
programming interface (API).

Back 140

Profiler (9:2)

Front 141

SQL Trace exposes events that can be captured to ____ actions, _____ an instance, examine _______ queries, and troubleshoot ________ issues.

Back 141

audit (9:2)
monitor
baseline
performance

Front 142

You can specify the ____________ of data you want to capture for a given event.

Back 142

columns (9:2)

Front 143

Trace output can be limited by applying ____________

Back 143

filters (9:2)

Front 144

Use ________ for large and long-running traces.

Back 144

SQL Trace (9:2)

Front 145

Use _____________ to define traces and then script them for __________.

Back 145

SQL Server Profiler (9:2)

SQL Trace

Front 146

_______ must be defined by using a series of system stored procedure calls, whereas _______ provides a graphical interface for configuring the tracing activity.

Back 146

SQL Trace (9:2)

SQL Server Profiler

Front 147

Use _______ management objects for real-time monitoring and troubleshooting.

Back 147

dynamic (9:3)

Front 148

Use _________ for easy access to performance information.

Back 148

Activity Monitor (9:3)

Front 149

The ______________ DMVs provide general space and index usage information.

Back 149

sys.dm_db_* (9:3)

Front 150

The _______________ DMVs return information about currently executing queries and queries that are still in the query cache.

Back 150

sys.dm_exec_* (9:3)