Wednesday, June 2, 2010

Microsoft SQL Server 2008 Upgrade Advisor

With the release of Microsoft Dynamics GP 2010, I have been seing more and more questions regarding upgrading from earlier versions of Microsoft SQL Server -- for example Microsoft SQL Server 2000 -- to Microsoft SQL Server 2008. The questions usually comes in the form of "we are currently running [some prior version of SQL Server here] and they are ready to move to Microsoft SQL Server 2008. Is there a checklist in place for this upgrade? How does this affect GP?"

To start, the reason you will not find much of a checklist on upgrading Microsoft SQL Server on CustomerSource or PartnerSource is because this topic does not have much to do with the Microsoft Dynamics GP application itself, but rather falls in the domain of the Microsoft SQL Server Team. As such, one of the cool tools available from the SQL Server Team is the SQL Server Upgrade Advisor.

SQL Server Upgrade Advisor is a wizard-based application that helps you prepare for upgrades to SQL Server 2008 or SQL Server 2008 R2. The Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade your database server.

Upgrade Advisor analyzes the following SQL Server components:

  • Database Engine
  • Analysis Services
  • Reporting Services
  • Integration Services
  • Data Transformation Services
While Notification Services appears in the Analysis Wizard, it is not included in Upgrade Advisor scans because it has been removed beginning in SQL Server 2008 R2.

You can find more information about Microsoft SQL Server Upgrade Advisor at the following links:

Using Upgrade Advisor to Prepare for Upgrades @ MSDN
Microsoft SQL Server Upgrade Advisor download page @ MS Download Center
Microsoft SQL Server versions and editions upgrade @ MSDN

Until next post!

Mariano Gomez, MIS, MCP, PMP, MVP
Maximum Global Business, LLC


Scott said...

You say "this topic does not have much to do with the Microsoft Dynamics GP application itself", however, given that GP 9 runs on SQL 2005 at what point do you do the upgrade to SQL 2008?

Also, some of the features of GP 2010 are only available on SQL 2008 like "Microsoft SQL Server Reporting Services Charts & KPIs" according to the docs.

So if plenty of people are asking the question then clearly more guidance is needed on how you would get your environment from Win2k3 & SQL2005 to Win2k8 and SQL2008? It may seem obvious to you but not to the rest of us.

Mariano Gomez said...

Thanks for your inquiry.

Since GP9 runs on SQL Server 2005, you will want to upgrade to SQL Server 2008 when you are considering moving to GP10 or GP2010.

Nothing is "obvious" to me. I write for the general public, so I can't say what each individual circumstances are.

However, I will venture to say this: upgrades from W2K3 & SQL2005 to W2K8 & SQL2008 (or 2008R2) should factor in the company's systems upgrade policies. This is, I am sure many organizations just don't look at GP as the sole reason for upgrading their entire systems infrastructure, or do they? I personally have not come across this. At many of my clients, GP is one in hundreds of other applications they support and sure does not drive systems policies.


Scott said...

Hi Mariano, I agree upgrades need to be planned but if GP has its own server(s), once you have decided to upgrade, what is the recommended order to do this?

Is it:
1) Upgrade Dynamics GP to 2010
2) Upgrade Win2K3 to Win2k8
3) Upgrade SQL2005 to SQL2008

If you do this will you get all the available functionality since some of the features of GP 2010 require SQL 2008?

Does that mean you then need to reinstall some of the additional features once you upgrade the OS and SQL?

If you do the upgrade in some other sequence then you would effectively have GP 9 on either win2k8 or SQL2008 which is technically unsupported.

So there is a bit of a catch 22. Do you see what I mean? What is the recommended order? The documentation is particularly vague in this area.


Mariano Gomez said...

My preferred upgrade plan is as follows:

1. Condition another server with Windows 2008 and SQL Server 2008 R2.

2. Transfer the databases from their current SQL2005 server to SQL 2008 (see KB article 878449).

3. Use the GP2010wSP1 DVD image on CustomerSource to upgrade the databases on your new server.

If you only have one server to work with, then your steps are what I would do. In any case, you will end up with all the functionality.

The documentation is vague, because is virtually impossible to predict all the possible configurations companies will end up with.


Scott said...

Thanks Mariano.