MCITP SQL Server 2008 Cram

Data Types:

• Filestream - data is actually stored in NTFS NOT in SQL. SQL provides the filestream to access the data. For those that don't have experience programming or with file streams: a when not using file stream, the file must be loaded from the database into SQL memory, then served to the client. If the file is large, this consumes a great deal of memory. With filestreams we essentially stream the file to the client as we can - minimizing the strain on memory. Research shows that > 1 MB is more efficient in NTFS. Also NTFS doesn't share the SQL 2 GB Blob limit. Source
  

Performance / Monitoring

• Data Collectors can be used to collect data from several sources, for example if you want to correlate performance issues it would be helpful to see what TSQL statements are running (otherwise you would use SQL Profiler), blocking and deadlock (otherwise you would use SQL Profiler or Dynamic Management Views), and system counters like cpu, memory, disk (System Monitor). Using Data Collectors you can capture all of these and correlate the data to determine when the server is slow, what query is running, and why.
  
• Affinity - is the coupling of a thread to a processor. From an OS perspective - it is ideal to be able to move threads from one processor to another as the OS sees fit. So from an OS perspective affinity is a bad thing. So why would we have affinity - the SQL server can recognize that the data required by the thread is also going to be needed by other threads. So it can queue them together using affinity. When affinity is not set - the OS - not knowing better - will move the thread - then the required data (which is already in the preferred processor cache) will have to also be loaded into that processor's cache. Source 1, 2
• Extended Events - ...
• Partitioning - Vertical partitioning - think one-to-one relationship - which allows us to move a group of columns to another table. This can be very beneficial if we have a group of columns that are often needed together but not very often. Now the queries that happen more often will not have to include these columns (by not joining to this table). Horizontal partitioning - think union - move certain rows to another table (or filegroup). This is often done by moving records(rows) older than X (3 months) to a History/Archive table. This can greatly improve performance as indexes and the like don't have to reconstruct for every record but only those in the current (within 3 months) data. What is even cooler, is that this can be done at a lower layer by using file groups - so that to the client (user / application) it is just one table, but under the covers, using a partition function, old data goes to one file group while new data goes to another file group. Source
• 
  

High Availability / Disaster Recovery

• Log shipping - allows you to transmit your log (record of every transaction) to another instance - likely far away over serial link - if the primary server goes down - the data is not lost and clients can be pointed to this backup and resume operations

Security

• Trustworthy - Database option - while a database is offline - someone could tamper with the database (ie, execute as SA, etc.). This trustworthy flag allows you to limit what the database can do. By default it is off and the database should not need trust beyond its scope. Source
• EXTERNAL_ACCESS vs SAFE - SAFE is more restrictive as it doesn't allow you to be able to access external like files, networks, environment variables, registry. Source
  

FREENAS and iSCSI for Server 2008 Clustering

Great walk through http://www.trainsignaltraining.com/how-to-setup-iscsi-drive-using-freenas/2009-01-19/

PS. Once the drive shows up in computer management on Server 2008 - it may be offline (with some bogus error about security policy). Right click offline and click online. You will likely also need to right click again and initialize. Then you should be able to right click the drive and partition.

Great video on Server 2008 Quorum http://www.youtube.com/watch?v=j9E1LgLwG88

CISSP - Operations Security

Scanning identifies open ports - fingerprinting identifies OS / Application - this can be active (creating the traffic) or passive (watching existing traffic)

War Chalking - used to indicate where wireless networks are

TCSEC Assurance Levels -

1. System Architecture
2. System Integrity
3. Covert Channel Analysis
4. Trusted Facility Management
5. Trusted Recovery

Common Criteria - Recovery

1. Manual Recovery
2. Automated Recovery
3. Automated Recovery without Undue Loss

Componenets
1. failure preparation(backups), system recovery

CISSP - Information Security and Risk Management

RFC 2196 - Site Security Handbook

Formulai

Total Risk = Threat * Vulnerability * Asset Value

Annual Loss Expectancy = Single Loss Expectancy * Annualized Rate of Occurence

Residual Risk = Annual Loss Expectancy * Control Gap

Single Loss Expectancy = Asset Value * Exposure Factor

Risk Analysis

FRAP - Facilitated Risk Analysis Process - team gets together to brainstorm through. 26 commong controls.

Delphi - answers are in written form - good for getting some quiet opinions - not good for discussion

Risk Assessment Steps
1. Reduce, Transfer, or avoid risk
2. Derive annual loss potential
3. Perform a threat analysis
4. Estimate potential loss
5. assign value to assets

Labeling

Government = Unclassified -> Top Secret

Commercial = Public -> Confidential

Roles

Information Security Officer - Functional Role of Security

Auditors -> provide reports on effectiveness to senior management

Senior Management - ultimately responsible for security

CISSP - Application Security

Waterfall Model - discrete sequential steps

SDLC Phases
1. Project Initiation and Planning
2. Functional Requirements Definition
3. System Design Specifications
4. Development and Implementation
5. Documentation and Common Program Controls
6. Testing and Evaluation Control, Certification, and Accredidation
7. Transition to Production (Implementation)
8. Operations and Maintenance Support
9. Revisions and System Replacement

Data Dictionary - database of schemas

Mobile Code - code that can be executed in network browsers (ie, firefox)

Software Capability Maturity Model

1. Initiating
2. Repeatable (Project Management has been documented)
3. Defined (quantitative process improvement)
4. Managed
5. Optimizing

replaced in 2007 by CMMI = CMM + Integration

now has 22 process areas

Distributed System Requirements

1. interoperability
2. portability
3. transparency
4. extensibility
5. security

spoofing is changing the ip address, masquerading is changing the email from or caller id

perturbation - is injecting noise (meaningless data) to prevent inferrence.

SODA - Secure Object-Oriented Database Access - uses polyinstantiation to remedy the multiparty update conflict

Change Control Process

1. formal request
2. analyze
3. record
4. approve
5. develop (ie. scripts)
6. report to management

CISSP - Telecommunications and Network Security

WAN

ISDN BRI = 2 B and 1 D

Packet Switched = Frame Relay, X.25, ATM

Circuit Switched = DDS

Networking Models

OSI Model is an ISO Standard

TCP IP Model = Network Interface -> Inter networking -> Transport -> Application

SSL is between transport and session layers

802.15 = Wireless personal area - think bluetooth

IP

protocol field of packet -> 0x01 = ICMP, 0x06 = TCP, 0x11 = UDP, 0x58 = IGRP

determine Class
0xxxxxxx = <128 = A
10xxxxxx = <192 = B
110xxxxx = <224 = C
1110xxxx = <240 = D

private addresses
10.0.0.0/8
*127.0.0.0/8
169.254.0.0/16
172.16.0.0/12
192.168.0.0/16


RAID

0 = Striping
1 = Mirroring
2 = bit level striping w/ parity
3 = byte level striping w / parity
4 = block level striping w/ parity
5 = block level striping w / distributed parity
6 = block level striping w/ double distributed parity (requires 4 disks but can tolerate 2 down disks)

VPN

PPTP (PPP w/ MPPE, MS-CHAP, EAP-TLS), L2TP, IPSEC, L2F

Authentication

EAP, RADIUS, TACACS, PAP, CHAP

Wireless Security

WEP, WAP, WPA, TKIP

WAP 1 = no authentication, 2 = server authentication, 3 = server and client authentication

Attacks

Wormhole - a shortcut(wormhole) is created between 2 networks tricking routing protocols into using the wormhole. Then all traffic can be monitored or disrupted.

Firewalls

Dual-homed / bastion = think pc w/ 2 nics filtering traffic

Screened host = separation is logical instead of physical - in other words - only one nic. I picture this as a router only allowing 80 traffic to a proxy server, and the proxy server being the screened host firewall - though dependent on the router.

Screened subnet = 3 nics - think DMZ

CISSP - Physical Security

Types of Motion Detectors:
Wave pattern: think microwave, if the frequency bouncing back changes from norm then there is motion.
Capacitance: monitors an eletrical field for change - used for small area - think of an area surrounding an object in a museum.
Audio: listens for noise
Photoelectric: think grid of light (visible or not)

Types of fire suppression systems
Water Sprinklers
Wet Pipe: Pipe is full of water to the sprinkler head - quit to get water to fire - but if it was a false alarm, can cause equipment damage for no reason
Dry Pipe: Pipe is not full of water - providing a little bit of time to react to a false alarm - pipes could have leaks that aren't realized until a fire forces water into the pipes.
Deluge: Similar to dry pipe - but for high volumes of water - which is why they are not used around computer equipment
Preaction: dry until heat sensor primes it (now wet pipe) - then another heat sensor releases the water

Classes of Fire
A - Common combustibles - like wood
B - Burnable fules - like gas
C - Electrical - like a data center
D - Grease or chemical - like a kitchen

Halon 1301 (1211 - portable) above 10% and >900 degrees it degrades into hydrogen flouride, hydrogen bromide, and bromine which is toxic. For this reason, it has been replaced (via Environmental Protection Act of 1990) by FM-200 (Argon and Inergen are also options but not as effective). If a Halon system is in place - it CAN continue to be used, but extra measures must be taken when it is used

CCTV: Closed Circuit Television - Decrease Focal Length to widen view larger iris for less light areas

CPTED: Crime Prevention Through Environmental Design

Fences:
2 inch - Normal
1 inch - High
3/8 inch - Extremely high
Gauge - smaller gauge = bigger diameter (tougher fence)
3-4 feet - Deter casual
8 feet - deter determined

Piezoelectric: think kinetic energy - not really related but it was thrown in as a decoy answer and I didn't know what it was

Exterior Lighting: 2 feet of candlepower at 8 feet above the fence so as to blind intruders from seeing past the fence and illuminating them for the cameras

Glare Protection: pointing lights towards potential intruders and away from guards.

Fixed lighting = Fixed Iris as it doesn't need to adjust for changes in light

Classes of Gates:
1 - Residential
2 - Commercial
3 - Industrial
4 - Restricted

Static Electricity
1500 static volts can damage a HDD and as little as 10 static volts can damage some electrical components. Humans cannot perceive until 1500 and the typical scuff on the carpet produces closer to 12,000.

Access to Server room - the technician side of me says that admins need access. The CISSP side of me (needs to) says that the server room should be highly controlled. Admins should be able to do most everything remotely.

HVAC
Must have positive pressurization - meaning if the doors are opened - air rushes out as opposed to pulling dusty dirty air in.
Too much moisture = corrosion
Too little moisture = static electricity
Too much heat = over heat
Too little heat = slowed performance

Doors
Fail-safe - doors are open
Fail-secure - doors are closed with an emergency bar(or other method) to keep people from being trapped in

Locks
Warder < Pin&Tumbler
Bump key is cut to number 9 position (not sure what that means) - and allows the lock picker to bump the key while applying pressure to the lock to open the lock

Annunciation System verbally alerts guards so that they can take action

Bollard - blocks a vehicle from passing - usually metal or cement and arranged in a line of columns.

Mantrap is two sets of doors - like in castles where you go through two sets of gates and in between they are ready to poor boiling oil on you!!