Post-it notes: Chapter 1 – New Features in SQL Server 2008

November 10, 2012

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

Reporting Services Features SQL Server 2008

Compressed backups

  • No need for 3rd party tools such as Quest Litespeed.
  • It’s quicker (even though it’s compressing) as less data being written to disk.

Copy Only backups

  • One-off backup that does not affect transaction/differential backups.
  • Can’t be used as a base for tran/diff when restoring.

Change data Capture (CDC)

  • Captures inserts/updates/deletes into another table.
  • This other table is a mirror of the table it’s capturing with some extra columns summarizing nature of change.
  • We can then query this table directly or use Stored Proc’s or functions.
  • Only tracks changes in user tables.
  • select is_cdc_enabled from sys.databases.
  • exec sys.sp_cdc_enable_db.
  • Creates ‘cdc’ schema.
  • Requires SQL Agent. 2 cdc jobs (capture and cleanup).
  • CDC has automatic cleanup, default to 3 days, can be configured.

File Stream Data Types

  • Store all data on disk
  • Must enable first. GUI or stored proc sp_filestream_configure
  • File group must be added to db.
  • Limitations: no mirroring, no db snapshots and no native encryption

 Sparse Column support

  • Optimize storage of null columns
  • Reduces null storage space requirements at the expense of overhead to retrieve the null
  • Using sparse can save 20%-40%
  • Following column types cannot be sparse (geography, geometry, image, ntext, text, timestamp, user defined, filestream attribute)

Encryption Enhancements (TDE)

  • Transparent data encryption
  • Encrypts contents of your db
  • No changes to the application
  • Create master key, certificate then enable TDE – [alter db xxx set encryption on]

 High Availability

  • Improved mirroring
  • Hot add CPU/Memory (enterprise ed and special hardware req)
  • Failover clustering enhancements

Performance Data Management

  • Collects performance data over time
  • 3 components (warehouse DB, data collector, collection)

Resource Governor

  • Manage workload
  • Limit resources to a process
  • User sessions are mapped to workload groups based on classifier functions.
  • Classifier functions by IP, username or app name

Freeze Plan

  • Cached plan used for a query

 Declarative Management Framework (DMF)

  • Policy based system
  • Similar to windows, enforce rules & regulations
  • 3 components: Policies, Conditions, Facets

 Development Improvements

  • LINQ support, MERGE statement and Spatial data type (location based data)
  • SSIS enhancements, code in C# instead of vb.net (if desired)

Reporting Services

  • Support rich text
  • Sharepoint integration
  • IIS not required (http .sys listener)
  • Graphing
  • Export to word

 Depreciated and discontinued features

  • A whole bunch – look it up as required.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: