none
Is it necessary to migrate the msdb internal database in a SQL 2000 to SQL 2008 R2 migration?

    Question

  • Hello All,

    The SQL Upgrade Advisor utility identifies errors in SQL Server and Data Transformation Services components on my SBS 2003 Premium server with SQL 2000. But it appears the issues are only associated with the msdb database. Is it necessary to migrate the msdb database from the old server to the new Server 2008 R2 with SQL 2008 R2?

    As a SQL rookie, I would very much appreciate any guidance.

    Thanks!

    Thursday, March 22, 2012 12:46 AM

Answers

  • Hi blmoore1,

    When you run Upgrade Advisor, the Upgrade Advisor Home page appears. From the Home page, you can run the following tools:

    Upgrade Advisor Analysis Wizard
    Upgrade Advisor Report Viewer
    Upgrade Advisor Help

    The first time that you use Upgrade Advisor, run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, view the resulting reports in the Upgrade Advisor Report Viewer. Each report provides links to information in Upgrade Advisor Help that will help you fix or reduce the effect of the known issues.
    For more information, please refer to http://msdn.microsoft.com/en-us/library/ms144256(v=sql.105).aspx.

    Then you could solve the issue. And upgrade SQL Server 2000 to SQL Server 2008 R2. Before you upgrade, you need to backup all your databases.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Friday, March 23, 2012 8:49 AM
  • Hi blmoore1,

    The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
    If you do not find any objects, you could ignore issues associated with msdb database.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Thursday, March 29, 2012 9:04 AM

All replies

  • Hi ,

    If you do an in-place upgrade the msdb database will be automatically upgraded (then you might have to check the DTS packages) but if you perform a side-by-side upgrade the new server will have a clean msdb database so you have to script out msdb  objects (sql jobs, alerts, operators,etc.) and apply them to the new server


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thursday, March 22, 2012 3:25 AM
  • Thanks, Javier, for your quick response.

    When I've used the SQL Enterprise Administration tool to look at the msdb, I don't find any objects listed. Does that mean I can safely ignore issues associated with the msdb database during the migration to a new platform? Just to clarify, this will be a migration to a new server; not an inplace upgrade on the existing server.

    Thanks again for your help.

    Thursday, March 22, 2012 4:10 PM
  • Hi blmoore1,

    When you run Upgrade Advisor, the Upgrade Advisor Home page appears. From the Home page, you can run the following tools:

    Upgrade Advisor Analysis Wizard
    Upgrade Advisor Report Viewer
    Upgrade Advisor Help

    The first time that you use Upgrade Advisor, run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, view the resulting reports in the Upgrade Advisor Report Viewer. Each report provides links to information in Upgrade Advisor Help that will help you fix or reduce the effect of the known issues.
    For more information, please refer to http://msdn.microsoft.com/en-us/library/ms144256(v=sql.105).aspx.

    Then you could solve the issue. And upgrade SQL Server 2000 to SQL Server 2008 R2. Before you upgrade, you need to backup all your databases.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Friday, March 23, 2012 8:49 AM
  • It will do automatic upgrade as backward compatiblity is always there.

    Friday, March 23, 2012 9:58 AM
  • Hi Maggie,

    Thank you for your response. I have already run the Upgrade Advisor, but my lack of experience with SQL Server makes it difficult to know exactly what steps to take to implement the suggested resolution. I noticed that the Upgrade Advisor only identified issues with the msdb database. So I wondered if migrating the msdb database is even necessary since it appears to be system-related and not associated with the applications I wish to migrate.

    Does that help clarify my question?

    Thanks for your input.

    Friday, March 23, 2012 4:12 PM
  • Hi blmoore1,

    The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
    If you do not find any objects, you could ignore issues associated with msdb database.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Thursday, March 29, 2012 9:04 AM