Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL Server > 70-762 > Flashcards

70-762 Flashcards

1
Q

What is the minimum recommended amount of RAM for SQL Server 2012 Enterprise?

<span>A. 512 MB </span>

<span>B. 1 GB </span>

<span>C. 2 GB </span>

<span>D. 4 GB </span>

<span>E. 8 GB</span>

A

B. 1 GB

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

Which of the following editions of SQL Server 2012 can you run on a computer that is running the Windows 7 Professional (x64) operating system? (Choose all that apply.)

A. SQL Server 2012 (x64) Developer edition

B. SQL Server 2012 (x64) Web edition

C. SQL Server 2012 (x64) Enterprise edition

D. SQL Server 2012 (x64) Standard edition

A

A. SQL Server 2012 (x64) Developer edition

D. SQL Server 2012 (x64) Standard edition

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

Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)

A. Database Engine Services

B. SQL Server Replication

C. Analysis Services

D. Reporting Services

A

A. B. C.

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

On which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition? (Choose all that apply.)

<span>A. Windows 7 Ultimate (x64) edition </span>

<span>B. Windows Server 2008 R2 SP1 (x64) Standard edition </span>

<span>C. Windows Server 2008 SP2 (x64) Enterprise edition </span>

<span>D. Windows Server 2003 R2 (x64) Enterprise edition</span>

A

B. Windows Server 2008 R2 SP1 (x64) Standard edition

C. Windows Server 2008 SP2 (x64) Enterprise edition

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

You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization’s SQL Server 2012 deployment. Which of the following tools would you use to accomplish this goal?

A. SQLIO

B. SQLIOSim

C. SQLIOStress

D. chkdsk

A

B. SQLIOSim

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

What is the default collation on computers with the United States regional setting?

A

SQL_Latin1_General_CP1_CI_AS

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

During installation, on the Error Reporting page, you can choose whether to send ____ and ____ to both Microsoft and your organization’s corporate reporting server.

A

Windows and SQL Server error reports

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

A text file, named _____, is saved. You can use this file to replicate the chosen configuration when installing other Database Engine instances

A

ConfigurationFile.ini

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

You use SQL Server setup to install ________ as part of the normal installation process, but you can install only one instance of ___ on a computer.

A

SQL Server Integration Services (SSIS)

SSIS

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

You can determine which features are installed on SQL Server 2012 by running the _______ report. You can access this report through the Tools Menu of the SQL Server Installation Center.

A

Installed SQL Features Discovery Report

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

Installing SQL Server 2012 does not automatically configure ___ for the appropriate services installed; you must configure ____ manually on each ___ or configure them through the ______.

A

firewall rules

firewall rules

server

application group policy

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

Default Instance port number

A

TCP 1433

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

Dedicated Admin Connection port number

A

TCP 1434

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

You are planning on deploying a server that will be dedicated for ETL processes. You want to ensure that SSIS packages will run on this dedicated ETL server and not on any other server on which they were started. Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?
A. Database Engine
B. SQL Server Reporting Services
C. SQL Server Analysis Services
D. Client Tools SDK

A

A. Database Engine

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

You have installed the SQL Server Integration Services (SSIS) feature on a server running Windows Server 2008 R2 SP1. Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?
A. Client Tools SDK
B. Data Quality Client
C. SQL Server Data Tools
D. Client Tools Backwards Compatibility

A

C. SQL Server Data Tools

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

You want to allow access to SQL Server Analysis Services on a server running Windows Server 2008 R2 for a client running SQL Server Management Studio on Windows 7. You have installed SQL Server Analysis Services in the default location. Which of the following paths should you use when creating the firewall rule by using Windows Firewall With Advanced Security?
A. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
B. C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin
\msmdsrv.exe
C. C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe
D. C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services
\ReportServer\bin\ReportingServicesService.exe

A

B. C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin<br></br>\msmdsrv.exe

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

You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed. Which of the following tools can you use to accomplish this goal?
A. SQL Server Management Studio
B. SQL Server Configuration Manager
C. Add/Remove Programs in Control Panel
D. SQL Server Installation Center

A

C. Add/Remove Programs in Control Panel

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

You want to reproduce the same SQL Server 2012 installation configuration across five servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal?
A. Configuration.xml
B. Setup.ini
C. Setup.xml
D. ConfigurationFile.ini

A

D. Correct: SQL Server Setup generates a file named ConfigurationFile.ini to store installation settings

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

Memory

You can use the ____ and _____ instance-level settings to configure the amount of memory (in megabytes) an instance of SQL Server 2012 uses. A setting of ______ means that minimum server value is not set.

A

Min Server Memory

  • default is 0

Max Server Memory

  • lowest maximum value - 64MB for 32-bit OS & 128MB for 64-bit OS

​Zero

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

An instance does not use _____ at startup, but when client load reaches this amount, SQL Server 2012 will not release memory back to the operating system if it means going ___ this value. If an instance never uses the ______ specified, that amount will not be reserved by the instance, and memory will be released back to the operating system.

A

minimum reserved memory

below

minimum amount

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

What is processor Affinity?

A

Processor affinity assigns specific server processors to specific threads. This eliminates processor reloads and reduces thread migration across processors.

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

What is IO Affinity?

A

I/O affinity binds an instance’s disk I/O to a specific set of CPUs.

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

What is fill factor?

A

“Fillfactor” is a setting for indexes in SQL Server.

When you create or rebuild an index, you can tell SQL Server what percentage of each 8K data page used in the “leaf” level of the index it should fill up, therefore reserving a percentage of free space for future growth.

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

Which system database must you modify if you want all future databases to
use the Full recovery model?

A

You must modify the model database because all new databases created on an instance inherit settings from the model database

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

Use sp_configure to…

A
  • Enable Database email
  • Instance wide fill factor
  • Min/Max server memory
  • I/O affinity
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3. You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?

A. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO<br></br>B. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3<br></br>C. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1<br></br>D. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4

A

<span>A. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO</span>

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

You have run the following command on a SQL Server 2012 default instance:

EXEC sys.sp_configure ‘show advanced options’, 1;<br></br>GO<br></br>Which of the following commands must you run to configure the instance so that themaximum amount of memory the instance uses does not exceed 4,096 MB? (Eachanswer presents part of a complete solution. Choose two.)

A. RECONFIGURE;<br></br>GO<br></br>B. EXEC sys.sp_configure ‘min server memory’, 1024;<br></br>GO<br></br>C. EXEC sys.sp_configure ‘min server memory’, 4096;<br></br>GO<br></br>D. EXEC sys.sp_configure ‘max server memory’, 4096;<br></br>GO

A

Answer: D, A

D. EXEC sys.sp_configure ‘max server memory’, 4096;<br></br>GO

A. RECONFIGURE;<br></br>GO

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

You want to ensure that disk input/output operations of an instance are bound to aspecific processor. Which of the following would you configure to accomplish this goal?<br></br>A. Minimum server memory<br></br>B. Maximum server memory<br></br>C. Processor affinity<br></br>D. I/O affinity

A

D. I/O affinity

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

You want to ensure that all future databases created on a SQL 2012 instance areconfigured to Auto Shrink. Which of the following system databases do you modify toaccomplish this goal?<br></br>A. master<br></br>B. model<br></br>C. msdb<br></br>D. tempdb

A

B. model database

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

Which stored procedure do you use to enable Database Mail on an instance?<br></br>A. sp_configure<br></br>B. sp_rename<br></br>C. sp_monitor<br></br>D. sp_depends

A

A. sp_configure

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

How many instances of SQL Server 2012 can you install on a failover cluster?

A

25 instances

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

If you have a deployment with two instances on the same host and you want to update only one of them, you must ensure that the update applies to the ________.

A

shared features

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

_________ is a SQL Server 2012 feature that enables you to place limits on the consumption of CPU and memory resources within a SQL Server 2012 Database Engine instance.

A

Resource Governor

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

Resource Governor

    1. ______ pool
      * ________
      * ________
    1. _____ group
      * ________
    • ________
A
  1. Resource Pools
  • internal pool
  • default pool

2.Workload group

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

The session runs by using resources assigned to the ____ group through the ______ pool

A

workload

resource

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

Work groups function as containers for _____ that share similar classification criteria.

A

session reuqests

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

Cycle SQL server Error log

  • default ______
  • use sp____ to force the error log to cycle wo restarting the database engine
A
  • retain the current error log and the five most recent error logs
  • sp_cycle_errorlog
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

You want to cycle the SQL Server Agent error log. From which of the following system databases must you run the sp_cycle_agent_errorlog stored procedure to accomplish this goal?

A. master
B. msdb
C. model
D. tempdb

A

B. correct: You must run the sp_cycle_agent_errorlog stored procedure from the msdb system database to rotate the SQL Server Agent error log file

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

Which feature should you enable and configure so session requests addressed toa specific instance can be allocated different processor resources based on sessionrequest properties?<br></br>A. Resource Governor<br></br>B. Windows System Resource Manager<br></br>C. Processor affinity<br></br>D. I/O affinity

A

A. <strong>correct</strong>: Resource Governor enables you to allocate session requests to differentresources based on the characteristics of the session request properties

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

What is the maximum number of SQL Server 2012 Enterprise edition Database Engineinstances that you can deploy on a non-clustered server running Windows Server 2008R2 Enterprise edition?<br></br>A. 10<br></br>B. 25<br></br>C. 50<br></br>D. 100

A

c. 50 instances

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

You have configured Resource Governor with three resource pools. You have assignedthe first resource pool a minimum CPU and memory value of 20%. You have assignedthe second resource pool a minimum CPU and memory value of 30%. You want toassign maximum CPU and memory values to the third resource pool. What is the maximum CPU and memory value you can assign to this resource pool?<br></br>A. 30%<br></br>B. 50%<br></br>C. 70%<br></br>D. 100%

A

B. 50%

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

A server that has four processors has three SQL Server 2012 Database Engine instancesinstalled. Which feature should you configure to assign 60% of a host server’s processor resource to the first instance, 20% to the second instance, and 15% to the thirdinstance?<br></br>A. Resource Governor<br></br>B. Windows System Resource Manager<br></br>C. Processor affinity<br></br>D. I/O affinity

A

B. Windwos System Rezource Manager

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

You can install Analysis Services in these 3 modes.

A
  • Multidimensional & data mining
  • Tabular
  • Powerpivot
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

SSRS installation

You can choose the “Install And Configure” option only if you have installed the _____ prior to attempting to install the ________ and you are also installing the ________ on the same instance

A

web server role

Report Server instance

Database Engine

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

In SQL 2012, SSIS is _____ by default. Grant users access using ______.

A

secure

Dcomcnfg.exe

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

Use FILESTREAM under the following conditions

A
  • Objects that you want to store are greater than 1 MB.
  • Fast read access is important
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Filegroup

  • Primary filegroup
  • Secondary filegroup
A

Primary filegroup

  • host system tables
  • mdf

Secondary filegroup

  • can be hosted on different volume
  • ndf
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

You can standardize the configuration of databases by configuring appropriate settings such as Auto Close, Auto Shrink, and database recovery model on the ___________.

A

model system database

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

Contained Databases

A
  • no configuration dependencies
  • can easliy be moved to other instance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Data compression

A
  • save storage space
  • use on tables and indexes
  • but increase CPU usage due to compress and decompress
  • cannot use on system tables
  • only for enterprise and developer edition
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

Forms of data compression

A
  • row-level compression
    • more efficient storage format for fixed-length numeric & CHAR
  • unicode compression
    • compress fixed-length NCHAR(n) and NVARCHAR(n)
  • page-level compression
    • store repeat values once and make only reference from other locations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

If you split partition, the new partitions ____ data compression attribute of the ____ partition.

If you merge partition, the resulting partition ____ data compression attribute of the ____ partition.

A

inherit

original

has

destination

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

After the appropriate keys and certificates are created, which Transact-SQL statement do you use to encrypt a database?

A

Use ALTER DATABASE with the SET ENCRYPTION ON option

54
Q

The data of partitioned tables and indexes is divided into units that can be spread across more than one _____ in a database.

In which edition of SQL server?

A

filegroup

Only in Enterprise and Developer edition.

55
Q

Partition function

A database object that defines how the 1 or 2 are mapped to a set of partitions based on the values of certain column, called 3.

A
  1. rows of a table
  2. index
  3. a partitioning column.

The partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined

56
Q

What does Partition Schema do?

A

Placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions

57
Q

Log truncation occurs

A
  1. simple recovery model - after a checkpoint
  2. full recovery or bulk-logged recovery model - after a backup
58
Q

Command to monitor the amount of log space used

A

DBCC SQLPERF (LOGSPACE)

DBCC = database console commands

59
Q

DBCC CHECKDB

A

Checks the physical and logical integrity of all objects in a specific database.

It runs …

DBCC CHECKALLOC

DBCC CHECKTABLE

DBCC CHECKCATALOG

60
Q

Which statement would you use to add a filegroup to an existing database?

A. ALTER DATABASE<br></br>B. CREATE DATABASE<br></br>C. ALTER TABLE<br></br>D. CREATE TABLE

A

A. ALTER DATABASE

61
Q

The STUDENTS table contains columns include Student_Name, DOB, Telephone, Email, Street_Address, Town, State, and Zip Code. IDX1 is an index on the Student_Name column. 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=ROW)
B. ALTER TABLE STUDENTS REBUILD WITH (DATA_COMPRESSION=PAGE)
C. ALTER INDEX IDX1 ON STUDENTS REBUILD PARTITION ALL WITH
(DATA_COMPRESSION=ROW)
D. ALTER INDEX IDX1 ON STUDENTS REBUILD PARTITION ALL WITH
(DATA_COMPRESSION=PAGE)

A

B. ALTER TABLE STUDENTS REBUILD WITH (DATA_COMPRESSION=PAGE)

62
Q

Which of the following must you do before enabling Transparent Data Encryption for a database? (Choose three.)
A. Create a master encryption key.
B. Create a certificate.
C. Create a database encryption key.
D. Enable page-level compression

A

A. Create a master encryption key.
B. Create a certificate.
C. Create a database encryption key.

63
Q

Which command would you run if you wanted to check the physical and logical integrity of all objects within a specific database?
A. DBCC CHECKFILEGROUP
B. DBCC CHECKDB
C. DBCC SQLPERF
D. DBCC SHRINKDATABASE

A

B. DBCC CHECKDB

64
Q

Supported OS

Ensure that the host operating system of the instance you want to upgrade is running _____ or later because ____ and ____ are not supported platforms for SQL Server 2012

A

Windows Server 2008

Windows Server 2003

Windows Server 2003 R2

65
Q

You cannot perform cross-architecture _____, instead you’ll have to _____.

A

upgrade

migrate

66
Q

Checklist before detach a database

A
  1. Stop replication and publish
  2. Stop mirroring
  3. Drop all snapshots
  4. not ystem databases
67
Q

Two methods for Copy database wizard

A
  • detach and attach method - faster but source is taken offline
  • SQL management object method - slower but source remain online
68
Q

Which of the following can you upgrade to SQL Server 2012 Standard edition w/o having to apply additional service packs? (Choose all that apply.)
A. SQL Server 2005 SP4 Standard edition installed on Windows Server 2008 with Service Pack 2
B. SQL Server 2008 SP2 Standard edition installed on Windows Server 2003 R2
C. SQL Server 2008 SP2 Enterprise edition installed on Windows Server 2008 R2 SP1
D. SQL Server 2008 R2 SP1 Standard edition installed on Windows Server 2008 R2 SP1

A

A. SQL Server 2005 SP4 Standard edition installed on Windows Server 2008 with Service Pack 2
D. SQL Server 2008 R2 SP1 Standard edition installed on Windows Server 2008 R2 SP1

69
Q

Which of the following tools can you use to migrate Windows-authenticated SQL logins from an x86 instance of SQL Server 2012 to an x64 instance of SQL Server 2012 if contained databases are not in use? (Choose all that apply.)
A. Copy Database Wizard
B. Import and Export Wizard
C. Backup and Restore Database
D. Generate A Script

A

A. Copy Database Wizard
D. Generate A Script

70
Q

You want to migrate a database, including logins and user-defined error messages, from one SQL Server 2012 instance to another. Which of the following tools can you use to accomplish this goal?
A. Copy Database Wizard
B. BACKUP DATABASE Transact-SQL statement
C. Import and Export Wizard
D. sp_detach_db stored procedure

A

A. Copy Database Wizard

71
Q

You want to use the detach and attach method of migrating a database to another SQL Server 2012 Database Engine instance. The database is currently published and replicated. Which of the following steps must you take prior to detaching the database from the source instance? (Choose all that apply.)
A. Drop database snapshots.
B. Unpublish the database.
C. Create a format file.
D. Create a database snapsho

A

A. Drop database snapshots.
B. Unpublish the database.

72
Q

Name 2 Migrate methods

A

Detach / Attach

Copy database

73
Q

5 tools to copy and export data

A
  • bcp command-line utility for data from files
  • BULK INSERT statement for file stored on the file system
  • OPENROWSET(BULK) for OLE DB data source
  • SELECT INTO statement
  • SSIS
74
Q

Which statement or function would you use if you wanted to import data into a table from a remote data file through an OLE DB provider ?

A

You can use the OPENROWSET(BULK) function to import data into a table by connecting to a remote data source through an OLE DB provider

75
Q

You must create an XML format file based on a database table to use later when bulk importing data on other instances. Which of the following tools would you use to accomplish this goal?
A. bcp utility
B. Import and Export Wizard
C. BULK INSERT statement
D. OPENROWSET function

A

A. Correct:You use the bcp utility to create XML format files. These files are used when importing files that use fixed-width fields

76
Q

Which of the following tools can you use to create an Integration Services package that enables you to repeat a specific data import or export operation?
A. OPENROWSET function
B. BULK INSERT statement
C. Import and Export Wizard
D. bcp utility

A

C. Import and Export Wizard

77
Q

You must copy the complete contents of a table in the Hovercraft database into a new table in the Watercraft database. The destination table does not currently exist in the Watercraft database. The Watercraft and Hovercraft databases are hosted on the same SQL Server 2012 instance. Which of the following tools can you use to accomplish this goal? (Choose all that apply.)
A. bcp utility
B. SELECT…INTO Transact-SQL statement.
C. Import and Export Wizard
D. Copy Database Wizard

A

B. SELECT…INTO Transact-SQL statement.
C. Import and Export Wizard

78
Q

You must connect to a remote OLE DB data source and import a substantial amount of data into a table hosted on a local database. Which of the following tools can you use to accomplish this goal? (Choose all that apply.)
A. Import and Export Wizard
B. Copy Database Wizard
C. bcp utility
D. OPENROWSET(BULK) function

A

A. Import and Export Wizard
D. OPENROWSET(BULK) function

79
Q

You have a table that contains 20,000 customer records. Which of the following tools can you use to export these records to a file? (Choose all that apply.)
A. bcp utility
B. BULK INSERT Transact-SQL statement
C. Import and Export Wizard
D. Copy Database Wizard

A

A. bcp utility
C. Import and Export Wizard

80
Q

Which security principals can you use with a SQL Server login that uses Windows authentication?

A

You can use

a local user account, a local security group,

a domain user account, or a domain security group

as the basis for a Windows-authenticated SQL Server login.

81
Q

You cannot drop a login that owns a ____, a ____, or a ____.

A

a SQL Server Agent job,

a server-level object,

or a securable.

82
Q

Server role simplify the assignment of permissions at instance level. ____ are assigned to server roles rather than to ______. To grant a set of permissions to a specific SQL login, add ________.

A

Permissions

individual SQL logins

the login to a server role

83
Q

Credentials store the _______ that facilitates _____ external to the Database Engine instance.

A

authentication information

a connection to a resource

84
Q

Which permission must a security principal have at the instance level to be able to create SQL logins?
A. ALTER ANY LOGIN
B. ALTER ANY CREDENTIAL
C. ALTER ANY ENDPOINT
D. ALTER SETTINGS

A

A. ALTER ANY LOGIN

85
Q

You are managing a SQL Server 2012 instance installed on a server named SQL-SYD. You want to create a SQL Server login that is mapped to the Research security group in the CONTOSO domain. Which of the following statements would you use to accomplish this goal?
A. CREATE LOGIN [Research] WITH PASSWORD = ‘Pa$$w0rd’
B. CREATE USER [Research] WITH PASSWORD = ‘Pa$$word’
C. CREATE LOGIN “CONTOSO\Research” FROM WINDOWS
D. CREATE USER “CONTOSO\Research”

A

C. CREATE LOGIN “CONTOSO\Research” FROM WINDOWS

86
Q

You want to create an instance-level security principal named Development on server SQL-A. This security principal should use the password Pa$$w0rd and should be authenticated by the SQL Server 2012 instance. Which of the following Transact-SQL statements would you use to accomplish this goal?
A. CREATE LOGIN “SQL-A\Development” FROM WINDOWS
B. CREATE USER “SQL-A\Development”
C. CREATE LOGIN [Development] WITH PASSWORD = ‘Pa$$w0rd’
D. CREATE USER [Development] WITH PASSWORD = ‘Pa$$word’

A

C. CREATE LOGIN [Development] WITH PASSWORD = ‘Pa$$w0rd’

87
Q

Following the principle of least privilege, to which of the following fixed server-level roles would you add a SQL Server login if you wanted the user to be able to drop and restore databases?
A. sysadmin
B. securityadmin
C. setupadmin
D. dbcreator

A

D. dbcreator

88
Q

You want to configure SQL Server so that the Database Engine instance can automatically authenticate against a remote FTP server. Which of the following should you create to accomplish this goal?
A. Database User
B. SQL Login
C. Credential
D. User-defined server role

A

C. Credential

89
Q

SQL server 2012 support 4 types of logins, they are …

A
  • Windows-authenticated login
  • SQL-authenticated login
  • Certificate
  • Asymmetric key
90
Q

Server Roles

_____ are assigned to server roles rather to ______.

A

Permissions

individual SQL logins

91
Q

To grant a set of permissions to a specific SQL login, add ____ to _______.

A

the login

a server role

92
Q

All SQL Server logins are members of this role. You can alter the permissions assigned to this role, but you cannot alter the membership of the role.This is the only server role you can alter permissions.

A

public

93
Q

What is the maximum number of database users that can exist in a database for a specific SQL Server login?

A

You can have only one database user mapped to a SQL Server login in any
specific database.

94
Q

T-SQL to create

  • flexible database-level roles
  • user-defined server role
A

CREATE ROLE

CREATE SERVER ROLE

95
Q

In contained databases, database user accounts do not need to ___ to _______ .

A

to map to existing SQL logins.

96
Q

In uncontained databases, ______ map to existing SQL logins. Orphaned users are users in database with no _________.

A
  • database user accounts
  • corresponding SQL Server login
97
Q

You have created a database instance security principal that maps to a domain-based user account. Which of the following should you create at the database level so that you can grant the database instance security principal the appropriate privileges to create and drop tables in the database?
A. Login
B. Server role
C. Credential
D. User

A

D. User

98
Q

You want to create a security principal at the database level, add existing database users to this security principal, and assign permissions to it. Which of the following Transact-SQL statements would you use to accomplish this goal?
A. CREATE ROLE
B. CREATE SERVER ROLE
C. ALTER ROLE
D. ALTER SERVER ROLE

A

A. CREATE ROLE

C. ALTER ROLE

99
Q

Which of the following statements or stored procedures do you use to add database users to a fixed database role? Choose all apply.
A. ALTER SERVER ROLE
B. ALTER ROLE
C. sp_addrolemember
D. CREATE ROLE

A

B. ALTER ROLE

C. sp_addrolemember

100
Q

To which of the following fixed database roles should you add a database user if you want to grant the ability to manage database access for logins without assigning unnecessary privileges?
A. db_owner
B. db_accessadmin
C. db_securityadmin
D. db_ddladmin

A

B. db_accessadmin

101
Q

Which of the following steps must you take before you can create a database that allows database logins that do not map to a SQL Server login?
A. Enable contained database authentication at the instance level.
B. Disable contained database authentication at the instance level. Create a userdefined server role.
C. Create a flexible database role.
D. Create a user-defined server role

A

A. Enable contained database authentication at the instance level.

102
Q

A securable is an item in SQL Server for which you can assign ______. Securables can be contained within other securables; the term for these nested hierarchies is ____.

A

permissions

scope

103
Q

You can add a security principal, including another role, to a database role by using
the _________ stored procedure

A

sp_ addrolemember

104
Q

Which permission must you assign on a table if you want to allow a database user to view the information in that table?
A. REFERENCE
B. INSERT
C. UPDATE
D. SELECT

A

D. SELECT

105
Q

You want to configure permissions on a table so that members of a database role are
allowed to insert data into a table but cannot change existing data in that table. Which
of the following permissions should you assign?
A. REFERENCE
B. INSERT
C. RECEIVE
D. UPDATE

A

B. INSERT

106
Q

Which of the following Transact-SQL statements removes the INSERT permission from the Person.Address table for the Sydney_Users role?
A. GRANT INSERT ON [Person].[Address] to [Sydney_Users]
B. GRANT SELECT ON [Person].[Address] to [Sydney_Users]
C. DENY INSERT ON [Person].[Address] to [Sydney_Users]
D. REVOKE INSERT ON [Person].[Address] to [Sydney_Users]

A

D. REVOKE INSERT ON [Person].[Address] to [Sydney_Users]

107
Q

To which fixed database role would you add a security principal to ensure that he or she cannot add, modify, or delete any data stored in user tables in the database?
A. db_datawriter
B. db_denydatawriter
C. db_denydatareader
D. db_owner

A

B. db_denydatawriter

108
Q

Which Transact-SQL statement can you use to unlock a locked-out login?

A

Use the ALTER LOGIN statement to unlock a locked-out login.

109
Q

To use catalog views for viewing security info at the database engine instance level, the security principal must have ______ permission.

To use catalog views for individual database level, the security principal must have _____ permission.

A

VIEW SERVER STATE

VIEW DATABASE STATE

110
Q

You have configured the default, ALPHA, BETA, GAMMA, and DELTA instances on a single host server so that users can connect by using their Active Directory domain credentials. This morning, users can connect to the default instance but report that they cannot connect to the ALPHA, BETA, GAMMA, and DELTA instances. Which of the following should you do when attempting to resolve this problem?
A. Configure the default instance to use mixed authentication.
B. Start the SQL Server Agent service on the default instance.
C. Start the SQL Server Browser service.
D. Restart the SQL Server service on the default instance.

A

C. Correct. Clients cannot connect to named instances that do not use fixed ports if the SQL Server Browser service is not running

111
Q

A colleague in the Finance department cannot connect to the Database Engine, although other workers in the finance department can connect. You suspect that the user’s login is disabled. Which of the following catalog views can you query to determine whether this is the case?
A. sys.sql_logins
B. sys.server_permissions
C. sys.server_principals
D. sys.server_role_members

A

C. sys.server_principals

112
Q

You are determining which SQL Server–authenticated logins on an instance are configured without a password expiration date. Which of the following SQL catalog views could you query to make this determination?
A. sys.server_permissions
B. sys.sql_logins
C. sys.server_role_members
D. sql.server_principals

A

B. sys.sql_logins is for SQL authenticated logins

113
Q

You are resolving a problem a database user is having. You must determine whether instance, database, or Windows authentication is being used. Which of the following
security catalog views can you query to determine this information?
A. sys.database_principals
B. sys.database_permissions
C. sys.server_principals
D. sys.sql_logins

A

A. sys.database_principals

114
Q

SQL Server Audit enables you to configure auditing at the ____ and ___ levels

SQL Server Audit forwards audit results to a target, which can be either a ____ or the _______ or ______ event logs on the host computer.

Only _____ edition supports database-level audit.

A

instance, database

flat file, Windows Security ,Windows Application

SQL Server 2012 Enterprise

115
Q

What must you create before creating a server audit specification?

A

You must create a server audit before creating a server audit specification.

116
Q

You can configure Login auditing at …

A

Server Properties > Security tab

117
Q

__________ supersedes c2 audit mode.

A

Common criteria compliance

118
Q

<span>To create, alter, or drop a database audit specification, security principals must have<br></br>permission to connect to the database and be assigned either the ______permission or the___ or ____ permissions on the database.</span>

A

ALTER ANY DATABASE AUDIT

ALTER

CONTROL

119
Q

You are configuring the Windows Security log as an audit target on the default instance of server SQL-A. The SQL Server service uses the contoso\SQL-SRV account as its service account. The SQL Server Agent service uses the contoso\SQL-AGT account as its service account. Which of the following policies must you configure to accomplish this goal? Choose all that apply.

A. Add the contoso\SYD-SQL-SRV account to the Generate Security Audits policy.
B. Add the contoso\SYD-SQL-AGT account to the Generate Security Audits policy.
C. Enable success and failure audits on the Audit Object Access policy.
D. Enable success and failure audits on the Audit Privilege Use policy.

A

A. Add the contoso\SYD-SQL-SRV account to the Generate Security Audits policy.

C. Enable success and failure audits on the Audit Object Access policy.

120
Q

You must perform database-level audits in a production environment. Which of the following SQL Server editions supports this functionality?
A. Enterprise
B. Business Intelligence
C. Standard
D. Web

A

A. Enterprise

121
Q

An audit failure has caused the Database Engine to shut down. What steps can you take to restart the Database Engine so that you can modify the server audit and resolve the issue? Choose all that apply.
A. Restart the SQL Server Agent service.
B. Start SQL Server by using the -f option.
C. Start SQL Server in single-user mode.
D. Restart the SQL Server Browser service

A

B. Start SQL Server by using the -f option.

C. Start SQL Server in single-user mode.

122
Q

SQL Server Audit has not been configured on a Database Engine instance. You will use SQL Server audit to track insert activity on specific tables in the Hovercraft database.
You do not want to track insert activity on any other user databases hosted on the instance. Which of the following Transact-SQL statements would you use to configure SQL Server Audit to accomplish this goal? Choose all that apply.
A. CREATE SERVER AUDIT
B. CREATE SERVER AUDIT SPECIFICATION
C. CREATE DATABASE AUDIT SPECIFICATION
D. ALTER DATABASE AUDIT SPECIFICATION

A

A. CREATE SERVER AUDIT
C. CREATE DATABASE AUDIT SPECIFICATION

123
Q

You can mirror only databases that are configured to use the ____ recovery model.

A

Full

124
Q

When configuring database mirror,

you can change to the high safety _______ only if a ___ server is present.

A

with automatic failover mode

witness

125
Q

Automatic failover will occur if

  • configured for _______ mode
  • has a ____
  • _____ and ____ are still connected but cannot contact ____.
A
  • configured for high-safety mode
  • witness
  • witness and mirror are not connected but not principal
126
Q

You can disable automatic failover in SQL Server Management Studio by changing the operating mode to either high performance (asynchronous) or high safety without automatic failover (synchronous) on the _______ page of the _________.

A

Mirroring

mirrored database properties

127
Q

You are preparing an instance that will function as a mirror server. You must restore a backup of the database that will be mirrored to this instance. Which of the following restoration options must you use when performing this operation?
A. WITH RECOVERY
B. WITH NORECOVERY
C. WITH STANDBY
D. WITH KEEP_REPLICATION

A

B. WITH NORECOVERY

128
Q

In which of the following situations must you use certificate-based authentication when configuring database mirroring?
A. SQL Server service accounts are members of an Active Directory domain.
B. You are configuring a witness server on a Server Core–host operating system.
C. SQL Server service accounts are managed service accounts.
D. SQL Server service accounts are virtual accounts

A

D. SQL Server service accounts are virtual accounts

129
Q

Which of the following editions of SQL Server 2012 support high-performance mode for database mirroring?
A. Web edition
B. Standard edition

C. Enterprise edition
D. Business Intelligence edition

A

C. Enterprise edition

130
Q

The AdventureWorks database is participating in a high-safety mirroring session. The principal instance is hosted on server SQL-A. The mirror instance is hosted on SQL-B. You must configure an instance hosted on server SQL-CORE as a witness for this mirror. The endpoint that supports mirroring on each instance uses TCP port 7024. You have configured the appropriate permissions on each endpoint. Which of the following steps should you take to configure the SQL-CORE instance as the witness?
A. Execute SQL-A: ALTER DATABASE AdventureWorks2012<br></br>SET WITNESS = ‘TCP://SQL-CORE:7024’;<br></br>B. Execute SQL-B: ALTER DATABASE AdventureWorks2012<br></br>SET WITNESS = ‘TCP://SQL-CORE:7024’;
C. Execute SQL-CORE: ALTER DATABASEAdventureWorks2012

SET WITNESS = ‘TCP://SQL-A:7024’;
D. Execute SQL-CORE: ALTER DATABASEAdventureWorks2012

SET WITNESS = ‘TCP://SQL-B:7024’

A

A. Execute SQL-A:

ALTER DATABASE AdventureWorks2012
SET WITNESS = ‘TCP://SQL-CORE:7024’;

SQL Server (6 decks)

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