70-762 Flashcards by Wally Moss

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

^{A. 512 MB }

^{B. 1 GB }

^{C. 2 GB }

^{D. 4 GB }

^{E. 8 GB}

B. 1 GB

How well did you know this?

Not at all

Perfectly

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. SQL Server 2012 (x64) Developer edition

D. SQL Server 2012 (x64) Standard edition

How well did you know this?

Not at all

Perfectly

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. B. C.

How well did you know this?

Not at all

Perfectly

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

^{A. Windows 7 Ultimate (x64) edition }

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

^{C. Windows Server 2008 SP2 (x64) Enterprise edition }

^{D. Windows Server 2003 R2 (x64) Enterprise edition}

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

C. Windows Server 2008 SP2 (x64) Enterprise edition

How well did you know this?

Not at all

Perfectly

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}

B. SQLIOSim

How well did you know this?

Not at all

Perfectly

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

SQL_Latin1_General_CP1_CI_AS

How well did you know this?

Not at all

Perfectly

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

Windows and SQL Server error reports

How well did you know this?

Not at all

Perfectly

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

ConfigurationFile.ini

How well did you know this?

Not at all

Perfectly

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.

SQL Server Integration Services (SSIS)

SSIS

How well did you know this?

Not at all

Perfectly

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.

Installed SQL Features Discovery Report

How well did you know this?

Not at all

Perfectly

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

firewall rules

server

application group policy

How well did you know this?

Not at all

Perfectly

Default Instance port number

TCP 1433

How well did you know this?

Not at all

Perfectly

Dedicated Admin Connection port number

TCP 1434

How well did you know this?

Not at all

Perfectly

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. Database Engine

How well did you know this?

Not at all

Perfectly

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

C. SQL Server Data Tools

How well did you know this?

Not at all

Perfectly

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

^{B. C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin \msmdsrv.exe}

How well did you know this?

Not at all

Perfectly

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

C. Add/Remove Programs in Control Panel

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

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.

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?

Not at all

Perfectly

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.

minimum reserved memory

below

minimum amount

How well did you know this?

Not at all

Perfectly

What is processor Affinity?

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?

Not at all

Perfectly

What is IO Affinity?

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

How well did you know this?

Not at all

Perfectly

What is fill factor?

“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?

Not at all

Perfectly

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

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?

Not at all

Perfectly

Use sp\_configure to...

* Enable Database email * Instance wide fill factor * Min/Max server memory * I/O affinity

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
B. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3
C. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
D. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,4}

_{A. ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO}

^{You have run the following command on a SQL Server 2012 default instance:} ^{EXEC sys.sp_configure 'show advanced options', 1;
GO
Which of the following commands must you run to configure the instance so that the maximum amount of memory the instance uses does not exceed 4,096 MB? (Each answer presents part of a complete solution. Choose two.)} _{A. RECONFIGURE;
GO
B. EXEC sys.sp_configure 'min server memory', 1024;
GO
C. EXEC sys.sp_configure 'min server memory', 4096;
GO
D. EXEC sys.sp_configure 'max server memory', 4096;
GO}

Answer: D, A ## Footnote _{D. EXEC sys.sp_configure 'max server memory', 4096;
GO} _{A. RECONFIGURE;
GO}

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

D. I/O affinity

_{You want to ensure that all future databases created on a SQL 2012 instance are configured to Auto Shrink. Which of the following system databases do you modify to accomplish this goal?
A. master
B. model
C. msdb
D. tempdb}

B. model database

_{Which stored procedure do you use to enable Database Mail on an instance?
A. sp_configure
B. sp_rename
C. sp_monitor
D. sp_depends}

A. sp\_configure

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

25 instances

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 \_\_\_\_\_\_\_\_.

shared features

\_\_\_\_\_\_\_\_\_ 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.

Resource Governor

Resource Governor * 1. ______ pool * \_\_\_\_\_\_\_\_ * \_\_\_\_\_\_\_\_ * 2. _____ group * \_\_\_\_\_\_\_\_ * \_\_\_\_\_\_\_\_

1. Resource Pools * internal pool * default pool 2.Workload group * internal group * default group

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

workload resource

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

session reuqests

Cycle SQL server Error log * default \_\_\_\_\_\_ * use sp\_\_\_\_ to force the error log to cycle wo restarting the database engine

* retain the current error log and the five most recent error logs * sp\_cycle\_errorlog

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

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

_{Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?
A. Resource Governor
B. Windows System Resource Manager
C. Processor affinity
D. I/O affinity}

_{A. correct: Resource Governor enables you to allocate session requests to different resources based on the characteristics of the session request properties}

_{What is the maximum number of SQL Server 2012 Enterprise edition Database Engine instances that you can deploy on a non-clustered server running Windows Server 2008 R2 Enterprise edition?
A. 10
B. 25
C. 50
D. 100}

**c**. 50 instances

_{You have configured Resource Governor with three resource pools. You have assigned the first resource pool a minimum CPU and memory value of 20%. You have assigned the second resource pool a minimum CPU and memory value of 30%. You want to assign 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?
A. 30%
B. 50%
C. 70%
D. 100%}

B. 50%

_{A server that has four processors has three SQL Server 2012 Database Engine instances installed. 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 third instance?
A. Resource Governor
B. Windows System Resource Manager
C. Processor affinity
D. I/O affinity}

B. Windwos System Rezource Manager

You can install Analysis Services in these 3 modes.

* Multidimensional & data mining * Tabular * Powerpivot

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

web server role Report Server instance Database Engine

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

secure Dcomcnfg.exe

Use FILESTREAM under the following conditions

* Objects that you want to store are greater than 1 MB. * Fast read access is important

Filegroup * Primary filegroup * Secondary filegroup

Primary filegroup * host system tables * mdf Secondary filegroup * can be hosted on different volume * ndf

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

model system database

Contained Databases

* no configuration dependencies * can easliy be moved to other instance

Data compression

* 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

Forms of data compression

* 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

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.

inherit original has destination

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

Use ALTER DATABASE with the SET ENCRYPTION ON option

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?

filegroup Only in Enterprise and Developer edition.

Partition function ## Footnote 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**.

1. rows of a table 2. index 3. a partitioning column. ## Footnote The partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined

What does Partition Schema do?

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

Log truncation occurs

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

Command to monitor the amount of log space used

DBCC SQLPERF (LOGSPACE) DBCC = database console commands

DBCC CHECKDB

Checks the physical and logical integrity of all objects in a specific database. ^{It runs ...} ^{DBCC CHECKALLOC} ^{DBCC CHECKTABLE} ^{DBCC CHECKCATALOG}

^{Which statement would you use to add a filegroup to an existing database?} ^{A. ALTER DATABASE
B. CREATE DATABASE
C. ALTER TABLE
D. CREATE TABLE}

A. ALTER DATABASE

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)

B. ALTER TABLE STUDENTS REBUILD WITH (DATA\_COMPRESSION=PAGE)

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. Create a master encryption key. B. Create a certificate. C. Create a database encryption key.

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

B. DBCC CHECKDB

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

Windows Server 2008 Windows Server 2003 Windows Server 2003 R2

You cannot perform cross-architecture \_\_\_\_\_, instead you'll have to \_\_\_\_\_.

upgrade migrate

Checklist before detach a database

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

Two methods for Copy database wizard

* detach and attach method - faster but source is taken offline * SQL management object method - slower but source remain online

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. 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

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. Copy Database Wizard D. Generate A Script

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. Copy Database Wizard

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. Drop database snapshots. B. Unpublish the database.

Name 2 Migrate methods

Detach / Attach Copy database

5 tools to copy and export data

* 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

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 ?

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

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. **Correct**:You use the bcp utility to create XML format files. These files are used when importing files that use fixed-width fields

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

C. Import and Export Wizard

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

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

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. Import and Export Wizard D. OPENROWSET(BULK) function

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. bcp utility C. Import and Export Wizard

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

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.

You cannot drop a login that owns a \_\_\_\_, a \_\_\_\_, or a \_\_\_\_.

^{a SQL Server Agent job,} ^{a server-level object,} ^{or a securable.}

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 \_\_\_\_\_\_\_\_.

Permissions individual SQL logins the login to a server role

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

authentication information a connection to a resource

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. ALTER ANY LOGIN

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”

C. CREATE LOGIN “CONTOSO\Research” FROM WINDOWS

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’

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

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

D. dbcreator

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

C. Credential

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

* Windows-authenticated login * SQL-authenticated login * Certificate * Asymmetric key

Server Roles ## Footnote \_\_\_\_\_ are assigned to server roles rather to \_\_\_\_\_\_.

Permissions individual SQL logins

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

the login a server role

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.

public

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

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

T-SQL to create * flexible database-level roles * user-defined server role

CREATE ROLE CREATE SERVER ROLE

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

to map to existing SQL logins.

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

* database user accounts * corresponding SQL Server login

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

D. User

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. CREATE ROLE C. ALTER ROLE

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

B. ALTER ROLE C. sp\_addrolemember

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

B. db\_accessadmin

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. Enable contained database authentication at the instance level.

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 \_\_\_\_.

permissions scope

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

sp\_ addrolemember

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

D. SELECT

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

B. INSERT

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]

D. REVOKE INSERT ON [Person].[Address] to [Sydney\_Users]

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

B. db\_denydatawriter

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

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

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.

VIEW SERVER STATE VIEW DATABASE STATE

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.

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

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

C. sys.server\_principals

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

B. sys.sql\_logins *is for SQL authenticated logins*

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. sys.database\_principals

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.

instance, database flat file, Windows Security ,Windows Application SQL Server 2012 Enterprise

What must you create before creating a server audit specification?

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

You can configure Login auditing at ...

Server Properties \> Security tab

\_\_\_\_\_\_\_\_\_\_ supersedes c2 audit mode.

Common criteria compliance

^{To create, alter, or drop a database audit specification, security principals must have
permission to connect to the database and be assigned either the ______ permission or the___ or ____ permissions on the database.}

ALTER ANY DATABASE AUDIT ALTER CONTROL

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. 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.

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. Enterprise

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

B. Start SQL Server by using the -f option. C. Start SQL Server in single-user mode.

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. CREATE SERVER AUDIT C. CREATE DATABASE AUDIT SPECIFICATION

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

Full

When configuring database mirror, you can change to the high safety _______ only if a \_\_\_ server is present.

with automatic failover mode witness

Automatic failover will occur if * configured for _______ mode * has a \_\_\_\_ * _____ and ____ are still connected but cannot contact \_\_\_\_.

* configured for high-safety mode * witness * witness and mirror are not connected but not principal

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 \_\_\_\_\_\_\_\_\_.

Mirroring mirrored database properties

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

B. WITH NORECOVERY

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

D. SQL Server service accounts are virtual accounts

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

C. Enterprise edition

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
SET WITNESS = ‘TCP://SQL-CORE:7024’;
B. Execute SQL-B: ALTER DATABASE AdventureWorks2012
SET WITNESS = ‘TCP://SQL-CORE:7024’;} _{C. Execute SQL-CORE: ALTER DATABASE AdventureWorks2012} _{SET WITNESS = ‘TCP://SQL-A:7024’;} _{D. Execute SQL-CORE: ALTER DATABASE AdventureWorks2012} _{SET WITNESS = ‘TCP://SQL-B:7024’}

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