SQL Server?

[mounty76]

Dear All,

I have a large DB which is currently split, I've worked on it over the last 5 years and as such it's quite disfunctional in that it could be designed much better in how it works (IE much less tables and queries etc.), but as you live and learn you figure out better ways to do things. Essentially it works well but it could be better.

I'm looking at possibly starting from scratch and making a new DB using better methods to achieve the same results, reason for this is that the DB works well and I've been considering selling it/marketing it (small scale to start with and just see how things go).

Really I suppose my question is.......I'm thinking this should really then be done using an SQL Server so both ships and land based offices have access to the same backend using their FE. I've never done anything with a SQL Server so a few questions would be: 1. Can I just make a better functioning DB of my existing one then upload the BE to the SQL Server....is this an easy process? 2. Is it better to develop it using the SQL server from scratch. or 3. Is it better to get a developer to do it and pay to have it done properly haha....

Anyway, any guidance on the best way forward would be very much appreciated!

 

[CJ_London]

Usual reason for choosing sql server as a back end is data volume, data security or large numbers of users. Performance alone is not a reason (it is unlikely to be faster)

you mention ships which shouldn’t matter unless user is on a ship and the BE is on land somewhere in which case what matters is speed of the communication method and optimising your app to minimise network traffic

 

[amorosik]

Not exclusively Sql Server but certainly any "db server" (Oracle, Ibm, Postgresql, ...)

 

[mounty76]

Usual reason for choosing sql server as a back end is data volume, data security or large numbers of users. Performance alone is not a reason (it is unlikely to be faster)

you mention ships which shouldn’t matter unless user is on a ship and the BE is on land somewhere in which case what matters is speed of the communication method and optimising your app to minimise network traffic

Thanks for this, communication speed shouldn't be an issue now with Starlink which many ships are using. Ideally I'd like BE on a cloud so can be accessed from any FE user regardless where they are in the world.....from what I've read best solution is SQL Server for the BE but any other 'easier' suggestions much appreciated. If I can run it so that the DB is designed and split the same as you would using a local server (but using a cloud server instead of a local server) that would be perfect

 

[Lightwave]

This is a long term project - so give yourself plenty of time e.g year or more...

Its a good idea to get the backend into an enterprise grade database... My personal choice is usually sql server or postgres although mysql and maria db are options as well.

If you already have an MS Access front end I would go with SQL Server.

Once you have it in SQL server you won't need to worry about size... so an immediate win.

Chances are you have a complicated backend if you have been working on it for 5 years.. Get yourself a copy of SQL Express and SQL Server Management Studio (both free) and firstly see if you can get it installed and running and then take your biggest table and learn how to recreate it in SQL express. Just do this locally initially. That will give you an idea of the complications involved of just recreating the backend in SQL Server.

It is easy to take a backup of SQL Express database and upload it to the cloud if you want in fact provided you have an Azure account you can do it in minutes directly from SQL Server Management Studio. Here is a link to get SQL Server Pro and Express (choose the express version). In theory you will be able to link your existing access front end to the new SQL express database BUT you will have to be exceedingly accurate in the way you recreate the SQL backend to do that. Here's the link to download of SQL Server Express and Pro versions

SQL Server Downloads | Microsoft

Get started with Microsoft SQL Server downloads. Choose a SQL Server trial, edition, tool, or connector that best meets your data and workload needs.

www.microsoft.com

I believe there might be tools that will partially automate transition from Access to SQL Server but you should expect that they will not work... I personally have never used them and haven't investigated them but briefly so can't give you advice on that... But automatic transformation between platforms is usually a myth.

 

[mounty76]

Thanks very much for this, much appreciated. I had already given myself a year plus to develop this so all good for that, I'll do some research based on the above and more than likely revert on this forum! Thanks again

 

[mounty76]

Thanks very much Pat, much appreciated. Complicated stuff. I guess locally stored on a server with remote desktop for an external office would be the way forward in my circumstance.

Thanks again

 

[Gasman]

SQL Server, which was originally created by Microsoft, is a highly advanced and widely-used relational database system. It provides a robust framework for storing, managing, storing, and retrieving highly-structured data, making SQL Server a fundamental tool used by both businesses and organizations. SQL Server provides a comprehensive set of features that allow for efficient data management, data manipulation, as well as data analysis capabilities. Its scalability is complemented by performance optimizations that allow for processing large volumes and assisting high-demand applications. SQL Server features advanced security to safeguard sensitive information, as well as support for integration with Microsoft technologies, resulting in seamless data integration and interoperability. Overall, SQL Server works to create robust and secure data-driven solutions across a range of industries and use cases. View attachment 108654

Another ChatGPT lookup?