Mitch Wheat - Treat the cause, not the symptoms!

When I was awarded as a MVP, I had to decide whether to move my blog from blogger.com to the msmvps.com site. Conversations with a few MVPs about the site’s often downtime, convinced me to leave my blog where it is. Seems like it was a good decision; I haven’t been able to access the msmvps site for several hours today.

I put together these short notes a while back while helping with an upgrade.

Side-by-Side Upgrade versus In-Place Upgrade

There are two fundamental strategies for upgrading:

• In-place upgrade: uses the SQL Server 2008 Setup program to directly upgrade a SQL Server 2000 instance to SQL Server 2008. The older SQL Server instance is replaced.
• Side-by-side upgrade: performs operations to move all or data and other database components from SQL Server 2000 to a separate SQL Server 2008 instance.

In this case, we opted for a side-by-side upgrade, where a backup of the current database on SQL Server 2000 was restored to a clean install of SQL Server 2008 R2 and then upgraded in place.

Before upgrade

• Run the Microsoft SQL Server 2008 Upgrade Advisor for all databases (including system databases). Review any deprecated features identified by the upgrade advisor (e.g. Notification Services, backup with TRUNCATE ONLY etc.) (SQL Server 2008's TSQL parser is stricter and some things that ran in 2000 might fail in 2008, but it is mostly deprecated TSQL language features anyway)
• If using a SAN: Benchmark the SAN using SQLIO and capture baseline data before performing the upgrade.
• Run database/application performance benchmarks
• Formulate and test a rollback plan
• Ensure database consistency: Run DBCC CHECKDB on databases to be upgraded to ensure that they are in a consistent state before performing the upgrade.
• Back up all important files: Back up all SQL Server databases from the instance to be upgraded (including master, model and msdb), data and transaction log files, as well as any application files, script files, extract files, etc. so that you can completely restore them if necessary.)
• Perform transaction log backup for all user databases and disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans. [It is also recommended to take an image of your database server, in case you will have to rollback.]
• If the operating system of the server is Windows 2003, ensure Windows service pack 1 is installed. This needs to be done on all the nodes of a cluster in a Failover Clustered environment.
• Reserve enough disk space: Estimate the disk space required to upgrade SQL Server components, user databases, and any database files that might need to be created during the upgrade process. Might need two to three times the amount of disk space during the upgrade process.
• Ensure enough space for system databases: Configure system databases (master, model, msdb, and tempdb) to auto-grow during the upgrade process, and make sure that they have enough disk space for this growth.
• Transfer logins: Ensure all logins are transferred to the master database on the target server before upgrading the database.
• Disable all start up stored procedures: The upgrade process will usually stop and start services multiple times on the SQL Server instance being upgraded. Stored procedures set to execute on start-up might interfere with the upgrade process.
• Stop replication: Stop any replication and make sure that the replication log is empty at the start of the upgrade process.
• Quit all applications: Certain applications, including all services with SQL Server dependencies, might cause the upgrade process to fail if local applications are connected to the instance being upgraded.

After upgrade

• Review all logs
• Revisit Upgrade Advisor recommendations
• Rebuild Indexes
• Reconfigure Log Shipping (if this was in use)
• Transfer and verify SQL Agent jobs and maintenance tasks
• Verify security settings: especially if there any cross server and/or cross-domain access privileges
• Recreate any full text indexes removed from the databases. Re-populate Full-Text catalogs
• Update the statistics for all the databases. This step may take some time depending on the size of database.
• Change compatibility level: After initially restoring SQL Server 2000/2005 databases to SQL Server 2008, the compatibility level will still be 80/90, which corresponds to SQL Server 2000/2005 respectively. To take advantage of the new features of SQL Server 2008, you must change the compatibility level to 100.
• Rebuild Indexes: To optimize query performance.
• Update usage counters: In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGE on all databases following the upgrade.

Resources

• Resources for Upgrading to SQL Server 2008
• SQL Server 2000 to 2008 Upgrade Whitepaper – Microsoft
• Using Upgrade Advisor to Prepare for Upgrades
• The Microsoft SQL Server 2008 Upgrade Advisor can be downloaded from here. The Microsoft SQL Server 2008 R2 Upgrade Advisor is part of the SQL Server 2008 R2 Feature Pack

For December's meeting of the Perth .NET User Group (Thursday, 2nd December), we are running another DevJam community event, where anyone can give a short talk about anything .NET software development related.

• DATE:    Thursday, Dec 2nd, 5:30pm
• VENUE:  Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
• COST:    Free. All welcome

You can talk about any development topic but it must be between 5 – 10 minutes long:

• Anything you’ve done.
• Any new technology you’ve investigated.
• Any tips and tricks, or tools that aid development
• Basically, anything .NET software development related!

How do I enter? You must register by sending an email to mitch.wheat at gmail.com, no later than 20th Nov, with the word 'DevJam’ in the subject, stating your talk title in the body. Let me know whether you will be bringing your own laptop (preferable). Presenter places are limited, so it's first come, first served.

There are prizes for the three highest, audience voted talks (with the organisers’ decision as final in case of a tie break). The first prize is a copy of Visual Studio 2010 Ultimate with MSDN subscription, worth approximately $12,000! There will several other prizes which will be announced shortly.

There will be pizza and refreshments provided. If you have any questions, please send me an email.

Service Pack 2 for SQL Server 2008 includes new compatibility features with SQL Server 2008 R2, product improvements based on requests from the SQL Server community, and hotfix solutions provided in SQL Server 2008 SP1 Cumulative Updates 1 to 8.

You can download here.

I’ve been re-awarded as a Microsoft MVP. Once again, I would like to thank everyone for their support. It’s an honour and I will try to live up to it. Congratulations to all those who were also awarded.

Join us at the Perth .NET User Group for a talk exploring what the Windows Server AppFabric is, looking at both hosting and caching,  and learn how to configure and manage WCF and WF services in AppFabric. We will examine the diagnostic and tracing functionality while building high performance and fault-tolerant applications.

• TOPIC:   So what is the Windows Server AppFabric
• DATE:    Thursday, Oct 7th, 5:30pm - 7:00pm
• VENUE:  Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
• COST:    Free. All welcome

Bill is Mentor with SolidQ Australia located in Melbourne Australia. Bill started his career in IT some 25-years ago with the US Defence as an IBM Systems Programmer. He switched to the Microsoft Windows platform 14-years ago, and has been involved with Windows development ever since. Bill has worked on numerous enterprise projects using Microsoft C/C++, Visual Basic and SQL Server. Most recently, Bill has been driving various application integration projects using BizTalk Server (2000 – 2009) to connect a variety of Microsoft Business Solutions applications with other systems. Bill is also a Microsoft Certified Training and has been actively training BizTalk developers since the release of BizTalk 2004. Bill has been awarded as a BizTalk Server MVP since 2004 and is the leader of the Melbourne .Net User Group.

There will be a door prize of a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, RubyMine, IntelliJ IDEA), and several 30-day coupons to Tekpub's high-quality, online screencasts for programmers.

Also note: Bill will be presenting a talk on SQL Server virtualisation at the SQL Server User Group on Weds 6th Oct. Details here.