Separation of information. Separation of data into current and archive parts. Server database distribution
1. Preamble.
There was a need to organize accounting for two organizations in one IB. The situation is not unique, but it so happened that our very non-typical 250 GB UPP worked rather slowly, so instead of RLS we decided to try data separation. What it is is described, for example, or. In short, if RLS conditions SQL queries, then the data separator is an extra column in tables at the DBMS level, due to which the partitioning mechanism should work faster than RLS.
So, in the database where records were kept for OOO No. 1, it is necessary to transfer information from a separate database of OOO No. 2 and organize joint work. Just like in the picture:
Mere mortals work only with their LLC, and the chief accountant sometimes looks at data on two legal entities. In the access mode to both LLCs, you can only read data, so the chief accountant should be able to interactively switch between the "read all" / "write only for one organization" modes and select the LLC (i.e. set the value of the common attribute) to conduct, for example, cost calculation.
2. Implementation
Platform 8.2.19.90, no compatibility mode. DBMS - MSSQL Server 2008 R2 Standard.
We created a common attribute Organization Separator of the "number" type, agreed with the proposal to create session parameters, filled in the composition of the attribute (included several directories, all documents, accumulation, accounting and calculation registers). Separation of data - "Independently and jointly". The value of the session parameter is set from the default user settings in the SetSessionParameters procedure in the session module:
Organization = UserManagement.GetDefaultValue(glCurrentUser,"MainOrganization");
SessionParameters.OrgDelimiterValue = Organization.DelimiterValue;
In the interface of the chief accountant, they made a form with the ability to switch between organizations and enable / disable the separation mode:
With splitting disabled, when SessionParameters.OrganizationSeparatorUsage = False, the platform refuses to write documents, throwing out errors like "SDBL Error: expected expression (pos=12)", so you can't let the user write documents in this option. For reliability, we created subscriptions to the "Before recording" event for the objects that are part of the common attribute:
If SessionParameters.OrgDelimiterUse = False Then
#If Client Then
Warning("Cannot write because data sharing is disabled!");
#EndIf
Rejection = true;
EndIf;
Our action plan was as follows: we prepare the receiver configuration of IB No. 1, put down the values of the common attribute = 1, load data from IB No. 2, after loading for all objects with an empty (equal to 0) separator value, set Organization Separator = 2.
The configuration was prepared, the question arose, how to set the value of the common attribute for documents and their movements in closed periods, and quickly and without the risk that the numbers in the balance will fly? Through the 1C object model, it is impossible to write a separator separately from the object, so I had to break the license agreement to get out and write a query for MS SQL. Since there are many objects in the common attribute, and there are even more tables in the cheekbone for these objects, we wrote a processing that generates a query for SQL (for each metadata object that is part of the separator, we wrote "update" + DB_name + ".dbo._" + TableName + " set _" + Field GeneralAttribute + " = 1";)
The value was set, some of the data was transferred from IB No. 2, and testing began.
The result was disappointing. First, problems with the accounting register. When the separation is enabled, the analytics is not visible:
This is due to the fact that the accounting register at the DBMS level is stored as several tables, and not all tables had the value of the common attribute (processing was used to view the structure).
Well, we put down the value of the separator through MS SQL, we see the analytics. Now the reports don't work. It turns out that there are problems with queries to the virtual tables of the accounting register "Turnovers" and "TurnoversDtKt":
(Fld27033 is just a common attribute in the accounting register table)
The separator is set in all tables, it can be seen at the DBMS level, what could be an error, it is not clear. We deploy a typical empty SCP, make the configuration changes described above, enter a couple of documents (in this option, the platform itself puts down the separator value in all tables of the accounting register), but errors are reproduced. It's bad, but we exclude accounting registers from the general requisites, we continue testing.
Further, it turns out that the displacement mechanism for the calculation registers has stopped working. We did not separate plans for types of calculation, we are trying to look for a problem in the tables of the calculation register and in recalculations. We check, put down the value of the main attribute, do T&I - to no avail.
Along the way, we diagnose the problem when writing information to independent registers from the list form. In this case, the data is recorded, they can be seen after the restart. The problem is reproduced on the test base:
The information registers could not be "repaired" by manipulating SQL (the separator value is set in all tables), so they were simply excluded from the general attribute. After several days of experimentation, attempts to restore the working capacity of the displacement are also unsuccessful.
At this point, we decide to turn off data sharing and still use RLS. When setting the split to "do not use", we come across errors "Microsoft OLE DB Provider forSQL Server: CREATE UNIQUE INDEX terminated because a duplicate keywas found for index...". That is, it is not so easy to return to the state before the separation. Problem with indexes of recalculation tables, settings for storing totals, and others. The fact is that the tables store identical rows that differ only in the value of the common attribute. When deleting a common attribute, non-unique entries appear. You will have to delete unnecessary records directly in MS SQL, something like this (for the recalculation table):
usebase;
ALTER TABLE_CRgRecalc1399
ADD id INT IDENTITY(1,1);
GO
DELETE FROM_CRgRecalc1399
WHERE id< (SELECT MAX(id)
FROM _CRgRecalc1399 AS T1
WHERE _CRgRecalc1399._RecorderTRef = T1._RecorderTRef and
_CRgRecalc1399.[_RecorderRRef] = T1.[_RecorderRRef] and
_CRgRecalc1399.[_CalcKindRRef] = T1.[_CalcKindRRef] and
_CRgRecalc1399.[_Fld1400RRef] = T1.[_Fld1400RRef] and
_CRgRecalc1399.[_Fld1401RRef] = T1.[_Fld1401RRef] and
_CRgRecalc1399.[_Fld1402RRef] = T1.[_Fld1402RRef]
);
GO
ALTER TABLE_CRgRecalc1399
DROP COLUMN id;
And only after cleaning several dozen tables, it is possible to turn off data partitioning. After turning off the separation, there are no problems.
3. Conclusions.
There was a hope that 8.3 problems were solved. We were not too lazy, we checked on 8.3.4.482 (with disabled compatibility mode). We looked at an almost typical SCP-shke, with changes in the configuration only for the general props. On this test base, splitting was enabled before information was entered, i.e. the platform had to correctly write the separator value to all tables, they did not write anything directly to MS SQL on their own.
Result:
The problem with queries to the virtual tables "Turnovers" and "TurnoversDtKt" is reproduced.
The preemption problem is reproducible.
The problem with writing to independent information registers is reproduced.
The problem with turning off the separation - it will not work to get rid of it with one click of the button!
Thus, we failed to replace RLS with a new mechanism. This mechanism was conceived, apparently, for cloud services, and in the case of using shared data "independently", perhaps the separation will work, but we need a common NSI. It remains to wait for 1C to correct the errors, and even better, implement a typical mechanism for dividing by organizations in standard configurations.
Parallel processing of database operations (such as scans, joins, and sorts) is only one side of parallel SQL processing; Some processing operations often require data partitioning (splitting tables and indexes to store them on different disks) to linearly speed up some processing operations. As mentioned earlier, data partitioning is the distribution of information across multiple disks in order to avoid bottlenecks due to the limited bandwidth of the I/O subsystems of individual disks. Parallel requests may not be handled well when they request unshared data. For example, in fig. Figure 7 shows that an ordered scan of all rows of a large database table can be slow due to disk limitations.
On fig. 7 shows a situation called pipelined parallelism ( pipelined parallelism). The request is processed in parallel, but this parallelism is limited by the "pipe" ( " pipe" ) - the bandwidth of the disk on which the entire table resides. To avoid pipeline parallelism in a system with parallel SQL processing, data partitioning is used. On fig. Figure 8 shows that the same parallel query can be executed much faster after the large table information is partitioned across multiple disks.
Methods for separating data
There are many different ways to split data. Previously, we discussed how to use Oracle8's table and index partitioning capabilities to break these objects into smaller, more manageable chunks. When configuring Oracle for parallel SQL processing, we strongly recommend that you use range partitioning for tables and indexes to maximize the performance of your system.
The Oracle8 query optimizer is aware of the partitioning of information in those tables and indexes that have been partitioned using the built-in data partitioning facility. For example, if a parallel query only needs data from one partition of a large table, the Oracle Query Optimizer automatically cancels the scan of all other partitions of that table.
Unfortunately, some queries cannot benefit from Oracle8's proposed range partitioning of data. Another common splitting method is carousel ( round- Robin) division. In this case, the server randomly distributes the rows of the table among the available partitions of the table. Carousel partitioning can speed up the execution of any parallel SQL queries, since the data is not partitioned specifically to process any query. To distribute the physical storage areas of the Oracle database information among several disks, various services of an external operating system are usually used. For example, most operating systems that work with multiprocessor computers have special utilities for disk striping ( disk striping), allowing random distribution of blocks of operating system files among several disks. When using Oracle database carousel partitioning, it is recommended to use such utilities.
A relatively new functional feature of 1C, related to cloud technologies. After the appearance of the functionality, the 1C company finalized the BSP. The same improvements were included in the standard configurations based on the BSP, for example, UNF and UT11.
It was necessary to organize the receipt of general reports from several branches with the same configurations. I decided to try the developments from the BSP in the field of data separation. The idea was to load the data of each branch into its own data area and generate reports for all areas at once. I must say right away that it has not yet been possible to solve the problem, but the very attempt to solve it revealed problems and dubious functionality in the BSP.
Processing is attached to the article, which brings the hidden functionality of operations with data areas to a separate form.
There are two accounting delimiters in the BSP: Main Data Data Area and Auxiliary Data Data Area. It remains a mystery why these 2 delimiters refer to the same session parameters: DataAreaValue, DataAreaUsage.
Turning on the mechanism
If your self-written configuration is based on the BSP, then, most likely, before turning it on, you need to implement the mysterious library "1C: Library of Service Technology". It is strange that even Google does not know about such a 1C product. And in typical configurations, there is no procedure CheckAbility to Use ConfigurationInServiceModel in the WorkInServiceModel module. Most likely, you can find the missing parts from this library in typical configurations made on the basis of the BSP. In particular, one of the subsystems is called StandardSubsystems > RunInServiceModel > UploadUploadData.
The data splitting mechanism is enabled by setting the UseSplitByDataArea constant. Can be set via the menu item All functions.
Creating Data Region Users
This item is optional if you are using form entry to the data area. Users are created in configurator mode. One user with administrative rights must have all unset data separators in the Data Separation tab. For other users, in the Data Separation tab, the separator Data area main data must be set. This separator must be explicitly specified on the command line when starting 1C.
Launch 1C with a command line parameter
This item is not required if you are using the login to the data area via the login form.
1C can be launched immediately in data sharing mode. The /Z command line option is provided. For example, the parameter "/Z-,+1" indicates that 1C is started with the value of Data area main data equal to 1, the separator Data area auxiliary data is not set.
The method is very unreliable. On startup, an error occurs in the WorkInServiceModel procedure. WhenCheckingEnableSecureDataSharingMode. I did not find anything better than commenting out this procedure. The procedure checks whether the user has the right to change the current data area, whether his rights are limited and affects security.
Further, at startup, several errors occur among them: "Separated users cannot be assigned the role of System Administrator", "Separated users cannot be assigned the role of Starting a thick client".
The user was not found in the Users directory - the problem could not be overcome. In the traditional scenario, the User registers at the first login. I suspect that when separating data, Users are created through another 1C Fresh application.
Fill in the data area information register
For each area, you need to fill in an entry in the Data Area information register, assigning numbers to the areas and the status "Used". Processings can check for entries in this register before starting execution.
Entering the data area
Administration - Service - Entering the data area (CommonForm.EnteringData area)
Allows the user to change the current data area. Logging into the area is possible on behalf of a user started without specifying delimiters. When a data area is changed, it checks its status in the data area information register.
Unloading data from an area
The current area is unloaded via CommonForm. Uploading Data. Before using it, you must log in to the desired data area. The form is not displayed in the user interface in the Administration section.
The data is serialized by the configuration into XML format and packed in ZIP. That is, archiving does not take place using the 1C configurator, as traditional unloading.
Upload data to area
GeneralCommand.LoadDataToRegion
To appear in the Administration-Service, you need to set visibility through the Configurator as part of the Setup and Administration subsystem.
The data is loaded into the selected area. Before that, they must be uploaded in XML format.
conclusions
Failed to start the system by setting delimiters on the command line. The system refused to work due to an unregistered user in the Users directory. It was not possible to get to the directory for the reason that the system does not allow users with administrative rights. I think that this scenario provides for a mandatory connection with 1CFresh.
Experiences with entering different areas, unloading and loading areas were successful. An attempt to get all organizations for all data regions failed. Error: "Cannot use table without specifying all delimiters with independent use of shared data." The report works on one area, if you enter into any area.
The problem remained unclear, how to upload data from a database without separators to a specific data area of another database.
It would be interesting to learn about the cunning idea of the BSP authors regarding the general session parameters for two separators, if the call is with parameters: "/Z-,+1", "/Z+1,+1" and "/Z+1,-" .
Once we discussed the mechanisms for restricting user access in 1C and in particular.
It allows you to allow the user to work not with all documents, but only with those in which a specific organization or warehouse is indicated. The selections are made dynamically, so they impose a certain load on the database.
The property of the common attribute-separator - Separation of 1C users - allows you to set the availability of the list of users depending on the use of separators.
If the separator is enabled for the user, then it will be visible in the list of users in 1C Enterprise mode - otherwise it is not visible.
This way you can organize different lists of users for different parts of the database.
The property of the common attribute-separator - Separation of 1C authentication - allows you to create users with the same usernames for different parts of the database.
Conditional division 1C
Conditional separation 1C allows you to enable and disable the separator based on the database data. Thus, it is possible to create chains of delimiters that are dependent on each other, dynamically acting in one case or another.
To enable conditional separation 1C - you need to specify in the property of the general attribute-separator - Conditional division 1C -, which will be responsible for determining the fact that division 1C is enabled.
It is possible to use a constant with boolean type or a reference attribute with boolean type.
Important - you need to disable the use of this constant / this reference book (select Do not use) as part of the separators, only then it can be selected.
It is possible to split a database that is shared with multiple users over a network. Splitting the shared database helps improve performance and reduce the chance of database file corruption.
After splitting a database, it may be necessary to move the back-end database or use a different database with tables. You can use the Linked Table Manager to change the back-end database that is used.
Notes:
In this article
Overview
When a database is split, it is reorganized into two files: a server database that contains data tables, and a client database that contains all other database objects (eg, queries, forms, reports). Each user interacts with the data using a local copy of the external database.
To split a database, use the Split Database Wizard. After splitting the database, you must distribute its users to the client database.
Attention:
Benefits of a Partitioned Database
The benefits of a partitioned database are listed below.
Click the Start button and select command execute.
In the dialog box " execute" enter msinfo32 and press the button OK.
In section System Summary click the plus sign next to component components.
In section Components click the plus sign next to the caption storage and select the item discs. The dialog box displays information about the available disks in the panel on the right.
Enhanced performance Database performance is usually greatly improved because only data is transferred over the network. In a shared database that is not partitioned, the database objects themselves—tables, queries, forms, reports, macros, and modules—are transferred over the network, not just the data.
Increased availability Since the data is only being sent over the network, database transactions such as editing records are faster, leaving the data more editable.
How to use the msinfo32 option to check the file system?
Enhanced Reliability If the user encounters a problem and the database closes unexpectedly, then any database file corruption is usually limited to the copy of the client database that the user has open. Since the user is only accessing data from the back-end database using linked tables, the back-end database file is likely to be corrupted.
Flexible Development Environment Because each user works with a local copy of the client database, each user can independently develop queries, forms, reports, and other database objects without affecting other users. Similarly, you can develop and distribute a new version of the client database without disrupting access to the data stored in the server database.
Training
Before you can split a database, you must complete the following steps.
You should always create backups before splitting a database. If you split the database and decide you don't want to split it, you can restore the original from a backup.
Splitting a database can take a long time. You must notify users not to use the database when splitting it. If the user changes the data while splitting the database, the changes will not be reflected in the back-end database.
Advice: If the user changes the data while splitting the database, you can import the new data into the back-end database when you're done.
Although sharing a database is one way to share data, everyone who uses the database must have a version of Microsoft Office Access that is compatible with the back-end database format. For example, if the database file is in the .accdb format, users will not be able to access their data using Access 2003.
You can use the older Access file format for the back-end database if you are using features that are no longer supported. For example, when using Data Access Pages (DAPS); You can continue to use them if the backend database is in an older file format that supports DAPs. You can then use the new file format with a front-end database so that users can access the benefits of the new format. Note that you cannot change data on a data access page using Access 2010 or later.
Database partitioning
Perhaps you should use the name suggested by Access. It keeps the original filename and indicates that the database is a back-end database by inserting _be to the name immediately before the filename extension.
Do not change the file type unless some users will be using an earlier version of Access to access the data.
You can enter the path to a network resource in the field File name before the filename. For example, if the network location for the back-end database is \\server1\share1\, and the filename for the back-end database is midb_be. accdb, you can enter \\server1\share1\MyDB_be.accdb in field File name .
The selected location must be accessible to all users who will be using the database. Because drive mappings can vary, you must specify the UNC path in place instead of using the mapped drive letter.
On your computer, create a copy of the database you want to share. Start with the database file on your local hard drive, not on a network share. If the database file is currently shared on your local hard drive, you can leave it where it is.
Open the copy of the database that is on the local hard drive.
On the tab Working with databases in a group Data movement press the button Access database. The Database Partitioning Wizard starts.
Click the button split the database.
In the dialog box Creating a server database provide a name, file type, and location for the table database file.
Notes:
When the wizard completes, a confirmation message is displayed.
The database is now split. The client database is the file you started with (a copy of the original shared database), and the server database is in the network location you specified in step 5 of this procedure.
Restriction on changing the structure of the back-end database
To limit the changes that are made to the client database that you distribute, we recommend that you save it as a compiled binary file (an .accde file). A compiled binary is a database application file that is saved along with any compiled Visual Basic Access (VBA) code. The compiled Access binary is missing VBA source code. Users cannot change the structure of objects in the file. ACCDE.
Open the front end database (.accdb) file that you want to save as a compiled binary (.accde) file.
Server database distribution
After splitting the database, you distribute the client database to users so that they can start working with the database.
Attention: To help protect data when there are multiple end users in a database, we recommend that you do not share database copies that contain links to SharePoint lists. When linking to a table that is a SharePoint list, it is possible for any attacker to modify the target of the link and potentially change permissions on the SharePoint site because the connection information for the linked tables is not encrypted.
Do one of the following:
Send an email message to database users and attach a server database file to the message. Include any instructions that will enable users to quickly start using the client database immediately.
Save the back-end database file to a network location that is accessible to all database users, and then send users an email message that includes the network location and any other instructions they may need to access the database.
Distribute the front end database file using removable media, such as a CD or USB flash drive. If you installed the file yourself, you can test it to make sure it works. If users must install a file, include a document explaining what steps they need to take to install the file and who is having problems.
Changing the used back-end database
You can move the back-end database or use a different back-end database using the Linked Table Manager.
If you want to move the back-end database, first create a copy of it in the new location, and then follow the steps below.
On the tab External Data in a group Import _amp_ links press the button Linked Table Manager.
In the Linked Table Manager, select the tables that are in the current back-end database.
Advice: If you have not linked to any of the databases, click the button select all.
Install checkbox always check new location and press the button OK.
Find and select the new backend database.
Loading...