Tech4Him – Technology with Integrity

On Call Duties

TJ Belt

http://tjaybelt.blogspot.com
http://www.twitter.com/tjaybelt

You are on-call, maybe middle of the night and you get a call and there’s a problem. What do you do?

• What tasks were last performed by what persons?
• Start documenting incidents if you don’t already. Great for helping resolve things later down the road.
• Document, document, document

Items to be sure of and continuously check

• Scan OS error events
• Check on backups
• System specific output files (xml, reports, files)
• Log and data file sizes
• Space available / Free space on drives
• Replication health
• SQL Server logs
• Other notes of interest

Work to get your logs clean over time so that problems become more apparent and you will spend less time chasing red herrings.

“You only need one. Either a good backup or a good resume.”

Only notify on error, not on success. (Of course there may be exceptions)

• What are Microsoft SQL Server operators and how to define them (Part 1)
• Undocumented SQL Server 2005 Database Maintenance Plan Stored Procedures
• DB2 and the Procedural DBA, Part 1

Dimension Table ETL with SSIS

Erik Veerman
Mentor, Solid Quality Mentors
SQL Server MVP

Dimension tables contain the “text descriptors” or attributes. Also organized as hierarchies of categories, levels and members.

Slowly Changing Dimension (Ralph Kimball)

• Type 0:No change (Fixed Attribute)
• Type 1:The value is overwritten (Changing Attribute)
• Type 2:Slow changes. A new row is added (Historical Attribute)

Type 2 – New surrogate key and Start Time/End Time updates so to add the new record.

Inferred Members

• Missing dimension member during Fact load later becomes available in the Dimension source
• Also known as late arriving dimensions
• Fact load, placeholder record added to dimension table with new surrogate key generated
• Dimension process, for inferred members, all dimension attribute columns are updated with new values (type 2 attributes are handled as type 1)

Additional SSWUG Articles

• Troubleshooting SQL Server 2005 Integration Services
• MSDN Webcast Summary: Introducing SQL Server Integration Services for SQL Server 2005
• Tips for using SQL Server 2005 Integration Services (Part 3)

Fact Table ETL with SSIS

Erik Veerman
Mentor, Solid Quality Mentors
SQL Server MVP

Identify Dimension Surrogate Keys

• Locate dimension candidate keys in transaction source
• Validate uniqueness and data integrity
• Considering historical changes
  • Identifying current dimension record, combine business keys with current identifying meta data (data identifier or boolean)
  • Handle missing dimension records
• SSIS Data Flow Options
  • Lookup transformation
  • Merge Join transformation
  • Fuzzy Lookup transformation
• SSIS Control Flow Option
  • Execute SQL Task

• Fact ETL involves surrogate keys, grain changes, and measure calculations
• SSIS handles these through several out-of-the-box transformations

Loading a Data Warehouse with SSIS

Brian Knight
bknight@pragmaticworks.com
CEO, Pragmatic Works

Why Data Warehouse?

• Get users off of transactional systems
• Keeping more data online
• Easier reporting
• Consolidate data sources

Data Separated into fact and dimension tables

• Dimension tables answer the pivot or where clause
  • Make as wide and descriptive as possible
  • Surrogate keys operate as unique ID for each row
  • Keep surrogate keys as small as possible
• Fact tables answer the what or select statement
  • Intersect all dimension tables
  • Surrogate keys from each dimension in this table
  • Measures are the “what” like Price, Quantity, Duration

Additional Articles

• MSDN Webcast Summary: Introducing SQL Server Integration Services for SQL Server 2005
• SQL Server 2008 Katmai Change Data Capture (CDC)
• Troubleshooting SQL Server 2005 Integration Services

• http://Blogs.pragmaticworks.com/brian_knight
• http://www.twitter.com/brianknight

Live – SQL Server Analysis Services – Real World Performance Tuning (4hrs)

Ted Malone

(I only caught a few minutes here and there of this marathon live session, but the parts I caught were good. We’ll have to watch the on demand once it goes up.)

• Recommends looking at a product called Performance Advisor from SQL Century for getting performance insight into SSAS.

DBA Checklist

Buck Woody, Microsoft SQL Server Specialist

http://blogs.msdn.com/buckwoody
http://www.informit.com/guides/guide.aspx?g=sqlserver
http://edge.technet.com/People/BuckWoody/

SSMS

• Object Explorer: Right click to see additional columns including Last Backup.
• Standard Reports available via right click
• Log Viewer

oSQL or SQLCMD

PowerShell – Latest and greatest

Post Installation

Daily, Weekly, Monthly and Yearly

Recommends being alerted of both error AND success in order to know that maintenance task really did run.

Maintenance tasks

• Do not run the auto-shrink option in your tasks.

Take a baseline with PerfMon. Email him for a list of counters.

Keep a SQL Server run book containing all configuration changes.

Popularity: 2% [?]