How to detect if database is in use when restoring

[MarioFB]

I am using FB 3.0.6 GBAK -rep command to restore and replace database from an existing .FBK backup file.

I would like to detect if the database is in use (e.g. opened on a networked Windows 10 computer), when using the localhost + ServerName links to access the same, shared database file.

When executing the  GBAK -rep restore and replace command, it fails to restore the database, when the same database file is already opened (in use) on the networked Windows computer (e.g. across the local office network). 

However, I am not able to detect if the shared database is in use, which causes silent failure to restore.

P.S. I have tried to use the alternative command  GBAK -r o but the result is exactly the same, with the database restore failing "silently".

Any ideas how to detect if the database is in use will be greatly appreciated.  

[Dimitry Sibiryakov]

11.12.2020 06:53, MarioFB wrote:
> I am using FB 3.0.6 GBAK -rep command to restore and replace database from an existing
> .FBK backup file.
> I would like to detect if the database is in use (e.g. opened on a networked Windows 10
> computer), when using the localhost + ServerName links to access the same, shared database
> file.

1. A database must not be on shared drive.
2. Restore is an emergency routine that should be used only in cases when the database is
lost.

If you violated both of these rules, something is wrong in your network/system management.

--
WBR, SD.

[MarioFB]

Re.  1. A database must not be on shared drive  
It is a common, standard practice to share the database across the office network, using the peer-to-peer network connection. Firebird client-server is designed to enable the database sharing in such environment.

Most users cannot afford expensive, dedicated Windows database servers and they don't need them anyway.

Besides, trying to restore the database into a shared office server computer, while the database stays open on one of the workstations, creates exactly the same restore issues.

Re. 2. Restore is an emergency routine that should be used only in cases when the database is lost.

Nonsense ! 

Totally disagree.

Database Backup and Restore routines are performed routinely on a daily basis, e.g. to share the data files between remote office locations. This is a standard business practice, NOT some "emergency routine".

Assuming the Firebird does not provide any means to warn the user that the database might be in use while trying to Restore it, this is a major weakness in Firebird design and functionality, which should be urgently addressed.

[DougC]

Sharing of a database file is NOT a common practice with Firebird databases. In fact, Firebird has never supported this, although in some versions it allowed it. It has NEVER been recommended.

Moving a database between locations is an odd way to share data in a client-server setup. Normally, all locations access a central database using networking protocols.

So, in short, you are misusing the Firebird system and will probably not find much support here for what you are doing.

---- On Fri, 11 Dec 2020 16:48:58 -0500 MarioFB <elit...@gmail.com> wrote ----

[MarioFB]

I would like to clarify the meaning of the term "Sharing the database". How would you define the standard Firebird client-server connection, where a database file is open and accessed simultaneously from several workstations within the same office ?

Specifically, when using the ServerMode = Super   across the office network i.e. "where attachments share single DB pages cache inside process"

Regarding Moving the database between remote locations, it is impossible to "share" the data across long distance remote office locations, unless the database is stored somewhere in the cloud database server.

Backing up and transferring the database file via email or a cloud storage system, is the only feasible way to physically transfer the data between these remote office locations.

The backed up database will then need to be restored on the recipient computer, to bring it up to date.

If this recipient computer happens to be part of the local office client-server database access, then we can have a potential issue, where the database we want to restore is already opened on another office workstation.

All I am trying to do is to detect this commonly occurring situation and warn the local user to disconnect from the database on all other networked workstations, prior to restore.

Is it possible to achieve this,  i.e. detect the existing local connection to the server on another workstation ? 

[DougC]

"Sharing the database" as you have described is simply making the database accessible through the client-server networking protocols. The normal situation is for the database file to be physically located on the server and NOT on a shared volume. For best practices the database files should only be accessible from the Firebird server process.

If you need to have the data accessible from a remote location that does not support the networking connection of a normal client-server setup, you need to replicate the database. Sometimes a replication protocol will work when a client protocol does not. Depends on your needs.

If no remote access is available, then your can copy the database to the remote site. To ensure the existing database at the remote site is not being accessed when you want to replace it with a newer copy you should shut down the running database at the remote site. There are documented operations/commands to do that. Regardless of whether a user is currently accessing the database, you should run the shutdown on it when it is time to perform the replacement.

There are also documented system tables that can be quieried to see how many users are currently connected, so you might want to start with those. But you still need some type of procedure to notify current users that the database is going to be shut down, so I would focus on doing that.

---- On Fri, 11 Dec 2020 18:00:00 -0500 MarioFB <elit...@gmail.com> wrote ----

[Ertan Küçükoglu]

Hello,

If you can "code" maybe you can write yourself a small console application which connects to the database, query mon$attachments table for any existing connection other than your console application. If there are other connections you can warn your automated restore the way you find suitable.

MarioFB <elit...@gmail.com>, 12 Ara 2020 Cmt, 02:02 tarihinde şunu yazdı:

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/692328bd-c250-41de-8503-b9c12cb1ae67n%40googlegroups.com.

[MarioFB]

Thank you. The advice from the last two posts above is very helpful.

In reply to @Doug re. "sharing the database", I can confirm that the database file is physically located on the server and NOT on a shared volume and it is only accessible via the Firebird server process.

I have been using the GBAK backup routines prior to transferring the database to a remote location as a simple, most efficient and the fastest method of "sharing" the data remotely.

In most cases (the rural environment), the internet speed is unacceptably slow to permit the database replication or copying the full, uncompressed database files via internet.

The backed up, compressed database files are many times smaller than the full, uncompressed data files, which makes it feasible to transfer them by email or via cloud storage service (e.g. Dropbox or OneDrive).

We have tried to copy the uncompressed databases into the remote sites, but this was very slow and very inefficient, because of the database file sizes.

I will certainly look into your suggested operations/commands to shut down the running database at the remote site.

I have already implemented the GFIX -shut -force 0  command prior to GBAK -rep, however that does not seem to work correctly when the database is open on another workstation connected using the ServerMode = Super  connection mode.

The GFIX -shut -force 0  command appears to only work when the ServerMode = Classic is used.

If you could please point to some relevant links on this database "shut down" topic, it will be highly appreciated.

Ideally, I would like to prevent the user from running the Restore process, if the Firebird detects an existing database connection from another office workstation.

[Karol Bieniaszewski]

>> I would like to clarify the meaning of the term "Sharing the database

 

You really do not clarify it 😉 For me only fasible way to work with database is one computer with it and installed Firebird server which listen on some port (default 3050). And all clients in the network connect to db using that TCP port.

Your connection string should be e.g.

10.100.200.15/3050:D:\mydatabase.fdb

And it really should not be \\10.100.200.15\mydatabase.fdb.

 

Which one do you use?

 

 

>> Backing up and transferring the database file via email or a cloud storage system, is the only feasible way to physically transfer the data between these remote office locations

>> Regarding Moving the database between remote locations, it is impossible to "share" the data across long distance remote office locations,

 

Why? Vpn+RDP and even with only VPN (if db application is designed as thin client).

 

>> All I am trying to do is to detect this commonly occurring situation and warn the local user to disconnect from the database on all other networked workstations, prior to restore.

 

Is this db ReadOnly for users? If not, then you lost users work by replacing db.

And by detecting connected users. Simply connect to DB and send event to the users. But what then, do you replace db without copy of current one?

And Firebird do not allow you to replace db currently used by users as any other file opened in system.

To be absolutely sure that db is not used, simply rename database file. If succesfull, then noone is connected.

You can also look at database shutdown modes.

 

regards,

Karol Bieniaszewski

--

[Karol Bieniaszewski]

You can rename remote file by PS

https://www.reddit.com/r/PowerShell/comments/6u1wiz/rename_file_on_remote_pc_to_name_of_remote_pc/

 

You can choose also different way, not so elegant.

Connect to DB and run

DELETE FROM MON$ATTACHMENTS WHERE MON$SYSTEM_FLAG<>1 AND MON$USER<>CURRENT_USER.

I recommend to send first to users db event. And in app you must catch this event and monit users that they should finish their job in e.g. 3 minutes.

And after 3 minutes you can run this delete command. Add also shutdown mode to prevent new connection. All together will be more elegant.

I recommend also to send first email that such action will be e.g. at 8pm to 8:30pm. So users now that they should not work in this time.

 

regards,

Karol Bieniaszewski

 

Od: Dr Mariusz Skorupski
Wysłano: sobota, 12 grudnia 2020 12:32
Temat: RE: [firebird-support] How to detect if database is in use when restoring
Ważność: Wysoka

 

Bardzo dziekuje za odpowiedz i sugestje.

 

I will make further comments in English.

 

Yes, I use Firebird server set up in a correct way, as you have suggested below.

 

When restoring the database on a remote location computer, the old database is replaced with the new one. This is by design.

Users fully understand that restore will delete the old database. It is usually done on a computer that is using the database as read-only.

 

I have implemented your idea of trying to rename the database, to test if it is being used (connected to).

That works really well when the database renaming is done on the server (master) computer, i.e. the one connected locally to the database as

localhost:C:\mydatabase.fdb 

Lets assume that this server computer name is  XYZ and it has the database file stored in C:\mydatabase.fdb

I have written the code in RAD Studio (Delphi) that attempts to rename the C:\mydatabase.fdb to C:\mydatabase.fdb1.

If rename is successful, the database file is renamed back to the original name and the Restore is successfully processed.

If the database is in use (locked), i.e. is being accessed from the client computer (which uses the following access string  XYZ:C:\mydatabase.fdb)

the renaming of the database file fails and the Restore process is cancelled with an appropriate error message.

 

Unfortunately, this database renaming test does not work properly when the Restore of the database is initiated from the client computer.

When trying to rename the database file stored remotely on the server computer, i.e. renaming XYZ:C:\mydatabase.fdb to XYZ:C:\mydatabase.fdb1

it always fails to rename it, irrespective if the database file is in use on the server computer, or not.

I haven’t found a solution to this second (file renaming) issue.

 

I have already implemented the GFIX -shut -force 0  command prior to GBAK -rep, however that does not seem to work correctly when the database is open on another workstation connected using the ServerMode = Super  connection mode.

The GFIX -shut -force 0  command appears to only work when the ServerMode = Classic is used.

 

Note that users must be allowed to run the database Restore from either the server computer or from the workstation/client computer.

The Restore works fine when run from either end, as long as the database is not opened on the other computer.

 

If you have any other suggestions how to overcome this problem, or how to detect if the database is in use, or how to check (in code) for any existing external database connections, I would greatly appreciate your advice.

 

Z gory dziekuje za pomoc i serdecznie pozdrawiam

 

Mariusz

New Zealand

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/QE7zcY-s-Cg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4Ct7wl4XlBzljBxM%40smtp.poczta.onet.pl.

 

[Mark Rotteveel]

On 12-12-2020 01:03, MarioFB wrote:
> I have already implemented the GFIX -shut -force 0  command prior to
> GBAK -rep, however that does not seem to work correctly when the
> database is open on another workstation connected using the ServerMode =
> Super  connection mode.
> The GFIX -shut -force 0  command appears to only work when the
> ServerMode = Classic is used.
> If you could please point to some relevant links on this database "shut
> down" topic, it will be highly appreciated.

The documentation:
https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/gfix/firebird-gfix.html#gfix-dbstartstop

Consider using a timeout higher than zero.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 12-12-2020 01:35, Karol Bieniaszewski wrote:
> And it really should not be \\10.100.200.15\mydatabase.fdb

> <file://10.100.200.15/mydatabase.fdb>.

I'm curious, why should it not be \\10.100.200.15\mydatabase.fdb ? That
is a valid connection string for the WNET (aka NetBEUI) protocol of
Firebird.

Mark
--
Mark Rotteveel

[MarioFB]

I have now resolved this problem by implementing the solution suggested by Ertan (Thank you).

I am using the following code (in Delphi RAD Studio) to identify the number of connected database users:

    with SQL_Select do

    begin

        if Transaction.Active then Transaction.Commit;

        Transaction.StartTransaction;

       Clear;

       Add('SELECT COUNT(MON$ATTACHMENT_ID)');

       Add('FROM MON$ATTACHMENTS');

       Add('where MON$ATTACHMENT_ID <> CURRENT_CONNECTION'); 

       ExecSQL; 

    end;  

    iUsers := Fields[0].AsInteger;  

The important tip is to Commit the transaction first, to get the above code working.

Karol's suggestion to kill the connection using the MON$ATTACHMENTS table is not very elegant, and was not considered.

Also, renaming the remote file by PowerShell was tried and discarded as not practical - too difficult for ordinary users to know and provide the required Credential details.

I would like to thank everyone who contributed to this discussion. 

[Vlad Khorsun]

On Tuesday, 15 December 2020 at 12:07:10 UTC+2 MarioFB wrote:

I have now resolved this problem by implementing the solution suggested by Ertan (Thank you).

I am using the following code (in Delphi RAD Studio) to identify the number of connected database users:

    with SQL_Select do

    begin

        if Transaction.Active then Transaction.Commit;

        Transaction.StartTransaction;

       Clear;

       Add('SELECT COUNT(MON$ATTACHMENT_ID)');

       Add('FROM MON$ATTACHMENTS');

       Add('where MON$ATTACHMENT_ID <> CURRENT_CONNECTION'); 

  Add " AND MON$SYSTEM_FLAG = 0" here.

 

Regards,

Vlad

[MarioFB]

Vlad

Could you please explain why it is necessary to add   " AND MON$SYSTEM_FLAG = 0 " to the above query and what this flag actually means/does.

I was not able to find any explanation in the Firebird user guides and the online information.

Thank you. 

[Mark Rotteveel]

On 10-01-2021 10:23, MarioFB wrote:
> Vlad
> Could you please explain why it is necessary to add " AND
> MON$SYSTEM_FLAG = 0 " to the above query and what this flag actually
> means/does.
> I was not able to find any explanation in the Firebird user guides and
> the online information.

It will exclude internal system connections like the garbage collector,
which are always present.

--
Mark Rotteveel

[MarioFB]

Thank you Mark. 

Your clarification is much appreciated.

I have noticed that after adding this extra line of code to the query, it now lists zero users (instead of two), when there are no external connections to the database.

This is exactly what I was expecting to see.
Thanks.

Mariusz

[Thomas Steinmaurer]

Hi,

> Thank you Mark.
> Your clarification is much appreciated.
> I have noticed that after adding this extra line of code to the query,
> it now lists zero users (instead of two), when there are no external
> connections to the database.
> This is exactly what I was expecting to see.

Please keep in mind, that replacing/overwriting (-rep) an existing,
working database by a faulty backup (for whatever reason) will result in
nothing.

You'd better keep the existing one untouched, restore the backup into a
different database name ...



--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.