So you've decided to upgrade to SQL Server 2008 – what now? Let's take a look at some of the basics steps to ensure a successful migration, as well as the features you'll want to take advantage of right away.
Playing it safe
Your first step is to back up everything. Have a recent backup of the master, msdb and model SQL Server system databases, along with all of your own. Most SQL Server upgrades are done in place, so you'll be able to easily reinstall the old version and recover your data if you have good backups.
You can also do a point-to-point migration, or a "move." This is as simple as setting up your new SQL Server 2008 and using the built-in Copy Database Wizard to copy your databases over the wire. For larger databases, you have to detach them on the old server, copy the files to the new server and attach them. This is a fairly quick and reliable process, although you need to pre-create server login accounts from the old server so your attached databases can find them.
The migration itself
SQL Server migrations are typically straightforward. But remember that you must upgrade an entire instance from the prior version; you cannot pick and choose between databases. You should also ensure that your applications don't create complications with the new version. In my experience, though, moving from SQL Server 2005 to SQL Server 2008 doesn't present many issues that compromise an entire migration. If all you use is SQL Server's database services, you probably won't have any problems. If, however, you use SQL Server Reporting Services (SSRS) or SQL Server Integration Services (SSIS), which was formerly known as Data Transformation Services, you'll need to do some light reading before the migration. Microsoft offers specific instructions for migrations involving SSRS and SSIS to help limit ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
complications.
Microsoft also provides information on backward compatibility, or in other words, what is likely to break after a migration. Deprecated features aren't a problem, as SQL Server 2008 still supports them, but keep in mind that the next version may not. Discontinued features and breaking changes are what you really need to worry about. Most discontinued features, however, have been deprecated for several versions, so hopefully you won't run into any major issues.
New features to consider using immediately
Apart from core improvements such as performance enhancements and enhanced manageability, migrating to SQL Server 2008 doesn't offer many benefits in and of itself. you'll have to start taking advantage of new features by making changes to your databases and your applications.
My favorite new feature is the FILESTREAM data type. It takes binary large objects (BLOBs), such as the varchar (MAX) data type, out of the database files and moves them to standalone files on the Windows file system. This helps cut down on the database size and actually provides great performance (as it turns out, Windows is pretty good at reading and writing files all by itself).
To use this feature, you need to enable it in a database and change your columns to use the new data type. Applications will remain as they were, although you can improve their performance by modifying them to work directly with the new APIs for FILESTREAM columns.
You should also take advantage of the Policy-Based Management feature, which makes managing multiple SQL Server instances easier. It allows you to define centralized configuration policies, and SQL Server will enforce and report on those settings throughout the enterprise. Policy-Based Management, which requires no changes to your applications or databases to function, also allows you to run a T-SQL query against multiple servers from a centralized management server. This is (sort of) a part of the Policy-Based Management framework.
Performance management is improved in SQL Server 2008 with the new data collector feature (which, again, requires no database or application changes). The new data collector makes it easier to centrally collect performance information from multiple SQL Server instances.
If you deal with sensitive data, transparent data encryption improves SQL Server's encryption capabilities by transparently encrypting the entire database file rather than specific columns. You'll be delighted to know that this also requires little or no application changes – although there are some important interactions with other SQL Server features..
Finally, if you're dealing with compliance and auditing concerns, the new data auditing features help audit events like logons, password changes, data access, data modification, schema modification and more. It is a relatively easy feature to configure using the GUI, meaning you will not need a great deal of extra code, and its audit data is stored outside your database file, so you won't necessarily suffer a lag in performance there, either.
Dispelling the new features myth
It is a myth that all SQL Server features require database or application changes. Most of the half-dozen major new features I've described here can be implemented with little or no application changes, and most without any database changes at all. Any database changes required by the others are minimal at best. These features can improve performance, security and manageability, and their impact on SQL Server can bring you a long way toward justifying the relatively minimal effort involved in a migration.
ABOUT THE AUTHOR:
[IMAGE]Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.
