MCTS Exam 70-432 Chapter 2, Lesson 3

Lesson 3: Maintaining Database Integrity

Database Integrity Checks:

• PAGE_VERIFY has two options: TORN_PAGE_DETECTION and CHECKSUM
• TORN_PAGE_DETECTION: For backwards compatibility only
• CHECKSUM: SQL Server calculates the checksum prior to each write, each time a page is read from disk it is recalculated and compared.
• Corrupted pages cause logging to suspect_pages on msdb & aborted connection occurs
• Use DBCC CHECK to verify database integrity

MCTS Exam 70-432 Chapter 2, Lesson 2

Lesson 2: Configuring Database Options

Recovery Options:

• Determine the behavior of the transaction logs & how damaged pages are handled.

Recovery Models:

• Full: All changes to the DDL & DML are logged to the transaction log. This makes recovery to a give point in time possible.
• Bulk-Logged: Logs on the extents; minimally logs large inserts and indexes. No Point in time recovery. Transaction log is minimally logged.
• Simple: Logs the same as full but at check points discards transaction logs.

Damaged Pages:

• PAGE_VERIFY_CHECKSUM: checks/logs damaged pages.
• suspect_pages table in the msdb database.
• Mirroring automatically fixes damaged pages.

Auto Options:

• Five options enable certain actions to occur naturally.
• AUTO_CLOSE: Close the database on client disconnect.
• AUTO_SHRINK: If free space of a database is > 25% then the file automatically shrinks.
• AUTO_CREATE_STATISTICS: Allow query optimizer to build more efficient execution plans.
• AUTO_UPDATE_STATISTICS: Update statistics during query optimization.
• AUTO_UPDATE_STATISTICS_ASYNC: Use another process to seperatly update statistics.

Change Tracking:

• CHANGE_TRACKING: Each row has a version number to different changes.
• CHANGE_RETENTION: How long to keep the changes.
• AUTO_CLEANUP: Whether tacking information should automatically be cleaned up.

Access:

• ONLINE: All operations can be performed.
• OFFLINE: Database unavailable.
• EMERGENCY: db_owner accessible only, SELECT only.
• Changing from READ_ONLY to READ_WRITE causes the transaction log to recreate.
• RESTRICTED_USER: Only db_owner, dbcreator, sysadmin roles.
• When switching to SINGLE or RESTRICTED_USER modes you can’t use ALTER DATABASE until all users disconnect.
• Using ROLLBACK_IMMEDIATE or ROLLBACK_AFTER <seconds> will knock users offline.

Parameterization:

• PARAMETERIZATION_FORCED.

Collation Sequences:

• COLLATE clause on either CREATE or ALTER DATABASE.

MCTS Exam 70-432 Chapter 2, Lesson 1

Lesson 1: Configuring Files and Groups

Files and Filegroups:

• Filegroup: Logical structure defined withing a database that maps a database and the objects within to the data files on disk. Can contain more then one data file.
• Used for data + transaction logs
• Filegroups abstract individual files from the database
• Objects contained (data, tables, indexes and indexed views) have an ‘on‘ clause to specify the filegroup
• File Extensions: SQL Server uses .mdf (main data file), .ndf (all other data), and .ldf (transaction log)
• Check Pointing: Moving data out of the transaction log and into the data file

Transaction Logs:

• All changes hit the transaction log prior to a commit

FILESTREAM data:

• Brought on by the growing unstructured data, SQL Server 2K8 feature
• Associate files w/database
• Stored on a folder on the file system; linked in a database where files can be backed up, restored, full-text-indexed, and combined with other structured data
• System creates filestream.hdr to manage the data

tempdb:

• Used for temp objects, group/sort work tables, work tables supporting cursors, version store supporting isolation level, overflow for table variables
• Should be on a separate disk
• One file per processor

SCMDBA 5.0 Exam Part I, Chapter 1

MySQL Architecture

Client/Server Overview:

• MySQL Server: The mysqld program manages databases and tables.
• Client Programs: Character based programs run over a network (or socket/piped) connection and send requests to the mysqld server. Example: mysql
• Non-Client Utility Programs: Generally used for special purposes (backup, etc) and normally don’t connect with the server (myisamchk).
• MySQL Connectors allow API access to the database for C, .NET, ODBC, Java.
• MySQL is supported across multiple operating systems as well as cross-platform interoperable.

Communication Protocols:

• Named Pipe: Windows only, disabled by default. Server needs -nt in it’s name or –enable-named-pipe as a startup option. Slower (generally) then TCP/IP.
• Shared Memory: Windows only, disabled by default. –shared-memory is the startup option.
• Connector/ODBC  connections ride on top of the C library connection stack, and are inherently slower than the C library connection stack.
• Connector/Java connections are roughly the same speed as the C library connection stack.

The SQL Parser and Storage Engine Tiers:

• Two tiered processing model consisting of upper and lower tiers.
• Upper Tier: The upper tier is the SQL parser and optimizer. Doesn’t actually interact with the tables, merely sends commands to the lower tier.
• Lower Tier: The modular architecture used in managing tables of a specific type.
• Some indexes are only for certain engines, OLTP commands are only in InnoDB.

How MySQL uses disk space:

• Each database has a single directory containing all supporting files
• Table format files (.frm) that describe the format of the table
• Data/index files are created for some engine types
• InnoDB has it’s own table space (ibdata1) and log files (ib_logfile0/1)
• Server logs/status files

How MySQL uses memory:

• Thread handlers
• Buffers/caches
• Memory storage engine
• Temporary tables
• Client buffers (connection buffer / read and sort operations)

MCTS Exam 70-432 Chapter 1, Lesson 4

Lesson 4: Configuring Database Mail

Database Mail

• Primarily used to let admins know of performance issues or changes to objects
• Added in SQL Server 2K5
• Replaced SQL Mail to allow the removal of a dependency (Microsoft Mail Application Programming (MAPI)), simplified configuration/management, provided a fast/reliable way to send mail messages
• Uses SMTP
• When mail send is initiated the message along with all properties are logged into a table on the Msdb database.
• Periodically a background service managed by SQL Server Agent executes and forwards all unsent mail using the appropriate mail profile
• Profiles are the core element of database mail; can contain multiple email accounts for failover if a specific mail server is down
• Mail accounts define all email properties

MCTS Exam 70-432 Chapter 1, Lesson 3

Lesson 3: Installing and Configuring SQL Server Instances

Service Accounts:

• All core SQL Server components run as services
• Service accounts need to be created for proper configuration of each component
• Dedicated accounts are required for the following components: DB Engine, SQL Server Agent
• The accounts utilized by SQL Server allow it to access resources within the Windows Security framework

Collation Sequences:

• Controls how SQL Server treats character data for storage, retrieval, sorting, and comparison operations
• Collation Sequences are available at: instance, database, table, and column levels
• Instance level is the only one required and is the default level for all others

Authentication modes:

• Option set during the install
• Modes: Windows only (integrated security); Windows and SQL Server (mixed mode)

SQL Server Instances:

• Up to 50 instances per machine for Enterprise Edition, Standard\Workgroup\Express\Compact only support 16
• Default connection uses machine name, named instance: <machinename>\<instancename>
• Reasons for more than one instance per machine: QA Testing or Development instance, support of multiple service pack/patch levels, restricted access per user on different databases, supported multiple sets of config options

SQL Server Configuration Manager:

• Start/Stop/Pause/Restart a service
• Change service accounts/passwords
• Manage the startup mode of a service
• Configure service startup parameters
• Always use SQL Server Configuration Manager to change passwords!
• Allows you to control client communication protocols and whether the connection needs to be encrypted or if the instance should respond to enumeration support

MCTS Exam 70-432 Chapter 1, Lesson 2

Lesson 2: Selecting SQL Server Editions

SQL Server Services

Service Broker:

• Message queueing system to provide async data processing capabilities.
• Can provide advanced business processing orchestration to handle data processing across different platforms.

SQL Server Integration Services (SSIS):

• Contains all enterprise capabilities found in Extract, Transform, Load (ETL) applications
• Allows FTP, filesystem, database access, execution of commands, message sending through MS Message Queue, and the ability to respond to Windows Management Instrumentation events.

SQL Server Reporting Services:

• Allows report building
• On the fly or Cached data
• Execute reports and send those via email
• Two components: Report Server and Designer

SQL Server Analysis Services:

• Contains OLAP + Data Miner
• OLAP engine allows deployment, query, and management of data ‘cubes‘ that have been designed in Business Intelligence Development Studio (BIDS)

SQL Server Editions:

• Enterprise: Fully featured for large organizations
• Standard: Small/Medium business, reduced feature set
• Workgroup: Department level projects, limited features
• Express: Freely redistributable, embedded apps, basic data storage for server apps or for small web apps (limited users)
• Compact: embedded systems
• Developer: For developers, contains all features of the Enterprise Edition, not allowed in production environments
• Evaluation: All capabilities of Enterprise, 180 day license, not allowed in production environments

MCTS Exam 70-432 Chapter 1, Lesson 1

Lesson 1: Determining hardware and software requirements

Minimum Hardware Requirements

• 32bit: P3 (1.0ghz), 512MB
• 64bit: Itanium, Athlon, Opteron, Xeon, Pentium (1.6ghz), 512MB

Disk Space depends on Services/Utilities installed

• Database engine, data files, replication, utilities installed. 280MB
• Analysis Services and data files. 90MB
• Reporting Services and Report Manager. 120MB
• Integration Services. 120MB
• Client Components. 850MB
• SQL Books online and Compact SQL books online. 240MB
• Required temporary space for installation. 2.0GB

Supported Operating Systems

• Supports all SQL Server 2008 Editions: Windows Server 2k3 SP2 / 2k8 (Standard Edition minimum)
• Supports Developer/Evaluation/Express: XP Pro SP2 and higher, Vista Home Basic and higher
• Supports Express Only: All XP reduced SP2 or higher
• Unsupported: Windows Server Core 2k8 (Lack of .NET framework)

Software Requirements

• .NET 3.5
• MS Data Access Components (MDAC) 2.8 or higher
• Shared Memory, Named Pipes, or TCP/IP Networking Support
• IE6 SP1 and higher

Summary

SQL Server 2k8 Supports 32/64 bit, all editions run on Windows Server Standard (2k3 needs SP2) and up, not on 2k8 Core.