1 / 60

SQL Server Deployment Planning Services Upgrading to SQL Server 2012

SQL Server Deployment Planning Services Upgrading to SQL Server 2012. February 2012. Guidance Deck Overview. Best practices for implementing upgrade and consolidation engagements. Three types of Upgrade/Consolidation Assessments. 1. Assessment & Plan. 2. Sample Upgrade. 3.

valentine-raymond
Download Presentation

SQL Server Deployment Planning Services Upgrading to SQL Server 2012

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. SQL Server Deployment Planning ServicesUpgrading to SQL Server 2012 February 2012

  2. Guidance Deck Overview Best practices for implementing upgrade and consolidation engagements Three types of Upgrade/Consolidation Assessments 1 Assessment & Plan 2 Sample Upgrade 3 Sample Database Upgrade • 3 day assessment • Identify applications and databases for upgrade, assess complexity, and prioritize 5 day assessment Architecture review Choose from: Initial Upgrade & test Plan, or Sample Server Upgrade 10 day assessment Adds POC, sample upgrade or consolidation, deeper architectural review of SQL Server Upgrade paths, and a detailed upgrade and/or consolidation plan Target Customers: Customers ready to upgrade to SQL Server 2012

  3. Assessment Outcomes Deliverables and goals by type of assessment

  4. Customer Considerations Guidance points for customer assessments The size of client organization will determine the feasibility and duration of assessment and the depth of analysis. For large customers For 10 day assessment • 3 and 5 days engagements not recommended due to complexities related to number of data sources and business logic. • Assessment should include a focus on consolidation of existing systems as well as upgrading database servers to SQL Server 2012. • Position to address one or a few SQL Server upgrades, possibly including testing consolidation • Position assessment to include a detailed plan on achieving full upgrade of identified systems to SQL Server 2012.

  5. Scope of Engagement Clarify with customer what analysis will NOT include Establish scope of analysis and clarify with the customer

  6. Scope of Engagement Establish scope of analysis and clarify with the customer • Engagement process begins with pre-kickoff meetings and initial conversations with the customer • Results of pre-engagement activities determine most appropriate resources, define high level objectives, and schedule interviews • As engagement begins, scope and condition of satisfaction are defined • Execution includes: • Documenting the existing environment • Reviewing the results of the MAP toolkit • Building plans on how to perform a consolidation and/or upgrade effort • Proof of Concept is optional in the 5 day engagement and required in the 10 day assessment • Present final findings to the customer along with clear roadmap for implementing the rest of the upgrades/consolidation to SQL Server 2012

  7. Engagement Process Execution Implement assessment engagements following the project activity flow Start If 5+ Day Engagement If 10 Day Engagement Pre-Kickoff Meeting or Conference call Review Customer survey and pre-engagement Questionnaire 5 day engagements choose a path Review System Arch for Consolidation/Upgrade Explain how to download and run MAP Toolkit Review (or run) results of the MAP toolkit Project Activity Flow Resource Available 5 or 10 days 5 days POC of Upgrade/ Consolidation (one instance as Proof Of Concept) Initial Plan for Upgrade /Consolidation Review System Arch for Consolidation/Upgrade Give Customer the Customer Survey Review Architecture considerations for Upgrade and Consolidation Detailed Plan for complete upgrade and/ or consolidation Produce a test plan for the upgrade/ consolidation effort POC of Upgrade/ Consolidation (one instance as Proof Of Concept) Agree on Deliverables Review Customer Pre-requisites/ Requirements If 10 Day* Resources? Finish No Resources *If 5 day customers acquire new points, they can continue with the 10 day engagement.

  8. Pre-Engagement Activities Engagement pre-requisites, tools, and deliverables 1 Conference Call 2 Survey and Toolkit 3 Deliverables Agreement • Hold a conference call or meeting to agree on the engagement scope and length • If conducting a 5 day engagement, determine whether it will include an upgrade plan or a POC • Determine customer needs and appropriate staffing • Identity SQL Server version to upgrade to (2008 R2 or 2012) • Set explicit agreement with the customer on deliverables for the engagement • This should easily derive from the length of the engagement • Ensure scope is well defined so customer expectations will match the engagement • If conducting a 5 day engagement, determine whether it will include an upgrade plan or a POC • The customer should complete the Customer Survey and Pre-Engagement Questionnaire before the consultant arrives on-site • The customer should download and run the Microsoft Assessment and Planning Toolkit before the engagement begins. • Found at the link above or http://technet.microsoft.com/en-us/library/bb977556.aspx. • This will require a day or two of investment full-time on the customer’s part. If not completed before the engagement begins this tool must be run during the engagement, significantly impeding progress during the engagement.

  9. Day 1 – Kickoff Hold a kick-off meeting with the customer • Discuss business objectives, challenges, and problems that are being targeted for resolution • Take notes of all points discussed and collect all relevant documentation available from the client. • Discuss efforts that are being done or that had been attempted to address business objectives and challenges • Review engagement process flow

  10. Day 1- Pre-Engagement Questionnaire Survey the customer for background information • Purpose: Gather and organize customer information and background data relevant for Upgrade and Consolidation • Use Customer documentation when available • Identify gaps where further information is required.

  11. Day 1- Questionnaire Coverage & Follow-up Key areas covered by the pre-engagement questionnaire Questionnaire Coverage Areas Post-Questionnaire Follow-up Activities • Identify high priority missing relevant and required information • Ask additional questions as needed • Ask for further documentation • Identify customer statement of issues, pain points, and customer definition of success • Application Architecture: Determine the number and overall architecture of the affected applications • Infrastructure: Number and nature of existing servers and instances • Inspect appended or embedded user documentation

  12. Day 1- Questionnaire Specifics (1) Specific information areas to assess in questionnaires

  13. Day 1- Questionnaire Specifics (2) Specific information areas to assess in questionnaires

  14. Day 1 – The MAP Toolkit Microsoft Assessment and Planning Toolkit (MAP) • Try to get the Customer to run the MAP Toolkit before the engagement • At least get the Customer to install it and identify personnel with sufficient security to collect server data • The MAP toolkit works best using an account with local administrator rights to each machine • For additional data, network rights are required • Help the customer run the MAP toolkit • If customer did not run toolkit prior to your arrival • Work through the customer when running the toolkit • This activity could run into the second day if you have a large customer MAP Toolkit Components • MAP Toolkit Setup (.exe) • Getting Started Guide (.docx) • MAP Sample Documents (.zip) • MAP Training Kit (.zip) • Readme (.htm)

  15. Day 1- MAP Toolkit Set-Up Determine security then set-up • Preinstall: Determine security for MAP 6.5 toolkit • Needs a domain account and password • Must have sufficient security to collect desired information • Local administrator rights to each server for • SQL Server log files • Windows log files • Network rights: dependent on data being collected • Sysadmin rights on each SQL Server for: • SQL Server configuration and other information • Setup automatically installs SQL Server 2008 R2 Express • The Instance named MAPS

  16. Day 1- Prepare the Environment WMI, directory, and remote registry configuration WMI Considerations • Determine security for WMI data collection • Remote administration exception Active Directory Remote Registry Service Group policies Configure • Most of the above require local administrator privileges

  17. Day 1-Using the MAP Toolkit Use the inventory and assessment wizard Inventory and Assessment Wizard • Windows Server 2008 R2 Readiness • Determines which servers can support Windows Server 2008 R2 and suggests hardware upgrades • Generates two reports: proposal Word document and Excel detailed report • Microsoft SQL Server Discovery • Identifies SQL Server instances and components, indicating ability to upgrade to SQL Server 2012

  18. Day 1- Inventory and Assessment Wizard Scenarios

  19. Day 1- Inventory and Assessment Wizard Discovery Methods

  20. Day 1- MAP Sample Documents Examples to review • Inventory • HardwareAndSoftwareSummary-12-08-2011-15h28m17s.xlsx • InventoryResults-12-08-2011-15h28m25s.xlsx • Windows Server 2008 R2 • WS2008R2Proposal-12-08-2011-15h27m18s.docx • WS2008R2HardwareAssessment-12-08-2011-15h28m30s.xlsx • SQL Server • SQLMigrationProposal-12-08-2011-15h27m43s.docx • SqlMigrationDiscovery-12-08-2011-15h29m51s.xlsx • SqlServerAssessment-12-08-2011-15h29m33s.xlsx • SqlServerDatabaseDetails-12-08-2011-15h29m52s.xlsx • SQLServerProposal-12-08-2011-15h27m26s.docx

  21. Day 1- MAP Training Kit Step-by-step guide with example databases • MAP6_5_Demo_Exercises.docx • Step by step user guide • Exercise 2.1: Windows Server 2008 R2 Readiness • Exercise 4.1: Discovering SQL Server • Exercise 4.2: SQL Server Migration Assessment • Appendix B: Installing the MAP Toolkit • 1Demo SQL Server database

  22. Day 1- Getting Started Guide Sending log audit events • SQL Server Usage • Enable SQL Server 2012 to send log audit events to the Windows security log

  23. Day 2 – Architecture Considerations Review key factors • Upgrade Decision Points • Upgrade Strategies • Upgrade Rollback Considerations • Consolidation • Consolidation Drivers • SQL Server Consolidation Features/Support • Consolidation Choices/Options • Consolidation Decision Points

  24. Day 2- Upgrade Decision Points Architecture considerations • 5 Day Engagement Choice • If you are running a 5 day engagement, choose one of the following: • Create and Upgrade and Consolidation Test Plan • Run a proof of concept • Hardware Support • You may want to upgrade your hardware at the same time you upgrade your database • Take advantage of 64-bit technology • Processors have higher core density, more memory, solid state drives • Windows Support • SQL Server 2005 runs on Windows 2000, Windows Server 2003, Windows XP • SQL Server 2012 runs on Windows Server 2003 SP2 or later, Windows XP Pro SP2 or later • Other system software support • .NET Framework 3.5 SP1 or later • Windows Installer 4.5 or later • Internet Explorer 6 SP1 or later • Application Support • Many 3rd party applications restrict which version of SQL Server they support • For each database supporting a 3rd party application, you will need to do a separate investigation of version level support for SQL Server • In some cases a client or application-side upgrade will be necessary to upgrade to SQL Server 2012

  25. Day 2- Upgrade Schedule Timelines/Dependencies for Upgrade • For systems with dependencies (3rd party applications with restricted version support, for example) a longer upgrade time window may be required • Client updates may be required if new functionality is to be used • Required licenses must be acquired before the upgrades can proceed • Consolidation considerations may significantly slow down initial upgrades

  26. Day 2- Upgrade Strategies In-place versus side-by-side • In-place upgrade • Upgrade the existing instance in place • Will affect other instances on the same machine • Common files will be upgraded to SQL Server 2012 version • Minimizes other changes that might affect applications or users • Must use the same edition of SQL Server • Can’t upgrade from standard to enterprise, for example • Side-by-Side upgrade • Install another instance on the same machine, and then use a migration technique as described later in the presentation • Still requires a client change to point to new instance name • Use if you only wish to migrate some databases from a server

  27. Day 2- Upgrade Strategies (cont.) Database migration • Migration • Detach/attach • Detach your database(s), and then copy the database files and attach them to a new or existing SQL Server 2012 instance • Backup/restore • Use the latest backup from your database to restore to a new or existing SQL Server 2012 instance • Either method allows you to change editions, availability strategies, etc. • Also allows you to upgrade hardware and/or version of Windows Server • Master and MSDB considerations • If you migrate a database at a time, externals such as logins, linked servers, SQL Server Agent jobs will need to be scripted and migrated separately

  28. Day 2- Upgrade Drivers Rollback considerations • Strategies will vary based on the upgrade strategy selected • In all cases there are risks associated: • To mitigate risks of migration, make sure no data has changed since the upgrade (assuming that the original database is still in place) • If rollback is an important requirement: • Back-up all your data • Plan it early into your process • Practice your rollback strategy before beginning any upgrade efforts • Designing and testing this is outside the scope of this engagement

  29. Day 2- Consolidation Drivers Business value of consolidation Reduce Cap-Ex • Upgrade & standardize to fewer hardware • Reduce space, power and thermal needs Underutilized hardware Reduce Op-Ex Hardware computing capacity Improve Hardware Utilization Efficiency Improve Management Efficiency Lowered cost and complexity for High Availability Number of database apps Overburdened Administrators Number of DBAs Infrastructure Agility 1990 2000 2010 Load Balancing & Dynamic Provisioning Standardization of services

  30. Day 2- Consolidating database servers Why consolidate? Realize impressive savings by consolidating on SQL Server 2012, an enterprise-class, low-cost, feature-rich, and easy-to-use data platform Reduce cost Improve control Improve consistency • Standardize platform, easier integration, easier management • Reduce hardware, licensing and operational costs • Greater responsiveness to business needs and improved compliance Benefits of SQL Server 2012 • Flexible consolidation options • Multiple databases. Up to 32,767 databases in a single instance. Use Resource Governor to isolate workloads • Multiple instances. Enables database engine instances to co-exist on the same physical server • Virtualization: Use Windows Server 2008 Hyper-V for virtual server isolation on a single physical machine • Support for up to 256 logical cores & up to 2TB memory • On-premise or cloud-based data storage • Manageability • Powerful management tools. SQL Server Management Studio, SQL Server Agent and Central Management Servers • Policy-based management. Define configuration policies on one instance, and apply to many instances • Simpler management and deployment with Multi-Server Management • Security & Performance • Integrity of consolidated database. 3-levels of application isolation, Transparent Data Encryption, Enterprise Key Management • AlwaysOn High Availability • Huge data warehouse support

  31. Day 2- Consolidation Decision Points Ask the right questions • If consolidation efforts are combined with upgrade efforts, additional considerations apply • What kind of consolidation makes sense? • May be different for each database (or even each schema) in your existing systems • If evaluating virtualization – • Do you have the necessary Operating System Support? • Hyper-V or 3rd party virtualization solution? • Details of Consolidation Architectures are examined for the 10 day engagement

  32. Day 2- Client Upgrades Evaluate connectivity drivers and DBA workstations • When upgrading your servers to SQL Server 2012, you should evaluate whether client-side updates to connectivity drivers are required • May need to upgrade your DBA workstations as well • New version of SQL Server Management Studio • New BI Development Studio of supporting AS/IS/RS

  33. Don’t ignore if Present Day 2- Engagement Scope Reminder Document out-of-scope items You can’t ignore out-of-scope items if present! • Document that they are affected but out of scope • Customer can always engage you for those upgrades separately

  34. Day 2- Review Identified Servers Determine which servers are upgrade/consolidation candidates • Help the customer evaluate their consolidation strategy • This may involve deciding on a virtualization strategy • The implementation of both will be out of scope for anything but the POC during the 10 day engagement • With the upgrade and consolidation points in mind: • Spend the rest of the day identifying which servers from the MAP toolkit results are candidates for upgrade and consolidation • Keep connectivity and client application issues in scope

  35. Day 3 – Consolidate Findings Customer Prerequisites and Requirements 1 Assure Prerequisites 2 Identify Pain Points 3 Clarify Success Criteria • Assure that the customer has satisfied prerequisites • Has provided sufficient information about current environment • Identified people responsible for major components • Made time and personnel available • Define success • Get a clear statement of the customer’s view of success • Translate into clear criteria of a successful change • Identify major pain points motivating the upgrade or consolidation • Determine where upgrade or consolidation can help • Clearly define what problems upgrade or consolidation cannot solve

  36. Day 3- Three Day Engagement Ends Final steps • Have a close-out meeting with the customer • Identify lessons learned, provide agreed upon documentation • Great time to propose further consulting engagement to help upgrade the identified servers

  37. Day 4—5 Day Engagement Scope Choice Choose to Create a Plan or Run a POC If you are running a 5 day engagement, choose one of the following: If you are running a 10 day engagement, proceed to the next slide. Database upgrade or consolidation 1 instance as proof of concept • Initial Upgrade and Consolidation Plan • + Test Plan • Proceed to Next Slide • Proceed to Slide 55

  38. Day 4 – Upgrade and/or Consolidation Example Plan and Test Plan Example Plan • Consultant prepares the example scenario based on the MAP Toolkit results and the customer requirements • Reviews the upgrade and consolidation strategies with the client • Chooses an example scenario • Produces a plan for an example upgrade/consolidation • Begin production • of the test plan

  39. Day 4- Upgrade Strategies Compared In-Place versus Side-by-Side

  40. Day 4- Review Consolidation Strategies Identify the proper scenario(s) • Multiple Servers to Single Server • Consolidating to multiple databases on a single server • Consolidating to multiple instances of SQL Server on a single server • Consolidation to multiple virtual machines on a single server

  41. Day 4- Choosing an Example Scenario Criteria for a good scenario choice • Choose a scenario that has well defined and limited scope • Scope should be realistic to accomplish relatively quickly • Avoid VLDBs • Avoid HA and DR scenarios • Avoid any direct interaction with business critical data • Choose a scenario that has a high chance of success • Ensure the customer understands and can define success criteria

  42. Day 4- Example Scenario Requirements Steps to outline the requirements • Get background data and documentation • Use MAP Toolkit reports • Get customer definition of success criteria • Outline the upgrade or consolidation strategy

  43. Day 4- Upgrade/Consolidation Plan Strategy guide and test plan checklist • Produce a Step by Step guide for implementing the strategy • Use actual server names, account names, instance names • Identify all software versions • Address unresolved issues as well as can be done • Document all open issues • Checklist for the Test Plan • Identify projected milestones • Identify responsible parties for each step • Identify points to touch base and make decision

  44. Day 4 and 5 –Develop Test Plan Focus on upgrade/consolidation scenario • Create the test plan for the upgrade/consolidation effort • Detail steps to verify a successful upgrade/consolidation • See the document “Upgrade and Consolidation Services Test Plan Template_SSDPS”

  45. Day 4 and 5- Test Plan Defined For upgrade and consolidation • Purpose: Specify in detail what steps will be used for testing the results • State how the customer will verify the upgrade or consolidation has met its goals • Key: Specify what the customer defines as success • Key : Establish Testing Scope and Exclusions • Identifies Test Teams • Development team for unit testing • Test Team for system and integration tests • User Acceptance team and testing

  46. Day 4 and 5- Example Scenario Test Plan Details to include in the plan Items to include in the Test Plan: • Build a test or staging environment. • Pre-Install the .NET Framework 3.5 SP1 and SQL Server 2012 drivers on the target server • Execute Upgrade Advisor remotely to detect post-upgrade tasks • Test the upgrade in the test environment multiple times • When the upgrade is complete, apply any later scripts • Assure correct compatibility level • Develop test scripts to: • Compare objects on source and target servers • Test permissions through impersonation • Test application connectivity and operation • Develop and test a rollback

  47. Day 5- Finish Test Plan and Documentation 4 key tests: unit, system, integration, and acceptance testing Unit Test System Test • Specification of unit test cases and test data by the developer • Self-testing and defect fixing by the developer • Developer self-review is followed by a peer review Specify the tests for each component as a system Purpose is to verify database functionality after upgrade or consolidation Performed by Test Team May involve multiple cycles Defects logged and assigned severity Integration Test User Acceptance Test Specify the testing of multiple systems integrated with applications Ensures all security and performance requirements are met Performed by Test Team Should involve at least two cycles Defects logged and assigned severity Specify who will verify, and how, that business requirements have been met Should be performed by select subset of users SMEs in their application area More technically experienced Used for the final decision on the upgrade or consolidation For putting the system into production May not be the final sign-off, which may come after a short period in production

  48. Day 5- Five Day Engagement Ends Final steps • Have a close-out meeting with the customer • Identify any learnings, provide agreed upon documentation • Present the test plan • Great time to propose further consulting engagement to help upgrade the identified servers

  49. Day 6 – Database Upgrade/Consolidation Phase 1 • Use one SQL Server instance as a proof of concept • Select the system to upgrade/begin consolidation • Perform the database upgrade • May involve creating a virtual machine, may involve windows upgrade, hardware change • Will either be in place or a migration effort • If interested, you may evaluate rollback plans and try them out for day 6 for the selected database/instance • But don’t get too far in this unless it’s an explicitly agreed-to deliverable • May involve client side updates for connection strings • Ensure you document the upgrade process as performed • Record training tips and/or unusual work for the customer • Point out likely sticking points for future upgrades/migrations

  50. Day 7 – System Architecture Consolidation Review architecture considerations and use guide documents • Review additional/detailed architecture considerations as appropriate for the customer’s chosen direction for consolidation • Complete the Architecture-Design-Virtualization document for the customer • Provide references to appropriate content for training on the choices and considerations for the selected virtualization/consolidation approach • You may need to gather/filter whitepapers and other public content for the customer • Some reference whitepapers are provided as part of this offering

More Related