locked
Sql server upgrade from 2000 to which latest edition RRS feed

  • Question

  • Hello,

    We are finally planning to upgrade our Sql server 2000 and wondering which latest edition i can upgrade it?
    It will be a side by side upgrade. I know that i have to upgrade first 2008R2 then next edition but little confused as It's only support from 2008R2 to Sql 2014/2016 or 2005 to Sql 2014/2016 or i can upgrade it to Sql 2017?

    Ex. Sql 2000 ==> Sql 2008R2 ==> 2017 ==>>>>>>>>> i can go this route?
    Is it better i should go Sql 2000 ==> Sql 2005 ==> Sql 2017 I can go this route?

    Thanks

    Friday, July 3, 2020 6:43 PM

All replies

  • Hi pdsqsql,

    According to Supported version & edition upgrades (SQL Server 2017),  the oldest version of SQL Server supported to upgrade to SQL Server 2017 is SQL Server 2008 R2.

    Also according to Version and Edition Upgrades, it is possible to upgrade from SQL Server 2000 to 2008 R2.

    So it is better for you to go route Sql 2000 ==> Sql 2008R2 ==> 2017 .

    Besides, you could download Microsoft SQL Server 2008 Upgrade Advisor to help you prepare for upgrades to SQL Server 2008. 

    You could also use Microsoft® Data Migration Assistant v5.2 to get more help to upgrade to SQL Server 2017.

    In addition, the lastet version is SQL Server 2019 right now. You could consider to upgrade to lastet version.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com




    Monday, July 6, 2020 2:23 AM
  • Hi pdsqsql,

    Could you please provide any update?

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 7, 2020 8:17 AM
  • Thanks Melissa for your feedback.

    I will go to route Sql 2000 ==> Sql 2008R2 ==> 2017

    We have Sql 2005 and i am confused that I can upgrade directly from Sql 2005 to Sql 2017 or i have to go from Sql 2005 to  Sql 2008R2 then upgrade to Sql 2017?

    I already ran the Upgrade Advisor and i see Breaking Changes, Behavior Changes, Depreciated Features, Information Issues for Different Compatibility.

    Is it anything mandatory to resolve before i migrate it?

    Tuesday, July 7, 2020 9:40 PM
  • Hi pdsqsql,

    Thanks for your update.

    As I mentioned before, the oldest version of SQL Server supported to upgrade to SQL Server 2017 is SQL Server 2008 R2. So you could not go the route directly from Sql 2005 to Sql 2017.

    Since you mentioned that you already had SQL 2005, you could follow the guide in Are you upgrading from SQL Server 2005?.

    In addition, discover issues (Breaking Changes, Behavior Changes, Depreciated Features) that can affect an upgrade to an on-premises SQL Server. It would be better for you to fix them before migration. Reference:Overview of Data Migration Assistant


     

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com





    Wednesday, July 8, 2020 5:23 AM
  • Thanks Melissa for your detail answer and supporting link.

    Ok, Got it so Going from Sql 2005 to Sql 2017 is not possible then which version i should go directly from Sql 2005?

    I have to do like Sql 2005 ==> Sql 2008R2 and then Sql 2017?

    Wednesday, July 15, 2020 8:14 PM
  • Hi,

    It is possible to upgrade from SQL Server 2005 to SQL Server 2017 directly. In fact I'm working on upgrading our Legacy Databases and I have just upgraded the 2005 version directly to 2017 without any issues.  See Paul's blog here: https://www.sqlskills.com/blogs/paul/you-can-upgrade-from-any-version-2005-to-any-other-version/

    For SQL Server version 2000 you need to do 2 hop upgrade and that is :

      SQL Server 2000 --> SQL Server 2008R2
      SQL Server 2008R2 --> SQL Server 2012 or anything above it.  

     Hope this helps. 

    Wednesday, July 15, 2020 9:25 PM
  • Hi pdsqsql,

    According to the link Are you upgrading from SQL Server 2005?, you could upgrade from 2005 to 2014.

    Then you could go route sql 2005->sql 2014->sql 2017 or later.

    Note:You can't upgrade a SQL Server 2005 server to a SQL Server 2014 server in place. You have to install SQL Server 2014, then migrate your SQL Server 2005 databases to the new installation.

    You could also follow Shaddy's advice to check whether it is also working.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 16, 2020 1:07 AM
  • Thank you Shaddy for the link and info.

    We can try that route then.

    I have a question, while running Sql Upgrade Advosir for 2017, i got one breaking Behavior Changes - FOR XML queries that execute in AUTO mode return references to derived table aliases.

    How can i modify the query to replace or change the FOR XML AUTO clause?

    Recommendation shows: 

    "Object [dbo].[usp_UpdatePrice] runs a FOR XML query that executes in AUTO mode, which returns references to derived table aliases. Under compatibility level 90 or later, the query returns references to the derived table alias instead of to the derived table's base tables, so you should modify your application as required to account for the changes. For more details, please see: Line 83, Column 177."

    How can i manage this one?

    Thank You!

    Tuesday, July 21, 2020 9:36 PM
  • Thank you Melissa for your timely update and sharing the knowledge which was very helpful.

    I have a question, while running Sql Upgrade Advisor for 2017, i got one breaking Behavior Changes - FOR XML queries that execute in AUTO mode return references to derived table aliases.

    How can i modify the query to replace or change the FOR XML AUTO clause?

    Recommendation shows: 
    
    "Object [dbo].[usp_UpdatePrice] runs a FOR XML query that executes in AUTO mode, which returns references to derived table aliases. Under compatibility level 90 or later, the query returns references to the derived table alias instead of to the derived table's base tables, so you should modify your application as required to account for the changes. For more details, please see: Line 83, Column 177."
    
    How can i manage this one?
    
    
    
    
    
    
    Thank you!

    Tuesday, July 21, 2020 9:38 PM
  • If you can post that offending line of code (you can obfuscate if needed) then we probably be able to help with the correct syntax.

    I also suggest to start a new thread for that particular problem only (e.g. find that procedure and offending line of code and ask how to adjust it).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 21, 2020 9:51 PM
  • Thanks Naomi for your quick response.

    I thought it was same migration related issue that's why i posted here.

    DECLARE 
    
    @createdDate DATETIME = CURRENT_TIMESTAMP
    @UpdatedDate DATETIME = CURRENT_TIMESTAMP
    
    BEGIN
    			DECLARE @cashPricesXML XML
    			SET @cashPricesXML = (SELECT * FROM  dbo.CashPrices 
    								WHERE (CreatedDate = @updatedDate OR UpdatedDate = @updatedDate) 
    								AND AvgPrice IS NOT null FOR XML AUTO);
    			PRINT 'copying cash prices :'
    			PRINT CONVERT(NVARCHAR(MAX), @cashPricesXML)
    	END

    Thanks for your help!

    Tuesday, July 21, 2020 10:49 PM
  • I did found that this feature listed as deprecated (although the query works fine in SQL 2019). I'm not exactly sure how we need to change it - what is supposed to be the desired output. I didn't try to research that long enough, so you may try to look into alternative ways to write this particular query or just ignore this warning.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 22, 2020 2:20 AM
  • Hi pdsqsql,

    If it's producing the desired output then there's no need to alter the code and you could ignore this message.

    If not, you could have a try with 'FOR XML AUTO, ELEMENTS'.

    Please also refer below links and check whether they are helpful:

    IndexOptimize runs a FOR XML query that executes in AUTO mode, which returns references to derived table aliases. #23

    FOR XML AUTO queries return derived table references in 90 or later compatibility modes

    Use AUTO Mode with FOR XML

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 22, 2020 3:01 AM
  • Thanks Naomi.

    I was thinking the same but during my upgrade advisor run, i got this recommendation:

    "Object runs a FOR XML query that executes in AUTO mode,

    which returns references to derived table aliases. Under compatibility level 90 or later, the query returns references to the derived table alias instead of to the derived table's base tables, so you should modify your application as required to account for the changes.


    Wednesday, July 22, 2020 2:23 PM
  • Thanks Melissa for your suggestion.

    I was looking some docs and may be i will check with developer about this as what he wanted as a result otherwise i can try with different XML options like you suggested.

    Thank you!

    Wednesday, July 22, 2020 2:25 PM
  • I think XML RAW('MyTableName'), ELEMENTS may give the result similar to what was produced originally.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 22, 2020 2:34 PM
  • Thanks Naomi.

    I will test it and let know.

    Wednesday, July 22, 2020 11:03 PM
  • Thanks Naomi.

    I tried "XML RAW('MyTableName'), ELEMENTS" in Sql server 2008R2 and it's also works but question is that i can use it in Sql 2017 or still this one also considered as Depreciated features?

    Should i consider as a replacement of FOR XML AUTO?

    Thanks for your help!

    Thursday, July 23, 2020 9:22 PM
  • As far as I understood from reading documentation only XML AUTO is supposed to be deprecated and the above syntax should be OK to use.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 23, 2020 9:28 PM
  • Thanks Naomi for all your help!

    Appreciate it!

    Friday, July 24, 2020 2:04 AM