Link to home
Start Free TrialLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

How can I upgrade from SQL Server 2008R2 to SQL Server 2017?

We currently run SQL Server 2008R2 on a Windows 7 computer with 16GB of RAM and are being forced to upgrade as the Access SQL Server Migration Assistant no longer works with 2008R2.

I plan to purchase SQL Server 2017 Standard and would like to get suggestions as to what type of desktop hardware we will need and how much trouble I will have if I purchase a new machine and attach it to our network as a workstation, and then try to install 2017 Standard on the new machine, and then move the existing files through the LAN to the new machine.

My computer consultant told me all we need is a Windows 10 Pro machine for our application.. in addition to how much trouble it will be to get 2017 to work with 2008R2 files, what type of hardware do you recommend for the new machine if we attach it as a workstation.

By hardware I mean... would an i7 processor with 32MB and a 10T drive be a good choice?  I am confused because I was expecting to need a raid mirror drives, but don't seem to see many companies selling hardware configured this way.  

The SQL Server machine would be used exclusively on on our LAN as the BE for MS Access.
Thank you

SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
RAM is so relatively cheap, and if needed it can help performance so much, that I'd suggest getting 64GB RAM.  I wouldn't allocate all of it to SQL Server immediately, but you have some in reserve if it's needed. 
64 GB of RAM w/o any knowledge of database size and number of connected users?
Why not 128 GB then?
Ok, why not.  RAM is EXTRAORDINARLY CHEAP compared to improving I/O speed in other ways.  NO extra licensing costs.  Operating costs are super-marginal, at most (you can get 128GB in the same number of chips as you get 32GB or 64GB on all systems that I know of, just buy larger disk modules).

I have 512GB on my main cluster (it's Enterprise, so SQL can use what I give it out of that).

Additionals disks, controllers, paths, etc. are WAY more expensive than RAM.  Even if you just considered electricity and cooling alone.

NOTE: I am NOT a hardware person.  If I used the wrong technical hardware terms above, please substitute better ones.
Avatar of pcalabria

ASKER

Sorry to leave out such important details.  

We currently have ten users and six .MDF databases on our SQL Server 2008R2.  Two are 100GB, two are 50GB, one is 20GB, and one 2GB.  We currently have log files disabled as we do not have adequate disk space.

We plan to upsize at least two additional databases which are currently 1.0GB and 1.4GB in MS Access.  My experience has been that once these are upsized to SQL the sizes will balloon.

This is why we are concerned with drive size.

Can you please revise your suggestions concerning RAM and mechanical vs SSD drives.  Also, should we be purchasing a machine with RAID mirror drives?  Thank you.

Note than while we currently have ten users of our application and all users opens a connection to the SQL server when the application is first started, only two user makes heavy use of the SQL Server, although all users have connections open to all SQL databases.  I don't know if opening a connection is important in SQL Server.. it certainly is in Access.. and the application was developed before we upsized from Access to SQL.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gustav.. sounds very interesting but total new stuff for me..so this is a PC..?? HPE or HP?  I was checking Amazon and became confused... install Windows 2019 OEM ROK? .. why is this better than just adding another win10 desktop as a workstation?  
I'm interested.. of course.. just adding another win 10 server machine and installing a new version of sql server has a high comfort zone rating...I think I would need to hire someone to set this up and get it working on our network. 
HPE is Hewlett Packard Enterprise. HP was split some years ago into two, HP for consumer and printer, HPE for server, networking, and storage.

Any server is basically a pc, the difference being, that it is optimised to act as a server.

Likewise Windows Server. It is a clean OS - stripped from useless apps and games, thus it installs in minutes. Install it with the "desktop experience", not the "core edition", and you will have a full GUI for administration. The OEM or ROK versions are editions licensed by HPE to run on their servers only, thus a cheaper price.

Also, don't buy such stuff on Amazon. Go to a trusted local dealer (your computer consultant?) who can assist all the way. Not that it is difficult but - as with everything - experienced people are just faster and know the traps and how to avoid them.
OK, now it really seems the more RAM the better for you. Of course, the main speed contribution will do SSD drives.

RAID or not? If you have sufficient and frequent backups then you don't need RAID.

IMO, HPE Microserver with appropriate configuration will be rather expensive. Do you need Xeon CPUs?  The only advantage is the machine which is ready to go and maybe the support.
The Server Configuration and the Disk Configuration should be in line with the Business Continuity objectives of your Business!

If your business can cope with multiday downtime and can easily replay any missing transactions, then consumer PC's and Consumer storage would suffice, assuming you had frequent (and verified) Backups!
As your BC Window decreases so does the complexity (and cost) of your IT equipment! Do you need a Enterprise level server, rather than a Consumer PC for reliability, do you need a UPS, do you need Dual-PSU's wired to different circuits, do you need to use consumer disks or Enterprise grade disks, do you need to use RAID etc? These are all things that will drop out of a Business Continuity discussion with the business managers (who might scale back on their demands when you tell them how much it will cost!)
Work out some scenarios that will affect your Business E.g from a fuse blowing to an aircraft hitting the building! how will the business continue and what IT equipment and recovery time is required to meet that target! Then you can plan on what equipment you need and how it needs to be configured!

We dont know your business drivers, so its impossible for us to recommend what exactly you need to do, just recommend strategies that will help YOU to decide!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, all great info and help....

What is the consensus as far as the operating system???  Win10 Pro vs Server Software

..(keeping in mind) we are currently running SQL Server 2008R2 on a Windows 7 computer and will be upgrading because we need to migrate two new MS Access databases to SQL and migration is no longer supported to 2008R2.

Our network currently has about 15 PCs attached and we are using Windows Server 2019 which is also the DC...

If I use Windows 10 Pro it sounds like I should be able to install all the software, join the domain, and get everything working in one evening.... whereas using Server software adds a significant cost and complexity.. hire an engine.. pay the license fee.. integrate with our existing domain (I have no idea what I would be in for!)...

What is the BENEFIT of using server software?  Thanks!

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't believe there is one way to do it (consensus).
Basically Windows Server supports higher-end hardware and maybe a better fit with your backup and disaster recovery strategies. But at a much higher cost of both purchasing and management.
You could also Immediately move to the cloud and have it installed on Azure SQL Database, a new service from Microsoft that avoids more initial investment.
https://azure.microsoft.com/en-us/services/sql-database/
It feels like you need a partner to guide and assist you, not only in this small migration project but in more longer term strategic IT infrastructure decisions.
Usually the purchase cost is only a fraction of the total cost for a company.


Well pretty much every configuration should work.
My take on this would be a refurbished server with at least 32gb ram (better 64 or more) , dual CPU, dual PSU and no matter what SSD drives in any form.
I must say for only 10 users why such humogous databases (100 gb) ? and some question...at first why they rocketted to such high volumes (Normalization ?)...do you really need the features of Ms SQL ? ..maybe MySQL or PostGre to avoid licencing cost ....and last...you can always keep old SQL just by having an older version of SSMA...or at least take your time before rushing to upgrade
Thanks all.. I'm afraid I started this a few days too late.. When I reported to work yesterday I found the machine running the SQL Server 2008R2 software would not boot.

Here is a thread I published.. in case anyone can/will help.
https://www.experts-exchange.com/questions/29191409/How-do-I-attach-MS-SQL-Server-2008R2-database-files-to-an-MS-SQL-Server-2017.html