January 31, 2021 at 11:08 pm
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!
February 1, 2021 at 5:31 am
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
Change is inevitable... Change for the better is not.
February 1, 2021 at 11:53 am
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.
February 1, 2021 at 2:40 pm
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
February 1, 2021 at 3:03 pm
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.
February 8, 2021 at 3:46 pm
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.
February 8, 2021 at 4:25 pm
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!!
February 8, 2021 at 5:32 pm
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:
February 8, 2021 at 7:46 pm
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.
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
February 8, 2021 at 9:21 pm
Wow, that's perfect. Thanks so much for taking the time do document this process, this will definitely help
February 10, 2021 at 11:04 am
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
February 10, 2021 at 1:22 pm
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.
March 9, 2022 at 10:56 am
This was removed by the editor as SPAM
June 16, 2022 at 7:01 am
This was removed by the editor as SPAM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply