How to move a BAS database to a server in Europe with minimal downtime

For an administrator or manager moving a BAS database from a server in the office or in a Ukrainian data centre to a dedicated server in Europe. The result is a plan in which users stop work only for a short evening window and the way back stays open.

To be honest about the title: a working database cannot be moved with zero downtime — users are locked out while the final copy is made. Licences for BAS, M.E.Doc and the DBMS are not covered here.

What you will need#

• A new server with spare processor, memory and disk capacity — see A server for BAS for 5, 10, 20 and 50 users. In-stock servers are delivered within 12 or within 72 hours of payment (the server catalogue shows which) — allow for that in the plan.
• A VPN between the old site and the new server, for example WireGuard: for transferring copies and for users’ RDP sign-in.
• An agreed window for the work: an evening or a weekend, but not reporting deadlines or payroll days.

Step 1 Inventory#

Step 2 Preparing the new server#

1. Do the basic setup (the first hour on Windows Server) and close RDP to the internet: sign-in only through the VPN — see Secure RDP. Create a transfer folder (in the examples, D:\Transfer, shared as Transfer); SMB (port 445) must also be reachable only through the VPN.
2. Install the same BAS platform version and, for a client-server database, also the BAS server and a DBMS version that your platform supports. The MS SQL Server version must not be lower than on the old server: a backup from a newer version cannot be restored on an older one. Create the folders from the examples (D:\SQLData, D:\SQLLog) beforehand and give the SQL Server service permissions on them and on the transfer folder.
3. Create user accounts with the right to sign in over RDP; for a file-based database, also give them permissions on its folder.

Step 3 A trial move with timing#

A trial move shows how long the final one will take and what does not work in the new place; time every stage. Transfer time is easy to estimate: at an upload speed of 50 Mbit/s, 20 GB takes about an hour. Copy a file-based database only when nobody is working in it, or the copy may be damaged.

File-based database#

Copy the whole database folder through the VPN with robocopy (10.66.0.1 is an example VPN address of the new server; the log folder must exist):

robocopy "D:\Bases\Buh" "\\10.66.0.1\Transfer\Buh" /E /Z /R:5 /W:15 /NP /TEE /LOG:C:\Temp\buh-copy.log

/E copies subfolders; /Z turns on restartable mode: after a dropped connection, copying of the file resumes where it stopped; /R:5 /W:15 means five retries 15 seconds apart. An exit code below 8 means there were no errors. Then move the folder to where the database will run and add the database to the infobase list.

If the line is slow, first pack the folder into an archive (the database file compresses well), transfer it with the same robocopy command and test it before unpacking (7z t).

Client-server database on MS SQL Server#

On the old server, take a full backup (bas_buh is an example database name; the D:\Backup folder must exist). The Express edition does not support backup compression — remove COMPRESSION there. If the old server has scheduled differential backups, add COPY_ONLY so as not to break their chain.

BACKUP DATABASE [bas_buh]
TO DISK = N'D:\Backup\bas_buh_full.bak'
WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

Transfer the file to the new server (robocopy with /Z), look up the logical file names (the LogicalName column), put them into MOVE in place of bas_buh and bas_buh_log, and restore the database with the new paths:

RESTORE FILELISTONLY FROM DISK = N'D:\Transfer\bas_buh_full.bak';

RESTORE DATABASE [bas_buh]
FROM DISK = N'D:\Transfer\bas_buh_full.bak'
WITH MOVE N'bas_buh' TO N'D:\SQLData\bas_buh.mdf',
     MOVE N'bas_buh_log' TO N'D:\SQLLog\bas_buh_log.ldf',
     RECOVERY, STATS = 10;

Then register the database in the BAS server cluster, pointing it at the existing database; for a trial copy, turn on the scheduled jobs lock in the database properties straight away.

Client-server database on PostgreSQL#

pg_dump does not make differential copies, so both the trial and the final move are a full dump (pg_dump -Fc), a transfer and a restore (pg_restore); the commands are in Database backups.

Step 4 Checks by key users#

A trial copy is a fully working database: with exchanges and scheduled jobs enabled, it may send documents or emails a second time. Recent standard configurations ask whether this is a copy or a moved database: answer that it is a copy, and work with external resources will be blocked. If the question did not appear, switch the exchanges off by hand. Then each key user posts a document, runs a report and prints a form.

• Printing and equipment. A user’s printers are available in the RDP session if “Printers” is ticked on the “Local Resources” tab of the connection settings; print real forms on every type of printer. A barcode scanner that works as a keyboard usually needs no setup; test document scanners, scales and cash registers beforehand with the supplier.
• Electronic signature keys. A token plugged into the old server cannot be plugged into a server in a European data centre. Check whether a token connected to the user’s computer works in the RDP session: it depends on the token model and the software. If not, ask the key’s issuer about the options.
• M.E.Doc. Move it following the developer’s instructions: besides the database, you have to keep the program settings, user access and keys.

Repeat the trial move until everything works, and only then set the date of the move.

Step 5 The final move#

Warning. The MS SQL Server steps below use the REPLACE option: it overwrites an existing database of the same name, so run that command only on the new server. A differential backup is based on the most recent full backup: if a scheduled job takes another full backup on the old server in between, the differential will not restore on the new one. For these days, add COPY_ONLY to the scheduled full backups or use the scheduled backup itself as the base.

For MS SQL Server, do most of the work the day before: take a full backup with the same BACKUP command (without COPY_ONLY), transfer it to the new server, remove the trial database from the BAS server cluster and restore the backup over it with the same RESTORE command, replacing RECOVERY with REPLACE, NORECOVERY. The database stays in the Restoring state, so in the move window only a small differential backup is left to apply.

1. Warn the users and close sign-in at the agreed time: in standard configurations this is “Блокування роботи користувачів” (locking users out) in the administration section; for a client-server database, also in the database properties in the cluster console. Note the unlock code and make sure no active sessions remain.
2. Stop exchanges and scheduled jobs on the old server so that documents are not sent from two places.
3. Make the final copy. File-based database: the same robocopy command (or an archive) into an empty folder. PostgreSQL: a dump. MS SQL Server: a differential backup:

   BACKUP DATABASE [bas_buh]
   TO DISK = N'D:\Backup\bas_buh_diff.bak'
   WITH DIFFERENTIAL, INIT, CHECKSUM, COMPRESSION, STATS = 10;

4. Transfer the copy and restore the database (put a file-based one in place of the trial copy). For MS SQL Server:

   RESTORE DATABASE [bas_buh]
   FROM DISK = N'D:\Transfer\bas_buh_diff.bak'
   WITH RECOVERY, STATS = 10;

   With the full recovery model you can also apply transaction log backups: restore the differential backup and each log backup (RESTORE LOG) with NORECOVERY, and the last one with RECOVERY (Microsoft examples). Then register the database in the BAS server cluster again.
5. Check the database (see the next section), copy the files outside the database that have changed and enable exchanges on the new server. When the configuration asks, answer that the database has been moved.
6. Remove the sign-in lock in the new database (in a file-based database it travels with the folder: start it with the /UC launch parameter and the unlock code) and give users the new RDP shortcut.
7. Take the first backup on the new server straight away and make sure the scheduled backup has run.

How to check the result#

• File-based database: before it is first opened in the new place, compare the hash of the main file on both servers (Get-FileHash in PowerShell). MS SQL Server: run an integrity check — it must return no errors:

  DBCC CHECKDB ([bas_buh]) WITH NO_INFOMSGS;

• The built-in BAS check: in the Designer, in the “Адміністрування” (Administration) menu, run the infobase test and repair, in test-only mode first; for a file-based database there is also the chdbfl utility in the platform’s bin folder. Run the repair only when you have a copy.
• Control reports: the trial balance for the current period, stock and cash balances, the number of documents for the last day — the figures on both servers must match, and the last document entered before the lock must be in the new database.
• A user signs in with the new shortcut, prints and signs a test document; the exchanges with M.E.Doc and the bank work.

The old server and the rollback plan#

Do not switch off or wipe the old server for a few more days. Its database is read-only: sign-in is locked, exchanges are stopped, and the administrator signs in with the unlock code only to compare reports.

Write the rollback plan down before the work starts:

• Condition. By what hour and on what signs you go back: the control reports do not match, printing or signing does not work.
• Action. Remove the lock on the old server, enable exchanges there, give users the old shortcut back and close the new database to sign-in.
• Limit. Rollback is painless until documents are entered on the new server: after that they have to be re-entered in the old database. So decide before the working day begins.

Common mistakes#

• The full MS SQL Server backup was restored with RECOVERY: the differential can no longer be applied, and the full backup has to be restored again.
• The BAS server cannot connect to the restored MS SQL Server database: its login has to be created on the new instance and given rights to the database again.

What next#

• Backups from day one: Database backups. On the main lines a server comes with backup space on separate storage; you set up the copying yourself, and support will send the connection details.
• Moving more than the accounting database? Use the migration checklist.
• A server for an accounting database is in the catalogue: we recommend the Standard and Business lines; on choosing a country, see Locations.