none
upgrade SQL 2012 to 2017 RRS feed

  • Question

  • I would like to ask do you guys upgrade your SQL servers or do you do clean installs? I have always done clean installs on a new server however I don't know if this is the most
    effiecient method which is why I'm asking. I have a business unit that now runs SQL Server 2012 on Windows 2012 R2. The business unit wants to move to SQL Server
    2017. So I will need to migrate there production and dev server. The server they use now also runs SSAS it contains a tabular model as well as multidimensional. So what approach do you
    all take also do you have any advice when doing upgrades where SSAS is involved.
    Monday, October 30, 2017 4:07 PM

All replies

  • If possible, I recommend clean install, preferably on new server, or at least separate instance on existing server, if it's critical production server.

    Main reason is if things happen to go 'side ways' with installation/migration/upgrade, then you have a much easier backout path by simply going back to the old server, until issues and incompatibilities can get resolved.

    This approach also enables you to run things in parallel for awhile until all wrinkles get resolved and then make final cutover to new server at go-live time.

    Too many things can go wrong with an 'in place' upgrade and then your stuck, with no easy backout path.

    Just my 2 cents worth.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, October 30, 2017 5:28 PM
    Monday, October 30, 2017 4:21 PM
  • My personal experience, if you are just using database engine feature it is easy to do In-place upgrade and is quick and saves lot of time. With SSIS/SSAS/SSRS involved I personally prefer side by side upgrade like what you have mentioned. Now it is your call.

    Please note you MUST run SQL Server 2017 upgrade advisor(UA)/Migration Assistance before upgrade to know any breaking changes. You must fix that before you proceed.

    Breaking Changes in SQL Server 2017


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Monday, October 30, 2017 4:42 PM
    Moderator
  • Hi Shashank,

    I think the Upgrade Advisor has actually been replaced by Data Migration Assistant (DMA):
    Prepare for upgrade by running Data Migration Assistant

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Monday, October 30, 2017 5:20 PM
  • Thanks for all the info. BTW do you guys separate your SSAS servers from your database engine servers? I wonder of separating them will give more flexibility.
    Monday, October 30, 2017 6:41 PM
  • Thanks for all the info. BTW do you guys separate your SSAS servers from your database engine servers? I wonder of separating them will give more flexibility.

    That totally depends on requirement and mostly people separate them just to isolate their DB engine. If your DB engine is highly used and you have kind "just the adequate memory" for db engine and you do not want to take memory from it for SSAS then yes you have to get a new server which means new licensing cost. OTOH if you can make sure SQL Server, the OS and SSAS can all get required memory and there share of resources there is no harm in running them together.

    Phil,

    Yes you are correct it is now called as Data Migration Assistance and is mocu better than UA. Thanks for that


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, October 31, 2017 6:46 AM
    Moderator
  • I always recommend new server and migration for upgrades for several reasons.  It is safer and in most cases reversible.  Also, I usually take the opportunity to upgrade the OS and/or the hardware to a more current version in addition to the SQL Server.

    SSAS runs as a completely separate process on the server.  As long as you have the hardware to support both SQL Server and SSAS there is no problem running them on the same server. 

    Tuesday, October 31, 2017 12:06 PM
  • Use native  Import and Export data utility
    Tuesday, October 31, 2017 1:25 PM