Transcript Upgrading to SQL Server 2005

Upgrading to SQL Server 2005
Keith Burns Data Architect DPE
Agenda
How to upgrade
– Reporting Services
– Analysis Services
– Data Transformation Services
Upgrading
SQL Server Reporting Services
Reporting Services 2005 Setup
Part of the standard installation
Setup 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
Upgrade Process - UI
Install prerequisites
.Net Framework 2.0
Select Features
New
Instance
Existing
Instance
Select 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)
– 3rd 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 Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer
Upgrade Impacts: Existing Reports
2000 RDL (Report Definition Language)
– 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
Report Designer
– 2000
– Requires Visual Studio .Net 2003
– Does not work on Visual Studio 2005
– VS .Net 2003 and VS 2005 run side-by-side
– 2005
– Requires Visual Studio 2005 IDE – included in SQL Server 2005 box
– Upgrades RS 2000 RDLs on first load
– Does not output RS 2000 RDLs
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
Upgrading
SQL Server Analysis Services
Analysis Services Upgrade Options
1. Upgrade
– Just run the installation and chose to upgrade the existing instance.
– Runs the migration process under the hood
– Reuse of existing hardware and IP addresses etc
2. Migration
– Supports migrating individual databases
–
Enables customer to move databases one by one through migrate  process  verify 
production
– Side by side comparison with AS2000 possible
– Easier to troubleshoot upgrade issues
– AS2000 data is available to user during migration procedure
3. Redesign
– Makes best use of new features
– Takes resources to redeploy
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 SQL2005)
– Calculated cells, cell evaluation list…
Behavioral changes in some features
Check Books Online for all upgrade issues and
mitigations
Upgrade/Migration logic
Upgrade/migration logic does best possible job
while preserving compatibility with AS2000
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 AS2005
– Different from database redesigned using AS2005 tools
Demo
Analysis Services 2005 Migration
Wizard
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
Upgrading
Data Transformation Services
DTS Options
1. Use the DTS Runtime to run packages “as is”
– 100% compatibility
– 0% future proof
2. Incorporate DTS package into a larger SSiS package
– Provides an option to “enhance” a DTS package
3. Use the migration Wizard to convert to SSiS package
– Wizard may well resort to option 2.
4. Rewrite package using SSiS
Maintaining DTS 2000 Packages After an Upgrade
Tools install includes DTS 2000
runtime
Existing packages execute as before
ie running jobs still execute!
Repository support removed
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?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
Demo
Maintain DTS 2000 packages
with 2005 Tools
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, restartability
– 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
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.
Thank you for attending this TechNet Event
http://www.microsoft.com/uk/technet
PS (The evaluation form is now sent out electronically
with your thank you e-mail. This can take up to 5
working days. Please do feedback as we read all the
comments and use them to shape future event content)