I didn't build it, but we've got a 32-bit Windows Server 2008, with SQL server 2008 and 4GB of RAM.

I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

The problem - it looks like SQL Server 2016 requires, at minimum, Windows Server 2012, which is only 64-bit.

Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Is there a way to get around it with compatibility levels?

  • Have you considered SSIS or BCP ? – jyao Feb 20 '18 at 20:02
  • @jyao I have not. Sounds like a huge pain in the ass for a large DB. Know any good guides? – James Feb 20 '18 at 20:08
  • @James It may sound like a lot of pain for a large db, but there is no way you HAVE a large db on a server that is less powerfull than a decent tablet. Large does not mean "10g" - it means terabyte level database these days. – TomTom Feb 20 '18 at 20:15
  • @James, I do not know much about your business requirements. If log-shipping can solve your problem, you may still manually (via script of course) copy the log backup file of your sql server 2008 db and restore it to your sql server 2016 database (assuming the sql 2016 db is restored from your sql2008 db's full backup file). I did not test it, but you can try it. – jyao Feb 20 '18 at 20:17
  • @jyao, the wizard will fail for sure - I tried it already. The next plan is to try Ola Hallegren's scripts. – James Feb 20 '18 at 20:43

How can I get Sql Server 2016 on a 32-bit server?

You can't, SQL Server 2016 only comes compiled for 64-bit.

Is there a way to get around it with compatibility levels?

Thankfully, no.

I need to upgrade it to SQL Server 2016 in order to log ship to a new server, so we can put this thing out of its misery.

You'll need to have someone install an OS supported by SQL Server 2016, then install SQL server 2016.

Question: how can I migrate the data to a SQL 2016 server with <1 day of downtime?

Install a supported OS for SQL Server 2016 on the old 2008 server. Install SQL Server 2016, then setup log shipping as you normally would per your above quote.

  • It looks like I could use windows 10, right? 32 bit and supports SQL 2016. – James Feb 20 '18 at 19:33
  • SQL Server 2016 only comes in 64 bit. You can't run a 64-bit process on a 32 bit OS. @James – Sean Gallardy - Retired User Feb 20 '18 at 19:54

This is actually trivial unless the modern server is as bad (by todays standard) as the old one was by the standards of that time. I just did some digging and 4gb was what we bought as office systems in that timeframe - not as servers. Which means also that you don't have a large database. My oldest server is from 2007 and has 64gb memory and handles 4tb databases - something you could not do on a 32 bit OS, so your "large" databsae likely fits on a mid size SSD.

  • Set up an interim server, can be as a virtual machine given how comically low powered (compared to modern hardwrae, obviously) that old server is.
  • Take backup, restore on interim server. I say interim server because I am not sure you can actually restore the backup on 2016 without going to something like 2012 first - you run really old software there. You may check this, though.
  • If you indeed ned an interim server, take another backup then copy to final server. THis is why the interim should be on a VM - and yes, your laptop can virtualize something as small as your old server.

Other people will easily tell you whether you need an interim server - 2008 is VERY old and may or may not be supported for a restore on 2016. I stopped using it around 2012 or so... Too long to remember compatibility for restores.

<1 day downtime is quite trivial to do - this is 2017. There is no way your old server holds a large database by modern standards - 32 bit and 4gb memory will not have allowed anything in the range of hundreds of gigabytes. Using modern tech, saving that on a SSD and then restoring it should take WAY less than an 8 hour day. Using proper storage and a direct gigabit cable (supported by the motherboard even in 2007) you can transfer around 100 megabyte per second. That is 6 gigabyte per minute - how large do you think your database is? That is not a problem at all in an 8 hour day.

There is thank heaven no way that SQL 2016 still takes 32 bit into account - which was really not current when someone at your company originally decided to go with a low end server. And yes, this is really that ancient - I have servers here that predate SQL Server 2008 and they STILL have 64gb memory and were build on 64 bit operating systems. They are end of life, though and are due for replacement. Even in 2007 - you have a lower end workstation. Which means that anything you run there is not a problem to handle with modern technology.

  • TomTom, thanks for the answer. The DB is 900GB, and the new server is a couple of states away. Still not impossible to upload + download in that time. I think that's what I'll go with. In case anyone is curious, I can restore directly from 2008 to 2016 - I've been doing that to test my backups every day. You're right, I could set up a better server on my cell phone! – James Feb 20 '18 at 20:38
  • 1
    @James You can trick. Ship a full backup (physically, like on SSD), then just upload a log backup (which should be signicantly smaller). DHL etc. handle express next day delivery. – TomTom Feb 20 '18 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.