Hi

Two part problem

1.  I want to check if other users are in the database before I try to
restore a backup.

I have this SQL, which hopefully will tell me if there is anyone other than
me connected to the database.

SELECT DISTINCT
 User, LEFT(Process FOR POSITION(':' IN Process) - 1) AS ComputerName
FROM Configuration.ServerSessions
LEFT OUTER JOIN Configuration.ServerSessionLocks ON (SessionID = ID)
WHERE DatabaseName = 'DB0000'
 AND User <> CURRENT_USER
 AND LEFT(Process FOR POSITION(':' IN Process) - 1) <> CURRENT_COMPUTER

Is there a simpler way? And is my SQL idiot proof?

2.  Having discovered other users connected, is there a way to tell them to
depart other than killing their session using the server interface.
Ideally, I'l like to do it programmatically in my application.  It is
possible that the offender might be at some remote location, so a "Hoy You!
Log Off!" across the room won't work.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


I don't know about your SQL but your concept itself isn't idiot proof.

You're starting from a premise that all access to the database will be through the approved means of a c/s connection. If there is a possibility that this isn't the case (eg some moron going in through EDBManager)

Getting people off, and keeping them off will be a problem. I've implemented a locking scheme where I can say the system is isolated and no one is allowed to log on but log off is a trifle more difficult.

Roy Lambert

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:F8682768-8CE8-4B5E-A470-405BC8339480@news.elevatesoft.com...
>
>
> I don't know about your SQL but your concept itself isn't idiot proof.
>
> You're starting from a premise that all access to the database will be
> through the approved means of a c/s connection. If there is a possibility
> that this isn't the case (eg some moron going in through EDBManager)
>

OK, "Idiot-proof" was trifle optomistic.

However - morons or not, I'm not giving them EDBManager and if they somehow
get it it for themselves and call down catastrophe upon themselves, I'll be
happy to charge gazillions of dollars to unravell the mess.

App is c/s only.

> Getting people off, and keeping them off will be a problem. I've
> implemented a locking scheme where I can say the system is isolated and no
> one is allowed to log on but log off is a trifle more difficult.
>

For me, at the moment, the only real problems is having a way to kick off
forgottten or remote sessions.  Users will probably have administrator
privileges.

Cheers

Jeff


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


Will you need to run the restore remotely? If it could be on the server PC, or at least fileserver mode you could simply turn off the server and then use a separate small app to do the restore in f/s mode. That would kick them off and keep them off until the database was restored.

Roy Lambert

Jeff,

in addition to what Roy recommended:

you can use the "OnRemoteReconnect" of a session to tell the user that
there the connection to the server has been interrupted, e.g.:

procedure MySessionRemoteReconnect(Sender: TObject; var Continue,
StopAsking: Boolean);
begin
  Application.MessageBox(PChar('The connection to the serverwas
interrupted.' + #13#10 +
        'The application will be terminated now.'), 'Connect error', 16);
  Continue := false;
  StopAsking := true;
  Application.terminate;
end;

Uli

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:934E5AF0-FFF8-4C6E-94A0-98001BC246D7@news.elevatesoft.com...
> Jeff
>
>
> Will you need to run the restore remotely? If it could be on the server
> PC, or at least fileserver mode you could simply turn off the server and
> then use a separate small app to do the restore in f/s mode. That would
> kick them off and keep them off until the database was restored.
>
> Roy Lambert
>
Hi Roy, thanks for your reply.  You are one of the few posters I see from
Saturday to Tuesday - with the  time zones we sort of miss a day each
weekend.

We made a few mistakes first time round with our DBISAM app.  This time
round with EDB we are going c/s only.  Even the clients with just one PC
will be c/s - and probably won't even know!  Once we have the first sites
onto EDB we will be working on replication to the cloud - remote access via
EWB or remote laptops.

So yes, remote restore is possible and may even be done by ourselves in a
support role.

In our existing set up we have had to talk people through shutting down the
file server or the c/s server - most of the users are ignorant about how all
that, as they should be - they aren't IT professionals, so should be getting
on with running their business.

The only places where we get into trouble are those that employ a external
tech person - these guys seem to enjoy moving apps around so the shortcuts
no longer work, backups fail, directories become read only ... grrr!  And
sometimes it is the business owner's son who is "very good with computers"!

Since it doesn't matter what a session was doing if you are going to restore
the database, it would be nice to be able to send a message to the server to
say "disconnect these sessions".

Looks like the best I can do is attempt a restore.  If I get a error because
of the failure to get exclusive access, then throw up a screen showing
offending sessions and sit in a loop until I get access or give up.

Maybe I could use a shutdown flag in the database to tell users to b*gg&r
off and a timer in the app to check it (instead of RemotePing).

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Uli Becker" <johnmuller54@gmail.com> wrote in message
news:526B55EC-5C16-4FCE-BAFB-6FF286DF63F2@news.elevatesoft.com...
> Jeff,
>
> in addition to what Roy recommended:
>
> you can use the "OnRemoteReconnect" of a session to tell the user that
> there the connection to the server has been interrupted, e.g.:
>

Hi Uli, thanks for your reply

I can see the value in what you are suggesting, but clearly I have limited
understanding.

If the user is simply needing to reconnect because he had been inactive for
a "long time", then wouldn't this code shut him down regardless - i.e. not
just in the case oif a db restore?

Cheers

Jeff

> procedure MySessionRemoteReconnect(Sender: TObject; var Continue,
> StopAsking: Boolean);
> begin
>   Application.MessageBox(PChar('The connection to the serverwas
> interrupted.' + #13#10 +
>         'The application will be terminated now.'), 'Connect error', 16);
>   Continue := false;
>   StopAsking := true;
>   Application.terminate;
> end;
>
> Uli
>
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


>We made a few mistakes first time round with our DBISAM app. This time
>round with EDB we are going c/s only. Even the clients with just one PC
>will be c/s - and probably won't even know! Once we have the first sites
>onto EDB we will be working on replication to the cloud - remote access via
>EWB or remote laptops.
>
>So yes, remote restore is possible and may even be done by ourselves in a
>support role.

I don't use c/s so its all guesswork. The manual quotes a command DISCONNECT SERVER SESSION and a table ServerSessions which looks as though it could be used to throw people off (and as you say it doesn't really matter - apart from ruffled feathers - if you're doing a restore). Keeping them off may be a bit more difficult. The engine's LicensedSessions property may help, or having all users as a member of a group from which you remove access, or opening a table in exclusive mode, or changing the ports the server listens on.

You could also look at customising the server code. No idea how easy or difficult that is.


>In our existing set up we have had to talk people through shutting down the
>file server or the c/s server - most of the users are ignorant about how all
>that, as they should be - they aren't IT professionals, so should be getting
>on with running their business.
>
>The only places where we get into trouble are those that employ a external
>tech person - these guys seem to enjoy moving apps around so the shortcuts
>no longer work, backups fail, directories become read only ... grrr! And
>sometimes it is the business owner's son who is "very good with computers"!

Yeah - so - I do that to myself and then sit scratching my head (and other areas) for while until it finally penetrates what I've done

Roy Lambert

Jeff,

> If the user is simply needing to reconnect because he had been inactive for
> a "long time", then wouldn't this code shut him down regardless - i.e. not
> just in the case oif a db restore?

In my opinion a connection should never be closed because of inactivity.
But if the connection has been lost for whatever reasons, the user has
to restart the application anyway, except you are able to "reset"
everything in your application after a reconnection.

So my code just tells the user, that the connection is lost and the app
has to be restarted.

Uli

On 6/29/2014 12:15 AM, Jeff Cook wrote:
> Since it doesn't matter what a session was doing if you are going to restore
> the database, it would be nice to be able to send a message to the server to
> say "disconnect these sessions".

There has been a request for generic messaging layer in EDB for a while
but for now you'd have to roll your own solution to notify the clients.


> Maybe I could use a shutdown flag in the database to tell users to b*gg&r
> off and a timer in the app to check it (instead of RemotePing).

If you're on the LAN then a separate UDP broadcast or multicast might do
the trick - your app would need to listen to those though but it would
not require any polling so no constant polling traffic involved.

Otherwise yes a special table that clients would poll every X (say 60)
seconds and then the app would notify the user and close itself would be
next best alternative.   Combine this with checking this flag on app
startup and reconnect events (i.e. deal with disconnected sessions that
Uli suggested and that might not poll otherwise) and you should have a
full solution. Include a text message that app displays and you get to
tell users what's going on when app will be back.

Raul