HP StorageWorks Storage Mirroring application notes Guidelines for Using Storage Mirroring to Upgrade from SQL Server® 2005 to SQL Server 2008 Part number: T5437-96014 First edition: December 2009
Legal and notice information © Copyright 2009 Hewlett-Packard Development Company, L.P. Hewlett-Packard Company makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. Hewlett-Packard shall not be liable for errors contained herein or for incidental or consequential damages in connection with the furnishing, performance, or use of this material.
Document overview This application note provides guidelines on the use of HP StorageWorks Storage Mirroring in a specific environment. This document contains: • Document overview—Explains what an application note contains, how it should be used, what you need to know before trying to use the application note, and where you can go for more information. • Solution overview—Explains how the solution works with Storage Mirroring.
HP StorageWorks has application notes that describe how to configure Storage Mirroring with a variety of popular third-party applications. These application notes and other support help are available at http://www.hp.com/go/storage. For help using Storage Mirroring, refer to the Storage Mirroring online manual or online help. HP provides a support website at http://welcome.hp.com/country/us/en/support.
Figure 2 SQL server replication . 4. (Optional) Detach the databases from the SQL Server 2005 source using the stored procedure sp_detach_db from within SSMS, or using sqlcmd on the source server. 5. 6. Disconnect clients so that they cannot access the source. Stop any applications from accessing SQL data on the source, and stop application and SQL services on source. Verify that all data has been sent to target. Disconnect the replication set between the source and target.
Figure 3 SQL server replication results . 10. The master and msdb database objects related to the databases moved must be moved manually to the SQL Server 2008 instance, including logins, jobs, and alerts. 11. Either change the target identity to match the SQL 2005 server, then restart the target (so that clients can connect using the original server identity), or redirect clients and applications to the new SQL 2008 server.
Caveats and special considerations • If the user-created database(s) you are trying to migrate already exist on the target (for example, if there is a duplicate name because of a previous test), you must detach, delete, or otherwise get rid of that target database. Otherwise, you will need to replicate the source copy to a unique location and attach the database using a different name.
2. Record the drive and directory path(s) where the source SQL server database and log files are stored. The default directory for SQL 2005 is :\Program Files\Microsoft SQL Server\MSSQL\Data. Table 1 Source SQL database and log file paths Install software on the target 1. 2. Install SQL Server 2008 on the target, if it is not already installed.
path. (For detailed information on connecting a source and target, see the HP StorageWorks Storage Mirroring User’s Guide.) 10. If your desired target path is identical to the source path, select the One to One option in the Mappings section. If you want all of the source files to go to a single location, select the All to One option in the Mappings section. You may edit the Target Path shown by clicking on it.
-- environments or configurations are exactly the same, you MUST modify -- this file in order to make the solution work in your environment. EXEC sp_detach_db 'Sales', true EXEC sp_detach_db 'Accounting', true EXEC sp_detach_db 'Marketing', true Disconnect clients and verify that queues are empty 1. When you are ready to perform the migration, disconnect any clients that may be accessing the source databases.
1. Using Notepad or any text file editor, create a SQL command file and save it with a .sql extension. The file will utilize the stored procedure sp_attach_db. The following sample file illustrates the command syntax for attaching three sample databases, named Sales, Accounting, and Marketing. Note that the complete path and filespec to the database and corresponding log files on the target must be included. Refer to the file paths recorded in “Install software on the target” on page 8.
Post-upgrade recommendations The following procedures should be performed after the upgrade is complete. Update statistics To ensure optimal performance of an upgraded database, Microsoft recommends running SP_UPDATESTATS (update statistics) against the upgraded database on the SQL Server 2008 server. Updating statistics using SP_UPDATESTATS is described in more detail in the SQL Server 2008 documentation at http://msdn.microsoft.com/en-us/library/ms187348.aspx.
