Transcript Upgrading SQL Server Rob Carrol, Microsoft January 29, 2009 Why Upgrade? Upgrading SQL Server January 29, 2009 Support for SQL Server 2000 “A recent survey.

Upgrading SQL Server
Rob Carrol, Microsoft
January 29, 2009
Why Upgrade?
Upgrading SQL Server
January 29, 2009
Support for SQL Server 2000
“A recent survey of SQL Server Magazine readers
showed that 81 percent are still on SQL Server
2000 and only 40 percent are using SQL Server
2005.”
SQL Server 2000 Mainstream Support ENDED 8th
April 2008
Upgrading SQL Server
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Transparent Data Encryption
External Key Management
Data Auditing
Pluggable CPU
Transparent Failover for Database
Mirroring
Policy Management
Server Group Management
Streamlined Installation
Enterprise System Management
Performance Data Collection
System Analysis
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model
LINQ
Visual Entity Designer
Entity Aware Adapters
SQL Server Change Tracking
Synchronized Programming Model
Visual Studio Support
SQL Server Conflict Detection
FILESTREAM data type
Integrated Full Text Search
Sparse Columns
Large User Defined Types
Date/Time Data Type
LOCATION data type
SPATIAL data type
January 29, 2009
Enterprise Reporting
Engine
Internet Report
Deployment
Block Computations
Scale out Analysis
BI Platform Management
Export to Word and Excel
Author reports in Word
and Excel
Virtual Earth Integration
Report Builder
Enhancements
Partitioned Table Parallelism
TABLIX
Query Optimizations
Rich Formatted Data
Persistent Lookups
Personalized
Perspectives
Change Data Capture
Backup Compression
MERGE SQL Statement
Data Profiling
… and more
Upgrading SQL Server
Supported Upgrade Paths
Full list of supported upgrade paths available in BOL:
http://msdn.microsoft.com/en-us/library/ms143393.aspx
January 29, 2009
Upgrading SQL Server
January 29, 2009
In Place Upgrade
Pros
Cons
• Easier, mostly automated
• Generally fast overall
process
• Requires no additional
hardware
• Applications remain
pointing to same
server/database name
• Less granular control over
upgrade process
• Instance remains offline
during part of upgrade
• Not best practice for all
components
• Complex rollback strategy
Upgrading SQL Server
January 29, 2009
Side by Side Upgrade
Pros
• More granular control over
upgrade process
• Can be used to perform test
migration
• Ability to run systems side-byside for testing and verification
• Relatively straightforward
rollback Strategy
• Can leverage
failover/switchover to reduce
downtime
Cons
• Usually require additional
hardware
• Server/database name
changes
• Not practical for VLDB unless
utilizing SAN
Upgrade tools
Upgrading SQL Server
January 29, 2009
Upgrade Advisor
–Analyzes SQL Server 2000/2005 components, databases,
trace files and script files
–Read-only operation, can be CPU intensive
–Supports remote execution
–Supports default and named instance
–Requires .NET framework 2.0 sp1 or later
–Download or available with SQL2008 Media
Upgrading SQL Server
January 29, 2009
Upgrade Assistant (Application Compatibility
Testing)
• Automated ‘playback’ / simulation tool
• Developed in conjunction with Scalability Experts
• Available for Testing upgrades to 2005 & 2008
• ACT for SQL Server 2008 available here:
– http://ssua.scalabilityexperts.com/
Upgrading SQL Server
January 29, 2009
Best Practice Analyzer (SQL BPA)
• Run the SQL Server Best Practices Analyzer (BPA) against your current legacy
instances of SQL Server
• If bad or questionable practices exist, you could address them before the
upgrade
• Using best practices on the legacy SQL Server systems first will help ensure a
smoother upgrade
Upgrading SQL Server
January 29, 2009
System Monitor
• SQL Server: Deprecated Features Object
– Monitors whether your application is submitting commands to the SQL
Server 2008 Database Engine that are scheduled for removal from SQL
Server in future releases
– You should remove deprecated commands from SQL Server 2008
applications after they are detected
– System Monitor records the total number of times the deprecated feature
was encountered since SQL Server 2008 was last started
Upgrading SQL Server
January 29, 2009
Upgrading DTS
• Use the DTS Package Migration Wizard to move packages from DTS
to SSIS format
• Package migration will usually succeed unless the packages contain
unregistered objects or use scripting
• Packages that contain only tasks and features that are present in SSIS will
migrate successfully
• You can preserve packages that contain non-SSIS DTS tasks and
features by encapsulating them in an Execute DTS 2000 Package task
• SQL Server 2008 still provides support for running DTS packages.
– Support for Data Transformation Services (DTS) in SQL Server 2008 in SQL Server
2008 Books Online http://go.microsoft.com/fwlink/?LinkID=132618
• DTS is deprecated, so packages should be moved to SSIS as soon as possible
• DTS Migration Tool available from http://dtsxchange.com
Upgrading SQL Server
DEMO: UPGRADE TOOLS
January 29, 2009
Upgrade Planning
Upgrading SQL Server
January 29, 2009
Outline Upgrade Plan
• Identify Upgrade Requirements
• Verify System Compatibility
– Run the System Configuration Checker from the SQL Server 2008 Installation Center
landing page
•
•
•
•
•
•
Run Upgrade Advisor
Perform Application Compatibility Testing
Pre-Upgrade Considerations
Determine Appropriate Upgrade Strategy
Upgrade Process
Post Upgrade Considerations
Upgrading SQL Server
Pre Upgrade Preparation
• Document existing solution
– SQL Server Feature Discovery Report
Capture performance baseline data
Develop criteria and unit / verification tests
Test upgrade on pre-production/development system first
Optional: capture query plans for complex queries
Ensure Clean Environment
Backup old instance / database and Verify it!
Loop in Windows Administrators, SAN Administrators and Network
Operations
• Optional: Access to Support
•
•
•
•
•
•
•
January 29, 2009
Upgrading SQL Server
January 29, 2009
Preparing for a Smooth Upgrade – Best Practices
•
•
•
•
•
•
•
•
Begin by identifying your upgrade requirements
Select the appropriate upgrade strategy
Run Upgrade Advisor
Make a checklist of all issues, and then resolve all that you can before you upgrade
Develop criteria and tests that you will use to determine whether the upgrade was successful
Formulate a rollback plan
Perform the upgrade for each component you are moving to SQL Server 2008
Perform post-upgrade steps, including:
–
–
–
–
–
Update statistics
Reconfigure log shipping
Rebuild full-text catalogs and indexes
Change Compatibility Level
Download and install updates !
• Prepare to implement new features
Presentation Title
November 7, 2015
Additional Considerations Before Upgrading the
Database Engine to SQL Server 2008
• If you are upgrading a 64-bit edition of SQL Server, you need to upgrade Analysis Service first
and then the Database Engine
• Run the appropriate DBCC commands to ensure that both the system and user databases are
in good health
• Make sure the system databases are configured to autogrow
• Disable all startup stored procedures as the upgrade process may restart the server.
• If Replication is enabled, stop replication during the upgrade process.
• Conduct a rolling upgrade if Database Mirroring is used.
– First upgrade the mirrored instance, failover services, and then upgrade the principal instance
(which is now the mirror).
– It is also recommended to remove the witness and change the operation mode to high safety
during the upgrade
• It is not possible to upgrade a SQL Server 2000 system running log shipping to SQL Server
2008
– In SQL Server 2000, Log Shipping was established with a Database Maintenance Plan.
– The installation in SQL Server 2005 and SQL Server 2008 no longer uses a maintenance plan to
implement Log Shipping
Troubleshooting
Upgrading SQL Server
January 29, 2009
Troubleshooting a Failed Upgrade
• Pre-setup issues:
– Verify the log files for pre-setup errors
– %Program files%\Microsoft sql server\100\Setup bootstrap\Log\Date Time
– Pre-setup errors are typically logged in the Summary.txt file and in the Detail.txt file
• Issues that occur after an unsuccessful upgrade
– Search the Details.txt log file for errors
– Resolve the issue appropriately, and then uninstall SQL Server 2008 as detailed in the
Summary.txt log file
• The old instance is no longer running and that the new instance is not available
– Upgrade process has reached Point of No Return
– Search the Details.txt log file for errors
– The Summary.txt log file displays information that you must use to repair your
installation.
– Resolve the issue and repair:
• Setup.exe /q /ACTION=Repair /INSTANCENAME=instancename
Upgrading SQL Server
January 29, 2009
Some Upgrade Gotchas/Known Issues
• Upgrade to SQL 2008 can fail if you renamed the ‘sa’ account
– http://blogs.msdn.com/psssql/archive/2008/09/10/upgrade-for-sql-server-2008can-fail-if-you-have-renamed-the-sa-account.aspx
– This bug has been fixed and will be released with SP1 (date - ??/09 )
• Upgrade to SQL Server 2008 fails in a multi-node cluster environment when a
remote node is paused
– http://support.microsoft.com/kb/955509
– Resume the passive nodes to allow for the group to fail over during the upgrade
process by using the Cluster Administrator or the Cluster.exe program.
– If the SQL resources are offline, bring these resources online.
– Perform the upgrade to SQL Server 2008
Upgrading SQL Server
January 29, 2009
Further Resources
• Get SQL Server 2008 (UA included)
http://www.microsoft.com/sql/2008/default.mspx
• Get Application Compatibility Testing Tool (Upgrade Assistant)
http://ssua.scalabilityexperts.com/
• Try DTS Migration Tool
http://dtsxchange.com/
• SQL Server 2008 Upgrade Technical Reference Guide
– http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af759975aea5bea7&displaylang=en
• How to troubleshoot SQL Server 2008 Setup issues
– http://support.microsoft.com/kb/955396
• Talk to Us !
– [email protected] or http://blogs.technet.com/rob
– http://sqlblogcasts.com/blogs/thepremiers