Joe Yong Chief Architect Scalability Experts DAT 323 Before We Begin Goals Upgrade options, methodology, tools and planning Focus on RDBMS Technical and non-technical considerations Common issues, myths.
Download ReportTranscript Joe Yong Chief Architect Scalability Experts DAT 323 Before We Begin Goals Upgrade options, methodology, tools and planning Focus on RDBMS Technical and non-technical considerations Common issues, myths.
Joe Yong Chief Architect Scalability Experts DAT 323 Before We Begin Goals Upgrade options, methodology, tools and planning Focus on RDBMS Technical and non-technical considerations Common issues, myths and mistakes Lessons learned and recommended practices Non-goals Silver bullet, exhaustive list of all upgrade issues, magic upgrade tricks End-to-end coverage or in-depth drilldown of all SQL Server 2008 features – but feel free to ask questions Discuss every bullet/word on every slide Many slides are provided for reference only Pre-requisites Operational familiarity with SQL Server 2000/2005 Basic knowledge of SQL Server 2008 Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Agenda Upgrade concepts review Before loading the CD/DVD Upgrade methodology Upgrade options and considerations Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Review Your SQL Server Landscape Hardware and infrastructure review Server capacity – CPU, RAM, network, storage Storage capacity – size, IOs, throughput Infrastructure – new server/storage requirements (power, HVAC, lb/ft) Application and database domain ISV or in-house Owned by central IT, department, under-someone’s-desk How many SQL Server instances you really have Microsoft Assessment and Planning toolkit to scan network Identify dependencies – db, version, edition, 3rd party Features and editions Hardware platform affects limits (CPU, RAM, hot-swap) Features not available or limited on lower editions Review Your SQL Server Landscape SQL Server 2008 Books Online Review removed features These have been deprecated for at least 2 versions (usually) Updated August 2008 Transparent Benefits of Upgrading to SQL Server 2005/2008 Applicable for upgrades from SQL Server 2000 http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?cultu re=en-US&EventID=1032285808&CountryCode=US 2008 specific www.microsoft.com/sql/2008 http://blog.scalabilityexperts.com/ Microsoft Assessment & Planning Toolkit • Locating all the SQL Server instances on your network Upgrade Resources vs Benefits Benefits • Spatial Support • FILESTREAM Support • Hierarchy Id Support • LINQ Support • CDC* & Change Tracking • Entity Framework Support • ADO.NET Data Services Support • Policy Based Management (DMF) • Performance Data Collection • Enhanced date and time support • Transact-SQL enhancements • Sparse column support • Service Broker enhancements • SSIS / SSRS / SSAS enhancements* Significant Application, Operational or Deployment Changes Moderate Application, Operational, or Deployment Changes • Resource Governor* • Filtered Indexes/Statistics • Data/Backup Compression* • Enhanced SQL Server Audit* • Transparent Data Encryption* • SSRS/SSAS scalability improvements* • Query Optimizer / Storage Engine enhancements* Minor Changes Agenda Upgrade concepts review Before loading the CD/DVD Upgrade methodology Upgrade options and considerations Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Upgrade methodology overview Review SQL Server landscape Check documentation Check hardware, infrastructure and applications Check dependencies and supported version mix/match Identify Upgrade Requirements SQL Server edition Hardware considerations Run Upgrade Advisor Resolve identified issues Perform Application Compatibility Testing Ensure thorough testing of all RDBMS queries Pre-Upgrade Considerations Document existing system Infrastructure, system , security and application build Develop validation tests / performance benchmarks and capture baseline data Formulate and test rollback plan May required 3rd party applications Determine Appropriate Upgrade Strategy In-place or side-by-side (migrate) Upgrade Process Pre-upgrade tasks Post Upgrade Considerations Immediate / short-term / medium-term / long-term Agenda Upgrade concepts review Before loading the CD/DVD Upgrade methodology Upgrade options and considerations Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Upgradeable Components Components Database Engine Full-text Search Reporting Services Analysis Services Data Transformation Services / SSIS Editions Express, Developer, Workgroup, Standard, Enterprise Platforms 32-bit & 64-bit (IA64 and x64) Languages & collations New collations in 2008 (matching Windows) Upgrade options and considerations In-Place Updates an existing installation while preserving user data Instance name remains the same after upgrade Existing instance overwritten post-upgrade Mostly automated process Upgrade options and considerations In-Place SQLServer Server2000/5 2008 SQL Instance Upgrade Upgrade options and considerations In-Place Pros Easier, mostly automated Generally fast overall process System data upgraded May require no additional hardware Applications remain pointing to same server/database name Cons Less granular control over upgrade process – all or nothing Instance remains offline during part of upgrade Not best practice for all components Complex rollback strategy Upgrade options and considerations Side-by-side (migrate) Install new instance of SQL Server Database objects are copied between instances Copy Database Wizard Does not copy extended procs, alerts, DTS packages, linked servers. Detach -> Copy -> Attach Backup -> Restore T-SQL Scripts, BCP , Break SAN mirror -> attach New and old instance reside side-by-side Can be same server (named instance) or different server Mostly manual process Upgrade options and considerations Side-by-side (migrate) SQL Server 2000/5 Instance SQL Server 2008 Instance Compare and Verify Verified! Upgrade options and considerations Side-by-side (migrate) Pros More granular control over upgrade process Database level Can be used to perform test migration Document process & gather metrics Ability to run systems side-by-side for parallel runs May require 3rd party application to keep both in-sync Relatively straightforward rollback strategy Can leverage failover/switchover to reduce downtime Cons Usually require additional hardware Additional resources required if on same server Server/database name changes Not practical for VLDB unless utilizing SAN (hybrid approach) Beware of “loss of quick roll-back” Additional upgrade scenarios Failover Clustering Significant change in Failover Clustering setup for SQL Server 2008 running on Windows Server 2008 Setup on each node instead of single node install-push to cluster Upgrade supported from SQL Server 2008 Installation Center and command-line setup Select upgrade workflow and follow wizard prompts If 2005 cluster have different installed features in each node, 2008 RTM cluster upgrade will fail – apply 2008 CU1 or remove other features If 2005 cluster passive node is paused, upgrade will fail – resume passive node 2000 64-bit (IA) cluster upgrade is not supported Typical upgrade workflow Using Installation Center, select upgrade workflow and follow wizard for upgrading all nodes – failover will occur Using command line, upgrade each node manually Additional upgrade scenarios Database Mirroring Rolling upgrades supported to minimize impact Mirror version has to be equal or newer than Principal At least one manual failover required; failback is usually performed but not required (dependent on setup) Ensure system can do both without issues prior to upgrade especially if using any SAN replication technologies (e.g. SRDF, Business Copy, etc…) Consider temporarily disabling SAN replication but be aware of risks Typical upgrade flow Remove Witness (for HA configuration only) Witness can be upgraded anytime after removal Switch to High Safety mode (if current mode is different) Upgrade MIRROR instance, allow to rejoin Database Mirroring pair and synchronize Failover to MIRROR and upgrade original PRINCIPAL, allow to rejoin as the new MIRROR and synchronize Failback if appropriate Re-establish witness and/or High Performance mode where applicable Additional upgrade scenarios Replication Mixed versions are supported but Distributor version must always be equal or newer than Publisher Subscribers for transactional replication can be within 2 versions newer/older of Publisher Subscribers for merge replication must be equal or older than Publisher Verify features supported by editions before upgrade, especially for 2000 MSDE 2005 Express E.g. Merge in Express 2008 is subscriber only Process is similar to 20002005 upgrade 20002008 is supported Update agent security settings post upgrade to reflect new security model (2008 and 2005 have same security model) Typical upgrade flow: Distributor, Publisher, Subscriber Upgrading other core components Analysis Services 20052008 typically upgraded via 2008 setup 20002008 upgrade via setup supported but side-by-side upgrade recommended using AS Migration Wizard Requires DSO backwards compatibility components not installed by default Typical upgrade flow setup: run 2008 setup, select upgrade workflow, select 2000/2005 AS instance to upgrade migrate: install 2008 AS, run AS Migration Wizard and follow workflow Upgrading other core components Log Shipping Upgrade from 2000 to 2008 is not supported; migrate using same method as 20002005 Monitor server instance can be updated anytime but monitoring is stopped until completed – Log Shipping itself is not affected Secondary server must be upgraded first Instance is upgraded first but DB remains 2000/2005 because it is offline; upon failover/recovery it automatically upgrades Logs are accumulated during upgrade and applied when done Primary can be upgraded with Less downtime – failover and re-establish Log Shipping after upgrade Less effort – no failover but system is unavailable during upgrade of primary Typical upgrade flow: Less downtime – upgrade secondary, failover, upgrade primary, establish Log Shipping, failback, establish Log Shipping Less effort – upgrade secondary, allow catch-up, upgrade primary Upgrading other core components Reporting Services Upgraded via 2008 Setup Different patch level requirements from RDBMS RS2000: Service Pack 2 or later RS2005: RTM or later Manual upgrade for RS server using a remote SQL Server 2000 database RS2000 report server endpoint is no longer supported Reporting Services 2008 uses native http.sys Typical upgrade workflow Run 2008 setup, select upgrade workflow for RS, select report server instance to upgrade Upgrading other core components Integration Services and DTS DTS upgrade supported as side-by-side installation – DTS has no dependency on SSIS Simple in-place upgrade for 2000 and 2005 DTS packages are retained Need to run DTS Package Migration Wizard to migrate to SSIS format No 64-bit design/run time for DTS packages No 32-bit design/run time support for DTS packages on Itanium based systems Seriously consider converting DTS source packages to native SSIS • Conversion tool available from Microsoft partners such as www.dtsxchange.com Additional upgrade scenarios Multiple interdependent databases Provide for a common restore point – sync backups If on a single instance All get upgraded together with in-place If on separate instances Upgrade each instance separately Backup each database after upgrade Plan for any extended downtime if all databases must be available for any of them to function For future: Consider scale-out, queuing architecture to avoid downtime Or, re-visit why the databases are separate and consider consolidating into one database with multiple schemas Additional upgrade scenarios Upgrading to a virtual machine Classic side-by-side upgrade (aka migrate) Backup/restore, detach/attach, copy database, etc.. Same upgrade and application compatibility testing considerations, tools and methodology Additional upgrade scenarios Upgrading to a virtual machine Both Hyper-V and Virtual Server 2005 are fully supported – Hyper-V highly recommended 3rd party virtualization vendors supported if running Windows Server 2008 – see important notes and supported vendors Server Virtualization Validation Program http://windowsservercatalog.com/svvp.aspx?svvppage=svv p.htm Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment http://support.microsoft.com/KB/956893 Update/modify monitoring scripts and templates E.g. Monitor both host and guest CPUs using new hypervisor specific perfmon counters Additional upgrade scenarios Upgrading to a virtual machine Mid/large servers recommended 4-sockets or higher mostly for PCI slots; blades/compact server not recommended regardless of CPU/RAM capacity Multiple VMs per machine supported Tested up to 4 VMs with near linear txn/s increase Single user DB per VM, moderate workloads Note increase in host OS resource utilization (memory, CPU) CPU and memory utilization comparable to host installations Storage IO performance comparable to host installation All SQL Server IO tuning principals and best practices apply Always use pass-through disks for data/log files Always used fixed sized, pre-allocated hard disks Additional upgrade scenarios Upgrading to a virtual machine Network IO performance impact 10-30% Can be addressed using multiple NIC CPU load will also increase if network load is high 3rd party vendors have specific optimization recommendations Can be highly secure but be aware of new and different security considerations Risks from VMs/guest OS (often by internal perpetrators) Hypervisor vulnerability exposes entire system (all VMs) Patch management for all VMs and hyper-visor Monitor intra-VM chatter, activity patterns and event logs (including entries & dates) Consolidation and upgrade Target Environment Test & Production Consolidated SQL Server 2008 instances & some legacy instances Test Current Environment One at a time SQL 2000, SQL7, SQL SQL 2000, 6.5 &Server older SQL SQL7, SQL instances 2005, 6.5 & 2000, older 7.0, 6.5 & older instances instances Test Transitional stage: SQL Server 2008 instances One at a time or en-masse? • Monitor behavior • Stabilize Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Upgrade Tools Upgrade Advisor Analysis Analyzes SQL Server database, trace files and script files Read-only operation, can be CPU intensive Disk I/O intensive Supports remote execution Supports default and named instance Use alias for non-standard ports Generates a report of Detected issues Blocking Issues Pre-Upgrade Issues Post-Upgrade / Migration Issues Suggestions on how to fix / work around Check for updates before running Upgrade Advisor Upgrade Advisor 2008 • Scanning for potential upgrade issues Upgrade Tools Upgrade Assistant Application Compatibility Testing Used in addition to Upgrade Advisor for Application Compatibility Testing (ACT) Allows testing of actual TSQL execution against SQL Server not just syntactical checks Can detect changes in execution method, path and results – Upgrade Advisor does not Leverages Profiler/Trace capability Requires SQL Server 2008 Client Tools Collaborative development between SQL Server development team, DPE and Scalability Experts Free download from www.scalabilityexperts.com/ssua Important: beware of cross server/database dependencies and data modification during testing – fully isolate from other systems Application Compatibility Testing Process Capture realistic workload from test/production environment Compatibility test, not stress/scalability/performance test Establish functional and relative performance baseline in ACT environment: Restore SQL Server 2000/2005 database environment Replay captured trace and record baseline data Determine functional and performance measures of same application on SQL Server 2008 32-bit Upgrade database to SQL Server 2008 Replay captured trace and record functional and performance data Determine functional and performance of same application on SQL Server 2008 64-bit (x64 or IA64) Restore SQL Server 2005 database to 64-bit system Replay capture trace and record functional and performance data Submit reports/feedback to Microsoft via http://connect.microsoft.com/sqlserver Repeat with compatibility level 100 Application Compatibility Testing Beyond basic application tests Test setup of application against SQL Server 2008 Most applications have specific checks/requirements for setup/install; make sure these are updated also (including security) Basic optimization review Run Database Tuning Advisor against the database and trace file to identify potential “low effort” optimizations HADR performance test Failover and failback times Restore database Standby/DR latency Upgrade Assistant 2008 • Application compatibility testing Why perform Appcompat testing Upgrade Advisor will not detect Changes related to system objects Meta-data driven applications Undocumented functions/objects Settings/configuration issues – especially security (e.g. account privileges for DB and/or files/folders) Upgrade Advisor may not have rules for all possible compatibility issues Examples: UNION in an INSERT statement may cause data-type casting errors Using more than 1 query hint without the WITH keyword in dynamic TSQL Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Upgrade Planning Considerations Stakeholders Application Database Hardware Infrastructure (Windows, network, storage, security) Users Business sponsor Motivators New capabilities Support (application & database) Regulations / policies Euphoria after attending seminar, executive briefing, etc… Upgrade Strategy Considerations Advanced Upgrade Complex C o m p l e x i t y Upgrade Advisor Basic Upgrade Post Upgrade Optimization App Compat Testing Simple Strategic Importance Low High Deciding on an Upgrade Strategy In-place or Side-by-side Business criticality and application complexity Highly critical applications are not always complex but will have strict limitations on testing, down time windows, etc… Large complex application are not always mission critical but will require extensive testing and rollback strategy may be complex Practical considerations Database size and number of objects Local maintenance jobs Tolerance for downtime Hardware capacity / age System dependencies & other applications Back-out time & effort Note parallel operation requirements Mandatory in any upgrade plan Go/no-go checkpoint(s) with sufficient time for rollback Implies you have a tested rollback strategy Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Pre-upgrade Tasks Compatibility Discontinued – do not appear in SQL Server 2008 Example: Undocumented procedures, backup with TRUNCATE ONLY Deprecated – will not be supported in the immediate release following SQL Server 2008 (e.g. Notification Services) or in future releases Different behavior – same name but different results Example: Unusable plan guides creates new plan, REPLACE keeps trailing space, certain trace flags Check Books Online for full list and all relevant details Not just about T-SQL syntax (though most common); check for behavior changes, system functions, etc… Pre-upgrade Tasks Compatibility .NET framework dependencies (2005 upgrades) GAC updated to 3.5 SP1 If there is a new type in this version of the .NET Framework that has the same name as a UDT, SQL Server will mark your database “suspect” Check for the existence of any unsupported assemblies in your database SELECT name FROM sys.assemblies WHERE clr_name LIKE '%publickeytoken=b03f5f7f11d50a3a,%‘ Pay particular attention if your DB is 65/70 compatibility level Perform Application Compatibility Testing to identify issues that Upgrade Advisor and/or documentations may have missed Not all databases/applications require ACT; necessity is dependent on business criticality and complexity of application Note that business critical != complex and vice-versa Pre-upgrade Tasks Prepare environment Ensure clean environment Database consistency checks Consider shrinking databases (one of very few times it’s ok) Faster file copy for side-by-side Backup old instance / databases Include any external and/or 3rd party components like DLL, encryption keys, etc… Check security setting if upgrading in-place – fails if SA renamed Logged on with local account but service using domain account% Pre-upgrade Tasks Prepare environment Internal communications Ensure no collisions with other activities NOC does not re-start servers, SAN admin pager doesn’t go off, security team does not block port or network segment New system build out for side-by-side (migrate) Check all logs TempDB configuration Network (NWLink gone ) and data access protocols (DBLib gone, new JDBC) Security settings – service accounts, local accounts, cross – domain/server, SQL Server roles, etc… check ALL folder permissions if using Windows 2008 Server (e.g. 3rd party tools) Access to SME – emergency support contacts Pre-upgrade Tasks Dependencies Assuming Upgrade Advisor and Application Compatibility testing are complete and issues addressed Database Solution “COM Components”, Extended Stored Procedures, sp_OA%, CLR assemblies Data access providers (SNAC, MDAC, JDBC) DTS packages Third Party Dependencies Software – backup, management agents, clusters, MPIO, SAN mirroring Components – data encryption & keys, mail, etc… Anti-virus support/impact Usually good idea to shut down before upgrade but may not be allowed Some editions have a different feature set Example: Express does not have SQL Server Agent (SQL Server 2000 upgrades may be unhappy) Pre-upgrade Tasks Dependencies Don’t forget to upgrade your DBA/Admin scripts E.g. Rebuildm.exe is now setup /ACTION=rebuilddatabase /QUIET INSTANCENAME=<your_instance_name> /SQLSYSADMINACCOUNTS=<your_sysadm_accounts> /SQLCOLLATION=<your_collation> Linked servers settings and distributed queries Manage impact or synchronize upgrades Maintenance windows and checkpoints for go/no-go Re-check and re-broadcast project plan & timelines Establish checkpoints/milestones and checklist items for each to determine go/no-go Ensure adequate time for no-go No substitute for knowing your application Pre-upgrade Tasks Baseline Data Document existing SQL Server solution sp_configure, SQLDIAG, sp_dboption Storage, network & security configuration Capture/update performance baseline data System level (e.g. perfmon, waitstats, etc…) Application level (e.g. query response, concurrent users, etc…) Develop criteria and unit / verification tests Can leverage existing user acceptance test harness but be sure to update relevant components Optional (but recommended): capture query plans for complex and/or critical queries Pre-upgrade Tasks Document Current System Document existing SQL Server solution sp_configure, sp_dboption, SET <options> SQLDIAG Storage configuration Security configuration Export Management Studio custom settings Capture performance baseline data System level (e.g. perfmon, waitstats, …) Application level (e.g. query response, concurrent users, …) Develop criteria and unit / verification tests Can leverage existing user acceptance test infrastructure but be sure to update relevant components Optional (but useful): capture query plans for complex and/or critical queries Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns The Upgrade Build a checklist that documents EVERY step clearly, precisely Do not “just wing it” or “figure it out along the way” Check server, storage and network health and alerts Check your emergency contact info or have SME present Back up old instance and VERIFY Disable startup procedures Set max worker thread back to zero (0) Perform upgrade Monitor upgrade progress Check timing and compare to test upgrade timing Verify checkpoints are met and make go/no-go decision Backup successfully upgraded database Execute your post upgrade tasks Backup database pre-deployment Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Post-upgrade Tasks Immediate Review all logs Apply latest Cumulative Update/Service Pack Update statistics to ensure performance Full if possible, sample for very large tables Update usage counters if upgrading from SQL Server 2000 Reconfigure/re-establish high availability functions if not part of upgrade process (e.g. Log Shipping) Verify Agent settings, jobs and alerts Isolate old system (server, instance, database) Import Management Studio settings Post-upgrade Tasks Immediate Verify security settings Especially cross server and/or cross-domain access privileges In/out-bound linked servers Check database consistency With DATA_PURITY to verify correct ranges, precision, scale SQL Server configuration Critical for Side-By-Side to reset environment, protocols, … SQL Browser 2008 startup (if multi-instance with 2000/2005) Make sure tempdb is still configured correctly Verify maintenance and other jobs (SQL Agent) Re-populate Full-Text Search catalogs Perform unit / verification tests Commit upgrade or rollback! Post-upgrade Tasks Short Term Management Studio Import settings Upgrade administrator workstations Database options DB_CHAINING, EXECUTE AS PAGE_VERIFY CHECKSUM Evaluate AUTO_UPDATE_STATISTICS_ASYNC MaxDOP Auto update stats Lock escalation Trace flags Post-upgrade Tasks Short Term SLA for critical queries Review query plans saved earlier and baseline data De-commission old server Be careful when uninstalling/disabling service on a multiinstance server Post-upgrade Tasks Medium Term Re-evaluate Optimizer Hints Remove or document reasons to retain Consider using plan guides instead of hints Policy Based Management Review current standards enforcements Re-create standards/rules using PBM Resource Governor Start with minimal groups, pools and rules (in classifier function) Management benefits/changes Data Collector, reports, DMVs (including new and changed) Backup Compression Post-upgrade Tasks Medium Term Data Types Review precision levels and storage sizes Sparse column usage Database Tuning Advisor You already have a workload trace anyway Post-upgrade Tasks Long Term Data type changes More granular data types – date, time Custom CLR types to new native types in 2008 Re-write “COM components” as CLR XPs to C# or VB in CLR Security Schemas (for 2000 upgrades), new encryption option (TDE), new audit capabilities Update/remove deprecated features AS COM assemblies, DTS, ActiveX Script task, SQL Mail, sp_repladdcolumn, updateable subscriptions, sp_dbcmptlevel, …… Many more if upgrading from SQL Server 2000 – See BOL Agenda Upgrade concepts review Upgrade tools Planning the upgrade Pre-upgrade tasks The upgrade Post upgrade tasks Common issues and concerns Common issues and concerns Applications work fine on 80/90 compatibility mode but fails in 100 mode Additional ANSI SQL standards enforcements in 2008 (e.g. left and right outer joins using *= and =* no longer supported in 2008) Modify SQL statements to be standards compliant (will still work if in 8.0 compatibility mode) – often ignored when upgraded from 20002005 Difference is more than just SQL syntax – BOL has full list and detailed description References to undocumented objects/functions Undocumented objects are not supported, ever Modify application to utilize Dynamic Management Views and Functions (e.g. syslocks sys.dm_tran_locks) Common issues and concerns Can’t access instance, database and/or data or component won’t start after upgrade Secure by default, off by default, not added by default Configure via SSMS and Configuration Manager If encryption is used, don’t forget Service Master Key Enable “remote network access” and appropriate protocol Did security/network admins shutdown your port/segment? Verify SQLBrowser is running if running multi-instance and starts first if running multi-instance with SQL Server 2000 TDE encrypts log, backup, tempDB Don’t forget remote Dedicated Admin Connection Common issues and concerns Data file initialization taking long time TDE disables instant initialization is enabled to prevent “stray bits” Failover Clustering setup running for an hour Upgrade included hardware refresh exposing existing performance problems previously unnoticed Stored procedure creation scripts fail Delayed name resolution for SQL Server 2008 limited to table objects; 2000 allowed columns Common issues and concerns Poor performance post-upgrade Upgrade included hardware refresh exposing existing performance problems previously unnoticed Check queries for hints – review and remove unless fully validated TempDB optimization not implemented Update statistics Review query plans Less than 1% of users may have genuine compatibility issues resulting in performance problems – seek support escalation Summary Upgrades are conceptually simple – human errors are easily prevented but often missed There are no hard rules – consider factors discussed in the context of your system/environment Application Compatibility Testing is highly recommended for complex/business critical systems Not all systems need ACT A tested rollback plan is critical Don’t jump to conclusions There are lots of resources Feedback to Microsoft via Connect will drive improvements in product and upgrade process/tools – seriously Joe Yong [email protected] Resources www.microsoft.com/teched www.microsoft.com/learning Sessions On-Demand & Community Microsoft Certification & Training Resources http://microsoft.com/technet http://microsoft.com/msdn Resources for IT Professionals Resources for Developers www.microsoft.com/learning Microsoft Certification and Training Resources Related Content Breakout Sessions (session codes and titles) Interactive Theater Sessions (session codes and titles) Hands-on Labs (session codes and titles) Hands-on Labs (session codes and titles) Track Resources Resource 1 Resource 2 Resource 3 Resource 4 SQL Server Community Resources The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community. • Connect: Local Chapters, Special Interest Groups, Online Community • Share: PASSPort Social Networking, Community Connection Event • Learn: PASS Summit Annual Conference, Technical Articles, Webcasts Become a FREE PASSorganization Member: www.sqlpass.org/RegisterforSQLPASS.aspx • More about the PASS www.sqlpass.org/ Learn more about the PASS organization www.sqlpass.org/ Additional Community Resources SQL Server Community Center www.microsoft.com/sqlserver/2008/en/us/community-center.aspx TechNet Community for IT Professionals http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx Developer Center http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx SQL Server 2008 Learning Portal http://www.microsoft.com/learning/sql/2008/default.mspx Additional Resources • • Speaker Blog: blog.scalabilityexperts.com Other: www.scalabilityexperts.com External Resources SQL Server Upgrade Assistant www.scalabilityexperts.com/ssua SQL Server 2008 Upgrade and Application Compatibility self-paced training http://sqlserver2008jumpstart.microsofttraining.com Select App Compat & Upgrade track SQL Server 2008 Business Value Calculator: www.moresqlserver.com Complete an evaluation on CommNet and enter to win! © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.