April 27, 2021 at 11:03 pm
We are a small manufacturing company with about 8 – 12 users of our application that work mostly remote. We are using MS Access as the front end (with heavy business rules coded in it) with SQL as the backend. We don’t have SQL Server yet but have decided to get a box for SQL Server instead of the expense of Azure SQL. There seems to be some agreement that we should put SQL Server on a SANs box not a NAS or even just set up a computer with SQL Sever on it and have users remote into it via virtual desktop or something.
We are looking for something with low cost but will deliver in performance. Also, we are looking to use the MS Access front end with SQL backend on mobile devices in the near future like cell phones and tablet. So, looking for the best solution even if it means moving away from MS Access as the front end to something that will better support our mobile efforts with SQL as our backend data. My questions are:
1. Which box is best for SQL Server where we can back up our data on-premises and cloud?
2. What is the best way performance wise for us to remotely connect to it for the application, VPN, Virtual desktop computer, etc.
3. We will need a Production environment and a test environment/Instance. Which version of SQL Server will be best? The free Developer, Standard or Web version for mobile?
4. What’s a better front-end application other than MS Access for SQL since we will need to go mobile in the future? Any ideas to steer me in the right direction would be great. I was thinking VB.net desktop but not sure if it is transferable to mobile devices. Your thoughts are appreciated.
Thanks,
JP
April 28, 2021 at 9:17 am
Cost cheaper on prem than the cloud, maybe right, but if your a small shop you may find on-prem is more expensive than the cloud, maybe you need to go and look at the capex vs opex vs supportability arguments again.
Capex vs Opex is the businesses way of purchasing remember to include licenses, SQL Standard is around $2,000 a core and Enterprise is around $7,000 a core and you must buy 4 cores or more, so your instantly paying out approx $8,000 or $28,000 before you have even thought about the hardware. Add in a server, network infrastructure, storage, your into building a mini data centre just to run the database. How much do you have to spend as your easily looking at over $100,000 before your off the ground.
Then do you have the expertise on staff to manage all those different pieces of technology? Do you have infrastructure admins, network admins, storage admins, database admins? No well your going to have to hire those roles, more cost in recruitment expenses, more costs in salaries having to be paid out. Add in the costs for those employees to the cost your into a good $250,000+ outlay.
Then you have depreciation, replacing hardware every 3 to 5 years how much of this can your company absorb.
A 2vCore model in Azure is going to cost around $400 a month
A S2 50DTU model in Azure is going to cost you around $75 a month
Divide the initial Capex cost of the $250,000 outlay going vCore model you can get 625 months of Azure rental or 3334 months in a S2 model. Yes as you grow you will need to up theses to higher models but your still going to be saving money longer term.
April 28, 2021 at 9:23 am
1. Which box is best for SQL Server where we can back up our data on-premises and cloud?
SAN or local storage. NAS is fine for backups, but not for your data / log files.
2. What is the best way performance wise for us to remotely connect to it for the application, VPN, Virtual desktop computer, etc.
Neither, have a app / web server along with your DB server and make the web server publicly accessible.
For the MS Access piece you will want VPN's from your clients to the database server.
3. We will need a Production environment and a test environment/Instance. Which version of SQL Server will be best? The free Developer, Standard or Web version for mobile?
Web is a special license you can only get that via CSP's.
For Prod, if you can live with the EXTREAMLY limited functionality of Express use express. If not use Standard.
For test use developer, but I urge you to read the licensing documentation to ensure you follow the correct use cases and ensure test never goes anywhere near prod etc.
4. What’s a better front-end application other than MS Access for SQL since we will need to go mobile in the future? Any ideas to steer me in the right direction would be great. I was thinking VB.net desktop but not sure if it is transferable to mobile devices. Your thoughts are appreciated.
If your going mobile app, then you will want to do things web based, you will need to write API's and make something thats HTML5 friendly so you can easily port things between Andriod/iOS as if your going mobile you will want native apps I would guess or something which renders in Chrome or Safari using HTML5 etc so it renders the same on all devices.
April 30, 2021 at 7:15 pm
There is quite a bit to unpack here.
Based on your scenario, with everyone already working remotely and wanting to add access to mobile devices and tablets chances are very good that you're going to need to navigate away from MSAccess. As Anti-Green stated in an earlier post, you'll probably want to build a web front-end for the users to access the data. It also seems very likely that there is going to need to be some kind of development for an app to run on mobile/tablets as there is a huge gap in interoperability when comparing a browser on a device versus a laptop/desktop. As that is the case, getting set up in a Public Cloud might have been the better option because it is easy to spin up the necessary infrastructure to make something like that happen. If everything is on-premises the infrastructure can be built out, but that can be a major expense if some of it is not already in place.
1. Which box is best for SQL Server where we can back up our data on-premises and cloud?
This is not a hardware question, really. Making a redundant copy of backup files out to cloud storage can be done in pretty much any configuration.
When selecting the "best box" what should be a factor is what level of database performance is required. Here again, a public cloud offering would have had a slight advantage because of flexibility. Need more processing throughput? Slide the dial up a little and the processing increases. Need faster disk? Make a configuration adjustment. Need more disk space? Am I saying that a cloud implementation would have been better? My answer would be, "It depends." For this use case, I think it would be only because you've not had a SQL Server before and therefore have no idea if the box that you've purchased is going to perform at the expected level or not.
If it was a virtual server or a cloud instance your only barrier to ramping up the performance is making an adjustment in the configuration and getting the additional monthly expense approved. All in the same day, if necessary. Physical hardware allows you to increase the RAM or swap out a disk controller, etc. However, that whole procurement process comes with a different ask and lead time. Could be weeks to months and during that time the users are unhappy and suffering.
2. What is the best way performance wise for us to remotely connect to it for the application, VPN, Virtual desktop computer, etc.
Sorry, this is more dependant on items outside of your control. A VPN connection is only as fast as the medium it is being used on. If every user has fast Internet access (Broadband) maybe that is the best way to go. If not, maybe Citrix or virtual desktops are a better option. What about application requirements? Is this application only going to be used on company-issued hardware or could someone have their own device? If that is an unknown, a centralized delivery option might be better for this case.
3. We will need a Production environment and a test environment/Instance. Which version of SQL Server will be best? The free Developer, Standard or Web version for mobile?
Anti-Green is right on the money with this one.
You have to pay for your Production instance. Hard stop. I have my doubts that for this implementation that you'd need the Enterprise Edition of SQL Server since Standard Edition comes with support for 4 Cores and up to 24 Processors and 128 GB RAM (even if the OS supports more) and lots of features.
Developer Edition is free, provided you follow the licensing rules.
4. What’s a better front-end application other than MS Access for SQL since we will need to go mobile in the future? Any ideas to steer me in the right direction would be great. I was thinking VB.net desktop but not sure if it is transferable to mobile devices. Your thoughts are appreciated.
Well, it depends.
Largely I would lean your development team. What is going to get you the closest to the look & feel of MS Access, albeit developed that way? What will be easiest for the development team to jump to from MS Access? What IDE offers the easiest methods of building a connector to Microsoft SQL Server? Probably VB.net Or Microsoft .NET in general. I'm not a developer so I can't say that is the case for certain or not, but I suspect that it is probably a better option.
When it comes to building something for a mobile device app I think that developers will have to navigate away from .NET. Though I am not sure.
*Disclaimer: I'm not providing this information as "rock-solid" advice that should be taken as the best way to approach your questions. This is advice only based on my experience and what I have seen. My answers to everyone asking about performance always start with "It depends."
Regards, Irish
April 30, 2021 at 11:26 pm
Thank you Ant-Green and Irish for your feedback. Very well thought out details for me to consider.
Thanks again!
JP
May 3, 2021 at 5:35 pm
Mostly agree with the above. Use a SAN. VPNs tend to be fairly cheap and work well, but in the long term you might want some type of app. I'd look at the new Power stuff and Logic Apps. Easy to assemble, depending on what you're doing, and might fit your need.
Standard is likely for your prod use, developer for test, but read the rules.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply