none
Upgraded from SQL 2000 to SQL 2008 R2 and Version still shows 2000?

    Question

  • (Testing for future upgrade)  

    The OS is Windows Server 2003 Std.

    I did an in-place upgrade from SQL Server 2000 Std. to SQL Server 2008 R2 Std.  The upgraded "completed successfully" - no errors that I saw.

    When on the server I query "Select @@Version" it still comes up with SQL 2000:
    "Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)"

    Also when I try to run the "Activity Monitor" I get the error "This operation does not support connections to Microsoft SQL Server Standard Edition version 8.00.2039."

    It is like it didn't upgrade even though it said it was successful, I can see the DBs, tables, and query the tables.

    Did I do something wrong during the upgrade?  Any ideas?

    Thanks


    Murf

    Friday, May 18, 2012 11:58 AM

Answers

All replies

  • Hello,

    Please share with us the results of the Discovery Report:

    http://blogs.msdn.com/b/petersad/archive/2009/11/13/sql-server-2008-discovery-report.aspx

    Share with us the content of the Summary.txt log file:

    http://msdn.microsoft.com/en-us/library/ms143702(v=sql.105).aspx   

    Thank you in advance.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, May 18, 2012 12:05 PM
    Moderator
  • Here's the Discovery (note Replication and Full Text is install, but I do not use either one):

    Overall summary:
      Final result:                  Passed
      Exit code (Decimal):           0
      Exit message:                  Passed
      Start time:                    2012-05-18 08:15:44
      End time:                      2012-05-18 08:15:59
      Requested action:              RunDiscovery

    Machine Properties:
      Machine name:                  PERUTESTSERVER
      Machine processor count:       2
      OS version:                    Windows Server 2003
      OS service pack:               Service Pack 2
      OS region:                     United States
      OS language:                   English (United States)
      OS architecture:               x86
      Process architecture:          32 Bit
      OS clustered:                  No

    Product features discovered:
      Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         Database Engine Services                 1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         SQL Server Replication                   1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         Full-Text Search                         1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2                                                       Management Tools - Basic                 1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2                                                       Management Tools - Complete              1033                 Standard Edition     10.50.1600.1    No       

    Package properties:
      Description:                   SQL Server Database Services 2008 R2
      ProductName:                   SQL Server 2008 R2
      Type:                          RTM
      Version:                       10
      SPLevel:                       0
      Installation edition:          EVAL

    User Input Settings:
      ACTION:                        RunDiscovery
      CONFIGURATIONFILE:            
      ENU:                           True
      FARMACCOUNT:                   <empty>
      FARMADMINPORT:                 0
      FARMPASSWORD:                  *****
      HELP:                          False
      IACCEPTSQLSERVERLICENSETERMS:  False
      INDICATEPROGRESS:              False
      PASSPHRASE:                    *****
      QUIET:                         False
      QUIETSIMPLE:                   False
      UIMODE:                        Normal
      X86:                           False

      Configuration file:            C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120518_081542\ConfigurationFile.ini

    Detailed results:

    Rules with failures:

    Global rules:

    There are no scenario-specific rules.

    Rules report file:               The rule result report file is not available.

    Here's the Summary (apparently created when I started the app to create the Discovery rpt.):

    Overall summary:
      Final result:                  Passed
      Exit code (Decimal):           0
      Exit message:                  Passed
      Start time:                    2012-05-18 08:15:44
      End time:                      2012-05-18 08:15:59
      Requested action:              RunDiscovery

    Machine Properties:
      Machine name:                  PERUTESTSERVER
      Machine processor count:       2
      OS version:                    Windows Server 2003
      OS service pack:               Service Pack 2
      OS region:                     United States
      OS language:                   English (United States)
      OS architecture:               x86
      Process architecture:          32 Bit
      OS clustered:                  No

    Product features discovered:
      Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         Database Engine Services                 1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         SQL Server Replication                   1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2   MSSQLSERVER          MSSQL10_50.MSSQLSERVER         Full-Text Search                         1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2                                                       Management Tools - Basic                 1033                 Standard Edition     10.50.1600.1    No       
      Sql Server 2008 R2                                                       Management Tools - Complete              1033                 Standard Edition     10.50.1600.1    No       

    Package properties:
      Description:                   SQL Server Database Services 2008 R2
      ProductName:                   SQL Server 2008 R2
      Type:                          RTM
      Version:                       10
      SPLevel:                       0
      Installation edition:          EVAL

    User Input Settings:
      ACTION:                        RunDiscovery
      CONFIGURATIONFILE:            
      ENU:                           True
      FARMACCOUNT:                   <empty>
      FARMADMINPORT:                 0
      FARMPASSWORD:                  *****
      HELP:                          False
      IACCEPTSQLSERVERLICENSETERMS:  False
      INDICATEPROGRESS:              False
      PASSPHRASE:                    *****
      QUIET:                         False
      QUIETSIMPLE:                   False
      UIMODE:                        Normal
      X86:                           False

      Configuration file:            C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120518_081542\ConfigurationFile.ini

    Detailed results:

    Rules with failures:

    Global rules:

    There are no scenario-specific rules.

    Rules report file:               The rule result report file is not available.

    Thanks for your help!


    Murf

    Friday, May 18, 2012 12:26 PM
  • Did you try restarting the machine after upgrade ?

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Friday, May 18, 2012 12:32 PM
    Moderator
  • Addtional info.  I don't know if this matters, but here is how I created the test server and did the upgrade:

    Installed OS (win 2003 server) and SQL Server 2000 on test machine just like it is on Production machine.  I restored all the DBs (System and user) from backups made from the Prod.  Before upgrade, I ran "sp_changedbowner 'sa'" to change DBs back like they are on the Prod. (the restore changed them). 

    Also during the "Server Configuration - Service Accounts" page, I left everything as it was, did not change or add anything.  I just clicked "Next".

    The TEST machine has a different computer name than the production machine, but I notice in Management Studio it has production SQL server name with "(SQL Server 8.0.2039 - sa)" next to it.


    Murf

    Friday, May 18, 2012 12:39 PM
  • Yes.


    Murf

    Friday, May 18, 2012 12:48 PM
  • Hello,

    Rsetart the server as suggested by Ahmed.


    On the “Connect to Server” dialog of SQL Server Management Studio (SSMS), please type “PERUTESTSERVER” and click on Connect. Then let us know the build name that will appear next to the instance name in Object Explorer (SSMS).


    Thank you in advance.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, May 18, 2012 12:51 PM
    Moderator
  • Have you upgraded the compatibility level of your system databases?

    Friday, May 18, 2012 12:53 PM
  • Hello,

    Rsetart the server as suggested by Ahmed.


    On the “Connect to Server” dialog of SQL Server Management Studio (SSMS), please type “PERUTESTSERVER” and click on Connect. Then let us know the build name that will appear next to the instance name in Object Explorer (SSMS).


    Thank you in advance.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    FYI - Sorry if I sound "stupid" on this.  My primary job and focus is a developer, but I also have to "wear the hat" of the DBA which I am not as nearly as experienced at.

    Okay, maybe this is were the problem is coming from and my lack of understanding.  When I'm on the "Connect to server" page and have the following filled out -

    Server type: Database Engine

    Server name: PERUTESTSERVER

    Authentication: SQL Server Authentication

    Login: sa

    Password: (the password from the sa account on the production server)

    I get the error: "Cannot connect to PERUTESTSERVER" Additional info --> A network-related or instance-specific error occurred while establishing a connection to SQL Server.  ...  Could not open a connection to SQL server"

    So I thought I needed to change the "Server Name:" box to my production SQL Server 2000 name.  When I do that it then connects up.

    I have no idea what password it wants for PERUTESTSERVER.  When I set it up before upgrade I made "sa' have the same password as prod.  Also the Windows Administrator has the same password as Prod.  The only thing I can think of that I did differently is the Computer Names are different.  Also note, during upgrade on the "Server Configuration - Service Accounts" page, I left everything as it was, did not change or add anything.  I just clicked "Next".  Thinking everything would carry forward during upgrade.

    Thanks


    Murf

    Friday, May 18, 2012 1:15 PM
  • Have you upgraded the compatibility level of your system databases?


    Not sure how to do that and shouldn't the "in-place upgrade" do that for me? 

    Murf

    Friday, May 18, 2012 1:20 PM
  • Hello,

    Rsetart the server as suggested by Ahmed.


    On the “Connect to Server” dialog of SQL Server Management Studio (SSMS), please type “PERUTESTSERVER” and click on Connect. Then let us know the build name that will appear next to the instance name in Object Explorer (SSMS).


    Thank you in advance.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    However when I connect with the production server name used in 2000 (Not PERUTESTSERVER) in SSMS/Object Explorer it has the production SQL server name with "(SQL Server 8.0.2039 - sa)"

    Something just hit me.  Does that mean I am actually connecting to the PRODUCTION server from my TEST machine via SSMS!??!?  That is good to know!Good thing I didn't change anything on it! 

    Okay then, how do I know what the password would be for my PERUTESTSERVER or why I cannot connect?


    Murf

    Friday, May 18, 2012 1:28 PM
  • Hello,

    You did upgrade correctly. You did well.

    Please enable TCP/IP and Named Pipes protocols.

    http://msdn.microsoft.com/en-us/library/ms191294(v=sql.105).aspx

    Configure Windows Firewall or disable it on the test server.

    http://msdn.microsoft.com/en-us/library/ms175043.aspx

    Do not connect to the production instance. That is the reason you are seen version 8 on the server. Connect to “PERUTESTSERVER” as explained on my previous post.

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by B Murphy Monday, May 21, 2012 2:25 PM
    Friday, May 18, 2012 1:33 PM
    Moderator
  • Hello,

    You did upgrade correctly. You did well.

    Please enable TCP/IP and Named Pipes protocols.

    http://msdn.microsoft.com/en-us/library/ms191294(v=sql.105).aspx

    Configure Windows Firewall or disable it on the test server.

    http://msdn.microsoft.com/en-us/library/ms175043.aspx

    Do not connect to the production instance. That is the reason you are seen version 8 on the server. Connect to “PERUTESTSERVER” as explained on my previous post.

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    I have all the protocols enabled accept for VIA.  I have no Windows (or any) firewall running.

    Maybe you can help me figure our why I cannot connect to the PERUTESTSERVER, the local machine/DBs.  I'm trying to connect on the actual physical server itself via SSMS, not from another machine.  Like I said before, during the install on the "Server Configuration - Service Accounts" page, I left everything as it was, did not change or add anything.  I just clicked "Next".  Thinking the 'sa' would just carry forward and I would be able to login with it.  When I clicked on the button "Use same account for all SQL Server services", it prompts for something that does not look like a SQL 'sa' account, but some kind of windows account I guess.  So that is why I didn't entering anything and clicked Next.  Any ideas what I'm doing wrong to log in?

    Thanks


    Murf

    Friday, May 18, 2012 1:55 PM
  • Hello,

    If you clicked Next on every dialog then Mixed Authentication was not enabled (sa account will be disabled).  Only Windows Authentication is enabled by default. Try to use “Windows Authentication” instead of “SQL Server Authentication” to be able to connect to the instance, then change to “Mixed mode” and change the password for “sa”.

    http://msdn.microsoft.com/en-us/library/ms188670(v=sql.105).aspx

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, May 18, 2012 2:12 PM
    Moderator
  • Hello,

    If you clicked Next on every dialog then Mixed Authentication was not enabled (sa account will be disabled).  Only Windows Authentication is enabled by default. Try to use “Windows Authentication” instead of “SQL Server Authentication” to be able to connect to the instance, then change to “Mixed mode” and change the password for “sa”.

    http://msdn.microsoft.com/en-us/library/ms188670(v=sql.105).aspx

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Unfortunately, it does not connect with "Win. Auth." - disables so I cannot even enter a password.  I still cannot help to think I did something wrong on the "Server Configuration - Service Accounts" page, but I'm not sure what I'm suppose to put in there.  I want to do it the simplest way possible (despite the way MS wants you to set up a bunch of accounts).  I was hoping just to use SQL 'sa'.  I do know whenever there is an option I always choose "SQL Auth.".  That's way we connect to the servers via apps. 

    I have my pre-upgrade win2003/sql2000 machine saved as an image.  So I can restore back to it and TEST run the upgrade as many times as I want.


    Murf

    Friday, May 18, 2012 2:26 PM
  • Hello,

    When you install SQL Server 2008 R2 on the “Database Configuration” page select “Mixed mode” and click on the “Add Current User” button to give permission to your Windows Account to access the instance.

    http://www.sqlcoffee.com/SQLServer2008_0013.htm  

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, May 18, 2012 2:46 PM
    Moderator
  • Hello,

    When you install SQL Server 2008 R2 on the “Database Configuration” page select “Mixed mode” and click on the “Add Current User” button to give permission to your Windows Account to access the instance.

    http://www.sqlcoffee.com/SQLServer2008_0013.htm  

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Thanks for the advice.  I'll give that a try.

    Murf

    Friday, May 18, 2012 4:53 PM

  • Being I'm doing an in-place upgrade, all you get as far as security entries is a "Server Configuration" page.  (You get no “Database Configuration” page.)  It has a grid with 4 cols.  - Service, Account Name, Password, Startup Type.  I have 2 rows/entries.  The 1st is for SQL Full-text Filter Daemon Launcher -  I don't care about this one because I don't use Full-text search.

    The 2nd one is Service:SQL Server Browser, Account name:NT AUTHORITY SERVICE, Password:(empty), Startup Type:Automatic.   When you hold the cursor over the Account Name a pop-up comes up that says "This account is read only."  and the Password field is locked - will not allow input.  So nothing can be altered on this.

    Under the grid there is a button that reads "Use the same account for all SQL Server services".  When I click it the entry form open ups with 2 boxes, Account Name: and Password:.  Account Name: can be Browsed so you can enter your own or has a drop down.  The 2 drop down choices are "NT AUTHORITY\NETWORK SERVICE" and "NT AUTHORITY\SYSTEM".  I does not matter which one you choose, it disables the Password box so you cannot enter anything.

    So I don't know if I should pick "NT AUTHORITY\SYSTEM" or browse and enter "Administrator" and give my Windows administrator account password, or what?


    • Edited by B Murphy Friday, May 18, 2012 7:17 PM
    Friday, May 18, 2012 6:37 PM