High-level Upgrade Project Plan
Here is a high level checklist which can be built upon to derive an actual project plan.
On the current production:
Before Upgrade:
On UAT Server:
Before Upgrade:
During Upgrade:
After Upgrade:
On new Prod Server:
Before Upgrade:
During upgrade:
Additional steps for excellence (Post Upgrade)
Contingency plan:
Develop Acceptance Criteria and Rollback Steps:
An upgrade plan must consider all possible possibilities. Some might be ignored as improbable, but for all likely scenarios, the highest priorities must be to protect any production data and to be able to restore the system to its original state if it is required. The following tasks help provide that protection:
General Post-Upgrade Tasks:
Following features are disabled by default in new installations:
Upgrading Log Shipped Databases:
This section covers how to upgrade databases that are using the built-in log shipping feature. This section assumes an in-place upgrade of the instance containing either the primary or secondary database.
Feature Changes in SQL Server 2008 Log Shipping:
Unlike in failover clustering, any objects that reside outside the database itself must be manually accounted for. This means that items such as SQL Server Agent jobs, SQL Server-level logins, linked servers, and anything not self-contained within the database must be either scripted or moved to the destination server in another way. Think of this process as a disaster recovery drill: you need to take the same steps to prepare for the upgrade as you would if preparing to perform a role change. See "Script or Export All Objects " above for information about getting objects out of SQL Server and how to perform such tasks as synchronizing logins.
Whether you want to upgrade a SQL Server 2000 database participating in log shipping or a SQL Server 2005 database participating in log shipping, if an upgrade occurs and the secondary database is left in either STANDBY or NORECOVERY mode, the secondary database will not be upgraded to a SQL Server 2008 database until it is brought online WITH RECOVERY.
SQL Server 2008 supports one new log shipping feature not in SQL Server 2005 log shipping: backup compression. Backup compression, available only in SQL Server 2008 Enterprise, by default is not enabled after the upgrade to SQL Server 2008. To enable it, go into the log shipping configuration and manually enable it on the primary database.
Log Shipping Upgrade Scenarios:
SQL Server 2000 and SQL Server 2005 have similar upgrade scenarios for log shipping. The two scenarios are upgrading with or without a role change. A role change is the log shipping-specific terminology for the process of switching the current primary database to promote the secondary database as the new primary.
Upgrade with a Role Change:
In this upgrade scenario for log shipping, a role change is performed to shorten downtime. When upgrading with a role change, the only time the database should be unavailable is during the actual switch from the primary database to the secondary database. In theory, the process sounds simple, but in reality, it is more complex.
When the instance containing the secondary database is upgraded, the secondary database will not be upgraded because it is still in a state to accept the loading of transaction log backups. This means that after the instance is upgraded, there is the ability to apply the transaction logs from the primary database. However, once the cutover is started, the primary database can no longer be used because there is no way to keep the two databases synchronized: when a database is brought online WITH RECOVERY, it can no longer accept transaction logs. In addition, after the secondary becomes the new primary, all users and applications would need to be redirected to the new instance and database. If that process is not centrally managed, many desktops would need to be touched to achieve the switch. Thus, this is not a recommended upgrade scenario.
At this point, although the old primary database could be upgraded, log shipping would no longer be configured. You would have to completely reconfigure log shipping, which means making a backup from the new primary and restoring it on the "new" secondary (the old primary). If the database is small, that might not be difficult. But if you are dealing with a VLDB or limited network bandwidth for copying a full database backup—not to mention the time it would take to restore—upgrading without a role change might be a better option.
On the plus side, besides reducing downtime, upgrading with a role change provides a built-in fallback plan. If the SQL Server 2008 upgrade does not work for any reason or is not accepted after a few days, the original instance and database would be nearly ready to go. You would just need to use a manual method of getting the updated data out of SQL Server 2008.
Upgrade without a Role Change:
When upgrading without a role change, an upgrade is performed on an existing instance of SQL Server without doing the role change. This will preserve the state of the databases. But depending on the version of SQL Server you are upgrading (see the specific sections for SQL Server 2000 and SQL Server 2005 below), it might or might not upgrade log shipping in the process. This option generally has more downtime associated with it, but for those who cannot afford to reinitialize log shipping, upgrading without a role change might be a better option.
Unconfigure Log Shipping:
Similar to upgrading without a role change is the option of completely removing the log shipping configuration for a particular database. This would yield a result similar to upgrading without a role change. But in the case of SQL Server 2000, it might leave a cleaner upgrade, as we will discuss in the next section. Because a SQL Server 2008 transaction log cannot be applied to a SQL Server 2000 or SQL Server 2005 database, log shipping would need to be reinitialized.
Upgrading from SQL Server 2000 to SQL Server 2008 Log Shipping:
There is no direct upgrade path for log shipping configurations on a SQL Server 2000 database. However, you can migrate from SQL Server 2000 log shipping to SQL Server 2008 log shipping. Microsoft first introduced log shipping in SQL Server 2000 Enterprise. Log shipping was configured as part of the Database Maintenance Plan Wizard. Although it was mainly used to expose stored procedures triggered by SQL Server Agent jobs as well as xp_sqlmaint, it could not be set up via stored procedures. When Microsoft released SQL Server 2005, it changed how log shipping could be configured, and log shipping was no longer integrated and associated with a database maintenance plan.
Upgrading Replicated Databases
Side-by-Side Upgrade to a New Server or Cluster:
Snapshot replication tends to be the easiest in terms of a non-in-place upgrade because it is the least complex process.
Side-by-Side Upgrade to a New Server or Cluster:
When not upgrading merge replication in-place, the upgrade process complexity will depend on the complexity of the replication topology and which components will be changed to SQL Server 2008. Tackle the switch to the new instances in logical groups, starting from the top down.
Side-by-Side Upgrade to a New Server or Cluster:
When transactional replication will not be upgraded in-place, determine the complexity of the topology. Tackle the switch to the new instances in logical groups, starting from the top down. The complexity of this form of upgrade will depend on which pieces are going to be changed.
In the replication topology, where you are not doing an in-place upgrade of an existing instance to SQL Server 2008, install a new instance of SQL Server 2008. This may be for a Publisher, a Distributor, or a Subscriber.
Note on moving logins between SQL 2005 to SQL 2008:
Sp_helprevlogin changes for SQL 2008: http://support.microsoft.com/kb/918992 (‘How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008’)
Side-by-side Upgrade:
In a side-by-side upgrade, instead of directly replacing the older instance of SQL Server, required database and component data is transferred from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008. It is called a "side-by-side" method because the new instance of SQL Server 2008 runs alongside the legacy instance of SQL Server 2000 or SQL Server 2005, on the same server or on a different server.
There are two important options when you use the side-by-side upgrade method:
Both options let you run the new instance of SQL Server 2008 alongside the legacy instance of SQL Server 2000 or SQL Server 2005. Typically, after the upgraded instance is accepted and moved into production, you can remove the older instance.
Whether a side-by-side upgrade is to a separate instance on the same server or to a new instance on another server, data must be transferred in what is mostly a manual process. The result is two instances, legacy and new, that can run side by side.
As just noted, the key point in a side-by-side upgrade is that you must manually transfer data files and other supporting objects from the older instance of SQL Server to the instance of SQL Server 2008. The SQL Server 2008 Setup program will not perform this task. The objects that you must transfer include the following:
Steps for using Upgrade Assistant:
Whether you perform a side-by-side upgrade on the same server or separate servers, consider the following points:
In a side-by-side upgrade to a different server, consider the following additional item:
Now, perform final tests on the new SQL Server 2008 server. To rerun the tests and gain confidence in the plan and deployment, repeat the process by restoring the baseline target server.
Upgrade Known Issues:
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/6d43c321-8fe7-45a7-8406-8ec875e05ca9/
SQL Server 2000 to 2008 Upgrade Whitepaper:
http://www.google.co.in/url?sa=t&source=web&cd=2&ved=0CB4QFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F2%2F0%2FB%2F20B90384-F3FE-4331-AA12-FD58E6AB66C2%2FSQL%2520Server%25202000%2520to%25202008%2520Upgrade%2520White%2520Paper.docx&ei=z6ylTfS5F8SqrAezgN3lCQ&usg=AFQjCNHREhaRPPZE_tjdjNENilWNKPNpIw
SQL Server 2008 Upgrade Technical Reference Guide:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en
Windows Service Account:
http://msdn.microsoft.com/en-us/library/ms143504.aspx
http://msdn.microsoft.com/en-us/library/ms143504(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms191543.aspx
Here is a high level checklist which can be built upon to derive an actual project plan.
On the current production:
Before Upgrade:
- Best Practice Analyzer for SQL Server 2000
- Execute Upgrade Advisor and resolve necessary issues
- Backward compatibility
- Deprecated features
- Discontinued features
- Breaking changes
- Behavior changes
- SQL-DMO (Use SMO)
- Osql/isql Utility (Use SQLcmd)
- Rebuild.exe (Rebuilddatabases option from setup.exe)
- SQLMail (Use Database mail)
- SQL Server 2008 upgrade assistant (Optional) or SQL Server Profiler (Replay Trace)
- Continue capturing baselines of performance data and queries
- SQLDiag/PSSDiag
- RML Utilities: http://support.microsoft.com/kb/944837
- SQL Nexus: http://www.codeplex.com/SQLNexus
- PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
- Identify and tune high-resource consumption queries
- Run DBCC CHECKDB on the databases to be upgraded to make sure that they are in a consistent state.
- Make sure that the user databases to be upgraded are set to autogrow and that the PRIMARY file group of each user database has sufficient disk space. Additional disk space is required to allow for the additional space that is required for the PRIMARY file group when you install SQL Server 2008. You can turn off this option after the upgrade is complete.
- Make sure that the log file for each user database is set to autogrow and has sufficient additional disk space. Additional space is required by transaction log files of user databases. You can turn off this option after the upgrade is complete if it is required by your application or database maintenance plan.
- Set the AUTO_UPDATE_STATISTICS option to ON before upgrading to SQL Server 2008. Statistics are not upgraded as part of the upgrade process, and relying on statistics from previous SQL Server releases might result in suboptimal query plans. By setting the AUTO_UPDATE_STATISTICS option to ON, all statistics are updated when they are first referenced.
- Make sure that no pending replication activities on server. Stop & Break the Replication.
- Stop & Break the Log shipping
- Make sure that you have current backups of the databases that you are upgrading by using the BACKUP DATABASE command, and verify their validity by using RESTORE VERIFY ONLY before you start the upgrade process. If you use the detach/attach method, you should make sure that you copy instead of move the data files so that in the unlikely event something should go wrong, you can quickly and easily reattach your data files on the original SQL Server 2000 or SQL Server 2005 instance.
On UAT Server:
Before Upgrade:
- Deploy new hardware and OS / cluster
- Run SQLIOSim and other pre-deployment checks on new hardware
- Install SQL Server 2008 on new server with all patches and optimizations
- Establish a UAT environment for SQL Server 2008 and Install latest Service Pack
During Upgrade:
- Restore fresh copies of the production databases on UAT
After Upgrade:
- Set the database compatibility levels to 100
- Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range. After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database, you do not have to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity." This is the only DBCC CHECKDB check that you must run as a post-upgrade task.
- Update statistics on all databases after you upgrade them. Execute UPDATE STATISTICS in user-defined tables in SQL Server databases. (Full Scan)
- Execute DBCC UPDATEUSAGE on all attached databases to update usage counters and make sure that correct values exist for table and index row counts.
- Repopulate full-text catalogs.
- Migrate logins, jobs to new SQL instances (Save the script to run on new prod server)
- Logins ( sp_helprevlogin)
- Jobs
- Linked Servers
- Setup Replication
- Setup Log Shipping
- Make sure that the relational databases are working correctly by executing a sample set of queries.
- Update any scripts affected by SQL Server 2008 behavior changes. (Save the script to run on new prod server)
- Modify the impacted code (listed later on) to ensure that it runs in compatibility level 100. (Save the script to run on new prod server)
- Take special care for linked servers – they should not point to production
- Run System Monitor – SQL Server: Deprecated Feature Object also run profiler to capture deprecation events
- Conduct testing for modified code on the UAT
- Conduct stress testing of the system
- Capture performance related data and compare with baselines from production
- SQLDiag/PSSDiag
- RML Utilities: http://support.microsoft.com/kb/944837
- SQL Nexus: http://www.codeplex.com/SQLNexus
- PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
- If necessary troubleshoot any performance issues with help from Microsoft
- Verify that functionally we are good to go
On new Prod Server:
Before Upgrade:
- Deploy new hardware and OS / cluster
- Run SQLIOSim and other pre-deployment checks on new hardware
- Install SQL Server 2008 on new server with all patches and optimizations
- Establish a UAT environment for SQL Server 2008 and Install latest Service Pack
During upgrade:
- Move the databases to the new instances by using one of the following options.
- Backup and restore
- Detach and attach
- Manual schema rebuild and data export/import
- Log shipping
- Copy Database Wizard
- If required, take the older instances offline
- If required, rename the new instances to match the old instance names
- Take the older databases offline to avoid accidental usage / updates to the old copies
- Repeat (if required) the previous 2 steps for the rest of the databases
- Set the database compatibility levels to 100
- Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range. After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database, you do not have to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity." This is the only DBCC CHECKDB check that you must run as a post-upgrade task.
- Update statistics on all databases after you upgrade them. Execute UPDATE STATISTICS in user-defined tables in SQL Server databases. (Full Scan)
- Execute DBCC UPDATEUSAGE on all attached databases to update usage counters and make sure that correct values exist for table and index row counts.
- Repopulate full-text catalogs.
- Migrate logins, jobs to new SQL instances (If script is generated on UAT then use that script)
- Logins ( sp_helprevlogin)
- Jobs
- Linked Servers
- Setup Replication
- Setup Log Shipping
- Update any scripts affected by SQL Server 2008 behavior changes. (Use script generated on UAT Server)
- Take special care for linked servers – they should point to appropriate servers
- Modify the impacted code (listed later on) to ensure that it runs in compatibility level 100 (Use script generated on UAT Server)
- Make sure that the relational databases are working correctly by executing a sample set of queries.
- Conduct stress testing of the system
- Capture performance related data and compare with baselines from production
- SQLDiag/PSSDiag
- RML Utilities: http://support.microsoft.com/kb/944837
- SQL Nexus: http://www.codeplex.com/SQLNexus
- PAL (Performance Analysis of Logs): http://www.codeplex.com/PAL
- Setup Replication
- Setup Log shipping
- Change the data source in reporting services (Point to the new servers)
Additional steps for excellence (Post Upgrade)
- Making appropriate SQL Server settings using SQL Server 2008 Policy-Based Management.
- Configure Management Data Warehouse
- Using Database Mail instead of SQL Server 2000 SQL Mail.
- Recreating SQL Server Agent jobs and SQL Server 2000 or SQL Server 2005 maintenance plans.
- Transferring external Transact-SQL scripts to the correct location for the new instance.
- Configure/update server logins on the new instance and database users in the upgraded database.
- Configure alerts on the new instance.
- Configure DTS/SSIS packages on the new instance.
- Update connection strings at clients so that they can connect to the new instance, unless you are replacing the old server with a new server that has the same identity.
- Create <Nos of physical CPUs> TEMPDB data files, each of same size.
- Don’t add Startup flags which you were using on old server (8207,1204, 3605)
- Make sure that you do not have Virtual log file (VLF) Fragmentation issue
- Change Page verification setting to checksum at database level for all user databases
- Implement Database Mirroring
- Make a use of Data and Backup compression
- Make a use of Server /Database level Auditing
- Make a use of Resource Governor
- Make a use of Central Server Management
- Make a use of Transparent Data Encryption
- Make a use of Extensible Key Management
Contingency plan:
- Define SLA for acceptance of the new system and for rollback time if required
- Plan for a rollback action if required. Consider that the rollback might even be required many days after the actual upgrade was conducted.
- Identify data comparison tools which may be required to reconcile the data from the upgraded system back to the rollback system.
- Have simple scripts (such as a CHECKSUM based script) to check for any data discrepancies.
Develop Acceptance Criteria and Rollback Steps:
An upgrade plan must consider all possible possibilities. Some might be ignored as improbable, but for all likely scenarios, the highest priorities must be to protect any production data and to be able to restore the system to its original state if it is required. The following tasks help provide that protection:
- Back up production data. In the upgrade checklist, include steps for backing up all the databases and other data that would be required to rebuild the system.
- Develop acceptance criteria and a go/no-go decision point. As part of the upgrade checklist, at some point decide whether the upgrade is successful and the system can be put back into production. The final go/no-go decision might involve a team of people, including the testers who determine whether the application operates as expected.
- Have a rollback plan in place. Specify in sufficient detail how to restore the system if it is necessary. The more detailed the plan, the better, because rollbacks usually occur in high-stress situations. Clearly defined steps are easier to follow in those contexts.
- Test the rollback. Test the rollback plan to make sure that it will actually work. The degree of testing might be a function of how important the data is and how time-critical a rollback would be. There can be no confidence in an untested rollback plan.
General Post-Upgrade Tasks:
Full-Text Upgrade. Any databases that were marked full-text enabled or disabled before the upgrade will maintain that status after the upgrade. After the upgrade, the full-text catalogs will be rebuilt and populated automatically for all full-text-enabled databases. This is a time- and resource-consuming operation. For more information, see Chapter 6, "Full-Text Search."
Database Maintenance Plans. Database maintenance plans in SQL Server 2000 consisted of Transact-SQL commands executed by SQL Server Agent. Starting with SQL Server 2005, database maintenance plans are SSIS packages. If you upgrade from SQL Server 2000 to SQL Server 2008, your database maintenance plans will still work. However, they will be listed under the legacy branch of SSMS. To upgrade these packages to SQL Server 2008, right-click the plan you want to upgrade and select Migrate. For more information about this process, see Chapter 1, "Upgrade Planning and Deployment," and Chapter 2, "Management and Development Tools." You can also find more information in How to: Migrate SQL Server 2000 Database Maintenance Plans in SQL Server Books Online.
Following features are disabled by default in new installations:
- Ad hoc distributed queries
- Automation
- SQL Mail
- Web Assistant stored procedures
- Named pipes
- xp_cmdshell
Upgrading Log Shipped Databases:
This section covers how to upgrade databases that are using the built-in log shipping feature. This section assumes an in-place upgrade of the instance containing either the primary or secondary database.
Feature Changes in SQL Server 2008 Log Shipping:
Unlike in failover clustering, any objects that reside outside the database itself must be manually accounted for. This means that items such as SQL Server Agent jobs, SQL Server-level logins, linked servers, and anything not self-contained within the database must be either scripted or moved to the destination server in another way. Think of this process as a disaster recovery drill: you need to take the same steps to prepare for the upgrade as you would if preparing to perform a role change. See "Script or Export All Objects " above for information about getting objects out of SQL Server and how to perform such tasks as synchronizing logins.
Whether you want to upgrade a SQL Server 2000 database participating in log shipping or a SQL Server 2005 database participating in log shipping, if an upgrade occurs and the secondary database is left in either STANDBY or NORECOVERY mode, the secondary database will not be upgraded to a SQL Server 2008 database until it is brought online WITH RECOVERY.
SQL Server 2008 supports one new log shipping feature not in SQL Server 2005 log shipping: backup compression. Backup compression, available only in SQL Server 2008 Enterprise, by default is not enabled after the upgrade to SQL Server 2008. To enable it, go into the log shipping configuration and manually enable it on the primary database.
Log Shipping Upgrade Scenarios:
SQL Server 2000 and SQL Server 2005 have similar upgrade scenarios for log shipping. The two scenarios are upgrading with or without a role change. A role change is the log shipping-specific terminology for the process of switching the current primary database to promote the secondary database as the new primary.
Note: When upgrading without a role change and either preserving or reconfiguring log shipping, as long as the primary database and any secondary databases are at the same point, the secondary will be upgraded once log shipping is restarted or reconfigured. This works because the database upgrade is a logged operation: Once the transaction logs start flowing again to the secondary database, it will be upgraded as part of the log shipping process. This scenario greatly speeds up the upgrade process and reduces the number of steps required.
Upgrade with a Role Change:
In this upgrade scenario for log shipping, a role change is performed to shorten downtime. When upgrading with a role change, the only time the database should be unavailable is during the actual switch from the primary database to the secondary database. In theory, the process sounds simple, but in reality, it is more complex.
When the instance containing the secondary database is upgraded, the secondary database will not be upgraded because it is still in a state to accept the loading of transaction log backups. This means that after the instance is upgraded, there is the ability to apply the transaction logs from the primary database. However, once the cutover is started, the primary database can no longer be used because there is no way to keep the two databases synchronized: when a database is brought online WITH RECOVERY, it can no longer accept transaction logs. In addition, after the secondary becomes the new primary, all users and applications would need to be redirected to the new instance and database. If that process is not centrally managed, many desktops would need to be touched to achieve the switch. Thus, this is not a recommended upgrade scenario.
At this point, although the old primary database could be upgraded, log shipping would no longer be configured. You would have to completely reconfigure log shipping, which means making a backup from the new primary and restoring it on the "new" secondary (the old primary). If the database is small, that might not be difficult. But if you are dealing with a VLDB or limited network bandwidth for copying a full database backup—not to mention the time it would take to restore—upgrading without a role change might be a better option.
On the plus side, besides reducing downtime, upgrading with a role change provides a built-in fallback plan. If the SQL Server 2008 upgrade does not work for any reason or is not accepted after a few days, the original instance and database would be nearly ready to go. You would just need to use a manual method of getting the updated data out of SQL Server 2008.
Upgrade without a Role Change:
When upgrading without a role change, an upgrade is performed on an existing instance of SQL Server without doing the role change. This will preserve the state of the databases. But depending on the version of SQL Server you are upgrading (see the specific sections for SQL Server 2000 and SQL Server 2005 below), it might or might not upgrade log shipping in the process. This option generally has more downtime associated with it, but for those who cannot afford to reinitialize log shipping, upgrading without a role change might be a better option.
Unconfigure Log Shipping:
Similar to upgrading without a role change is the option of completely removing the log shipping configuration for a particular database. This would yield a result similar to upgrading without a role change. But in the case of SQL Server 2000, it might leave a cleaner upgrade, as we will discuss in the next section. Because a SQL Server 2008 transaction log cannot be applied to a SQL Server 2000 or SQL Server 2005 database, log shipping would need to be reinitialized.
Upgrading from SQL Server 2000 to SQL Server 2008 Log Shipping:
There is no direct upgrade path for log shipping configurations on a SQL Server 2000 database. However, you can migrate from SQL Server 2000 log shipping to SQL Server 2008 log shipping. Microsoft first introduced log shipping in SQL Server 2000 Enterprise. Log shipping was configured as part of the Database Maintenance Plan Wizard. Although it was mainly used to expose stored procedures triggered by SQL Server Agent jobs as well as xp_sqlmaint, it could not be set up via stored procedures. When Microsoft released SQL Server 2005, it changed how log shipping could be configured, and log shipping was no longer integrated and associated with a database maintenance plan.
Upgrading Replicated Databases
Side-by-Side Upgrade to a New Server or Cluster:
Snapshot replication tends to be the easiest in terms of a non-in-place upgrade because it is the least complex process.
- In the replication topology, where an in-place upgrade of an existing instance to SQL Server 2008 will not be done, install a new instance of SQL Server 2008. This might be for a Publisher, a Distributor, or a Subscriber.
- Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
- Upgrade the scripts to SQL Server 2008, and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
- The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is still in use.
- To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade.
- Assuming the Publisher is also going to be the main application database, upgrade it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades," above. Just using a snapshot will not necessarily move the entire database. Also, ensure that moving the database to a new instance follows whatever approved and supported procedures the application vendor provides. Do not just move it.
- Run the appropriate upgraded script at the Publisher.
- Run the appropriate upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. The Subscriber will need to be completely initialized. Remember to script and migrate any objects that are required by the database, but which are not accounted for by replication.
- Verify that the replication agents and jobs are started with no errors.
- Verify that transactional replication is working via the Replication Monitor.
- Configure administration such as backup jobs for the database.
- Generate new scripts for the upgraded replication architecture.
- To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been moved to the new instance. For more guidance, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" earlier in this chapter.
- The newly upgraded SQL Server environment is now ready for use by applications and end users. Point them to the new locations if necessary.
Side-by-Side Upgrade to a New Server or Cluster:
When not upgrading merge replication in-place, the upgrade process complexity will depend on the complexity of the replication topology and which components will be changed to SQL Server 2008. Tackle the switch to the new instances in logical groups, starting from the top down.
- In the replication topology, where an in-place upgrade of an existing instance to SQL Server 2008 will not be performed, install a new instance of SQL Server 2008. This may be for a Publisher, a Distributor, or a Subscriber.
- Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
- Upgrade the scripts to SQL Server 2008 and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
- Ensure that the existing Publisher is at a version that can participate in the replication topology when SQL Server 2008 is added.
- The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is still being used.
- If necessary, run the appropriate upgraded replication script at the Publisher. At this point, you are still actively using the old replication topology.
- To upgrade one tier of merge replication, follow these steps:
- Initialize the new Publisher. Assuming the Publisher is going to be the main application database as well, migrate it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades" earlier in this chapter. Just using a snapshot will not necessarily capture the entire database in the same way a backup will. Also, ensure that migrating the database to a new instance follows whatever approved and supported procedures the application vendor or developers provide. Do not just move it.
- Configure a new subscription from the old Publisher to the new Publisher.
- To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade. Because both sides can update data, no traffic or connections should be allowed at either side.
- Make sure that no transactions are left to replicate.
- After all transactions have been replicated, delete the subscription from the old Publisher to the new Publisher.
- Run the appropriate upgraded script at the Publisher to add it to the new replication topology.
- Run the appropriate upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. We recommend that you delete the old subscription to ensure a clean configuration. This will mean a complete re-synchronization of the Subscriber.
- Verify that the replication agents and jobs are started with no errors.
- Verify that merge replication is working via the Replication Monitor. Also, insert some "dummy" data and make sure it propagates to all Subscribers.
- Remember to script and migrate any objects required by the database that are not accounted for by replication.
- Configure administration such as backup jobs for the database.
- Generate new scripts for the upgraded replication architecture.
- To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been migrated to the new instance. For more information, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" earlier in this chapter.
- The newly upgraded SQL Server environment is now ready for use by applications and end users. Point applications and clients to the new locations if necessary.
Side-by-Side Upgrade to a New Server or Cluster:
When transactional replication will not be upgraded in-place, determine the complexity of the topology. Tackle the switch to the new instances in logical groups, starting from the top down. The complexity of this form of upgrade will depend on which pieces are going to be changed.
In the replication topology, where you are not doing an in-place upgrade of an existing instance to SQL Server 2008, install a new instance of SQL Server 2008. This may be for a Publisher, a Distributor, or a Subscriber.
- Generate scripts for the entire existing SQL Server 2000 or SQL Server 2005 replication topology.
- Upgrade the scripts to SQL Server 2008 and make sure that the instance names and databases are updated to reflect their new locations. Before upgrading the scripts, make copies so that the old environment can be restored if necessary.
- Ensure that the existing Publisher is at a version that can participate with SQL Server 2008 in the topology.
- The Distributor must be SQL Server 2008. Run the appropriate upgraded replication script at the Distributor to create publication and distribution. At this point, the old replication topology is in use.
- If necessary, run the appropriate upgraded replication script at the Publisher. At this point, the old replication topology is still being used.
- To upgrade one tier of transactional replication, follow these steps:
- Initialize the new Publisher. Assuming the Publisher is going to be the main application database as well, migrate it using one of the methods described in "Methods for New Hardware and Side-by-Side Upgrades" earlier in this chapter. Just using a snapshot will not necessarily capture the entire database. Also, ensure that migrating the database to a new instance follows whatever approved and supported procedures the application vendor or developers provide. Do not just move it.
- Configure a new subscription from the old Publisher to the new Publisher.
- To do the cutover, stop all traffic and kill any connections into the Publisher to ensure that no one tries to access it during the upgrade. If peer-to-peer, updating Subscribers, and/or bi-directional transactional replication are being used, ensure that neither side has any connections to the Publishers.
- Make sure that no transactions are left to replicate. Run sp_repltrans at the Publisher to get a list of the outstanding transactions marked for publication.
- Once the result set is empty, delete the subscription from the old Publisher.
- Run the appropriate upgraded script at the Publisher to add it to the new replication topology.
- Run the upgraded scripts at the Subscriber to connect it to the new replication topology. If the Subscriber is not a new instance of SQL Server 2008, make sure it is at a version of SQL Server that can be supported in a SQL Server 2008 replication topology. We recommend that the old subscription be erased to ensure a clean configuration. This will mean a complete re-synchronization of the Subscriber.
- Verify that the replication agents and jobs are started with no errors.
- Verify that transactional replication is working via the Replication Monitor. Also, insert some "dummy" data and make sure it propagates to all Subscribers.
- Remember to script and migrate any objects required by the database that are not accounted for by replication.
- Configure administration such as backup jobs for the database.
- Generate new scripts for the upgraded replication architecture.
- To ensure that no one will connect to the old environment, it is recommended that you stop the services if possible on the original topology after it has been migrated to the new instance. For more guidance, see the section "Decommissioning and Uninstalling After a Side-by-Side or New Hardware Upgrade" in this chapter.
- The newly upgraded SQL Server environment is now ready for use by applications and end users. Point applications and clients to the new locations if necessary.
Note on moving logins between SQL 2005 to SQL 2008:
Sp_helprevlogin changes for SQL 2008: http://support.microsoft.com/kb/918992 (‘How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008’)
Side-by-side Upgrade:
In a side-by-side upgrade, instead of directly replacing the older instance of SQL Server, required database and component data is transferred from an instance of SQL Server 2000 or SQL Server 2005 to a separate instance of SQL Server 2008. It is called a "side-by-side" method because the new instance of SQL Server 2008 runs alongside the legacy instance of SQL Server 2000 or SQL Server 2005, on the same server or on a different server.
There are two important options when you use the side-by-side upgrade method:
- You can transfer data and components to an instance of SQL Server 2008 that is located on a different physical server or on a different virtual machine, or
- You can transfer data and components to an instance of SQL Server 2008 on the same physical server
Both options let you run the new instance of SQL Server 2008 alongside the legacy instance of SQL Server 2000 or SQL Server 2005. Typically, after the upgraded instance is accepted and moved into production, you can remove the older instance.
Whether a side-by-side upgrade is to a separate instance on the same server or to a new instance on another server, data must be transferred in what is mostly a manual process. The result is two instances, legacy and new, that can run side by side.
As just noted, the key point in a side-by-side upgrade is that you must manually transfer data files and other supporting objects from the older instance of SQL Server to the instance of SQL Server 2008. The SQL Server 2008 Setup program will not perform this task. The objects that you must transfer include the following:
- Data files
- Database objects
- SSAS cubes
- Configuration settings
- Security settings
- SQL Server Agent jobs
- SSIS packages
Steps for using Upgrade Assistant:
- Prepare a test environment by setting up test servers that have the necessary software and appropriate configuration.
- Upgrade Assistant then guides the backing up relevant application databases and capturing the application database workload.
- It transfers the relevant files and databases to the test environment.
- The tool then runs Upgrade Advisor on the test databases to analyze database schema, trace files, and script files for potential compatibility issues.
- Upgrade Assistant next replays the baseline trace, by using the baseline workload to capture relevant execution data from the legacy application on the test server.
- You can now reset the test databases and upgrade the test server by using SQL Server 2008 Setup.
- Replay the test trace workload on the test server that runs SQL Server 2008 and capture the relevant SQL Server 2008 execution data.
- Compare the results of the baseline test workload on SQL Server 2008 against the original SQL Server 2000 or SQL Server 2005 results (also on the test server). If there are any material differences between the two results, work to resolve these in advance of the production upgrade.
Whether you perform a side-by-side upgrade on the same server or separate servers, consider the following points:
- A side-by-side upgrade requires manually transferring data and components to the instance of SQL Server 2008. After you install the parallel instance of SQL Server 2008, test the process of manually transferring components to the new instance.
- To repeat the side-by-side upgrade tests, you might not have to restore the test server from a ghost image. You could uninstall SQL Server 2008 and remove the data files to set the server back to its original state and repeat the upgrade tests. Make sure that you test uninstalling SQL Server 2008, and verify that the legacy version of SQL Server 2000 or SQL Server 2005 is working correctly.
- In a side-by-side upgrade, the rollback will most likely be to the original instance of SQL Server 2000 or SQL Server 2005. Make sure that you test the rollback scenario.
- You could run the legacy SQL Server instance in parallel with the new instance of SQL Server 2008. If that is part of the upgrade plan, make sure in the test environment that running in parallel will not require too much of the server's resources.
- When the cutover to the new instance of SQL Server 2008 occurs and the instance is verified as ready for production, stop the legacy instance of SQL Server, leaving it dormant for a while as a potential rollback instance.
- After the upgrade has passed acceptance tests, uninstall the legacy instance without disturbing the new production instance.
- Decide whether the production system will be online during the installation of SQL Server 2008. If this is the case, test the effect that SQL Server 2008 Setup has on application performance.
In a side-by-side upgrade to a different server, consider the following additional item:
- If the upgrade plan includes removing the old server from the domain and renaming the new server with the legacy name and legacy IP address, test this step as well.
Now, perform final tests on the new SQL Server 2008 server. To rerun the tests and gain confidence in the plan and deployment, repeat the process by restoring the baseline target server.
Upgrade Known Issues:
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/6d43c321-8fe7-45a7-8406-8ec875e05ca9/
SQL Server 2000 to 2008 Upgrade Whitepaper:
http://www.google.co.in/url?sa=t&source=web&cd=2&ved=0CB4QFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F2%2F0%2FB%2F20B90384-F3FE-4331-AA12-FD58E6AB66C2%2FSQL%2520Server%25202000%2520to%25202008%2520Upgrade%2520White%2520Paper.docx&ei=z6ylTfS5F8SqrAezgN3lCQ&usg=AFQjCNHREhaRPPZE_tjdjNENilWNKPNpIw
SQL Server 2008 Upgrade Technical Reference Guide:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en
Windows Service Account:
http://msdn.microsoft.com/en-us/library/ms143504.aspx
http://msdn.microsoft.com/en-us/library/ms143504(SQL.105).aspx
http://msdn.microsoft.com/en-us/library/ms191543.aspx
Ref : http://vivekharshranjan.blogspot.fr/2011/11/upgrading-sql-server-2000-to-sql-server.html
It's amazing blog, Thanks for sharing
ReplyDeleteSql server DBA Online Course Bangalore