The Microsoft SQL Server 2005 tools enable you to connect to and manage earlier SQL Server versions, so you only have to install one toolset on your local workstation. It is one of the safest, but possibly more expensive, ways to upgrade. Because the older system is still available, your exit strategy normally contains just the steps to enter any data back into the old system that you have been entering on the new. If it is time to replace several servers, you may be able to re-architect the system such that you can use fewer of them.Īnother advantage in keeping your old system is that you do not have to put as much effort into an exit strategy. SQL Server 2005 includes new features that enable you to consolidate multiple functions, such as reporting, onto fewer servers. That strategy also allows you to run a parallel system (old and new), a common practice in complicated mission-critical environments.Īlthough this might sound expensive, it can actually save you money. If you do that, you can recycle the old hardware after the cutover to the new system.
New advances in hardware, especially with lengthy upgrade schedules, might mean that it is time to upgrade the hardware as well as the database engine. It may be that a rewrite makes more sense than just tacking on a new back end to the system.
The reason this works out is that major changes in the underlying platform create new architecture possibilities. Many organizations coincide major platform releases with new versions of their internal applications. You might want to keep your older system just as it is and add SQL Server 2005 into your landscape. Provide the name of the database and the location of the files and the system will "adopt" the databases. You then copy the mdf and ldf files to the new server, right-click the Databases item in the Object Browser in the SQL Server Management Studio, and select Attach Database from the menu. You can also do the same thing graphically by right-clicking the name of the database in Enterprise Manager or SQL Server Management Studio and selecting Detach Database from the menu. You can use the sp_dbdetach stored procedure on a SQL Server 7 or 2000 server, copy the mdf and ldf files to the SQL Server 2005 system, and then use the sp_dbattach stored procedure to copy and migrate the databases to a new server. SQL Server 2005 will restore an older database (7.) using the standard RESTORE DATABASE command or with the graphical tools I explain in this book. This is a more useful feature when you are going to wipe the old system clean and re-install the operating system or the database software. You can also back up your older databases on one system and restore them to the new one. It hides much of the complexity of the process from you. This process enables you to move all or part of the database from one server to another with a lot of control. The wizard guides you through the process of selecting the objects, data, and security information you want to migrate (and even lets you schedule the process). Right-click any database and select Copy Database from the menu that appears. Drill down to the Databases object in the Object Browser. You can access that tool by opening the SQL Server Management Studio and then connecting to the older database server or instance. The first method to migrate databases from an earlier version of SQL Server to the latest is to use the Copy Database Wizard within SQL Server 2005. Even within this method, you have options. This method provides a lot of flexibility when you do not want to bring over all the data or objects from an older system.
In a migration upgrade, you copy the data and database objects from an earlier version of SQL Server to 2005. You can have the users hosted on a separate system while you copy the items to the new, and then either "catch up" the transactions or use a differential output from the older system to the new to handle the time difference for the changeover. If you need to allow users to access their current applications during the upgrade, you can either follow the migration or side-by-side upgrade methods I show you in a moment. The strategy you choose should be based on a cost/benefit analysis for your situation. You can also keep both versions on one server (called a side-by-side install). This is useful if you are upgrading your hardware at the same time.
You can upgrade your current servers "in place," or you can migrate the databases and other objects from an older version on one server onto SQL Server 2005 on another system. You can implement SQL Server 2005 in a few ways in your organization.