HELP!!!

  • I have an older Windows 2003 Server running SQL Server 2008, I need to move this to a Windows 2019 Server running SQL server 2016 and I'm at a loss on how to do it. I was hoping I could just back up every database and including master, model and msdb and restore them I've found out its not that easy.

    I'm not a DBA so I'm hoping someone on here knows of an easy to follow guide on how to do this migratation?

     

    Thanks in advance for your help!

  • If you're going to go through all that, why are you upgrading to a version of SQL Server that's coming up on being half a decade old already?  Why not just jump to SQL Server 2019?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • We already have sql server 2016 up and running for our document management and accounting software. The immediate risk to us is the server running windows server 2003 on older hardware.

     

     

  • Have a look at powershell module dbatools ! it has very nice features to support your migration.

    DBATools sql server migration enhancements

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks, I did see them but they require powershell 3.0 which isn't available on Windows Server 2003, I'm guessing I can run them from the 2019 server and connect to the sql server on the 2003 server but I just haven't figured that out.  Even If I do I'm not sure of what steps need to be taken to migrate everything. I looked the Export-Dbalogin but I'm sure there's way more that I have to do as we run SSRS and there are a ton of jobs running on this server.

  • You cannot backup/restore master, model, msdb into a newer version of SQL Server.  You have to script out all your users, jobs, etc and put them into the new one.

  • Thanks, that's what I ended up doing this weekend - it was less intimidating than I had prepped myself for .

    Thanks for all the input, it's very much appreciated!!

     

  • While you can upgrade, I'd do a side by side migration. This is lower risk, and if things go wrong, you have a backup.

    I would do this:

    • Install news Windows on another machine, Install SQL Server as well. I'd go with 2019, as 2016 is already halfway to EOL. Likely, most everything works the same, and you can run in 2016 compat mode
    • script out all logins and linked servers from master. Run on new instance
    • Script out all jobs from msdb, run on new instance.
    • Test, test, test
  • You sould get a check-list together of the tasks you need to do.  The following is suggested as a starting point, but you are the person who knows your site, not me, so there willl be other things to add.

    1. Document your existing environment

    This includes accounts, permissions, connection strings, Agent Jobs, etc.  You also need to list any non-Microsoft objects in master and msdb database - tables, views, stored procs, functions, plus their permissions

    If you have any connection strings with the server name in them, a good plan is to create a DNS alias for your existing server and change the connection string to use the alias.  The Alias can then get changed at cut-over without affecting yourt application (and also quickly be changed back if go-live gets abandoned).

    2. Plan how you will implement these in the new environment

    This includes deciding if any of the items in your inventory are no longer needed and explicitly marking them as not to be implemented

    You can use backup/restore for the user databases, but you must use the master and msdb that come with your new SQL version.  SQL Server will most likely not work if you restore a old version master or model and you will certainly be outside of Microsoft support if you have a problem getting it to work

    3. Plan your new environment

    This includes SQL Version and Edition, Windows version, server size, disk layout, connectivity requirements.  As others have said, aim for SQL2019, there is little point in using a 5-year old product for a new server

    You also need to plan how you will test the new environment.  You should be certain that everything works as expected before cut-over day.  You also need a go-live plan and a back-out plan in case go-live has to be abandoned

    Your go-live plan should include a checklist that details each step needed and who is responsible for doing it, plus a box to mark completion and another box to contain any notes that may be needed.  It should include what monitoring will be done to confirm everything is working ok after the go-live, such as specific checks 2 hours after thhe go-live. The plan should also document any situations that may cause the go-live to be abandoned and how you will revert to the old server.

    4. Build your new server

    5. Implement all the accounts, etc you listed earlier

    Be careful about the Agent jobs, some of these may need to be disabled before you go live if they populate downstream systems

    6. Restore your user databases

    7. Work through your test plan

    Some tests may need to be repeated until they give the required results.  Your server is not live at this stage so if the worst comes to the worst go back to step 4

    Check if any changes are needed to your go-live plan.

    8. Get sign-off for the go-live

    9. Work through your go-live plan

    If you have DNS aliases in the connection strings, the aliases can be changed to point to the new server

    If the go-live is expected to take more than 30 minutes, make sure there is food and drink available - people can make silly decisions when thirsty.

    Make sure any jobs disabled during testing are now enabled

    10. Verify that everything is working as expected and all post-go live checks have been completed

    11. Celebrate the success

    Or comiserate if the go-live was abandoned, and then plan how to do it better next time

     

    Hope thi shelps

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Wow, that's perfect. Thanks so much for taking the time do document this process, this will definitely help

  • One thing I forgot to say, can this move be used as an opportunity to consolidate to fewer SQL Server instances.  As the old server is W2003 it is probably low powered compared to modern boxes, and you may be able to move your database to an existing SQL instance.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Yes, what we've actually decided to do is move the databases on the W2003/sql2008 server to an existing w2016/sql2016 server. Eventually (12 to 18 months) we will be moving to a cloud based ERP system, all necessary historical data will be copied up to to the new system (another challenge we face) and the W2016/Sql2016 will be taken down.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply