Upgrading to SQL Server 2005 Keith Burns Data

Upgrading to SQL Server 2005 Keith Burns Data Architect DPE, Microsoft UK

Agenda «How to upgrade «Reporting Services «Analysis Services «Data Transformation Services

Upgrading SQL Server Reporting Services

Why Upgrade SSRS? « UI Enhancements « Rich Client Printing « Enhanced Expression Editor « Multi-Value Parameters « Date Picker « Interactive Sort « Floating Headers « Enhanced Analysis Services Support « Graphical MDX designer « MDX Parameters « Server Aggregate Support « « « Tool Integration (Mgmt Studio/BI Dev Studio) Report Builder Report. Viewer Controls

Reporting Services 2005 Setup « Part of the standard installation « SQL Server 2005 RS setup in has two modes: « Default Configuration « Files Only Installation « Default configuration assumes « Default web site with newly created App Pool « Relational database engine installed at same time « Use service account for database connection « Configurations no longer included in setup « « « Remote RSDB (including SQL 2000) Scale-out (web farm) installation SMTP Server configuration « After 2005 setup, E-mail delivery is DISABLED

Demo Reporting Services 2005 Configuration Tool

Supported Deployments Single Server « Report Server and SQL Relational engine on same computer « Same or different instances Server Report Server Two Server SQL Relational Engine « « « Report Server on server 1 SQL relational engine on server 2 Addresses resource contention Server Report Server SQL Relational Engine RSDB Scale-out SW/HW NLB « Report Server on several servers « Report Servers share same RSDB « SQL relational engine on another computer* Server Report Server SQL Relational Engine Report Server RSDB Server Multi-instance (2005 Only) « Several Report Servers on a single server « Each has own RSDB « May use same SQL relational engine, can be remote « RS 2000 one instance allowed, always Default Server Report Server (Default Instance) Report Server (Named Instance) SQL Relational Engine RSDB 1 RSDB 2

Upgrade Process - UI Install prerequisites. Net Framework 2. 0 Select Features New Instance Existing Instance Select which features to upgrade Yes Install the default Configuration No Specify credentials used during upgrade Run Upgrade blocker checks Proceed with upgrade Most likely scenario for new install Probably a “migration” upgrade. Will require running RS Configuration afterwards

Upgrade Impacts « Existing reports continue to work « Existing applications continue to work « Existing data sources can be used for reports « RS 2005 can use SQL Server 2000 data sources (AS & SQL) « 3 rd party data sources do not need to change « WMI provider is changed « New namespace, much richer « Old namespace is not supported in RS 2005 « Changed location on disk C: Program FilesMicrosoft SQL ServerMSSQL. XReporting ServicesReport. Server

Upgrade Impacts: Existing Reports « RS 2000 RDL « Can be published to RS 2005 « RDL is not changed when setting/getting from server « Published Reports « Snapshot format is upgraded on the fly at first access « RS 2000 Report Designer continues to work « Requires Visual Studio. Net 2003 « Does not work on Visual Studio 2005 « VS. Net 2003 and VS 2005 run side-by-side « RS 2005 Report Designer « Requires Visual Studio 2005 IDE – included in SQL Server 2005 box « Upgrades RS 2000 RDLs on first load « Does not output RS 2000 RDLs

Migrating Report Server « Migrate to keep your production system running « Allows for a test deployment/roll out process « When to migrate: « You encounter an upgrade blocker « Large scale deployment « Uptime-requirement eg internet facing « Changing deployment « New hardware « Topology change (esp. if cannot upgrade Relational Engine)

Upgrade Blockers – RS « Customized Virtual Directories « Usually changed for Internet scenarios « Custom virtual directory name DOES NOT block upgrade « Custom extensions « Complexity of handling possible extension scenarios is high « One assembly, multiple assembly, folder structure assumptions compiled into extension, GAC, etc. « Did not have time to handle all possible cases « ASP. Net account stored encrypted in registry « Process. Model tag in Machine. config « Workaround is to unencrypt account for upgrade, then change back « Bug with detection logic « Must have Report Manager installed to upgrade to 2005

Upgrading Relational Engine « NOT REQUIRED to upgrade Reporting Services « Relational Engine upgrade changes: « « « Files on disk Database format Does not affect RSDB contents nor schema « If Report Server and SQL Relational Engine are in different instances: « You can upgrade Report Server without upgrading RE « You can upgrade RE without upgrading Report Server « If Report Server and SQL Relational Engine are in the same instance (Default): « Either upgrade RS and RE at same time « Or migrate RS to another computer/instance

How to Migrate Reporting Services Covered on the Upgrade DVD 1. Backup 4. a. Backup symmetric key b. Backup RSDB and RSTemp. DB c. Copy configuration files i. iii. iv. v. vi. Rsreportserver. config Rswebapplication. config Rssrvpolicy. config Rsmgrpolicy. config Rsreportserverservice. exe. config Web. config for both RS and RM d. Capture Virtual Directory settings b. c. 5. b. c. RSTemp. DB automatically upgraded d. Configure report server to use the upgraded RSDB Configure View. State encryption if in a scale-out Need to copy Delivery, Rendering, Data extensions Need to update and recompile Security extension Update code access security settings 1. 2. a. Create New Virtual Directories i. Web. config for RS and RM Reporting. Services. Service. exe. config Update and copy extensions a. 3. Run configuration tool Rsreportserver. config Rswebapplicaiton. config Rssrvpolicy. config Rsmgrpolicy. config Need to copy tracing settings (if customized) i. ii. a. Choose ‘Install but do not configure’ option for RS b. Set service accounts c. Upgrade the RSDB – use a COPY/Backup Can just copy over the report server config files i. iii. iv. 2. Install SQL Server 2005 i. Configure Virtual Directories to match previous configuration Migrate configuration settings a. 6. 7. 8. Rssrvpolicy. config Rsmgrpoclicy. config Restart the report server Windows service Test report server and report manager virtual directories Test your reports

Quick Discussion « Does this sound complex? « How many people are using RS today? « How many people do NOT have all RS components running on a single box? « How many people are not running a default RS install? « How many people have ALL their reports backed up in Visual Studio Projects?

Upgrading SQL Server Analysis Services

Upgrading to SSAS 2005 « Three options for moving to AS 2005 « Upgrade (in place) « Migration (side by side) « Redesign « For each option we will cover: « Techniques « Pros « Cons « Upgrade Advisor « Known Backward Compatibility Issues « Upgrading Client Applications

Upgrade Advisor Known Upgrade Issues « Run Upgrade Advisor before any Migration or Upgrade! « Discontinued: « Virtual cubes, virtual dimension, custom level formulas « Mostly replaced by other features « Linked measure groups, multiple hierarchies, MDX scripts « Deprecated: « ie may be discontinued in next release (after SQL 2005) « Calculated cells, cell evaluation list… « Behavioral changes in some features « Check Books Online for all upgrade issues and mitigations

Upgrade « Run Upgrade Advisor and evaluate results « Make modifications to AS 2000 database if necessary « Backup all databases and registry settings « Run Setup and choose to upgrade default instance « Process the database from Mgmt Studio « Upgrade only brings over metadata to AS 2005 « Upgrade reuses migration logic under the covers

Upgrade Pros and Cons « Pros « No new hardware required « Streamlined process that happens during setup « Cons « Data is unavailable until cubes are reprocessed « Side by side comparison with AS 2000 not possible « Tougher to troubleshoot upgrade issues « Doesn’t make best use of AS 2005 features

Migration « Run Upgrade Advisor and evaluate results « Make modifications to AS 2000 database if necessary « Backup all databases and registry settings « Install AS 2005 « If on same machine as AS 2000 must be named instance « On different machine preferable if AS 2000 is live « Run Migration Wizard (from SQL Management Studio) « Process the database from Mgmt Studio « Migration will only bring over metadata to AS 2005

Demo Analysis Services 2005 Migration Wizard

Migration Pros and Cons « Pros « Migration Wizard is fast and easy - very little additional overhead from upgrade « Supports migrating individual databases « Enables customer to move databases one by one through migrate process verify production « Side by side comparison with AS 2000 possible « Easier to troubleshoot upgrade issues « AS 2000 data is available to user during migration procedure « Cons « Doesn’t make best use of AS 2005 features

Upgrade/Migration logic « Upgrade/migration logic does best possible job while preserving compatibility with AS 2000 database « Query results, schema rowsets, unique names, etc. « Some objects/features are not migrated: « Drill through « Linked cubes and dependant objects « Remote partitions « However migrated database is not best practice for AS 2005 « Different from database redesigned using AS 2005 tools

Post Upgrade Modifications « Two options: « Connect to live database via BI Dev Studio and edit « Import live database into BI project, work offline and deploy to server « Use wizards and designers to add features: « KPIs « Perspectives « Translations « MDX Script « Drill through and report actions

Redesign Database « Alternative to Upgrade and Migration « Use BI Dev Studio wizards and designers « DSV wizard & designer - model relational schemas, views, calculated columns « Dimension wizard & designer « Cube wizard & designers « BI wizard for adding calcs – time intelligence, account intelligence, etc. « Mining model wizard & designer

Redesign Database Pros and Cons « Pros « Produces best practice design « « « Multiple hierarchies & attributes Multiple measure groups & perspectives Richer relationships – many to many, referenced MDX Script – central place for all calcs Single mining structure with multiple models « Produces more maintainable design than migration logic « Opportunity to revisit DW design, enhance the application « Cons « Higher upfront cost, amortized over time

Upgrading Client Apps « End user apps « eg Excel, Proclarity, Panorams, Custom apps. . « How To Upgrade « Install MSXML 6 and AS 2005 OLEDB provider from SQL 2005 Feature Pack (www. microsoft. com/downloads) « Compatible with PTS 8 – most end user apps should “just work” after upgrading the server « No upgrade/install required for ADOMD. NET « Optionally install new version of ADOMD. NET from SQL 2005 Feature Pack for enhanced object model (for new apps) « Admin applications « Eg Analysis Manager, DTS 2000 packages, custom DSO app (e. g. parallel processing tool) « Use DSO (Decision Support Objects) to communicate with AS 2000 server « How to upgrade « Install Management Objects Collection (contains AMO) from SQL 2005 Feature Pack « Install Backward Compatibility Components (contains DSO 9) from SQL 2005 Feature Pack

Upgrading Data Transformation Services

Why Upgrade to SSIS « DTS - SQL Server 7. 0 « “Visual BCP” – a useful utility « DTS - SQL Server 2000 « Easy (but slow) workflow & transform engine « Customizable « SSIS – SQL Server 2005 « A completely new codebase « Enterprise class ETL « Exceptional BI integration – and more « Rich APIs and extensibility

Local Server Upgrade « Tools install includes DTS 2000 runtime. « DTS packages on server, file system are untouched by upgrade. « DTS packages on upgraded SQL 2005 server remain available for execution and maintenance. « DTS agent jobs run without interruption. « Apps written to DTS API continue to work. « Server upgrade removes SQL Server 2000 Admin Tools. « If you need legacy tools, install 2000 tools alongside 2005 tools. « Repository support withdrawn.

Maintaining DTS 2000 Packages After an Upgrade « Use SQL Server 2005 Management Studio to manage DTS packages on both 2000 and 2005 servers. « Access via Legacy node in Object Explorer « Legacy UI components don’t ship with 2005 « Download DTS 2000 Designer Components http: //www. microsoft. com/downloads/details. aspx? Family. ID =d 09 c 1 d 60 -a 13 c-4479 -9 b 91 -9 e 8 b 9 d 835 cdc&Display. Lang=en http: //www. microsoft. com/downloads/details. aspx? Family. ID=d 09 c 1 d 60 -a 13 c-4479 -9 b 91 -9 e 8 b 9 d 835 cdc&Display. Lang=en

Demo Maintain DTS 2000 packages with 2005 Tools

Support for Migration « Execute DTS 2000 Package Task « Migration Wizard « Upgrade Advisor

Execute DTS 2000 Package Task « Use the Execute DTS 2000 Package task to invoke DTS packages from within an SSIS 2005 package. « Wrap legacy packages to take advantage of improved SSIS: « Looping and sequencing constructs « Package security « Event handling, restartabililty « SSIS Service package enumeration and management « Embed legacy package in 2005 package for easier deployment. « Note – 32 -bit only

Demo Execute DTS 2000 packages from SSIS 2005

Package Migration Wizard « “Best Effort” migration « Creates new SSIS package; leaves original in place. « Migrate what we can « Variables and control flow « Most tasks « Wrap what we can not « Complex Data Transformation Tasks « AS Tasks « Some constructs not supported « Transactions « Dynamic Property Task « Access to old API; script on step

Package Migration Cases « Workflow « Simple Data Transformation « Complex Data Transformation « Self-modifying packages « Dynamic Properties « Nested packages « Custom Task Complexity

Workflow Packages « Most tasks migrate seamlessly. « « « « « FTP Execute Process Execute SQL Bulk Insert Copy Objects Send Mail Execute Package MSMQ Transfer Tasks « Control Flow and Precedents migrate without issue. « Global Variables too. « Active X Script task may require fix-up if referencing old API.

Simple Data Transformation « DTS Data Transformation and SSIS Data Flow object models don’t map 1 -1. « Goal: Migrate all packages created by the Import/Export Wizard any others with equivalent functionality.

Complex Data Transformation « Where migration not supported, migration wraps original task in an Execute DTS 2000 Package Task. « Script or other transforms « Multiphase pump, lookup, etc. « DDQ, or parallel data pump. « Redesign with SSIS 2005 transformations for performance « Add error handling for reliability

Self-Modifying Packages « DTS had limited control flow constructs. « Basic looping required altering step status values in a running package. « SSIS limits scope of changes to current task, but provides superior control flow constructs. « After migration, script that accessed legacy API will fail. « Update migrated packages to replace such script with new control flow.

Dynamic Properties Task « The purpose of the Dynamic Properties Task is to navigate the legacy API and update properties of the running package. « Migration wizard replaces the Dynamic Properties task with a placeholder. « In SSIS, use configurations and property expressions to do the work done previously with the Dynamic Properties Task. « Update migrated packages to use these features.

Nested Packages « Migration replaces 2000 Execute Package Task with a 2005 Execute 2000 Package Task. « New parent package continues to point at the originally invoked 2000 child package. « Every time a child package is migrated, a change to parent package is required to bring the new 2005 package into the control flow. « Also applies where child package is invoked from script.

Custom Tasks « Custom tasks are wrapped in the migrated package. They will continue to run. « Consider porting custom task to any. Net language. « No more threading issues. « Easier API, development, maintenance. « Take immediate advantage of property expressions, logging, events, etc. « Replace old custom tasks with new ones in migrated packages.

Demo Using the Upgrade Wizard to upgrade DTS packages to SSIS

DTS Recommendations « DTS API is deprecated in this release. « Complete migration to SSIS before next release of SQL Server. « Limit further investment in DTS 2000 packages. « Migrate quickly where complexity, stability, performance, of packages are low. « Rewrite where complexity, performance, features justify. « E. g. order of magnitude differences in script, lookup performance.