4
SQL Server 2012 Upgrade Technical Guide
Determine Application Acceptance ......................................................................................................... 77
Run the SQL Server 2012 Best Practices Analyzer ............................................................................. 77
Troubleshooting an Upgrade .................................................................................................................... 77
Decommission and Uninstall After a Side-by-Side or New Hardware Upgrade .................... 78
Considerations for Upgrading without a DBA ................................................................ 79
Conclusion ...................................................................................................................................... 81
Additional References ............................................................................................................... 81
Chapter 2: Management Tools ........................................................................... 82
Introduction ................................................................................................................................... 82
Feature Changes in SQL Server 2012 Management Tools ........................................ 82
Changes in SSMS ........................................................................................................................................... 82
Database Engine Tuning Advisor ............................................................................................................. 85
Changes in SQL Server Configuration Manager ................................................................................. 85
Changes in SQL Server Profiler ................................................................................................................. 85
Preparing to Upgrade ................................................................................................................ 86
Deprecated Features ..................................................................................................................................... 86
Discontinued Functionality ......................................................................................................................... 86
Breaking Changes .......................................................................................................................................... 87
Behavior Changes .......................................................................................................................................... 87
Upgrade Tools ................................................................................................................................................. 88
64-Bit Considerations ................................................................................................................................... 88
Known Issues and Workarounds .............................................................................................................. 89
SQL Server Agent ........................................................................................................................................... 89
In-Place and Side-By-Side Upgrade .................................................................................... 90
Upgrading from SQL Server 2005 ............................................................................................................ 91
Upgrading from SQL Server 2008/2008 R2 .......................................................................................... 92
Project Files ...................................................................................................................................................... 92
Post-Upgrade Tasks ................................................................................................................... 92
Database Maintenance Plans..................................................................................................................... 93
Database Diagrams ....................................................................................................................................... 93
5
SQL Server 2012 Upgrade Technical Guide
Conclusion ...................................................................................................................................... 94
Additional References ............................................................................................................... 94
Chapter 3: Relational Databases ........................................................................ 95
Introduction ................................................................................................................................... 95
Relational Database Configurations .................................................................................... 95
Upgrade Considerations........................................................................................................... 96
Full-Text Search .............................................................................................................................................. 97
What Can Be Upgraded? ............................................................................................................................. 98
What Cannot Be Upgraded? ...................................................................................................................... 99
In-Place Upgrade vs. Side-by-Side Upgrade ................................................................ 101
In-Place Upgrade.......................................................................................................................................... 101
Side-by-Side Upgrade ................................................................................................................................ 101
Evaluating Potential Upgrade Issues ................................................................................ 108
Deprecated Features ................................................................................................................................... 108
Discontinued Functionality ....................................................................................................................... 108
Breaking Changes ........................................................................................................................................ 109
Behavior Changes ........................................................................................................................................ 111
Preparing for an Upgrade ..................................................................................................... 111
Preparing for an In-Place Upgrade ........................................................................................................ 112
Preparing for a Side-by-Side Upgrade ................................................................................................ 116
Performing an Upgrade ......................................................................................................... 117
Performing an In-Place Upgrade ........................................................................................................... 117
Performing a Side-By-Side Upgrade .................................................................................................... 118
Post-Upgrade Tasks ................................................................................................................ 120
In-Place Upgrade.......................................................................................................................................... 120
Side-by-Side Upgrade ................................................................................................................................ 121
General Post-Upgrade Tasks ................................................................................................................... 121
Use Plan Hints ............................................................................................................................................... 122
Important Information About Query Plans ........................................................................................ 123
Connecting Client Applications to SQL Server 2012 ................................................. 123