SQL Server database upgrade - FAQs, How-to, Gotchas, links and blurb
Upgrading a SQL Server database should be straight-forward, when you haven't got much of constraints on the application code. In this regard we have blogged previously such as UpgradeTags posts.
Here in this post I would like to go with few FAQs that are related to Upgrade process & procedure:
- Say you need to manage SQL Server 2000 version intances with a single SQL Server and if you are willing to upgrade tools on that central-monitoring server then better to go within such process, as you can easily manage the backward compatibility (upto version 2000) using SQL Server Management Studio that comes with 2005 version. Bear in mind about the editing DTS packages, creating login scripts for previous version servers and peer to peer replication etc., as you will not be able to manage these instances and I recommend to go through this SSMS-information link on technet.
- Think about SQLMAIL usage within SQL Server 70 & 80 compatibility versions, though it is still available within SQL Server 2005 and it is a strong recommendation to the Community from the Microsoft that to use Database Mail as it has no dependancy to use a MAPI, further information on SQLMail & Database Mail refer to http://msdn2.microsoft.com/library/ms175887.aspx link.
- Make sure to use Upgrade Advisor, as it will enable the DBA to make it easier in examining the list of servers and their list of issues within this upgrade process. Make sure to download and upgrade your SQL Server 2005 tools with latest service pack in order to bug-fix any issues within previous tools version. Upgrade Advisor will definetly run against previous versions, but make sure to have database installed before initiating this tool. As it says this tool simply analyze your database and advise on the issues, while running this tool will have bit of server's CPU usage.
- If you need to use Upgrade Advisor from MSDE to SQL Server Express 2005 verison, then make sure to check more information from this SQLExpress edition link.
- Part of upgrade process involves upgrading from Evaluation Edition to Full Edition of SQL Server, in this regard the 180-day trial version of SQL Server 2005 Enterprise Edition can be upgraded to the fully licensed version of SQL Server 2005 Enterprise Edition without uninstalling the trial software prior to the end of the 180-day period. If upgrading to the fully licensed version of SQL Server 2005 Standard Edition, Workgroup Edition, or Developer Edition, it is recommended that you uninstall the 180-day trial version of SQL Server 2005 Enterprise Edition before installing the fully licensed version of the other editions.
- One of the benefit I found in using SSMS against the SQL instances, such as scripting the linked servers using Management Studio projects.
- When the Replication invovlement is high within your database environment and if you need to upgrade the replicated database instances then make sure to develop/plan a replication strategy. As it will have significant changes to the replication process on the version changes to the engine. In this regard refer to the WhatsNew and Replication-changes links on MSDN.
- Similarly when SQL native Log shipping is used, then it is not easy to upgrade directly from 2000 to 2005 version, as there are significant changes to the Database Maintenance plan wizard and integral to log shipping in SQL Server 2000, is not used as part of the log shipping configuration in SQL Server 2005. As a result, log shipping stops functioning when you upgrade a server to SQL Server 2005.
- As it says in the 'can' you can migrate the Log Shipping enabled database while managing & maintaining synchronization between your primary and secondary databases. As this process of "migrating with failover" allows you to maintain the availability of your database while you upgrade each server in the log shipping configuration.
- Once you have completed the migration of your log shipping configuration to SQL Server 2005, you can delete the tables and SQL Server Agent jobs that were created by SQL Server 2000 log shipping. In case if you need 2 servers in place then it is better to upgrade the secondary server first, but log shipping will need to be reconfigured in both instances of SQL. I have found an excellent article about How-to-Upgrade_replicaton-LogShipping process.
- If your database size is huge, say more than 50 GB and if you are worrying whether you will have a gain in performance or not then a straightforward answer to this, but it really depends on the environment. When you talk about Performance server resources such as Hardware and Network connectivity play such a key role and it is a huge topic to talk about, suggestion is to refer to PerformanceTuning section here.
- For further information on system requirement in using recent version of SQL Server refer to SQL2005EditionSYSREQ link.
- As talked previously when you have DTS packages management then make sure to migrate the packages to SSIS, for more information on backward compatibility refer to DTS-SSIS link.
- Passing on, when you have SSIS then you should think about using SQL Server Reporting Services. Similarly it is easy to upgrade most installations using the SQL Server 2005 setup program. All report server content and settings will be fully functional after the upgrade process. Keep in mind few changes related to customized report server deployment such as added extensions, changes to virtual directory setting and ASP.NET encryption etc. then you need these settings on the upgraded installations. You have to install a new instance of SSRS 2005 and use the Reporting Services configuration tool to migrate settings. Make sure to refer to upgraded BOL that has more information on "deployed custom extensions for Reporting Services 2000".
- When you need to monitor the Upgrade Process then best option is to check log file by referring to, system root directory: ~Microsoft SQL Server\90\Setup Bootstrap\LOG\Files.
- There are questions within forums that what is the best method to upgrade a database, is it a backup/restore or detach/attach method. Both are best as per my experiences and based on requirement & time better to choose backup/restore method that includes system databases too, as you have to take care of logins & packages too. There are references in Microsoft site to use Copy Database Wizard, but I have had issues in going this route and best option is to test by referring to SQL2005CDW link.