X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Access 2016, Server Express And Travel, Any Versions    
post Mar 15 2018, 07:49 AM

Posts: 296
Joined: 28-April 02
From: Heathfield, England

Hi, decision taken, it's time to rebuild/restructure/reconfigure my set-up but it means going into areas of which I have little knowledge and even less knowledge of the detail of how they interact so I'm asking for input/guidance on what would be considered the best configuration for my requirements. The constituent parts/needs are ...

Microsoft SQL Server Express Edition with Advanced Services (64-bit) (version: 10.50.1600.1)
This is the main database around which everything revolves (DB1). It is, to me, quite large but probably not so to professional coders. As at this morning the three main tables consist of ...

Table1: 2,128,023 records x 126 fields (growing at 120,000 records p.a.)
Table2: 210,894 records x 38 fields (growing at 13,000 records p.a.)
Table3: 170,292 records x 18 fields (growing at 9,000 records p.a.)

The .mdf file is 2.2GB so perhaps more large in number of records to be queried than in file size but still not something I would want to upload/download often.

I have no control over the structure and content of this database. It forms the foundation of a third party app. that automatically installs this server and the database on the local machine when the app. is installed. The app. automatically updates the database at midnight if the app is running and more frequently through the day if this option is selected. An update can be manually selected at any time. The database service runs 24/7 whenever the computer is running regardless of whether the app. is running. Under the software license I am allowed to have two installations. The app. can be installed and will run on a VPS as one of the licensed installations if desired.

Microsoft SQL Server Express (64-bit) (version: 13.0.4001.0)
I installed this server on my local machine with the intention of moving my data held in my Access database (see next para.) to an SQL database in the hope of gaining a speed advantage. I did not want to use the server installed by the app. simply because I wanted to keep my operation totally separate from the app. server/database. I know some will say it wouldn't be a problem but I wanted a clear divide between my data and the app's. data.

Access 2016
I currently use Access to hold my own data and as my front end to DB1 tables linked to it. My data is manually downloaded in xls/xlxs or csv format from websites, manually imported into Access tables and relationships are created with DB1 tables by cross referencing unique fields.

90% of the time I access the data when at home, on my home computer and use the servers and databases on that computer, no problem. My problem starts when I am travelling. Install everything on a VPS and use that you might say but ... when I'm travelling (particularly if sailing) I am not always able to get a good Internet connection and sometimes if I can it can be expensive especially if using my phone as a hotspot in a remote area, sometimes I cannot even get a phone connection so a VPS is not a 100% solution. Obviously I appreciate that no Internet connection means no updates but most of the research is looking for statistical patterns in the data so I can live with the data not being fully up to date but any research performed offline would need to be updated into the online version when a connection is available.

I am in the process of moving the Access tables to DB2 hence the thoughts on how to set this up properly ... I would be grateful for any suggestions.

Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
post Mar 18 2018, 12:34 PM

Posts: 75
Joined: 12-March 06
From: Redmond, WA

First, disk space is cheap, SSDs are relatively cheap and fast. Next, SQL server and the express version is made to handle many databases for many projects.
I run an HP laptop that handles many different projects as I have many clients. My databases vary from a handful of tables to a hundred, size varies from a few thousand rows to one project that has 8 million rows in many tables.
All done on my laptop. I take this traveling so I can visit clients and have my "office" with me everywhere. I use Access as the front end for many of my projects, including the very large one mentioned above.
So I would use the express version with advanced services (which is what I run on my laptop). Just make another database for your other project.
Also for security, I use VeraCrypt to create a secure container to hold my SQL and Access databases, just in case someone steals my laptop.
My laptop is an HP Envy with 16G memory (as memory is also cheap), 500G SSD subdivided C and D drive. Also have travel router to safely hook up to hotel wifi.
Hope that helps.


Willie McClure
Talk's cheap, takes money to buy whiskey.
Go to the top of the page
post Sep 11 2018, 01:25 AM

Posts: 296
Joined: 28-April 02
From: Heathfield, England

Willie, just noticed I didn't thank you for taking the time to respond to my post ... sorry, very ungrateful of me! frown.gif

I became distracted with a few things but am now back ... I take it that in short you are saying "get yourself a decent laptop"! My current set up is a reasonably decent laptop but it's too big and cumbersome for many of my travels. I like to travel hand-baggage only if at all possible to save time at terminals and a 'decent' laptop would take too muuch of the baggage allowance so I use a small notebook type when away and that's why I considered using a VPS as a way to make sure I am always using the same data. Maybe your suggestion is to get a 'decent' notebook and connect it to a remote screen and keyboard when at home! smile.gif I'll consider that ... thanks.

Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
post Sep 11 2018, 11:35 AM

Posts: 2,490
Joined: 27-February 09

I have a relative beater of a laptop... but one thing you absolutely want with any version of SQL Server is RAM. A major difference between the way SQL Server works and the way Access works is that SQL Server loads table data into memory and leaves it there unless it is forced out by other queries. So the more RAM your laptop has, the fewer results will get flushed from memory, because memory pressure will remain low. I don't think I'd run SQL Server with less than 12-16GB of RAM. Might just make you crazy - especially when you have no control over the super wide tables that you're dealing with.
Go to the top of the page
post Sep 13 2018, 12:25 AM

Posts: 296
Joined: 28-April 02
From: Heathfield, England

Thanks MadPiet, that's a helpful tip and may go someway to explain a few observations I have made in the past re speed. I am assuming that will apply to all SQL Server set-ups regardless of whether I address the Server directly (occasionally) or whether I address it via Access (usually). Would large RAM retain a noticable speed advantage over say an internal SSD or external USB3 SSD?

Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
post Sep 13 2018, 09:39 PM

UtterAccess VIP
Posts: 2,670
Joined: 12-April 07
From: Edmonton, Alberta Canada

Actually, SQL server was created and built during a time frame in which it liked lots of ram, but today, even a “bottom” feeder laptop with only 8 gigs of ram is plenty to run the express editions of SQL server. I mean, there as a time when a server with 4 gigs of ram was “huge”.

SQL express is throttled and restricted to only use 1 gig of ram max anyway. You have start using the paid versions for the SQL “server” to use more ram.

I will say, even on my weaker laptop (a duel core i3, 8 gigs of ram), I find that it has lots of ram, and is well up to the task.

SQL server really does a great job of managing the amount of ram. And just like Access, when you query data, it does not pull the whole table in question, and once again you thus get great performance.

I routine run SQL server on this weaker laptop, and I never noted any performance issues, and in fact when SQL server is running, I can’t even notice.

Lots of ram etc. is always nice, but 8 gigs on my laptop is MORE than enough to have outlook open, Visual Studio 2013, SSMS and Access open and a good number of web browser sessios open.

I see no disk thrashing or shortage of ram with this setup.

As noted, while the original free edition of SQL that was free (MCSE) was throttled rather severe. And file size was 4 gigs.

Now, the express edition allows file sizes to 10 gigs. And it aloso now uses multiple threads (I not sure of the limits in reards to multi-core cpu’s).

My little i3 does have two cores, but with hyper-threading the OS and system sees 4 cores. The threading and the ability of the i3 to “supply” all that stuff running constantly amazines everyday.

I would say that my laptop is a terrific setup, and it runs SQL express very well.

I could not be more thrilled with how well this weaker machine runs.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2018 - 08:41 PM