(Exam 3 Practice Questions)

One of your servers, located in New York City, contains a Sales database that needs to be replicated to your branch offices in Berlin, London, and Moscow. The branch offices are connected via a T1  connection that consistently runs at 80% capacity. your sales associates in New York City make changes to the database regularly throughout the day, but the users in the branch offices do not need to see the changes immediately. Which type of replication should you use?

a. merge replication

b. transactional replication

c. snapshot replication

d. transaction with updating subscribers

e snapshot with updating subscribers

a. merge replication - INCORRECT - not updating in two directions

b. transactional replication - CORRECT - one way transactional, per transaction

c. snapshot replication - INCORRECT - you're making changes throughout the day and you're running at 80% cpacity

d. transaction with updating subscribers - INCORRECT - the subscribers are not updating 

e snapshot with updating subscribers - INCORRECT - no such thing.

(Exam 3 Practice Questions)

(True or False)

A witness server is required in order to have automatic failover when using database mirroring.

True (The witness decides if the mirror is to be upgraded to principal)

(Exam 3 Practice Questions)

Each branch office of your company has its own accounting department. The network connections between the branch offices are realiable, but they are consistently at 80% usage during the day. each of your branch office accounting departments needs a copy of the main accounting database that they can update locally, and they need it to be as current as possible. Which type of replication best meets these needs?

a. merge replication

b. transactional replication

c. snapshot repllcation

d. transactional replication with immediate updating subscribers

e. snapshot with updating subscribers

a. merge replication - INCORRECT- they do not need to see the other department's info

b. transactional replication  - INCORRECT- subscribers cannot update with this method

c. snapshot repllcation - INCORRECT- this is not writable

d. transactional replication with immediate updating subscribers - CORRECT- the subscribers are updating

e. snapshot with updating subscribers - INCORRECT- does not exist

(Exam 3 Practice Questions)

What is SQL Server Integration Services (SSIS) used for?

It is used for extracting and transforming data from multiple sources and moving it to one or more destination data sources for data warehouses, data

marts, etc.

(Exam 3 Practice Questions)

What are Reporting Services?

Report Manager and Report Server for creating, managing, and distributing reports; built on IIS and

.NET Framework technology

(Exam 3 Practice Questions)

What does a Service Broker do?

It provides reliable queuing and messaging as a

central part of the database; allows queries to be stacked and performed as resources allow; delivers notification messages

(Exam 3 Practice Questions)

What does Sync Framework do?

It enables data synchronization for

collaboration and offline use

(Exam 3 Practice Questions)

SQL Server 2008 is not simply a database – it is what?

(Chapter 1)

What 3 factors should be considered when determining  how the SQL Server computer will be used

• 
  SQL Server Features required
• 
  Number of concurrent users
• 
  Amount of data to be managed

(Chapter 1)

What are the minimum hardware requirements for a 32-bit system?

• Processor: Pentium III or higher
• Processor speed: 1.0 GHz or higher
• Memory: 512 MB or higher

(Chapter 1)

What are the minimum hardware requirements for a 64-bit system?

• Processor: Itanium, Opterion, Athelon, or Xeon/Pentium with EM64T support
• Processor speed: 1.6 GHz or higher
• Memory: 512 MB or higher

(Chapter 1)

What's the recommended memory?

2048 MB

(Chapter 1)

SQL Server 208 supports ___ and can process complex

___ queries

SMP, parallel  

(Chapter 1)

For clustering, you will need space for the ___ disk

quorum 

(Chapter 1)

___ ___ is the recommended choice for high end

storage needs

Fiber channel

(Chapter 1)

Use ___ to configure fault-tolerant data storage

RAID 

(Chapter 1)

What is RAID 0?

disk striping without parity. SQL Server cannot continue until the drive is replaced

(Chapter 1)

What is RAID 1?

disk mirroring. a drive can

be rebuilt while the mirror allows processing to continue

(Chapter 1)

What is RAID 5?

disk striping with parity. Good protection against single drive failure but poor read/write performance

(Chapter 1)

When protecting data, which RAID should you use?

RAID 0+1 or RAID 5

(Chapter 1)

When protecting logs, which RAID should you use?

RAID 1

(Chapter 1)

Which Windows Server does NOT support SQL Server?

Windows Server 2008 Server Core. Does not have the .NET Framework required for FILESTREAM, SPATIAL, and DATE data types

(Chapter 1)

What is a Domain Account?

service uses a standard domain account

with privileges that you configure

(Chapter 1)

What is a Local System Account?

(Chapter1)

What is a Network Service Account?

(Chapter 1)

What is Object Explorer?

view & connect to SQL Server, Analysis Services, Integration Services, Reporting Services, and Compact edition

(Chapter 1)

What are registered Servers?

shows currently registered servers;

allow you to switch between servers of a particular type

(Chapter 1)

What is template explorer?

access to the default Query Editor

templates and custom templates

(Chapter 1)

What is Solutions Explorer?

access to SQL Server, Analysis Server,

and Compact edition projects

(Chapter 1)

What is SQL Server Configuration Manager?

perform essential

service, setup, and network configuration tasks

(Chapter 1)

What is Analysis Services Deployment Wizard:

to deploy the

output from an Analysis Services project to a target server

(Chapter 1)

What is Business Intelligence Development Studio

develop &

manage business intelligence objects

(Chapter 1)

What is

Database Engine Tuning Advisor:

tune performance

(Chapter 1)

What is Import & Export Data Wizard?

fine SSIS packages for

import and export

(Chapter 1)

What is SQL Server Installation Center:

central interface for

upgrading to 2008 from earlier versions

(Chapter 1)

What is SQL Server Profiler:

analyze user activities and generate

audit trails (the GUI to SQL Trace)

(Chapter 1)

What is ETL?

Extraction, transformation, and loading

(Chapter 1)

What is Pipeline parallelism?

takes full advantage of multiple processors

or processor cores by using shared thread pools

(Chapter 1)

What is Partitioned table parallelism?

takes full advantage of multiple

processors or processor cores when processing queries across

multiple partitions

(Chapter 1)

What is Partition-aligned indexed views?

brings along the indexed views

for the partition when you switch partitions

(Chapter 1)

What is Merge operations:

performs multiple insert, update, and delete

operations on the results of joining the target table or view with

a data source; merge operation requires only a single pass over

the data

(Chapter 1)

What is Change data capture

normally must use triggers, time-stamp

columns, or queries to determine what changed; this feature

tracks changes made to user tables

(Chapter 1)

What is Minimally logged inserts?

normally, an insert writes to the log

and to the database (to allow recovery and reprocessing); this

feature writes only the info required for roll-back, while

writing all data to the database

(Chapter 1)

What is Unified Dimension Model (UDM),

serves to

blur the lines between traditional relational databases and

multidimensional OLAP databases

(Chapter 1)

What is Scalable shared database (SSD)?

allows you to mark a database

as read-only and share it across multiple server instances

from a storage area network (SAN); no longer need to

replicate between servers for this purpose

(Chapter 1)

What is Log shipping?

establish a standby server: manually bring

it online if the primary server fails

(Chapter 1)

What is Cluster service

create a failover server; automatically

comes online if the primary server fails

(Chapter 1)

What is Database mirroring

continuously streams transactions

from a source server to a destination server; applications can

reconnect to the secondary server in seconds if the primary

fails

(Chapter 1)

What is Multiple active result sets (MARS):

improved

connectivity for users; provides the programming interfaces

necessary to handle multiple result sets in a single session;

allows multiple requests under ODBC and spawning of

connections under OLE DB

(Chapter 1)

What is the only edition that supports the installation of multiple instances on a single machine?

Enterprise 

(Chapter 1)

What does Database Mail require to be running?

SQL Server Agent