Tech4Him – Technology with Integrity

Below I am posting some of my notes from the various sessions, along with comments, observations and occasional odd humor.

The Accidental Business Intelligence Administrator

This session is designed for SQL Server DBAs who find themselves having to support SQL Server Business Intelligence projects using Reporting Services, Integration Services or Analysis Services. We’ll talk about the lifecycle of projects using these tools, how they impact your SQL Server infrastructure, and what adaptations you might need to make as a production DBA in order to maintain and secure them. Along the way, you might just learn what all those “other” services do that are listed in the SQL Server Configuration Manager.

Presenter: Chris Randall (crandall@ameriteach.com)

http://blogs.ameriteach.com/chris-randall

What is SQL Server Business Intelligence? “a method of storing and presenting your key enterprise data so that anyone in your company can quickly and easily ask questions of accurate and timely data.”

Microsoft end-to-end BI offering slide.

Components of SQL BI

• Integration Services
• Analysis Services
• Reporting Services
• Sharepoint?

SQL BI Administrative Tasks

• Install
• Configure
• Deploy and Store
• Secure
• Backup
• Monitor

Administering Integration Services

• Lifecycle
• Installation
• Configuration
• Security
• Backup
• Monitor

What is Integration Services

• Platform to extract, transform, load (ETL) applications
• Unit of  deployment is a .DTSX text file (XML)
• Contains Control Flow (execution sequence) and Data Flow (pump rows from source to destination) engines
• Allows for vast assortment of operations to be performed within a package.

As an administrator, anytime you here a developer say “I’m going to pull data from…” you need to immediately think if the tools for the source are installed on the server, not just the developer desktop.

Integration Services Lifecycle

• Develop Packages
  • Import/export wizard
  • Visual Studio/BIDS
• Deploy and Store
  • File System
  • MSDB
• Execute
  • SQL Agent Job, DTEXEC.EXE

For file system deployment there is a managed “magic” folder. But you can deploy to anywhere in the file system.

C:\Microsoft SQL Server\100\DTS\Packages\

Installing SSIS

• Optional Component of SQL Server install
• Only one instance per server
• Windows Service – management, not execution
• Roles added to MSDB
• Managed folder in SQL installation path
• Runtime, development files

Configuring SSIS

• Set it and forget it…up to a pint!
• Most configuration is package specific
• Develop best practices for storage, security, logging
  • For file system package storage, ser up NTFS permissions
  • For MSDB storage, determine access, growth needed

Securing SSIS

• Security at runtime is most common problem in production
• SQL Agent may not have appropriate rights
  • “It works on my machine!”
• Work with BI devs to standardize package encryption, storage locations
• For MSDB storage, roles provide access controls.

Backing Up SSIS

• Things to Backup:
• Developer source
• File and folders in the fs deployment
• scripts, configuration files
• MSDB for SQL Server deployment
• don’t forget that MSDB only stores packages
• supporting files are in the file system

Monitoring SSIS

• Logging Logging logging
• Limited utility of running Packages node in SSMS
• Logging Logging logging
• Some perfmon counters

Work with developer for multiple logging modes (debugging versus production run)

Easily manage file system package folders and packages via SSMS.

dbo.sysssispackages table stores the package info. Remember, the old name for DTS was dbo.sysdtspackages. Use the new one!

Administering Analysis Services

• Platform for creating multidimensional data repositories for querying
• Pre-calculates and stores aggregated data
• Presents to client application in format for flexible modeling and analysis (Excel Pivot Tables, for exmaple)
• Also includes Data Mining Engine and tools for predictive analysis

Analysis Services Lifecycle

• Develop in BIDS
  • SSAS “Database” is a unit of deployment
  • Not a relational database – Think of it as a project
• Deploy via BIDS, Deployment wizard or XMLA script
• Data can be stored in File system, Relational DMBS, or some combination
• Hosted, reprocessed, served up by SSAS engine

Installing SSAS

• Optional component of SQL Server install
• Supports multiple instances
• Windows service handles management and execution
• Data files may be in SQL Server file path

Configuring SSAS

• New install is empty. Needs databases deployed, user accounts created for use
• Like SQL, no access to no-administrators by efault
• Wealth of tuning options within the management tools

Securing SSAS

• Role based security
• Need permissions to connect. Permissions set at database,  per cube, and further down the object hierarchy
• Windows Authentication only
• Built-in RunAs functionality in BIDS, SSMS for testing.

Backing up SSAS

• What to Backup
  • Deployed database metdata (designs)
  • Security roles
  • Server configuration files
  • Logs?

Data stored mostly in the following folder:
C:\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data

Whew! Slow down. You can tell he’s starting to run out of time. How do I keep up taking notes and listening well.

What is SQL Reporting Services

• Provides traditional and interactive reporting
• Web hosting engine
• Provides reports in a number of output rendering formats
• Native mode with own web portal for viewing and management
• Sharepoint integrated mode

SSRS LifeCycle

• Develop
  • BIDS, Report Designer, Report builder
  • RDL, RDS, SMDL XML text files are produced
• Deploy and store
  • Stored in content database hosted in SQL Server
• Deploy via BIDS, rs.exe, Report manager/Sharepoint
• Manage and secure
• Consume via portal, web pages, embedded controls

Installing SSRS

• Optional feature in SQL Server setpu
  • Files only install or install and configure setup
• Multiple instance support
• Mix of Windows and Web Services in 2009

Configuring SSRS 2008

• Configuration tool.
• 2008 simplified configuration from 2005

Securing SSRS

• Role based security (if native mode)
  • Default roles installed
• Groups/Users
  • Windows/Active Directory
• Sharepoint Integrated
• SSL
• Auditing Report Access
• Data sources

Backing Up SSRS

• Report Server Database (two of them)
• Encryption Keys
• Sharepoint DB
• Custom Extensions
• Configuration files
• RSTempDB
• IIS Settings for RS 2005
• RDLs
• SSL Certificates

Whew! That was a whirl wind of a session. Tough to take all the notes but a good overview. Presenter was good a getting the information to us.

Popularity: 5% [?]