Transcript Effective Usage of SQL Server 2005 Database Mirroring

Upgrading To SQL Server 2005 & 2008:
Notes & Best Practices
Satya Shyam K Jayanty
[email protected]
Venue: Manchester Metropolitan University
Manchester, UK 28th March 2009
1
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
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’
3
lounge.
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
4
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
5
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
6
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
7
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
8
9
SQL Server 2008 Upgrade Technical Reference Guide
By Microsoft Corporation
http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f56902-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
19
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
20
21
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
22
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
23
24
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
25
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.
26
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
27
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-sqlserver-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
28
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
29
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
30
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
31
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
32
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 autogrow
• 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
33
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
34
Rolling Upgrades
Key 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
6. Upgrade the new Mirror
SQL Server Upgrade Best Practices [SQLBits
IV]- www.sqlserver-qa.net
An approach to minimize
downtime while upgrading
from SQL Server 2005 to
SQL Server 2008.
35
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/enus/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
36
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
37
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-byside)
SQL Server Upgrade Best Practices [SQLBits
IV]- www.sqlserver-qa.net
38
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
39
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
40
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&Fam
ilyID=24da89e9-b581-47b0-b45e-492dd6da2971)
SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net
41
• Complete feedback forms!
Thank you & have a great day ahead.
SQL Server Upgrade Best Practices [SQLBits IV]- www.sqlserver-qa.net
42