sql 2012 upgrade readiness guide
Post on 31-Oct-2014
602 Views
Preview:
DESCRIPTION
TRANSCRIPT
SQL 2012 Upgrade Guide
Parikshit SavjaniSr. PFE, Microsoft
2
Click icon to add picture
SQL 2012/2008R2 Upgrade Planning
3
SQL 2012 Upgrade Flowchart
4
Click icon to add picture
Pre-Upgrade Tasks & Considerations
5
Allowable Upgrade Paths
SQL 2012 Upgrade Path
Source Instance
Minimum Version requirements
SQL Server 2000
Not Supported
SQL Server 2005
SP4 is required. (9.00.5000)
SQL Server 2008
SP2 is required. (10.00.4000)
SQL Server 2008 R2
SP1 is required (10.50.2500)
SQL 2008 R2 Upgrade Path
Source Instance
Minimum Version requirements
SQL Server 2000
SP4 is required.(8.00.2039)
SQL Server 2005
SP2 is required. (9.00.3042)
SQL Server 2008
RTM is required. (10.00.1600)
6
Allowable Upgrade Paths
SQL 2012 Upgrade Path
Windows OS
Minimum Version requirements
Windows 2003 Not Supported
Windows 2008 SP2 is required
Windows 2008 R2
RTM is required
Windows 2012 RTM is required
SQL 2008 R2 Upgrade Path
Windows OS
Minimum Version requirements
Windows 2003 SP2 is required
Windows 2008 SP2 is required
Windows 2008 R2
RTM is required
Windows 2012 RTM is required
7
Pre-UpgradeConsiderations
• Is the current SQL Instance version (Service Pack level) supported for upgrade.
• Is the current Windows OS version supported for upgrade
• Is the Application supported & tested on the latest version of SQL Instance.
• Do we have test environment or cert instance where we can test the upgrade first?
• Do we need to upgrade the complete instance (including All Databases,SSAS,SSRS etc) or partial instance (Few Databases)
• Do we have multiple instances of the server & all needs to be upgraded (In case of multiple instances of the Server, the shared components might be
upgraded to latest version impacting the other instance)
• Do we also need to upgrade the Edition of the SQL instance (For e.g Standard to Enterprise etc)
8
Pre-Upgrade Tasks
• Run SQL BPA to identify if there are any issues or deviation from the best practice which needs to be fixed first before upgrade
• You can download the SQL Server 2005 version of BPA at the SQL Server 2005 Best Practices Analyzer
• You can download the SQL Server 2008 R2 BPA at the SQL Server 2008 R2 Best Practices Analyzer
SQL Best Practice Analyzer
9
Pre-Upgrade Tasks
• Upgrade Advisor analyzes objects and code within legacy instances and produces reports detailing upgrade issues, if there are any, organized by SQL Server component.
• The resulting reports show detected issues and provide guidance about how to fix the issues or work around them
• Fixing all the issues identified by SQL Upgrade Advisor will ensure smoother upgrade & help identify known issues with upgrade.
• With fixing the issues identified, if we proceed with the upgrade, the upgrade will fail.
SQL Upgrade Advisor
10
SQL Upgrade Advisor Pre-requisites
Download the latest version of SQL Upgrade Advisor
Microsoft® SQL Server® 2012 Upgrade Advisor
X86 Package (SQLUA.msi)X64 Package (SQLUA.msi)
Microsoft® SQL Server® 2008 R2 Upgrade Advisor
X86 Package (SQLUA.msi)X64 Package (SQLUA.msi)IA64 Package (SQLUA.msi)
Requirements for running Upgrade Advisor are as follows:
• Windows Vista SP1, Windows 7, or Window Server 2008 R2
• The Microsoft .NET Framework 4 (the same version of the .NET Framework included with SQL Server 2012 and Visual Studio 2010)
• Windows Installer 4.5
• Pentium III-compatible processor or a later version, with a processor speed of at least 500 MHz
• 15 MB of available hard disk space
11
Click icon to add picture
Choosing the right Upgrade Strategies
12
Upgrade Strategies
In-place Upgrade
Using the SQL Server 2012 Setup program to directly upgrade an instance of SQL Server 2005, 2008, or 2008 R2. The older instance of SQL Server is replaced.
Side By Side Upgrade
Side by Side Upgrade involves a new installation of SQL 2012 instance along side with legacy SQL 2005\SQL 2008 instance on the same server or a separate server
13
In-place upgrade Strategy
Pros• Easier & faster, especially for small systems, because data and
configuration options do not have to be manually transferred to a new server.
• Automated process.
• Upgraded instance has the same name as the original.
• Applications continue to connect to the same instance name.
• No additional hardware is required because only the one instance is involved. However additional disk is required by Setup
• Takes the least deployment team resources.
14
In-place upgrade Strategy
Cons
• You must upgrade the whole instance or a major SQL Server component. For example, you cannot directly upgrade a single database.
• You must inspect the whole instance for backward-compatibility issues and address any blocking issues before SQL Server 2012 Setup can continue.
• Upgrading in place is not recommended for all SQL Server components, such as some SQL 2000 DTS packages.
• Because the new instance of SQL Server 2012 replaces the legacy instance, you cannot run the two instances side by side to compare them. Instead, you should use a test environment for comparisons.
• Rollback of upgraded data and the upgraded instance in an in-place upgrade can be complex and time-consuming
15
Side By Side Upgrade
Pros
• It gives more granular control over which database objects are upgraded.
• The legacy database server can run alongside the new server. You can perform test upgrades and research and resolve compatibility issues without disturbing the production system.
• The legacy database server remains available during the upgrade, although it cannot be updated for at least the time that is required to transfer data.
• Users can be moved from the legacy system in a staged manner instead of all at the same time.
• Even though your system might have passed all validation and acceptance tests, a problem could still occur. But if a problem does occur, you will be able to roll back to the legacy system.
16
Side by Side Upgrade
Cons
• A side-by-side upgrade might require new or additional hardware resources.
• If the side-by-side upgrade occurs on the same server, there might be insufficient resources to run both instances alongside one another.
• Applications and users must be redirected to a new instance. This redirection might require some recoding in the application.
• You must manually transfer data—as well as security, configuration settings, and other supporting objects—to the new instance.
• Synchronization of data from the legacy server to a new server will be required to capture data modifications that occurred to the legacy system while setting up the new system and its original copy of the data.
Side by Side UpgradeIn Place Upgrade
Rollback Strategy
Manual Effort
Additional Hardware
Downtime Required
Very Complex Easy as legacy instance is up & running
Minimum if upgrade is successful More to transfer data, security, jobs etc
Not Required Additional Storage or Server required to install new instance
Less if upgrade is successful but is very large if rollback is executed Time required to transfer data
Decision Matrix
Easier & Faster
Easier & Faster Time consuming & involved manual effort
Preferred for
Small databases & less critical applications. Very Large Databases & Databases with Replication, Mirroring & Log Shipping
18
Click icon to add picture
Testing the Upgrade Strategy
19
Testing
Testing is the essential step especially in an in-place upgrade strategy to identify the possible bottlenecks, shortcomings which we can face while performing an upgrade.
• To test an in-place upgrade, we need to ensure that test (Cert) SQL Instance is near duplicate replica of the Production instance in terms of database, database properties, server configuration & application components which can connect to the cert environment similar to production.
• To test a side-by-side upgrade, we need to install a new instance of SQL 2012/2008R2 on the cert/test environment and transfer all the databases, logins, jobs, linked servers, Maintenance Plans, SSIS packages, SQL Configuration settings to the new server.
• Document any issues\bottlenecks identified during testing.• Document the time for each steps involved in the backup.• Following the Upgrade on the test environment, ask the application
team to fully functionally test the application
20
Click icon to add picture
Downtime Requirements & Rollback Strategy
21
Downtime Requirements & Rollback Strategy
In-place Upgrade
• The Downtime requirements for In-place upgrade would be minimal if ran successfully and can be best estimated from testing.
• The rollback strategy for in-place upgrade involves uninstalling & cleaning up the new SQL instance, reinstalling the previous SQL instance upto the latest service pack level followed by restoration of all the system & user databases.
• Considering the complex rollback plan for in-place upgrade, the overall downtime for in-place upgrade might be much higher than side-by-side upgrade if the rollback plan is executed.
• Rollback Plan time must be accounted in the overall Maintenance window of the upgrade
22
Downtime Requirements &Rollback Strategy
Side-by-Side Upgrade• The downtime requirements for Side-by-Side Upgrade would be higher
since it involves transferring or moving the data from one instance to another.
• The downtime can be reduced by configuring database mirroring between the legacy & new SQL instance & switchover during the maintenance window.
• The rollback strategy for side-by-side upgrade involves starting the legacy instance of SQL Server which might be up & running and restoring or attaching the databases back to the legacy instance.
• Backups from the higher version of SQL Server cannot be restored back to the lower version of SQL Server which means Rollback Plan should include the transfer of the data which might be new added, modified or deleted in the new instance of SQL Server.
• Rollback Plan time must be accounted in the overall Maintenance window of the upgrade
23
Before you Upgrade
• Run DBCC CHECKDB on all system and user databases – address any errors that are found.
• Backup everything (system dbs, user dbs, registry, sys db settings, config options, encryption keys, etc) and save off the backups.
• Capture Performance baselines metrics to measure the resource utilization, user workload.
• Choosing the Upgrade Strategy, running Upgrade Advisor & testing should have be completed and issues should be addressed
• Develop Successful Upgrade Acceptance criteria
• Develop & test Rollback Plan
• Estimate Downtime required and account for rollback time in the downtime estimation
24
Click icon to add picture
Post Upgrade Tasks
25
Post Upgrade Task (In Place Upgrade)
• Apply latest service pack for SQL Server following upgrade
• Re-configure Log-shipping, Database Mirroring or Always-ON
• Run FTS Crawl and check Crawl log in case of Full text catalogs
• Execute DBCC CHECKDB WITH DATA_PURITY
• Run DBCC UPDATEUSAGE
• Update statistics by using the sp_updatestats
26
Post Upgrade Task (Side by Side Upgrade)
• Transfer Logins to new instance & check of orphaned users (if any)
• Transfer Linked server
• Transfer Encryption keys on new instance
• Transfer Jobs & DB Maintenance Plans
• Configure DBA Jobs & alerts on new instance
• Transfer & Deploy SSIS Package on new instance
• You will have to restore the following settings after the upgrade: is_broker_enabled
is_honor_broker_priority_on
is_trustworthy_on
• Execute DBCC CHECKDB WITH DATA_PURITY
• Run DBCC UPDATEUSAGE
• Update statistics by using the sp_updatestats
• Run FTS Crawl and check Crawl log in case of Full text catalogs
• Reconfigure Log-shipping, Database Mirroring or Always-ON
• Update Application connection strings to point to the new instance
Contact
Parikshit Savjani
Premier Field Engineer
pariks@Microsoft.com
http://www.sqlserverfaq.net
top related