Learning Center
Plans & pricing Sign in
Sign Out

Upgrading to SQL Server 2005 - PowerPoint


to SQL Server 2005

강민석 과장(
한국마이크로소프트 기술지원부
PFE (Premier Field Engineering)
Why Upgrade ?
                     Higher Availability & Scalability
Enterprise Data
                     Stronger Security
                     Simplified Manageability

                     Better Insight & Deeper Analytics
   Business          Richer End User Analysis &
 Intelligence         Reporting
                     Stronger MS Office integration

                      Faster Application Development
  Developer           Simplified Application lifecycle
 Productivity          management
                      Enhanced Interoperability

                      Simplicity and Best Price / Performance
#1- Good Enough
Higher Availability & Security             50-75% code reduction for most scenarios
     35% faster transaction processing
     5x faster failover enabling 99.999%   “…already seeing 70 percent faster responses with
     availability                          XML data types, and has found it up to 90 percent
     Native data encryption                faster to create XML-based applications.”

Greater Visibility & Deeper Analysis
     Up to 2x performance increase         “Moving to Integration Services for our enterprise
     with large OLAP dimensions            ETL needs was the right decision—we were able to
     Rich End User Analysis &              immediately capitalize on the team development
                                           features and rapid learning curve…”
Enhanced Developer Productivity
     Up to 40% faster development time     “…we could create reports about 50 percent faster
     with VS and .NET integration          than we could using Crystal Reports or our other
     New Cachesync simplifies web          reporting tools”

                       Unmatched Performance and Low TCO
Why Upgrade?
Database Engine                           Database Maintenance                     Replication
     Service Broker                           Backup and Restore Enhancements         Seamless DDL replication
     HTTP Access                              Checksum Integrity Checks               Merge Web Sync
     Database Tuning Advisor                  Dedicated Administrator Connection      Oracle Publication
     Enhanced Read ahead & scan               Dynamic Configuration AWE               Peer to Peer Transactional replication
     Indexes                                  Highly-available Upgrade
                           with Included Columns                                        Merge replication perf and scalability
     Multiple Active Result Sets              Online Index Operations                 New monitor and improved UI
     Persisted Computed Columns               Online Restore                     Analysis Services and Data Mining
     Try/Catch in T-SQL statements       Management Tools                              Analysis Management Objects
     Common Table Expressions                 MDX & XML/A Query Editor                Windows Integrated Backup and
     Server Events                            Maintenance Plan Designer              Restore
     Snapshot Isolation Level                 Source Control Support                  Web Service/XML for Analysis
     Partitioning                             Profiler access to non-sa               Integration Services and DM Integration
     Synonyms                                 SQLCMD Command Line Tool                Eight new Data Mining algorithms
     Dynamic Management Views                 Database Mail                           Auto Packaging and Deployment
.NET Framework                            Performance Tuning                            Migration Wizard
     Common Language Runtime Integration  Profiling Analysis Services            Integration Services
     CLR-based Types, Functions, & Triggers Exportable Showplan & Deadlocks           New high performance architecture
     SQL Server .NET Data Provider            Profiler Enhancements                   Visual design and debugging
Data Types                                     New Trace Events                       environment
     CLR-based Data Types                Full-text Search                              Extensible with custom code and
     VARCHAR(MAX), VARBINARY(MAX)             Backup/Restore includes FT catalogs     XML task and data source
     XML Datatype                             Multi-instance service                  SAP connectivity
Database Failure and Redundancy           SQL Client .NET Data Provider                 Integrated data cleansing & text mining
     Fail-over Clustering (up to 8 node)      Server Cursor Support                   Slowly changing dimension wizard
     Database Mirroring                       Multiple Active Result Sets             Improved flow control
     Database Snapshots                  Security                                      Integration with other BI products
     Enhanced Multi-instance Support          Catalog and meta-data security     Reporting Services
XML                                            Password policy enforcement             Report Builder
     New XML data type                        Fine Grain Administration Rights        Analysis Services Query Designer
     XML Indexes                              Separation of Users and Schema          Enhanced Expression Editor
     XQUERY Support                           Surface Area Configuration              Multi-valued Parameters
     XML Schema (XSD) support            Notification Services                         Date Picker
     FOR XML PATH                             Embed NS in existing application        Sharepoint Web Parts
     XML Data Manipulation Language           User-defined match logic                Floating Headers
     SQLXML 4.0                               Analysis Services Event Provider        Custom Report Items
   Upgrade (or in-place upgrade):
   Updates an existing installation while preserving
    user data
   Instance name remains the same after upgrade
   Automated process

 Migration (or side-by-side migration):
   Starts with a new installation
   New & old instance reside side-by-side
   Objects are copied from the old to new instance
   Mostly a manual process
In-place Upgrade
SQL Server 7.0/2000             SQL Server 2005
   Instance: foo                  Instance: foo

Side-by-Side Migration

SQL Server 7.0/2000                           SQL Server 2005
   Instance: foo                               Instance: bar

What can be upgraded or
 Versions
    SQL Server 7.0 (latest service pack)
    SQL Server 2000 (latest service pack)
 Components
    Database Engine
          Includes sub-components like SQL Agent, Full-text, Tools, etc.
      Analysis Services
      Reporting Services
      Notification Services
      Migration of Data Transformation Services to Integration Services
 Editions
    Desktop, Workgroup, Personal, Standard, Developer, Enterprise
 Platforms
    32-bit & 64-bit (IA64 and x64)
 Languages
    All SQL Server 7.0 and SQL Server 2000 released languages
Upgrade Plan
 Smooth upgrade requires a good plan
 Devise an upgrade plan by breaking
  down upgrade tasks into,
    1. Pre-upgrade tasks
    2. Upgrade execution tasks
    3. Post-upgrade tasks
 Exercise the plan
 Execute the plan
Prepare Your Environment
 Study SQL Server 2005 minimum hardware
  & software requirements
 Get an inventory of your applications &
  legacy systems
   Releases, Components, SKU’s, Platforms
 Opt for the same or a compatible edition
   Check features in each SQL Server 2005 SKU
   Beware of cross-SKU upgrade matrix
 Run Upgrade Advisor
 Examine Upgrade Advisor report
 Fix or work around the backward
  compatibility issues
 Record benchmarks
Backward Compatibility
 Some features are discontinued:
    They do not appear in SQL Server 2005
    Example: Undocumented system stored procedures, Virtual
     cube, Virtual dimension…
 Some are being deprecated:
    They won’t be supported in the release following SQL
     Server 2005
    Example: SQL Mail, Calculated Cell, Cell evaluation list…
 Some features have a different behavior
    Example: Database Engine catalog security
 Some editions have a different feature set
    Example: Express does not have SQL Server Agent
 Check Books Online for a full list as well as
  replacements and techniques
 Run Upgrade Advisor before any migration or
Upgrade Advisor Tool
 Improves the SQL Server 2005 upgrade experience
 Avoids Surprises during or after upgrade
 Analyzes SQL Server 2000 and SQL 7.0 instances
 Analyzes live servers, scripts and trace files
 Performs read-only operation
 Provides a report for detected issues
 Presents guidance on when detected issues need to
  be fixed
 Describes how to fix or work around issues
 Links to documentation for additional content
 Available on the media and to download from

                   Ready for upgrade

 Upgrade Advisor               Upgrade Advisor
Upgrade Advisor
Upgrade Execution
 Choose an in-place upgrade or migration
  strategy for each component
 Understand upgrade and migration tools for
 Check database consistency
 Back up old instance
   Verify the backup
 Perform upgrade
 Monitor upgrade progress
 Execute your upgrade testing
 Be prepared to execute your back out plan
In-place Upgrade vs.
Migration   Before                               After

                SQL Server 7.0/2000              SQL Server 2005


                                                SQL Server 7.0/2000
                SQL Server 7.0/2000


                                                  SQL Server 2005
Pros & Cons of Migration
 Pros
   Migration provides more granular control over the
    upgrade process
   Having new and old instances side-by-side helps
    with testing & verification
   Legacy instance remains online during migration
   Flexibility to implement migration with failover
 Cons
   May require new or additional hardware
   Applications need to be directed to new instance
Pros & Cons of Upgrade
 Pros
   Easier, faster, less headache for small systems
   Requires no additional hardware
   Applications remain pointing to old instance
 Cons
   Less granular control over upgrade process
   Instance remains offline during part of upgrade
   Not best practice for all components
     Analysis Services cubes are recommended to be
Upgrade & Migration Tools
 Setup is in-place upgrade tool
   Database Engine
   Analysis Services
   Reporting Services
 Wizards for side-by-side migration
   Analysis Services
   DTS to Integration Services
 Specific configuration tools for migration
   Reporting Services
   Notification Services
 Upgrade Advisor is upgrade analysis tool for
  all components
Upgrading Database Engine
 Setup.exe performs the in-place
 Several migration techniques for
  Database Engine:
   Detach/Attach
   Backup/Restore
   Copy Database Wizard, DTS
   Manual scripts, BCP
Upgrading Analysis
 Choose one of the upgrade techniques
  1. Setup.exe for in-place upgrade
  2. Migration
  3. Migration ++
       Install a new instance
       Import as project
       Add new SQL Server 2005 features
  4. Redesign from scratch
Upgrading Reporting
 Reporting Services 2005 supports both
  upgrade and migration from Reporting
  Services 2000
 Migration tool
   Reporting Services Configuration tool
 Upgrade will work unless server is not
  configured with default configuration
Data Transformation
 DTS Packages will continue to run
  alongside with SQL Server 2005
  Integration Services (SSIS)
 SSIS is not the next version of DTS –
  SSIS is first version of a whole new
 Packages can be migrated to SSIS via
  DTS Migration Wizard
 Migration wizard copies DTS packages
  and then re-creates them in SSIS
   Migration is not perfect -- “Best Effort”
Upgrading Legacy SQL
Server Tools
 SQL Server Agent has a new security
  model (proxy account)
 Registered Servers are automatically
  upgraded to Management Studio
 Database Diagram are upgraded into a
  new format
 Database maintenance plans are
   Use new maintenance plan
In-place Upgrade

                                SQL Server 2005

              Upgrade Advisor
In-place Upgrade Using
 Perform post-upgrade tasks on
  Upgrade Advisor report
   Example: Update statistics, build cubes
   DTS to SSIS migration
   Change database COMPATIBLITY level
 Reconfigure log shipping
 Run upgrade tests
 Verify Agent jobs and maintenance
 Bring system online
 Monitor system activity
Upgrade Internals
1.  Install setup prerequisites
2.  Check for upgrade blockers
3.  Install new SQL Server 2005 binaries
4.  Stop SQL2K Bits
5.  Point service to new bits
6.  Start service in single user mode
7.  Attach resource database
8.  Stop service
9.  Restart service
10. Start updating all databases
11. Execute Replication and SQL Agent upgrade
12. Uninstall old binaries
System Availability
                                          Old instance fully
1.  Install setup prerequisites
2.  Check for upgrade blockers
3.  Install new SQL Server 2005
4. Stop SQL2K Bits               Old instance not available
5. Point service to new bits
6. Start service in single user           Point of no return
7. Attach resource database
8. Stop service
9. Restart service
                                          Partial availability
10. Start updating all databases              starts here
11. Execute Replication and SQL
    Agent upgrade scripts
                                        New instance fully
12. Uninstall old binaries                  available
Upgrade Tips
 Remote upgrade
 Upgrade of clustered systems
 Reduce upgrade down time
   Pre-install Setup pre-requisites
     Microsoft .NET Framework 2.0
     Microsoft SQL Native Client
     Setup support files
 Upgrade of 64-bit systems
 Migrate DTS packages to Integration
More Tips
 When using the Copy Database Wizard to
  upgrade a database, adhere to the following
   Before upgrade, place the database in single user
   make sure that no applications or services are
    trying to access the database
   Do not use read-only mode -- this will result in
    an error
   Cannot rename the database during this operation
 To reduce surface area of SQL Server 2005
  some services and features are switched off
  on new installs
   Use Surface Area Configuration tools
Upgrading Log Shipping
 Do not upgrade SQL Server 2000 log
  shipping configuration to SQL Server 2005
   Database Maintenance Plan Wizard is not used in
    SQL Server 2005 Log Shipping
 Use migration techniques:
   Migrating with failover
     Maintains availability during upgrade of each server in
      the log shipping configuration
     Requires that SQL Server 2000 log shipping
      configuration be configured to allow failover
   Migrating without failover
     Simpler procedure
     Does not require to fail over to your secondary server
     Log shipping database is unavailable during upgrade of
       the primary server
Related Content
Breakout Sessions/Chalk Talks
  DB300 Best Practices and Lessons Learned Working with SQL
   Server 2005 TAP Customers and MSIT
  DB106 Upgrading to SQL Server 2005 - Why Should Customers
  DB103 How to Select the Best SQL Server SKUs and Licenses for
   Your Customers: When to Lead with What
  DB200 Microsoft Business Intelligence - The All Up Technical Story
  DB312 Demonstrating the New Management Tools in SQL Server

Hands-on Labs
  DBHOL001 SQL Server Management Studio
  DBHOL003 T-SQL Enhancements
  DBHOL009 Integration Services
  DBHOL010 Analysis Services
  DBHOL012 Reporting Services
Please Complete An Evaluation
Your Input Is Important!
Two ways to access Online Evaluation Forms:
1. CommNet stations located throughout conference
2. From any wired or wireless connection to:

For more information please refer to your Pocket Guide

To top