10
SQL Server 2012 Upgrade Technical Guide
Upgrading to SQL Server 2012 Standard Edition ............................................................................ 246
Conclusion ................................................................................................................................... 246
Additional References ............................................................................................................ 247
Chapter 9: SQL Server Data Tools .................................................................... 248
Introduction ................................................................................................................................ 248
Preparing to Upgrade ............................................................................................................. 248
Upgrading Visual Studio 2010 Database Projects to SSDT .................................... 248
Breaking and Behavior Changes ........................................................................................ 250
SQL Server Database Project Template ............................................................................................... 250
Schema Definition Files .............................................................................................................................. 250
Schema Comparison Files ......................................................................................................................... 251
SQLCMD Variable Definitions .................................................................................................................. 251
Server Logins ................................................................................................................................................. 252
Other Breaking and Behavior Changes ................................................................................................ 252
Pre-Upgrade Tasks ................................................................................................................... 253
Convert .dbschema Files to .dacpac Files............................................................................................ 253
Convert SQLCMD Variable Definitions................................................................................................. 253
Post-Upgrade tasks ................................................................................................................. 255
Change the Debugging Instance for Full-Text Search ................................................................... 255
Correct Linked Server Definitions That Use SQLCMD Variables ................................................ 255
Additional References ............................................................................................................ 256
Chapter 10: Transact-SQL Queries ................................................................... 257
Introduction ................................................................................................................................ 257
Preparing to Upgrade ............................................................................................................. 257
Backup and Rollback Plan ......................................................................................................................... 258
Deprecated Features ................................................................................................................................... 258
Discontinued Features ................................................................................................................................ 260
Breaking Changes ........................................................................................................................................ 262
Behavior Changes ........................................................................................................................................ 267
Upgrade Tools ............................................................................................................................................... 277
11
SQL Server 2012 Upgrade Technical Guide
64-Bit Considerations ................................................................................................................................. 278
Known Issues and Workarounds ............................................................................................................ 278
Upgrading from SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2 280
In-Place Upgrade.......................................................................................................................................... 280
Side-by-Side Upgrade ................................................................................................................................ 281
Post-Upgrade Tasks ................................................................................................................ 281
Conclusion ................................................................................................................................... 281
Additional References ............................................................................................................ 282
Chapter 11: Spatial Data ................................................................................... 283
Introduction ................................................................................................................................ 283
Preparing to Upgrade ............................................................................................................. 283
Deprecated Features ................................................................................................................................... 283
Discontinued Functionality ....................................................................................................................... 284
Breaking Changes ........................................................................................................................................ 284
Behavior Changes ........................................................................................................................................ 289
Post-Upgrade Tasks ................................................................................................................ 291
Conclusion ................................................................................................................................... 291
Additional References ............................................................................................................ 291
Chapter 12: XML and XQuery ........................................................................... 292
Introduction ................................................................................................................................ 292
Preparing to Upgrade ............................................................................................................. 292
Deprecated Features ................................................................................................................................... 292
Discontinued Functionality ....................................................................................................................... 293
Breaking Changes ........................................................................................................................................ 293
Behavior Changes ........................................................................................................................................ 296
Post-Upgrade Tasks ................................................................................................................ 297
Features Added in SQL Server 2012...................................................................................................... 297
Features Added in SQL Server 2008...................................................................................................... 298
Conclusion ................................................................................................................................... 300