Work 1C enterprise under sql server. Setting up a Windows firewall for the database to work

This article contains information about the 1C installation procedure in the client-server version.

Installation of the 1C platform is described in our other article - “1C Administration”, in the “1C Installation” section. Installing on a server is almost exactly the same as installing on a local computer, with only one difference. In the server version, when selecting components to install, you must select “1C:Enterprise Server” and “1C:Enterprise Server Administration”.

Install 1C on client computers from which connections to the server will be made.

Installation on client computers is no different from the method described earlier in the article “1C Administration”.

Create an infobase in SQL.

Creating an information base in SQL is also very similar to creating a database in the file version. The difference is that at the stage of selecting the information base location type, you must select “On the 1C:Enterprise server”.

In the “Server cluster” item, specify the name (or better yet, the IP address) of the server on which you installed SQL.

In the “Infobase name” section, specify any name you want to give to the database.

DBMS type – SQL.

The database user and his password are the same superuser mentioned above during the installation of MS SQL.

Leave the date offset as default.

It is necessary to check the “Create a database if it does not exist” option and click “Next”.

Now the database has been successfully created on the SQL server and added to the list of available databases. Below in the picture you can see the result of the work done.

It is worth noting that the created database is still empty. This is a framework, a place allocated in SQL for your information base. In order to load your database into this framework, you need to use the Upload/Load information base tools. The Upload/Download procedure is also described in our other article “1C Administration”.

In order to bring the system to an ideal state in the future, it will be necessary to configure a “maintenance plan” for the created database. A maintenance plan is a set of procedures that SQL will perform regularly on a given schedule. For example, it will regularly make backups and delete temporary files. Working with SQL is beyond the scope of this article and will be described in one of the following.

In general, setting up MS SQL Server for working with 1C enterprise does not differ much from its usual setup, but there are still some nuances that have been identified experimentally.

Let's consider the most important points in the installation and subsequent configuration of the server and databases in order to optimize the operation of 1C.

Installing MS SQL Server

We will not consider all the installation steps and will only touch on those points that require special attention.

Selecting and Configuring Components

To work with MS SQL Server with 1C Enterprise, just select the following set of components:

Dtabase Engine Services
Client Facilities Communications
Controls - Basic
Management tools - full set (we will need the full set to create a maintenance plan)

Important! It is better to specify the directory of common components on a separate disk (separate from the operating system). This will increase speed and fault tolerance.

Server configuration

To start the SQL Server Agent and SQL Database Engine services, specify account. You can create a separate account with administrator rights, or specify the Administrator account. However, it is worth remembering that if you ever decide to change the password for the account you specified here, the service will stop starting. Therefore, use an account for which you do not plan to change the password.

Configuring the Databse Engine component

We specify mixed mode and set a password for sa - the SQL Server system account.

Add computer or domain accounts that can administer SQL.

Setting up a firewall for mssql and 1C Server

We create rules that allow incoming connections to port 1433 for MS SQL and 1541-1560 for 1C Server

We create a rule for the program. The path to the program will look something like this
C:\Program Files\Microsoft SQL Server\MSSQL13. \MSSQL\Binn\sqlservr.exe

Setting up Ms SQL server properties for working with 1C

Launch Microsoft SQL Server Management Studio and connect to the server.

Open the server properties window and go to Memory. We set the maximum allowable value of memory allocation for the needs of the SQL server. If this is not done, it will eat up all the free memory, because the default value is 2147483647 MB. The permissible memory value can be calculated using the formula (I used the experience of Alexey Novoselov from Infostat.ru):
[Total amount of server RAM] – – For example, if we have only 36 GB of RAM on the server, Windows 2008 is installed and 8 rphost processes are running, then the calculation goes like this: 36 – 4 – 1.5*8 = 20 GB, we set a limit for SQL.

Go to the Processor item. Maximum number It is also better to set worker threads manually and set the value to 2048, since with a value of 0 the number of threads may not exceed 255. Enable the Maintain SQL priority option.

Of course, these tips for setting up server properties are not a panacea and they will not be equally good in all conditions, but for most cases I think it’s quite suitable.

Setting up a 1C Enterprise working database

Open the properties of the custom database.

Now the most important thing is to decide on the database recovery model. They are configured in nukt parameters. Let's look at two main recovery models.

1. Simple. It should be used in the case when you plan to make backups once a day and the ability to restore with accuracy up to a certain point does not matter to you. This could be 1C Accounting or ZUP where there is not a large number of daily transactions. Make one backup every night and sleep well. No difficulties.

2. Complete. This model is best used for backing up databases with a large number of intraday transactions, for example, sales in 1C Retail. With this model, you will have all transactions saved in logs and will be able to restore the database to any point in time. But in this case you will have to tinker with the transaction log settings.

When we have decided on the recovery model, we can go to [Files]

Setting the [Journal] file type can be skipped if you are using a simple recovery model.
If you use the full version, you need to adjust the settings. Set the auto-expansion to 50MB. It is worth paying attention to the auto-expansion limitation and it is better to change it because The default value is greater than 2TB. For a large number of transactions, for example retail sales in 1C Retail, the transaction log will grow very quickly and you will soon run out of free space on your drive. Therefore, it is better to set the limit to 10GB. But this is just a recommendation, because everything is individual and depends on the number of transactions.

When setting a limit, it is worth remembering that when you reach the extreme value, you will receive an error: “the transaction log for the database is full” and 1C will not start. In order for the transaction log to be cleared in a timely manner, it is necessary to configure its backup in the database maintenance plan. Read about how to create a database maintenance plan.

But clearing the transaction log does not reduce the size of the file itself, but only frees up free space in it for new entries by deleting inactive completed transactions.

If the log is full, you will have to clear it manually for the database to work. Read how to do this in

In most cases, to install 1C:Enterprise 8.x in the client-server version, it is enough to run the 1C:Enterprise 8.x installation program. In this case, the 1C:Enterprise server receives standard parameter values necessary for its normal functioning.

Let's look at installing the 1C:Enterprise server in more detail. During the installation of the 1C:Enterprise 8.x server, the 1C:Enterprise 8.x installation program performs the following actions:

* Copies the 1C:Enterprise server boot modules to the directory specified by the 1C:Enterprise installation program as the final folder.
* If "Create user USR1CV81" is selected during installation, creates user USR1CV81. The 1C:Enterprise 8.1 server runs on behalf of this user if it is launched as a service. It has access only to those resources that the 1C:Enterprise server needs. It is important that the 1C:Enterprise server requires two directories to operate: a general directory with server data (usually "C:\Program Files\1cv81\server") and a directory of temporary files (usually "C:\Documents and Settings\usr1cv81\Local Settings \Temp" or "C:\WINNT\Temp"). User USR1CV81 receives rights to the shared directory with server data. The temporary files directory is usually accessible to all users.
* If during the installation process "Install 1C:Enterprise 8.1 server as a Windows service" is enabled, then it registers the 1C:Enterprise server agent service in Windows and starts it. At the first launch, a cluster of 1C:Enterprise servers is created with default settings. It has one worker server and one worker process. The working server address matches the name of the computer on which the installation was performed.

USR1CV81 or USR1CV82 user and his rights

1C:Enterprise Server is a server application whose operation should not depend on which user is logged into the server computer in interactive mode, if anyone is logged in at all. Therefore, when installing a 1C:Enterprise server, it is advisable to create a special user USR1CV81, endowed with the minimum rights required for the 1C:Enterprise server, and not intended for interactive login. The 1C:Enterprise server is presented to the Windows system by user USR1CV81.

Let's take a closer look at the rights set for the user USR1CV81. 1C:Enterprise server uses the following directories:

* The directory of loading modules is located in the directory specified by the 1C:Enterprise installation program as the final folder. It contains the loading modules of the 1C:Enterprise server. User USR1CV81 requires rights to read data and run programs from this directory and its subdirectories. It receives these rights implicitly by being included in the Users group.
* The server data directory is usually named "C:\Program Files\1cv81\server". User USR1CV81 requires full rights to this directory. When creating the user USR1CV81, the 1C:Enterprise installation program gives him rights to this directory.
* The temporary files directory is usually named "C:\Documents and Settings\usr1cv81\Local Settings\Temp" or "C:\WINNT\Temp", which is determined by the value of the user's environment TEMP variable or the system environment TEMP variable. You can view the value of this variable in the System Properties dialog (Start -> Settings -> Control Panel -> System -> Advanced -> Environment Variables). The 1C:Enterprise installation program gives user USR1CV81 full rights to this directory. Usually when Windows installations The temporary files directory is accessible to all users by including the CREATOR OWNER group in its access list. However, this access is not full. In particular, searching for files in this directory is not available to all users. Setting user USR1CV81 full rights to the temporary files directory allows the 1C:Enterprise server to perform all the operations it needs. You can view the access list in the directory properties dialog on the Security tab. The presence of the CREATOR OWNER group allows access to the directory by any user who creates any files in this directory or owns any files in this directory. In this case, in the access list of the created file, instead of the CREATOR OWNER group, the user who created the file will be written. Among the users who are allowed access to this directory, there must be user USR1CV81, who has full rights to this directory.
It is important to keep in mind that the temporary files directory for a given user (including user USR1CV81) is determined by a combination of that user's environment variables and system environment variables. To find out this directory, the 1C:Enterprise installation program requests the user context USR1CV81. To do this in Windows 2000, the user on whose behalf the 1C:Enterprise installation program is launched may need the following privileges: Act as part of the operating system and Bypass traverse checking. You can check user privileges using the Local Sequrity Settings utility in the Local Policies -> User Rights Assignment branch. In the process of installing a new software The installer usually gains these privileges automatically.

Registering a 1C:Enterprise server as a Windows service

1C:Enterprise Server is a simple Windows console application and can be launched interactively. However, for constant use this is inconvenient, since it requires the launch of the 1C:Enterprise server from the login of an inactive user to the server computer. To eliminate this dependency, the 1C:Enterprise server can be launched as a Windows service. To do this, it must be registered in the Windows service manager.

To view a list of Windows services and their parameters, use the Component Services utility (Start -> Settings -> Control Panel -> Administrative Tools -> Services). The 1C:Enterprise server is represented in the list of services by the service "1C:Enterprise Server Agent 8.1". The service parameters determine the launch of the 1C:Enterprise Server Agent process (ragent), the user under whose name it is launched, and the method of restarting in emergency situations.

In the properties dialog of the "1C:Enterprise 8.1 Server Agent" service, on the General tab, the line for launching the ragent process, which is the 1C:Enterprise Server Agent, is shown. Typically this line looks like:

It states that:

* the Server Agent process is the boot module "C:\Program Files\1cv81\bin\ragent.exe";
* the ragent process runs as a Windows service and must be managed by a service manager (-srvc);
* used as 1C:Enterprise Server Agent (-agent);
* when starting the service for the first time, a cluster must be created with default parameters and main IP port number 1541 (-regport 1541). Using this port, client applications must connect to infobases registered in the cluster;
* The server agent IP port must be numbered 1540 (-port 1540). Using this port, the Cluster Console must connect to the central server to perform administrative functions;
* when starting cluster processes on this server they will be dynamically assigned IP ports from the range 1560-1591 (-range 1560:1591).
* general cluster data will be located in the "C:\Program Files\1cv81\server" directory (-d "C:\Program Files\1cv81\server").

The "1C:Enterprise 8.1 Server Agent" service can be added or removed not only when installing or uninstalling 1C:Enterprise using the 1C:Enterprise 8.1 installation program, but also manually. To do this, you can execute from command line the ragent utility, specifying the appropriate parameters.

To create a service, you need to specify the -instsrvc parameter and the following parameters: -usr - the name of the user under whose name the service should be launched, -pwd - the password of this user. In this case, the remaining parameters will become the launch line parameters of the 1C:Enterprise Server Agent as a service. For example, for standard registration of the 1C:Enterprise Server Agent service in debug mode, the set of parameters should be as follows:

"C:\Program Files\1cv81\bin\ragent.exe" -instsrvc -usr .\USR1CV81 -pwd Password -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files\1cv81\server" - debug

To remove a service, you need to specify the -rmsrvc parameter. For example:
"C:\Program Files\1cv81\bin\ragent.exe" -rmsrvc

Sometimes it is useful to change the Server Agent startup line or other parameters of the Agent service, for example, enable debugging mode, or create several services of different versions. The service properties dialog does not allow you to edit the service application launch line and some other parameters, for example, the service identifier. To edit, you will need the regedit utility, designed to view and edit the Windows system registry.

Attention!
Editing the Windows registry requires extreme caution, since erroneous changes to it can lead to operating system into an inoperable state.

Run the regedit utility (open Start -> Run and type regedit) and select the branch:

Among its parameters is the ImagePath parameter, the value of which is the startup string of the 1C:Enterprise Server Agent. Here you can add new launch string parameters or change the values of existing ones. Full list possible parameters are given in the book "1C:Enterprise 8.1 Client-Server" documentation.

If you need to register several independent 1C:Enterprise Server Agent services, you need to specify them with different boot modules, different ports and different cluster data directories. You also need to register them with different service identifiers. This can be done like this:

* Create the first service:
"C:\Program Files\1cv81\bin\ragent.exe" -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files\1cv81\server"

* Using the regedit utility, change the identifier of the registered service. To do this: select a branch
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent First
* Create a second service:
"C:\Program Files\1cv81_10\bin\ragent.exe" -srvc -agent -regport 1641 -port 1640 -range 1660:1691 -d "C:\Program Files\1cv81_10\server"

* Perhaps his ID should also be changed. To do this: select a branch
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent
and change its name, for example to:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent Second

What can't the 1C:Enterprise installation program do?

As already mentioned, the 1C:Enterprise installation program copies the 1C:Enterprise boot modules and performs the necessary registration in COM and in the Windows service manager. The above is information necessary to understand the internal mechanisms of this registration. If not only the server is installed on the server computer, but also the client part of 1C:Enterprise, then it is ready to work immediately after installation (and connecting the security keys).

So that the 1C:Enterprise server is accessible from other computers in local network, you need to check the network settings on the server and client computer, as well as for the network as a whole. TCP/IP is used to transfer data between client applications and the 1C:Enterprise server, as well as between server cluster processes. The operation of 1C:Enterprise in the client-server version depends on its correct configuration.

Processes of a 1C:Enterprise server cluster connect to each other at the addresses defined as the values of the “Computer” property in the properties dialog of working servers. The cluster requires that the value of the Computer property be either an IP address in dot notation or a symbolic address from which the IP address can be determined using the gethostbyname function defined in the TCP API. The IP address is determined either based on the local symbolic address table (C:\WINNT\system32\drivers\etc\hosts) or using address tables in available DNS servers. If the symbolic address of the working server does not determine its IP address or determines it incorrectly (for example, the IP address does not match the actual IP address of this computer), then the cluster will not work. It is important that the names of computers and their addresses defined in Windows on each of the working servers in the cluster do not contradict their names in DNS.

On each working server, cluster processes use the following ports: IP port of the working server (usually 1540); IP ports from the workflow IP port ranges (usually 1560-1591). Additionally, the cluster's central server uses the cluster port (usually 1541). If the system uses firewalls, then data transmission on these ports must be allowed. Instead of allowing ports from the list above, you can allow data transfer to cluster processes (ragent, rmngr, rphost).

The connection between the 1C:Enterprise client application and the server is performed in 2 stages. It first establishes a connection to the cluster manager. This uses the central server address (symbolic or numeric) and the cluster port (usually 1541). Next, the client application establishes a connection with one of the worker processes. The value of the "Computer" property of the corresponding working server and the working process port, which is selected from the range of IP ports of the working server, are used as its address. Data transmission to these ports must be allowed in all firewalls along the route from the client application computer to the computers of the 1C:Enterprise server cluster. The IP address of server processes is determined using the gethostbyname function on the client computer. It is important that the names of the central and working servers and their addresses defined in Windows on each of the servers in the cluster do not contradict their names in the DNS accessible to the client computer.

And one last thing. Obviously, in order to successfully access the 1C:Enterprise server from other computers, it must be on the network and the necessary settings must be made for this. Connecting to a network and setting up methods relate to the administration of networks based on Microsoft Windows and are described in the corresponding instructions.

Features of setting up a SQL server

1C:Enterprise in the “client-server” version uses a SQL server to store data. In this case, only 1C:Enterprise Server accesses the SQL server. 1C:Enterprise clients do not have direct access to the SQL server. Installing and configuring a SQL server is described in detail in the Microsoft SQL Server documentation. For successful work 1C:Enterprise servers with a SQL server, you need to pay special attention to the following settings.

* Required SQL server components. To access the SQL server from the 1C:Enterprise Server side, Microsoft Data Access 2.6 or later components must be installed on the 1C:Enterprise Server computer.
* User authentication by SQL server. Access rights to SQL server databases are determined by the user on whose behalf the databases are accessed. From the computer on which the SQL server is installed, launch the SQL Server Enterprise Manager utility, find the Local node (Console Root -> Microsoft SQL Servers -> SQL Server Group -> (Local)) and open its properties. On the Sequrity tab you can see that the SQL server supports two methods of user authentication: SQL Server and Windows and Windows only. Windows authentication will allow the 1C:Enterprise Server to access the SQL server only on behalf of the user USR1CV81, which does not allow differentiating access rights to different infobases served by one 1C:Enterprise server. It is recommended to select the SQL Server and Windows mode. In this case, access to a specific infobase will be performed on behalf of the user who was specified as the SQL server user when creating this infobase. It is important that this user must have not only full rights to the infobase database, but also rights to create databases in the SQL server and to read Master database tables.
* Network protocols for accessing the SQL server. If 1C:Enterprise Server and the SQL server are located on different computers, then you need to configure the network access protocols to the SQL server. This can be done using the SQL Server Client Network Utility. On the General tab, you can select a list of network protocols used to access the SQL server. The fastest and most versatile is to use the TCP/IP protocol. When using other protocols, be aware that some, such as Named Pipes, perform additional authentication using Windows when exchanging data with a SQL server. In this case, to successfully work with the SQL server, the user USR1CV81 must be registered on the computer with the SQL server, with the appropriate rights. The access protocol for this SQL server can be changed on the Alias tab.

In addition to the article

There is no doubt that the combination of MS SQL Server + 1C: Enterprise 8 server is the most popular and frequently used combination in its niche. For its high-quality support, an understanding of both products is desirable. At the same time, in practice, a support specialist usually either specializes in administering MS SQL Server and is not familiar with the features of the 1C: Enterprise 8 server, or, conversely, specializes in administering the 1C: Enterprise 8 server and is not familiar with the features MS SQL Server.

This article was written to help both those and other specialists, designed to save your time and draw your attention to the most important details when using these software products together.

To make the information easier to understand, case studies, notes and tips are provided (in italics).

Three-link circuit

As the reader may already know, the database in the case under consideration has a three-tier architecture:

Link 1: MS SQL Server DBMS. “Stores” and maintains the database, ultimately performing all types of database operations. Thus, the performance of the database, the speed and parallelism of reading and writing data are largely determined by the performance of MS SQL Server.

Link 2: Server "1C: Enterprise 8". Serves as an intermediary in the interaction between clients (users) and MS SQL Server. All client requests are sent to the server, which “translates” them into the MS SQL Server query language, receives the results of executing these queries, and sends the results to the client.

There is only a small part of the operations that are performed at the 1C: Enterprise 8 server level, without accessing MS SQL - this is, in particular, tracking the so-called “managed locks”, reading and writing “session parameters”. In such cases, access to the DBMS is not required, since these operations are performed not with database data, but with auxiliary server information.

Link 3: Client part of "1C: Enterprise 8". Accesses the 1C: Enterprise 8 server, receives results from it (that is, for example, data samples), and is responsible for the user interface.

"I wanted the best."

After reinstalling the 1C: Enterprise 8 server, users complain of a sharp drop in performance. The 1C: Enterprise software implementation specialist who carried out the reinstallation is only surprised - they say he wanted the best, the system should have started working faster... Analysis of the situation showed that the 1C: Enterprise 8 server was allocated too many resources: it processes (see point 3) rphost occupied 15.5 GB of the server’s 16 GB of RAM, as a result, there was practically no available RAM for the compliant MS SQL Server.

The result is a constant "swap", an unnecessary load on the disk subsystem, and extremely slow execution of database operations - due to the fact that MS SQL Server does not have time to process requests coming from the "overclocked" 1C: Enterprise 8 server.

Product Compatibility

Current information about versions of MS SQL Server recommended for use in conjunction with 1C: Enterprise 8 should be found at this link http://v8.1c.ru/requirements/.

At the time of writing this article, 1C developers recommend the following options:

1. SQL Server 2008 R2.

2. SQL Server 2008, requires installation of Service Pack 1 (SP1).

3. SQL Server 2005, requires installation of Service Pack 3 (SP3).

It is technically possible, but not recommended, to use MS SQL Server 2000; it requires installation of Service Pack 2 (SP2), and installation of Service Pack 4 (SP4) is desirable.

Please note that this version is currently deprecated and does not have a 64-bit version for x86-64 architecture.

Note:

It is necessary to pay attention to the operating system settings: for example, for efficient work M SQL Server 2008 under Server 2008R2 OS requires disabling balanced power mode and switching to maximum performance mode.

Installing the client-server version of "1C: Enterprise 8"

"1C installed"

For one of the customers, the installation of 1C: Enterprise 8 was carried out by a system administrator who had no experience working with 1C: Enterprise 8. And although, according to him, he “installed 1C”, there was no client part on the user computers, and a server part on the server. Analysis of the situation clarified the picture - the 1C: Enterprise 8 kit included 2 disks - installation of the platform and installation of database templates. The administrator did not delve into the installation procedure - and installed database templates, rather than executable files, platform components.

Of course, this is an atypical example of an exceptionally inattentive attitude to work.

When installing "1C: Enterprise 8" you should take into account that the following are installed separately:

The 1C: Enterprise 8 platform is an executable application, an integrated environment for the development and operation of databases. When you launch it, you select one of two operating modes - “Enterprise” (user database shell) or “Configurator” (integrated development environment). A more complete description can be read at the link

"1C: Enterprise" configuration templates are a file of the platform's internal format, with the help of which the platform can create a pure or demo database of the structure contained in the template. You can also use the update pattern to update the structure of an existing database that is already filled with data.

When installing the platform, you should pay attention to the selection of components:

The 1C: Enterprise component may not be installed on the server(s).

In this case, the server will provide client computers with access to 1C: Enterprise databases, but working with the database in user mode directly from the server will not be possible.

Note:

The 64-bit version of the platform does not contain a client part. Therefore, when installing on a server, 64-bit server components are installed separately, and 32-bit client application components are installed separately.

The "1C Server: Enterprise" component is needed to connect to MS SQL Server - it is an application server, a connecting link between the platform on client workstations and MS SQL Server.

It is possible to install the component in the mode of a simple application or system service, and, of course, the second option is recommended.

When installed "as a service", this component will be launched and executed on behalf of the selected user:

After loading, the component spawns several processes, such as: “server agent”, “server cluster manager”, “server worker processes”.

Queries to the database are executed by worker processes, and the server cluster manager distributes the load between them.

Server work processes can be managed (added, deleted, set limits on RAM usage, declared primary or backup) if the 1C: Enterprise Server Administration component is installed.

Note:

For the 32-bit version of the server, it is recommended to install working processes in such a number as not to leave RAM unused - each of them has a noticeable limitation on the use of RAM, from 2 to 4 GB depending on the system configuration.

For the 64-bit version of the server, two worker processes are theoretically sufficient - one worker and one backup. However, in practice, to ensure reliability and stability of connections for a significant (several hundred) number of users, a larger number is required, it depends on many factors - on the number of users, the content of the database and the volume of queries performed, so the authors believe that the number of processes in this case should be selected experimentally.

"Ouroboros"

After unsuccessful optimization of the 1C: Enterprise 8 server settings, users reported extremely slow system operation, and the system administrator noted a constant 100% processor load on the server.

An analysis of the situation showed the source of the problem - during configuration, too small a limit was set on the use of RAM by working processes.

But the fact is that this limitation works as follows:

When the server cluster manager sees that a worker process has exceeded the RAM limit, the process is terminated, it is disabled, a new worker process is created, and connections and user requests are redistributed between worker processes.

The limit set was so small (300MB) that the worker process could not fully service even one intensive user - as a result, the server cluster manager was constantly restarting worker processes and reconnecting users. As soon as a new process was created and users connected to it, the RAM limit was almost instantly reached and caused the next restart. This took 100% of the processor load.

The "1C Server: Enterprise" component is not needed on client workstations, and will not be able to start there, since it requires the physical presence of a security key.

If the number of connected users is small (less than 50), the application server is usually installed on the same computer where MS SQL Server is running.

For systems with a large number of users and/or a large volume of information flows, separate installation is recommended, as well as the use of a server cluster.

The "1C: Enterprise Server Administration" component can also be useful on clients - for example, with its help you can see a list of infobases connected to a given "1C: Enterprise" server.

It is strongly recommended to install it on the server itself.

Access

Note:

To verify that access is provided, it is not enough to use the 1C: Enterprise server administration utility, and even more so, the presence of the server in the “Network Neighborhood” is not enough!

It is necessary for each client to log in to the database installed on the server - only this will give 100% confidence that access is provided.

1. Depending on the security policies, MS SQL Server uses Windows account authentication or MS SQL Server account authentication.

In the latter case, when creating a 1C: Enterprise database, the system will request the login and password of the MS SQL Server account (for example, sa), in the first case the login and password should be left empty:

and the system user on whose behalf the 1C: Enterprise server is running must be given rights in MS SQL Server, namely:

full rights to the database in which the information base is located

access to the master database (public role)

recommended - rights to create a database, otherwise each new database will need to be first created using MS SQL Sever, and only then connected to the 1C: Enterprise server

recommended - right to delete your database

For example, you can assign the user in question the fixed role processadmin or sysadmin.

Advice.

If all users simultaneously lose access to the working database, you need to double-check the user rights and roles in MS SQL Server, including those set for a specific database, that is, User mapping:

2. Server 1C: Enterprise accesses MS SQL Server through the Microsoft Data Access mechanism, so its components must be installed, and the user of the server 1C: Enterprise (see the previous paragraph) must have rights to run them.

3. Communication between clients and server is supported by TCP protocol, so it is necessary that this protocol is supported by both sides. There may be problems matching the server name and its IP address, for example, if a peer-to-peer network is used. In this case, you should record the correspondence in the file [C:\WINDOWS\] system32\drivers\etc\hosts .

Advice.

If the network is peer-to-peer, to ensure a permanent connection to the server, create a network drive that accesses any of the folders of this server.

4. If the Named Pipes protocol is used, and if MS SQL Server and the 1C: Enterprise server are installed on different computers, the user on whose behalf the 1C: Enterprise server is running must be registered in the list of users of the computer on which MS SQL Server is running.

5. In some cases, additional configuration of the Windows firewall may be required, that is, adding exceptions.

6. Some antivirus programs may block “unwanted” network traffic, so you may need to add to their exclusion lists.

7. The release of the 1C: Enterprise 8 platform must be absolutely identical on the client and on the server.

"Twins"

"One of the customers used two database servers, each of which housed one working database. Users worked - each simultaneously with both databases. The support service updated the 1C: Enterprise 8 platform on servers and clients.... And then complaints started pouring in for the impossibility of connecting - first to one or another database. Analysis of the situation showed that the update on clients and servers was done by several people, and the installing specialists did not double-check that they were installing the same release. Therefore, on one server there was one release of the platform, on the second - another, on half of the clients - the first of these releases, on the other half - the other... It turned out that each user has access to only one of the databases.

To quickly solve the problem, each user had to install both releases of the platform and create separate shortcuts to log into each database.

Initial settings of MS SQL Server and database

“And that’s how it works”

MS SQL Server is distinguished by its simple initial installation, so not all administrators bother with its additional configuration - after performing the default installation, the database is working, users are logged in - the job is done. This approach almost always leads to problems arising after about a month or two - and, of course, suddenly and at the most inconvenient moment.

For example, if the database is intended for record keeping - before submitting tax reporting Often there is a need to urgently recalculate certain data, and recalculate en masse, say, “all receipts of fixed assets from the beginning of the year.” Moreover, during the working day, without stopping the work of other database users.

And, of course, it is at this moment that it will be discovered that during such a recalculation the database “freezes”, or “crashes”, or does not allow other users to work.

This kind of “Murphy’s Law” applies to each of the following points.

Before starting to use MS SQL Server as a DBMS for 1C: Enterprise, it is recommended:

1. Set the value of the max degree of parallelism parameter to 1.

That is:

After connecting to the server, enter the server properties through the context menu, Properties item

then select the Advanced page and edit the max degree of parallelism parameter

Otherwise, some queries generated by the 1C: Enterprise server may cause the error "Intra-query parallelism caused your server command (process ID #XX) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1 )". After this error, the client part often crashes.

The error will not appear consistently, since the query plan is formed differently depending on the accumulated statistics - it will manifest itself on large and complex queries, that is, at the most unfortunate moment.

2. Create a Maintenance Plan that shrinks the tempdb temporary table database on a nightly basis. The database of temporary tables is not always cleared automatically by the 1C: Enterprise server, and sometimes, as a result of an unsuccessfully written query, a temporary table of, for example, 50 GB in size may be created and not cleared. As a result, disk space may run out, as a result of which both the client and server parts may crash, and there is also a slight risk of data integrity violation.

That is, you need:

go to MS SQL Management Studio

after connecting to the server, expand the "Maintance plans" section

create a new (or add to an existing) Service Plan,

add the item "Execute T-SQL Statement task" to it (since you cannot select the tempdb database in the "Shrink database" task) with the code

1.USE
2.
3.GO
4.
5.DBCC SHRINKFILE (N"tempdev" , 0, TRUNCATEONLY)
6.
7.GO
8.
9.DBCC SHRINKFILE (N"templog" , 0, TRUNCATEONLY)
10.
11.GO

Note that the temporary table database file name may not be "tempdev". You can use a script to check this name

1.USE tempdb
2.
3.GO
4.
5.EXEC sp_helpfile
6.
7.GO

“Pot, don’t cook”

The most common way in practice to overfill tempdb and thereby crash the server is to forget to specify a condition when joining tables.

Namely, let’s say we have two tables in the database, each with 20 thousand records in size. Let's say there is a one-to-one correspondence between their records, and we write a query that creates a temporary table that contains 20 thousand records with fields from both source tables. But if we forget to specify the join condition, every record of the first table will be joined to every record of the second! That is, the resulting table will consist of 20’000 * 20’000 = 400 million records. And so on.

3. In order to reduce the load on the disk subsystem, it is recommended, if possible, to distribute the working database and tempdb, logs, and system swap file across different physical disks.

It is better to set the desired path for storing the working database files when creating it by editing the Path column:

To change the physical location of temporary table database files, use the ALTER DATABASE command, that is, in MS SQL Management Studio you need to run the following script ("New query" command)

1.USE master
2.
3.GO
4.
5.ALTER DATABASE tempdb
6.
7.MODIFY FILE (NAME = tempdev, FILENAME = "New_Disk:\New_Directory\tempdb.mdf")
8.
9.GO
10.
11.ALTER DATABASE tempdb

12.
13.MODIFY FILE (NAME = templog, FILENAME = "New_Disk:\New_Directory\templog.ldf")
14.
15.GO

4. The “growth” of the working database and its log should not be hampered – there should be no size limit, the “Autogrowth” property should be set as a percentage, the recommended value is 10%. Otherwise, adding data to the database, restoring from an archive, and other operations may take an unreasonably long time.

To set this property, you need to go to the database properties through the context menu, select the Files section, and open editing file properties:

5. It is recommended to enable support for the TCP/IP network protocol in MS SQL Server and disable all others, otherwise collaboration MS SQL Server and 1C server: Enterprises will be less stable.

6. In the same place - clear the Alias section, because its installation leads to errors in the interaction between MS SQL Server and the 1C: Enterprise server.

Before starting to use the database, it is recommended:

1. When creating a database from "1C: Enterprise", set the "date offset" to 2000, otherwise an attempt to record a date earlier than 01/01/1753 (which is possible due to human factor) will cause failures in the database.

Attention! The date offset cannot be changed for an existing database!

2. Set the Recovery model to Simple, or create a Maintenance Plan, which will create a daily backup of the database and trim the transaction log (log file). Otherwise, during some operations the transaction log (log file) will grow very quickly: for example, when restructuring a database, the growth in the size of the log file can be several times larger than the size of the database itself.

3. Create a Maintenance Plan that performs the following routine tasks at least once a week:

Creating a backup copy of the database.

Update database statistics and clear the procedural cache (note that the autoupdate statistics property does not imply clearing the procedural cache).

Clearing the procedural cache is not included in the standard operations of Maintenance Plans; this step must be defined as executing a script (Execute T-SQL Statement) with the following content:

DBCC FREEPROCCACHE

Reindexing database tables.

Of course, it makes sense to set up automatic sending of emails about successful/unsuccessful completion of tasks.

Conclusion

The issues that most often cause difficulties for system administrators and implementers of 1C: Enterprise 8 are considered in connection with the joint use of MS SQL Server and the client-server version of 1C: Enterprise 8.

The author hopes that he has covered “both sides of the coin” in a fairly consistent and accessible way.

P.S. Make backups often!

The topic of installing MS SQL Server is usually ignored. Indeed, it is difficult not to install this DBMS, even doing it for the first time, and it is equally difficult not to launch 1C:Enterprise Server in conjunction with it. However, there are a number of non-obvious subtleties that can significantly poison the life of an administrator, which we will talk about today.

MS SQL Server ranks first in the number of implementations in conjunction with 1C:Enterprise, this is largely due to the low barrier to entry; a person without experience is quite capable of mastering this combination, purely using the Next - Next - Finish method. And, what’s most interesting, all this will work. Let's say more, in the overwhelming majority of cases, the default SQL server settings are more than enough to ensure productive operation of the 1C: Enterprise server and touching them is not only unnecessary, but even harmful.

First of all, you should remember about the system base tempdb, which is actively used by 1C for storing temporary tables and intermediate results. Moreover, it is used immediately by all 1C databases running on the server. And since by default it is located in the SQL server installation folder, i.e. on the system disk, then when the load increases, it is tempdb becomes a bottleneck for the entire server. Very often this leads to situations: you bought a fast HDD / SSD, there are enough disk resources, but 1C is slow, which can cause serious difficulties for novice administrators.

Second point. Comparison encoding tempdb must match the encoding of the comparison of information bases, otherwise this may in some cases lead to unexpected results, even serious errors in calculations.

At the same time, these difficulties are not at all difficult to avoid; you just need to spend a couple of extra minutes during installation or carefully review the settings of an already installed server.

Installing MS SQL Server to work with 1C:Enterprise

As we have already said, installing a SQL server is extremely simple, and we will not describe this process in detail, focusing only on the necessary settings. Let's start with the selection of components, since 1C does not use most of the SQL server mechanisms and if you are not going to use them for other purposes, then we leave only Database Engine,Client Facilities Communications And Controls(optional).

Management tools do not need to be installed on the server, but can be installed separately on workplace administrator and manage all available MS SQL servers from there.

You should also check the sorting parameters; if you have your regional settings configured correctly, then most likely you won’t have to change anything there, but it is advisable to check this parameter, it should be there Cyrillic_General_CI_AS.

IN Server configurations please indicate Mixed mode authentication and set the password to the SQL superuser - sa. Also indicate below the administrators of this instance of the SQL server; by default, there is already an account under which the installation was performed, but if other users must also administer this instance, then it makes sense to indicate them right away.

Next bookmark - Data directories- requires the closest attention. Be sure to specify the storage location for user databases and database tempdb space on a performance array or a separate disk. Despite the fact that the location of the database can be specified when creating it, setting the correct default settings saves you from unnecessary work, as well as from the situation when the database is created using 1C tools and ends up in the default directory, i.e. on the system disk. You can also immediately specify the directory for storing backups.

You can leave the remaining settings as default and complete the installation.

Setting up MS SQL Server to work with 1C:Enterprise

If you are dealing with an already installed SQL Server instance, make sure that the comparison encoding Cyrillic_General_CI_AS, otherwise the data should be downloaded using 1C tools, and the server should be reinstalled (or another instance should be installed if this one is used by other services).

To do this, open Management Studio, select the required SQL server instance and right-click on it and go to Properties.

Then go to the bookmark Memory and indicate the amount of RAM available to the SQL server, otherwise the SQL server will try to recycle all available memory. In a situation where the roles of the SQL server are combined with other roles, and in small and medium-sized implementations it is usually located on the same machine with the 1C server, you should subtract from the total amount of memory needed by the system and the 1C server, giving SQL what remains.

It is difficult to give unambiguous recommendations here; it all depends on the volume of data being processed; in practice, it makes sense to allocate half of the free memory to the SQL server, subsequently adjusting given value based on its actual load.

The next setting will be related to security. To connect 1C to the server, the account is most often used sa, which, to put it mildly, is unsafe, as it gives the person who enters under it full access to the SQL server. Considering that the administration of 1C databases is often carried out by third-party specialists, it makes sense to create a separate account for them.

To do this, open Security - Logins and create a new name (account), specify authentication SQL server and set a password.

Then go to the bookmark Server roles and allow dbcreator, processadmin And public.

Then use this account to connect to the SQL server from 1C.

Another setting applies to already created databases, open the properties of the desired database and go to the tab Files. Find the option Auto-grow/maximum size for the data file. By default, it is 1 MB, which is very non-optimal; when actively working with the database, the DBMS will only be concerned with increasing the file size; in addition, when several databases are actively working, this will lead to significant fragmentation of the data file. Therefore, based on the size of the database and work activity, set a higher value that will not lead to a constant increase in the database file.

Transferring the tempdb database

To conclude our article, let us again turn to the database tempdb, there are often situations when the file of this database needs to be moved to another location. For example, the server was installed with default settings and tempdb located on the system partition, or you purchased an SSD and want to transfer not only the databases there, but also tempdb(which is the correct solution). Also under heavy load tempdb It is recommended to put it on a separate disk.

To change the file location tempdb open Management Studio, select Create a request and in the window that opens, enter the following text, where E:\NEW_FOLDER- new location for the base:

Use master
alter database tempdb
modify file(
name = tempdev,
filename = N"E:\NEW_FOLDER\tempdb.mdf")
go

alter database tempdb
modify file(
name = templog,
filename = N"E:\NEW_FOLDER\templog.ldf")
go

Then click Execute, after executing the request, restart the SQL server, database and log files tempdb will be created in a new location, files in the old location must be deleted manually.

We will finish here today, finally reminding you not to forget about database maintenance and backups.

Let's consider the issues of installing and configuring MS SQL Server for 1C.

The first thing I would like to note is that the installation method for MS SQL Server largely depends on the expected load on 1C.

Depending on this, the following options can be identified:

Option 1. An enterprise with up to 500 transactions per day;
Option 2. An enterprise with more than 500 transactions per day, but mostly non-interactive (downloading payments from the Internet and client bank, downloading orders from an online store, automatic generation of warehouse and transport operations, etc.);
Option 3. An enterprise with more than 500 transactions per day, most of which are interactive, i.e. initiated and formalized by users.

For the first two options, MS SQL Server can be installed on the same server where 1C is installed, which is especially true, rather, for the second option, provided that the hardware requirements are met. But in the third case, MS SQL Server needs to be installed only on a separate server.

Let's look at the hardware characteristics for all three cases

If installed on a separate server:

CPU

Option 1. At least 1.8 GHz (preferably 2-core)
Option 2. At least 2x2GHz with at least 2 cores
Option 3. At least 4x3GHz with at least 4 cores

Option 1. At least 8 GB
Option 2. At least 32 GB
Option 3. At least 128 GB

Disk subsystem

Option 1. SAS at least 120 GB
Option 2. SAS of at least 500 GB (preferably SSD)
Option 3. SAS at least 1 TB (SSD recommended)

Network connection

Option 1. At least 1 Gb/sec
Option 2. At least 1 Gb/sec (preferably optics)
Option 3. At least 1 Gb/sec (optics recommended)

In case of joint installation with 1C Enterprise server:

CPU

Option 1. At least 2x2GHz with at least 2 cores
Option 2. At least 4x3 GHz with at least 4 cores

Option 1. At least 32 GB
Option 2. At least 128 GB
Option 3. It is strictly forbidden to do this

Disk subsystem

Option 1. SAS of at least 500 GB (preferably SSD)
Option 2. SSD at least 1 TB
Option 3. It is strictly forbidden to do this

Network connection

Option 1. Preferably 1 Gb/sec
Option 2. Preferably 1 Gb/sec
Option 3. It is strictly forbidden to do this

Please note that these are the minimum requirements for comfortable work. More precise parameters are selected based on the specific situation, guided by the basic needs of MS SQL Server for 1C in the disk subsystem and the amount of RAM.

Now let's move directly to the installation process of MS SQL Server for 1C in the MS SQL Server 2014 version on the MS Windows Server 2012 operating system

First of all, you need to rewrite the MS SQL Server installation distribution for 1C to local HDD server.

Run the installation of MS SQL Server for 1C as an administrator.

In the window that opens, select “Install” in the right panel.

The installation of MS SQL Server for 1C will begin. During installation, you will be asked to enter your product key and review the licensing terms. After this, you need to select the server role by selecting “Install MS SQL Server Components”.

The next step is to select the components to install. Usually they are all selected, but if not, then click the “Select All” button.

Don't change anything, i.e. Click “Next” until the “Database Engine Configuration” window appears. In this window, on the first tab, specify the user “sa” and set its password (you can also assign Windows user, but the user “sa” is considered more secure).

On the other two tabs, you can configure new directories for storing user databases (but it is better not to change).

Now you have installed MS SQL Server for 1C

Let’s “open” our MS SQL Server for 1C. To do this, go to applications and find SQL Server 2014 Management Studio there.

After connecting, select “Security” in the tree on the left, and “Login names” in it. By right-clicking, select “Create Login” from the submenu. In the form that opens, indicate the user name and password (remember this password and this user, because this is what you will indicate when connecting databases from the 1C server).

Go to “Protected objects” and set the right to connect to SQL, as in the figure.

Click on “Ok”. Installation of MS SQL Server for 1C and its configuration is completed.

Let's move on to connecting the 1C database on the 1C server

Launch the 1C:Enterprise server administration console.

Fill out the dialog that opens as shown in the figure.<Имя базы на латинице>- indicate the name of your database.<Имя компа MS SQL Server>- indicate the name of the computer on which you previously installed MS SQL Server for 1C.