1 / 42

Satya Shyam K Jayanty sqlmaster@sqlserver-qa

Upgrading To SQL Server 2005 & 2008: Notes & Best Practices. Satya Shyam K Jayanty sqlmaster@sqlserver-qa.net. Venue: Manchester Metropolitan University Manchester, UK 28 th March 2009. Agenda. Upgrade – Why/What/How/When? Basics in planning SQL Server Upgrade

gavin
Download Presentation

Satya Shyam K Jayanty sqlmaster@sqlserver-qa

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Upgrading To SQL Server 2005 & 2008: Notes & Best Practices • SatyaShyam K Jayanty • sqlmaster@sqlserver-qa.net Venue: Manchester Metropolitan University Manchester, UK 28th March 2009

  2. Agenda Upgrade – Why/What/How/When? Basics in planning SQL Server Upgrade Proven upgrade methodology (Planning & Deploying) In-place vs. side by side upgrade strategies Focus on Upgrade Issues & Troubleshooting What's your stake on testing? Reference & QA

  3. Introduction – Speaker • IT Experience • Been in the IT field over 17+ years (SQL Server Architect) • SQL Server DBA for over 13 years (working since ver. 4.2) • Principal Consultant & Director – SSQA.net Limited (www.ssqa-net.co.uk) • Recognition • SQL Server MVP • Speaker : Microsoft Tech-Ed, SQLPASS, SQL Bits, User Group (Scottish Area SQL Server User Group) • Writer & Technical Reviewer for SQL Server 2008 certfication. • Community Contributions • Webmaster(SQLMaster) of www.sqlserver-qa.net • Contributing Editor & Moderator - www.sql-server-performance.com[SSP] • Active participation in assorted forums such as SSP, SQL Server Central, MSDN, SQL Server magazine, dbforums etc. • SQL Server 2008, HeroesHappenHere – Launch Leader & 'Ask The Experts’ lounge.

  4. Preface • What we will not cover: • Features of each edition & version • Real-time upgrade procedure • Not a specific techie area - Dev & IT PRO • Pre-requisites • Basic knowledge of operations and test environments : SQL in particular • Concepts of database upgrade • Previous exp. on upgrade (optional) • Basic knowledge of SQL 2000 & 2005 features SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  5. Upgrade – Basics … • Upgrade: • Any kind of transition from SQL Server (previous version) to existing supported version (2005 or 2008) • Server: • A windows server or in some cases SQL instance • Component: • One of the several relatively independent executables included within SQL Server & Windows OS • Database engine, HA solutions, SSAS, SSIS, SSRS and SSNS... many more • SQL Server instance: • Copy of SQL services running on a server, containing system/user objects related to an edition/version. SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  6. Upgrade – Why/What/How/When? • Why Upgrade? New versions & Features! • Enhancement on features from 2000 version • DB Engine, SSIS, AS and RS features • Accountable on delivering agile database system • Accomplish objective of new features in SQL 2008: • Leverage new capabilities in the product • Organization benefits in getting on with feature enhancements • Compliance with regulations and policies • Ease of manageability (multi site/server environment) SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  7. Upgrade – Why/What/How/When? • What? • Identify upgrade requirement • SQL Server instance(s) & Database(s) • Application components & Operating system (in some cases) • Scripts/TSQL code • Process: In-place & Side-by-Side • How? • Pre-upgrade considerations • Tools: Run Upgrade Advisor • Perform Application Compatibility Testing • Determine Appropriate upgrade strategy • Post upgrade considerations, issues & fire-fighting (DBA) • When? • Start planning as soon as existing version out of mainstream support SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  8. Basics in planning SQL Server Upgrade • Identify Upgrade Requirements • Check documentation: Product & Process …a first step to giant leap! • SQL 2008 BOL sections • SQL 2008 upgrade technical reference guide • Check existing hardware, infrastructure and applications (ownership) • Disk free space is key • Hardware considerations, planning for future • Tools • Discover additional SQL instances (Tool) • Your own script or tool • MS Assessment & Planning Toolkit • SQL Server editions and versions you choose to upgrade SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  9. SQL Server 2008 Upgrade Technical Reference Guide By Microsoft Corporation http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7

  10. DEMO Discovery of SQL instances (recent release) Free to download from MS Downloads site

  11. Proven upgrade methodology (Planning & Deploying) • Pre-Upgrade considerations: • Document your existing SQL system • Develop validation test even a simple change is involved • Develop performance benchmarks and baseline data (blog post) • Formulate your test plan • Involve all the applications that are dependant on that Database • Develop Upgrade plan: • Treat this as a project, even for smaller databases • Schedule for long downtime window, just in case • Minimize variables of other components, .NET & App.code • Evaluate acceptance criteria for GO/No-GO decisions • No going back once the database is upgraded • Not possible to restore upgraded database • Schema data is updated on user database, not real-app data SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  12. Outside of Databases: • Backup old instance / database and Verify it! • Loop in Windows Administrators, SAN Administrators and Network Operations • Optional: Access to Support • Backup points of upgrade plan • Take backups of all databases before and after upgrade • Take backup of all logins, linked server definitions & scheduled jobs information • Take a stage-wise approach on the upgrade process • Test the backup media, not a backup alone. • Rollback strategies: • Required in all cases! • See Upgrade technical reference guide: Appendix 2 "Upgrade Planning Deployment and Tasks checklist“ SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  13. Preparing for a ‘Smooth’ Upgrade • Things to Watch: • Backward Compatibility features • SQL 2005 & 2008 are generally backward compatible with SQL 2000 • Major difference is ETL tool (SSIS and DTS packages) • BOL to check on Deprecate features, discontinued features, breaking changes and behavioral changes • Begin by identifying your upgrade requirements • 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 SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  14. Tools: -Upgrade Advisor -Upgrade Assistant DEMO

  15. SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  16. Tools - What we see: • Application Compatibility Test (ACT) • Test actual code execution against SQL Server • Your own covering test harness • Upgrade Assisant (Microsoft Dev.Team & Scalability Experts) • Another bow in your arsenal • Used in addition to Upgrade Advisor (Free download) • Testing of TSQL execution (in specific) • -Dynamic & Embedded • Detects changes upgrade advisor cannot: • Execution Method • Undocmented objects/procedures • Leverages profiler capability • Must require SQL 2008 tools • Collaborative work from MS Dev.team & ScalabilityExperts SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  17. In-place vs Side-by-Side Upgrade • In-place • Using SQL Server 2008 setup to upgrade or download SQLUA • Choose SQL instance(s) & database(s) • Process • Older instance of SQL is replaced, data is preserved • Mostly automated, keep your eyes-open • Implementation time • Do not run any other applications SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  18. In-place vs Side-by-Side Upgrade • Side-by-Side • Using SQL Server 2008 setup to upgrade or download SQLUA • Choose target SQL instance(s) & database(s) • Connectivity between source & destination is key • Process • New & Old instances reside • Application is still available for general use • Manual process to move database, objects etc. • Can be scripted & 2 variations • 1 server: multiple instances • 2 servers: new instance on new servers SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  19. In-place vs Side-by-Side Upgrade • Pros & Cons : In-Place • Pros • All in one place & automated (mostly) • General fast process • System data upgraded instantly • No changes on Application side, connectivity • Cons • Less granular & control over process • Long process for rollback, reinstall everything • Not a suitable for all kinds of database upgrades • Not for third-party tools, at all • Complex rollback to ensure like-to-like availability SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  20. In-place vs Side-by-Side Upgrade • Pros & Cons : Side-by-Side • Pros • More granular control over process • Database or instance level , easy for testing & performance baseline • Ability to run Application parallel • No rollback issues, not touching live instance • Leverage failover/switchover to reduce downltime • Cons • Additional hardware required • Long process for rollback, reinstall everything • Not a suitable for all kinds of database upgrades • Not for third-party tools, at all • Complex rollback to ensure like-to-like availability SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  21. Focus on Upgrade Issues & Troubleshooting • Instance – Version Upgrade paths • SQL Server 2000 SP4 or later • SQL Server 2005 RTM or later version (Win2003) • SQL Server 2005 SP2 or later version (Win 2008) • IF upgrading from SQL 2000 to 2008 choose side-by-side (real-time testing) • Smaller databases (not 24/7) choose in-place • Pro-active approach helps • Upgrade Technical Reference : • See section 1.3.1.3 - Comparing in-place and side-by-side methods • See table 1-3 - Summary of factors affecting the upgrade strategy decision SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  22. Focus on Upgrade Issues & Troubleshooting • How to minimize downtime window? • Bring legacy instances to upgradable version (4.2 & 6.5) • Documentation is key for all components • Ensure O/S, SP & Hotfix levels are met • Pre-install .NET & Windows 4.5 installer (2008) • Side-by-side upgrade is best suitable for test • Use new service accounts (testing) • Check Data-consistency (before & after) • Perform DB maintenance (REBUILD) • Backup of all database, linked server definitions, scheduled jobs, DTS packages & logins.

  23. Troubleshooting a Failed Upgrade Pre-setup issues: • Log Files are key to look for troubleshooting • 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 SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  24. Troubleshooting a Failed Upgrade • 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 • 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-2008-can-fail-if-you-have-renamed-the-sa-account.aspx • This bug has been fixed and will be released with SP1 (date - ??/09 ) SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  25. Troubleshooting: known issues • 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 • Can do an in-place upgrade on same OS only SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  26. Troubleshooting: known issues • SQL Server 2005 and SQL Server 2008 • supported in clustered configurations with Windows Server 2008 • Windows Server 2003 to 2008 • Upgrade a cluster is not straightforward • Version Differences (2005 & 2008) • There are subtle, yet important, differences in failover clustering and their support on Windows Sever 2008 • Cluster Uninstall/Remove • To removing nodes cleanly, you must remove-cluster SQL Server first SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  27. OS Upgrade Considerations • Cannot do a rolling upgrade from Windows Server 2003 to Windows Server 2008 • If reusing hardware, will essentially have to “break” old cluster • Migrate Cluster Wizard • Can move some settings • Cannot be used to migrate SQL Server • New hardware is the best way • Must take into account database migration SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  28. OS Upgrade Considerations • Cannot do a rolling upgrade from Windows Server 2003 to Windows Server 2008 • If reusing hardware, will essentially have to “break” old cluster • Migrate Cluster Wizard • Can move some settings • Cannot be used to migrate SQL Server • New hardware is the best way • Must take into account database migration SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  29. To Watch: SQL Server 2008 • Whole suite of SQL Server: • 64-bit edition of SQL Server, upgrade Analysis Service first and then Database Engine • DBCC check compulsory to ensure that both all the databases are in good health • Make sure the system databases are configured to auto-grow • Disable all STARTUP stored procedures as the upgrade process may restart the server. • High Availability features used: • Replication • stop replication during the upgrade process. SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  30. To Watch: SQL Server 2008 • High Availability features used: • Database Mirroring (graphic next slide) • Conduct a rolling upgrade • First upgrade the mirrored instance, • Failover services, and • Upgrade the principal instance (which is now the mirror). • Remove the witness and change the operation mode to high safety during the upgrade • Log Shipping • It is not possible to upgrade a SQL Server 2000 system running log shipping to SQL Server 2008 • Remove Log Shipping (due to DB Maintenance Plan) • The installation in SQL Server 2005 and SQL Server 2008 no longer uses a maintenance plan to implement Log Shipping SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  31. Rolling UpgradesKey steps 1. Upgrade the Mirror First Asynchronous Mirroring: Steps 1 to 6 2. Wait for low activity window 3. Alter mode to synchronous, and wait till SYNCHRONIZED Synchronous Mirroring: Steps 1, 4, 6 4. Failover to mirror: the new Principal 5. Alter mode back to asynchronous An approach to minimize downtime while upgrading from SQL Server 2005 to SQL Server 2008. 6. Upgrade the new Mirror SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  32. To Watch: SQL Server 2008 • Upgrade Process: Guidance • SQL Server 2008: System configuration Checked • Wizards & Tools to consider • Analysis Services Migration Wizard • DTS Package Migration wizard ((msdn.microsoft.com/en-us/library/cc768544.aspx) • Pragmatic works DTS xChange • Notification Services backward compatibility add-in • Do not upgrade the Windows operating system at same time as SQL upgrade • Increases the variables (risk of entire server) • More downtime required to install non-SQL services • Do not GO until you have tested completely from scratch SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  33. To Watch: SQL Server 2008 • Post upgrade • Now your new server is ready for application interaction. • New database server, application must be ready for .NET & new features of SQL 2008 • Determine whether upgrade was successful, with simple connectivity • Decommission and Uninstall after side-by-side or hardware upgrade • Do not leave unused application services (in-place check again) • SQL 2008 admin tasks • Consider deploying SQL 2008 Policies (PBM) • Review maintenance jobs, optimization settings • Re-create backup plans (maintenance plans, SSIS) • Re-consider and check backup completion timings • Re-create DBCC REBUILD and REORGANIZE tasks for database • Use relevant system monitor application to monitor performance availability • Thoroughly follow system for 24 hours and upto weekend if any schedules are aligned SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  34. What's your stake on testing? • Perform Application Compatibility Testing (ACT) • Determine how much ACT is appropriate • Application Compatibility testing • Highly recommended for complete/business critical systems • Testing is essential for all kinds of tasks • May be not all systems need ACT • Ensure testing of all TSQL queries • Test DB with current compatibility level (60 to 100) • Inventory and assessment of your existing SQL instance (2000 and 2005) • Tested rollback plan, implemented (in-place & side-by-side) SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  35. What's your stake on testing? • Upgrades are conceptually simple • Human errors are possible, can be prevented with testing • No hard rules • Consider factors discussed in context of your system • Microsoft Assessment & Planning solution accelerator • Do you have third party applications? • Third party tools databases are prone to problems in upgrade • Make sure vendor confirms the product is tested with latest version. • Check with Vendor on any certified process on SQL 2008 • Microsoft offers numerous ISV resources for this purpose • Involve Developers, Application users and Support teams SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  36. Q & A • I will do my level best to answer your question (time-permitting). • Reference: http://sqlserver-qa.net/blogs/Bloggers.aspx SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  37. Reference • Live Search or Google: Tools • MAP Toolkit 3.2 • SQL Server Upgrade Advisor • SQL Server 2008 policies • SQL Server Upgrade Assistant -Scalability Experts • Pragmatics Works DTS xChange • BPA tool • Upgraded books online for SQL 2008 & 2005 • SQL Server 2008 Feature pack download • Connect for SQL Server – http://connect.microsoft.com/sqlserver • Testing Resources - www.microsoft.com/mtc • Application Compatibility Toolkit (MS Downloads) (http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=24da89e9-b581-47b0-b45e-492dd6da2971) SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

  38. Complete feedback forms! Thank you & have a great day ahead. SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net

More Related