Mcts 70-432 Sybex

Front 1

DMF
PBM
SAU model

Back 1

DMF - Declararive Management Framework
PBM - Policy-Based Management
SAU - Standardize, Automate and Update

Front 2

flat-file database def. + notes

Back 2

is stored in a single storage container
1)Flat-file databases result in high levels of data redundancy
2)Flat-file databases cost more when data is added

Front 3

Normalization def

Back 3

is defined as the process used to ensure that relational
data is stored in a manner that removes or reduces anomalies in data
modifications. The process also results in a reduction in redundancy within
the data store.

Front 4

Relational databases def. + notes

Back 4

store information in separate containers called tables. Each table
represents a single entity
1)Relational databases can be indexed and optimized more efficiently.
2)Relational databases consume less space to store the same information as flat-file databases.
3)Relational databases can handle more concurrent users more easily.

Front 5

record set def.

Back 5

usually references a result
set acquired by a SELECT statement that may include all or a portion of the table data

Front 6

function returns the number of records in a table

Back 6

SELECT COUNT(*) FROM table_name

Front 7

Select all user tables

Back 7

SELECT * FROM sys.all_objects WHERE type = 'U' or type = 'V' and is_ms_shipped = 0

Front 8

Select all columns in a DB

Back 8

USE UserDBName

SELECT * FROM sys.all_columns

Front 9

Information_Schema.Table
show information about tables and views in a particular table

Back 9

SELECT * TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables

Front 10

All custom DB on an instance

Back 10

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

Front 11

Configuration Servers def

Back 11

Configuration servers are special SQL Servers that are used to centrally configure other
servers. Any SQL Server 2008 instance can be converted to a configuration server. Once
the configuration server is implemented, two primary tasks can be performed: centralized
management of PBM and multiserver queries.

Front 12

Multiserver Query feature def

Back 12

With this feature, you can execute a query
from the configuration server to be run against all of the servers in a configuration group.

Front 13

Data collectors def

Back 13

are
simply the tools used to collect performance information about your server. Historical performance
data can be automatically stored in a management data warehouse, allowing the
DBA to review historical performance data at any time. The process of collecting the data
is as follows:
1. SQL Server Agent schedules and launches the Data Collector component.
2. The Data Collector component launches the needed SSIS package.
3. The SSIS package collects the performance data and stores it in the management data
warehouse.

Front 14

Change Data Capture

Back 14

Developers have been writing triggers and stored procedures for years in order to capture
data changes. When a user modifies a record, for example, the trigger fires and saves to a
History table a copy of what the data looked like before the modification. SQL Server 2008
Enterprise and Developer editions now support a feature called Change Data Capture. It
is easily enabled for the entire database or a specific set of tables. Once enabled, historical
states of the data can be queried.

Front 15

SQL Server 2008 provides several data type enhancements and changes please list

Back 15

datetime2
hierarchyid
filestream

Front 16

Language Integrated Query (LINQ)

Back 16

feature for .NET development
some time ago; however, there was no direct support for it in SQL Server 2005 and earlier.
SQL Server 2008 implements a LINQ to SQL provider, which means that developers can
write queries in standard .NET code (instead of embedded SQL variables), and the SQL
Server will take care of translating the request into T-SQL that the server can process.

Front 17

DAC port

Back 17

DAC listens on TCP port 1434 by default

Front 18

Separated Owners and Schemas Before SQL Server 2005,

Back 18

Before SQL Server 2005, if you made a user the owner of
a table, that table was placed in the user’s schema. You would end up with table names such
as fred.sales and jose.marketing. Needless to say, this structure was less than ideal. Because
of this functionality, most DBAs chose to use the dbo schema for everything and, therefore,
ensured that the dbo owned everything. With the release of SQL Server 2005, schemas became
useable—in a practical way—for the first time. A user can own a table, and that table can
remain in the assigned schema.

Front 19

Service Broker def

Back 19

You’re probably used to databases working in a synchronous manner. The client submits
a request to the SQL Server and waits for the server to respond. The server receives the
request from the client and processes it as quickly as the current workload allows and then
responds to the client. This traditional database communications model does not scale well
when a customer submits an order at a website and that order must update an inventory
system, a shipment system, a billing system, and a website tracking system. Service Broker
provides asynchronous communications without the need for building the entire core communications
engine. It provides queuing, queue processing, and other services that allow a
more scalable application.

Front 20

Notification Services def

Back 20

If you’ve used SQL Server for any amount of time, you’ve probably used operators and had
notifications sent to you when a backup completes or when a job fails. Notification Services
provides this concept to your users. For example, Notification Services provides the framework
to allow a sales representative to subscribe to a change in pricing. Imagine a customer
who is ready to buy 50,000 units of item number 2043978 as soon as the price drops below
$0.70 per unit. The salesperson can configure a notification so that she is notified immediately
when the price drop takes place.

Front 21

Transactional Processing def

Back 21

Transactional processing ensures that the database maintains
consistency. For example, in a banking application, you would not want to allow a transfer
from savings to checking to take place in such a way that the money is removed from
savings but doesn’t make it into checking. Transactional processing ensures that the entire
transaction is successful or none of the transaction components are allowed. Transactions
can be implicit or explicit, and all changes are treated as transactions.

Front 22

SQL Server service for the default instance is named _____________
The SQL Server service for named instances is named ________________

Back 22

MSSQLSERVER

MSSQL$Instance name

Front 23

SQL Server Agent service is used to monitor the databases and database server. It is
also used to automate maintenance and administration tasks through the use of jobs. The SQL Server Agent service is named __________ for the default
instance and _______________ for named instances

Back 23

MSSQLSERVER

SQL Serve Agent (instance name)

Front 24

all user database access occurs through what service

Back 24

the SQL Server service.

Front 25

Database Atomicity def

Back 25

Atomicity is a database system term that means all tasks in a transaction
are completed successfully or no task is performed. The transaction log assists with
atomicity in that every step in the transaction is entered in the transaction log, but if a step
fails all previous steps can be removed (rolled back). The transaction log is essential because
many transactions may be too large to completely perform in memory.

Front 26

Automatic Recovery

Back 26

Automatic recovery occurs when the SQL Server service starts. During
startup, the service looks in the transaction log for any transactions that are completely
entered in the log, but have not been committed. Data is only committed to the physical MDF
file when a checkpoint occurs. The automatic recovery process uses checkpoints and transaction
commits to determine which transactions should be executed again (rolled forward) and
which should be ignored (rolled back).

Front 27

Disaster Recovery

Back 27

Disaster recovery is a manual process performed by an administrator.
Assuming your data (MDF and NDF files) is on one physical drive and your transaction
log (LDF file) is on another physical drive, you can restore to the point of failure. This restoration
is accomplished by using the transactions in the current transaction log that were
executed since the last backup.

Front 28

max lenght of a DB name in SQL

Back 28

maximum of 128 characters

Front 29

At least what version should the source DB be in order for us to be able to be attached

Back 29

at least SQL Server 2000 with compitability of 80

Front 30

schema

Back 30

defined as a container that provides a logical security
boundary for database objects

Front 31

One-to-One

Back 31

In a one-to-one relationship, one record in one table matches one and only one record in
another table.

Front 32

One-to-Many

Back 32

The one-to-many relationship is probably the most common relationship type

Front 33

SDLC

Back 33

many DBAs will use the Systems or Software Development Lifecycle (SDLC) as the foundation
for their database design process

Front 34

DBLC
BUM

Back 34

Database Lifecycle (DBLC)
Business, User, Model (BUM)

Front 35

BCNF def

Back 35

A table must meet the following requirements to be in Boyce-Codd form (BCNF):
1)The table must must meet 3NF requirements.
2)The table must not have multiple overlapping candidate keys.

Front 36

A table must meet the following requirements to be in fourth normal forms (4NF):

Back 36

1)The table must meet BCNF requirements.
2)The table does not contain an unrelated multivalued dependency.

Front 37

cmd. that provides transaction log space usage statistics for all DB. It can also be used to reset wain and latch statistics.

Back 37

dbcc sqlperf

Front 38

cmd. that unloads the specified extended stored procedure DLL from memory

Back 38

dbcc dllname

Front 39

when is error 824 returned?

Back 39

Error returned to the calling application and logged to SQL Server error log and Windows Application Event Log and the ID of the damaged page os logged to the suspect_pages table in the msdb DB

Front 40

kpi (abrr&def)

Back 40

key performance indicator
predefined measure that is used to track performance of a strategic goal, objective, plan, initiative, or business process. A KPI is evaluated against a target. An explicit and measurable value taken directly from a data source.
KPI are used to measure performance in specific area for example, revenue per customer.

Front 41

latch (def)

Back 41

Latch
is a short-term synchronization object protecting actions that need not be locked for the life of a transaction.
A latch is primarily used to protect a row that the storage engine is actively transferring from a base table or index to the relational engine.

Front 42

How then do you determine if an index is disabled or not? Only with query SSMS doenst have an immediate indicator

Back 42

SELECT INDEXPROPERTY
(
OBJECT_ID(‘dbo.Customers’),
‘ix_City’,
‘IsDisabled’
)
GO

Front 43

DML statements include:

Back 43

SELECT
INSERT
UPDATE
DELETE

Front 44

DDL statements include:

Back 44

CREATE
ALTER
DROP

Front 45

recursive trigger

Back 45

one that is fired by an event and ends up executing the same or similar code equal to original firing event. AFTER triggers will only be able to fire themselves if the RECURSIVE_TRIGGERS options is turned on

Front 46

nested triggers

Back 46

in addition to recursion triggers can be nested - one trigger fires another
up to 32 levels of triggers

Front 47

cmd to disable indirect recursion

Back 47

EXEC sp_configure 'nested triggers', 0

Front 48

normalization def

Back 48

the process used to ensure that relational data is stored in a manner that removes or reduces anomalies in data modification