2 Data migration to MSSQL
Users of the program can now convert traditional accounting units to SQL themselves.
The user who will convert the unit or units must have sysadmin rights to the SQL server!!!
New accounting units cannot be created directly in SQL databases. First, it is necessary to create a new accounting unit in the standard way and enter this unit. After the first successful entry into the newly created unit, it can then be converted to the SQL server.
Before converting the unit to SQL, we will finish work in this unit and ensure that no one is logged into the program (there is no user in any unit)!!!
Data Conversion to SQL
The function for bulk conversion of units to SQL can be found on the screen "Select Accounting Unit" → "Statistics, Bulk Functions."
On the "Information about accounting units" screen, the feature "Bulk transfer to Enterprise version" is available.
Bulk transfer (even of a single individual unit) is only possible for those accounting units that have the status "OK" in the "Upgrade" column. This status is when someone has been logged into the unit after the last update.
The program will now prompt you to enter a security password.
The password is SQL
The next step is to select accounting units with the status "OK" that are to be converted to SQL.
The final step is to enter the connection strings and log in with the sysadmin account on MSSQL.
Data:
Server Name: The exact name of the SQL server instance.
Port: Leave blank if the server is configured to the default port 1433.
Winaut: If Windows authentication is used, the program will use the credentials from the domain of the currently logged-in user (there is no need to enter the additional two details such as SQL user and SQL password).
SQL user: If the Winaut option is not selected, enter the login for connecting to SQL, which must be a "sysadmin" account (after the transfer, the sysadmin privileges can be removed and only public can be retained).
SQL password: The password for the user.
Approle: The application role that the program creates itself during the transfer process. This application role handles all operations with SQL data (reading, writing, deleting).
Approle password: The password for the role.
Sqldbname: The name of the database for this unit will be generated by the program itself. The prefix "FAxx_unit_name" will be used.
Datadir: The location of the databases from the perspective of the SQL server.
Logdir: The location of the log files of the databases from the perspective of the SQL server.
The driver must be the one that is installed on the SQL server (any version from 11-17). The current version of the ODBC driver can be found on the SQL server in Windows - "Administrative Tools - ODBC Data Sources (32-bit)".
If everything is set up, we can perform a "SETTINGS TEST". During the test, the program will attempt to log in, create a test empty database, and delete the database. If all goes well, an "OK report" will be displayed, and we can start the conversion by pressing the "OK" button.
In the first phase, the program will check the consistency of the database files in the accounting unit and then gradually begin the conversion in the selected units.
If the initial installation of MSSQL and the conversion of 1-3 units were carried out by our IT staff, everything in the "Server Settings" dialog will already be automatically filled in. The program will use the settings from the already existing unit that has been converted to SQL.
After the data transfer, it is also necessary to install the ODBC Driver on all PCs that run the program from the local network. The installation of ODBC Driver 11 is part of the visual libraries installer, which can be run on a specific PC with administrator rights by executing the file "\premier\setup_ocx.exe". ODBC Driver 11 for SQL Server will be installed on the PC. Therefore, it is necessary to use this version in the unit settings. For working in the program, the ODBC version is not important; it must be at least version 11. Conversely, during data transfer, which is usually performed on the SQL server, it is advisable to use the same version as the base installation of MSSQL. The default version after installing MSSQL 2017 is ODBC Driver 17 for SQL. However, it is also possible to install the visual libraries on the SQL server and then use ODBC Driver 11.