Side-by-side migration

Side-by-side migration:

A side-by-side upgrade consists of installing SQL Server 2008 and moving the databases from the old instance to the new instance. The side-by-side method gives us a chance to test the effects SQL Server 2008 will have on the application before moving from the older version. The new instance can be installed on a second server, or we can use the same server provided it meets the installation requirements. Migration provides access to two instances of the system, letting us verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

Once we have installed the new instance of SQL Server 2008, there are three methods for moving the databases to the new instance:

• Copy database Wizard
• Database Backup and Restore
• Detaching and Attaching

Installing SQL Server 2008 in this side-by-side approach is no different from doing a fresh install. However, with a side-by-side upgrade, we need to worry is about how to migrate the database to the new instance.

Copy database Wizard: The Copy Database Wizard lets us move or copy databases and their objects easily from one instance to another instance, with no server downtime. We can use the Copy Database Wizard to copy our database to an upgraded instance of SQL Server 2008. We are given the option to make a copy of the database or completely move the database. We may also choose to copy the database by using the detach and attach method, or by using SQL Management Objects (SMO). If we use the detach and attach method from the wizard, we need to make sure that there are no users trying to access the database before running the wizard. If we use SMO, the database will remain online during the entire process. We can also choose to move any database-related objects, such as logins and jobs. We should point out that while we can copy logins using the Copy Database Wizard, for security reasons, the wizard creates the login on the destination server with a random password and then disables the login.

Using this wizard, we can do the following.

1)     Pick a source and destination server.

2)     Select databases to move or copy.

3)     Specify the file location for the databases.

4)     Create logins on the destination server.

5)     Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

6)     Schedule when to move or copy the databases.

NOTE: The Copy Database Wizard creates a SQL Server Agent job that executes an SSIS package. We need to make sure that we have Integration Services installed and the SQL Server Agent running on the destination server prior to executing the Copy Database Wizard.

Database Backup and Restore: Using the backup and restore method is a good way to copy the database to the new instance without impacting the availability of the current database. All we need to do is take a full backup of the current database, copy that backup to the new location, and restore it. SQL Server will upgrade the database during the restore process. The result will be a SQL Server 2008 database that we will not be able to move back to an earlier release.

We need to copy any objects outside the database, such as logins and jobs, since we cannot restore system databases to a newer version of SQL Server.

Detaching and Attaching: We can prefer the detach and attach method when permanently moving databases to a new instance of SQL Server. By moving each file itself instead of a copy of the file at any given point in time, we can be sure that we have captured the exact state of the database as it existed on the previous instance. Since the database is detached, it will be inaccessible for any transactions, ensuring that no data will be committed on the old system during the upgrade. Detaching the database also helps to validate that all the connections have been reconfigured to point to the new system.

We can use the sp_detach_db stored procedure to detach a database. We should also set the database to single-user mode and immediately roll back any transactions before trying to detach the database.

T-SQL Script to Detach a Database:

USE [master] GO ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC master.dbo.sp_detach_db 'DatabaseName' GO

To attach a database in SQL Server 2008, we should use the CREATE DATABASE statement with the FOR ATTACH clause. This statement and clause replace the sp_attach_db stored procedure that was previously used to attach a database. The sp_attach_db stored procedure has been deprecated and will be removed in a future release. We will also need to specify the locations of the data files that are going to be attached.

T-SQL Script to Attach a Database:

USE [master] GO CREATE DATABASE [DatabaseName] ON ( FILENAME = N'C:\MSSQL\DATA\DatabaseName.mdf' ), ( FILENAME = N'C:\MSSQL\DATA\DatabaseName_log.ldf' ) FOR ATTACH GO

We can detach and attach databases using the GUI in SQL Server Management Studio.

To detach a database using the GUI, we can right-click on the database we want to detach, select Tasks, and then select Detach from the context menu. This will bring up the Detach Databases screen. Select OK to detach the database.

To attach a database using the GUI, we can right-click on the Databases folder and select Attach from the context menu. This will bring us to the Attach Databases screen. Selecting Add will bring up the Locate Database Files screen, which will allow us to navigate to the data file we would like to attach. Once we have selected the data file, select OK to close the Locate Database Files screen and OK once again on the Attach Databases screen to close the screen and attach the database.