Working offline (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
I have searched the forum and some threads are close to addressing my issue but I am hoping for a better answer here.

Bottom Line: I am looking for a way for users to work offline if the server is down and then synch changes when the server is up. I have researched and found some ideas but I wanted to ask here how this has worked for others. Code examples would be appreciated too...
 

deletedT

Guest
Local time
Today, 11:33
Joined
Feb 2, 2019
Messages
1,218
I hope you don't mind if I add one question to yours.
What will happen if two users edit the same record while the server is offline? Which one would be applied when the server comes back online?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:33
Joined
Feb 19, 2013
Messages
16,553
what is the back end? Access? Is the server down by design (e.g. for maintenance) or by accident (power cut)?

I have used my own form of data replication which requires additional fields in tables. But this was designed for processes where the user would be offline on a regular basis and the process involved a requirement for syncing when leaving and returning to the office (or at least disconnecting/connecting to the server). Works well but is typically for small changes in data and typically users would be working with their own data (e.g. sales person out for the day collecting orders, engineer taking readings) - although catered for, it would be unlikely a customer would receive visits from two sales people or two engineers would read the same meter). No reason why it shouldn't work for large data changes

With regards Tera's question, that is handled within the additional fields which can determine which was later based on timestamp comparisons and either has a rule that pops up a conflict message when such a clash is found for a real person to make a decision, or applies a predetermined rule.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
@Tera and @CJ_London ,

Thanks for taking the time to reply. The BE is SQL Server 2014(Not sure what version but I think that is right). Most users are off by 1700 CET and the Maintenance Plan (MP) I have in place runs at midnight CET.

On VERY rare occasions the server is offline - so rare that I question the need for an off-line feature. My plan is to look into it and IF it was an easy thing to implement then I would do it, if not then I would forego it.

My idea:

1. Add a copy table feature to the MP. This would copy the latest changes to a BE which would be on the LAN.
2. A user logs in, SQL connectivity is checked. If no connectivity, either the tables from the LAN are copied to that user's machine or the tables are linked from the LAN. I do not like either option but the linked LAN tables will only work for the users in Italy. The Spain and Bahrain users would be SOL.
3. Connectivity would be checked periodically and if found the synch routine would run and connect that user to the SQL BE.

A gross over-simplification and I am open to any all ideas, you will NOT hurt my feelings by disagreeing with me. As to Tera's question, I have a Function that coverts local time to GMT. When offline, all changes would have a GMT timestamp and records would reflect it. Even if there are "collisions" and two users enter the exact same data, it isn't that big of a deal and can easily be corrected.

Make sense?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:33
Joined
Feb 19, 2013
Messages
16,553
not sure! if you are only copying latest changes (at midnight)? then what about the data that has not changed? Or perhaps I'm taking that too literally

And are you saying the server will only go offline (on rare occasions) during maintenance?

and the copies tables - do you mean they are copied as sql server tables, just thinking if they are something else it would impact on all your queries
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
That was a poor choice of words. I meant to say the tables in their entirety would be copied. When I say offline, I mean they are offline at time other then the planed maintenance time.

As for the copied tables, I was saying that the server would insert them into an Access BE. However, typing out loud, I don't think that is even possible.

Even if it was, consider this; The MP copies the tables at midnight. Users come to work the next morning and the server is working and the get to work. Lunch time comes around and there in a power outage in the IT building. The users come back from lunch and the offline routine kicks in...only now, any changes made from the morning to lunch are not available because they are working with data that was saved at midnight.

My head hurts...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:33
Joined
Feb 19, 2013
Messages
16,553
think if there is an outage, they'll just have to go make a cup of coffee!

not sure if within budget, but sql server can be spread across many locations with auto updates between them
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
think if there is an outage, they'll just have to go make a cup of coffee!

Agreed!

not sure if within budget, but sql server can be spread across many locations with auto updates between them

I had a conversation with the IT folks while I was in Bahrain. They have an SQL Server there and I asked about our server "talking" to theirs. As you know, it CAN be done, but the red-tape is daunting. I put in the required paperwork but I am not optimistic that this will happen before I transfer.

When you said "sql server can be spread across many locations with auto updates between them", was this what you were talking about?
 

Minty

AWF VIP
Local time
Today, 11:33
Joined
Jul 26, 2013
Messages
10,354
In SQL server terms the multi-site thing is called Replication I believe, which allows you to use a failover recovery model.
Or plonk it in Azure and redesign it to cope with the lag of it being off-premise. (other options are available but Friday's choices are always a bit limited ;) )
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
Completely understand the Friday Choices limitations...!

Unfortunately, Azure, MySQL and any other software solutions are no-go from the start. I can only choose from what is offered that those didn't make the cut!
 

isladogs

MVP / VIP
Local time
Today, 11:33
Joined
Jan 14, 2017
Messages
18,186
The earlier comment by Tera questioning what happens if two users edit the same record online is the main stumbling block in my opinion.
Although rules can be implemented to deal with that, much better to avoid it happening.
As well as server downtime, occasionally it is necessary to close a database for essential maintenance.
In either case, I wouldn't advise having any system that allows users to work offline
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
I have come to that very conclusion. Appreciate everyone for their input and talking me off the ledge!
 

zeroaccess

Active member
Local time
Today, 06:33
Joined
Jan 30, 2020
Messages
671
In SQL server terms the multi-site thing is called Replication I believe, which allows you to use a failover recovery model.
I agree, I think a failover is needed in a group setting, otherwise things are going to get hairy if users can work offline and their computers are not working with the same data.
 

SQL_Hell

SQL Server DBA
Local time
Today, 11:33
Joined
Dec 4, 2003
Messages
1,360
I think some form of failover is a good option here, is there a budget for another DB server? There are a few different methods for high availability in SQL server, failover clustering, always on availability groups (Enterprise edition only), mirroring and log shipping. How much downtime are we talking here?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:33
Joined
Apr 27, 2015
Messages
6,280
Minimal and VERY rare occurrences, which is why I am taking CJL's advice about a cup of coffee!
 

AccessBlaster

Registered User.
Local time
Today, 04:33
Joined
May 22, 2010
Messages
5,823
What will happen if two users edit the same record while the server is offline?
Nothing because you wouldn't be able to logon the server is offline, unless I'm missing something. :unsure:
 

deletedT

Guest
Local time
Today, 11:33
Joined
Feb 2, 2019
Messages
1,218
Nothing because you wouldn't be able to logon the server is offline, unless I'm missing something. :unsure:
It's obvious that I meant: if two users edit the same record what will happen when the server comes on line.
 

Users who are viewing this thread

Top Bottom