Installing SQL Server for 1C 8.2. Creating an information database on the SQL server

Consider the installation issues and settings MS SQL Server for 1C.

The first thing I would like to note is the method of installing MS SQL Server largely depends on the alleged load for 1C.

Depending on this, you can designate the following options:

Option 1. Enterprise, with the number of operations per day to 500;
Option 2. Enterprise, with the number of operations per day more than 500, but mostly not interactive (loading payments from the Internet and client bank, download orders from the online store, automatic formation of warehouse and transport operations, etc.);
Option 3. Enterprise, with the number of operations per day more than 500, most of which are interactive, i.e. Initiate and are issued by users.

For the first two MS SQL Server options, you can install on the same server where 1C is installed, which is especially relevant, rather, for the second option, subject to the compliance with the requirements for the "hardware". But in the third case, MS SQL Server must be installed only on a separate server.

Consider the Characteristics of "Iron" for all three cases

In case of installation on a separate server:

CPU

Option 1. Not lower than 1.8 GHz (preferably 2-nuclear)
Option 2. not lower than 2x2 GHz with a number of nuclei of at least 2
Option 3. not lower than 4x3 GHz with a number of nuclei at least 4

Option 1. Not less than 8 GB
Option 2. not less than 32 GB
Option 3. At least 128 GB

Disk subsystem

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

Network connection

Option 1. Not less than 1 GB / s
Option 2. Not less than 1 GB / s (preferably optics)
Option 3. Not less than 1 GB / s (Optics recommended)

In the case of a joint installation with a server 1c enterprise:

CPU

Option 1. Not below 2x2 GHz with the number of nuclei of at least 2
Option 2. not lower than 4x3 GHz with the number of nuclei at least 4

Option 1. Not less than 32 GB
Option 2. not less than 128 GB
Option 3. It is strictly forbidden to do so

Disk subsystem

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

Network connection

Option 1. Preferably 1 GB / s
Option 2. Preferably 1 GB / s
Option 3. It is strictly forbidden to do so

It should be noted that these are minimal requirements for comfortable work. More accurate 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 scope of RAM.

We now turn directly to the MS SQL Server installation process for 1C in the MS SQL Server 2014 version on the MS Server 2012 operating system

First of all, you need to rewrite the MS SQL Server installation distribution kit for 1C to the server's local hard drive.

Run the MS SQL Server setting for 1C under the administrator.

In the window that opens on the right panel, choose the "Installation".

Installing MS SQL Server for 1C. During the installation, you will be asked to enter the product key and familiarize yourself with the licensing conditions. After that, you must select the server role by selecting "Installing MS SQL Server Components".

The next step will be the choice of components installed. Usually they are all selected, but if not, click the "Select All" button.

Do not change anything, i.e. Click "Next" until the Database Engine component window appears. In this window on the first tab, specify the user "SA" and set it to the password (you can assign a Windows user, but the user "SA" is considered more secure).

On two other bookmarks, you can configure new directories for storing custom databes (but it is better not to change).

Now you installed MS SQL Server for 1C

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

After connected, the left in the tree is chosen "Security", and in it - "input names". By pressing the right mouse button, select "Create an input name" to the submenu. In the form that opens, specify the username and his password (remember this password and this user, because it is it that you will specify when connecting the database from the 1C server).

Go to "Protected Objects" and establish the right to connect to SQL, as in the figure.

Click on "OK". Installing MS SQL Server for 1C and its configuration is complete.

Go to the connection of the 1C database on the 1C server

Run the 1C: Enterprise Administration Console.

Opened dialogue fill, as shown in the figure.<Имя базы на латинице> - Specify the name of your database.<Имя компа MS SQL Server> - Specify the name of the computer to which you previously installed MS SQL Server for 1C.

Click on "OK". The base is connected.

You can connect the database directly from the 1C dialog.

To do this, launch the dialog for opening the database 1c and click the "Add" button.

In the window that appears, we choose "Creating a new information base».

In the next window, select the desired template in the tree or indicate "Creating a new base without configuration ...".

In the next window, we specify the name of the base and indicate that it will be on the server "1C: Enterprise".

On the next tab, fill the fields, similar to how it was described in the database connection section from the 1C server.

SQL optimization under 1C completed. You can run and work.

Update database statistics

(one or several times a day)

If manually, then perform procedures:

Exec SP_MSForeachTable N "Update Statistics? WITH FULLSCAN" DBCC UpdateUSAGE (DBName)

Through the graphical interface:

Cleaning Procedure Cache DBMS

(after updating statistics)

We carry out the procedure

DBCC FreeProccache.

Database Reindexing

(once a day at no working time)

If manually, then

Sp_msforeachtable n "dbcc dbreindex (" "?" ")"

Through the graphical interface:

Use fragmented analysis to reduce load

For large databases, it is necessary to reduce unnecessary operations on the defragmentation of those for which it is not required.

Dynamic Management Table feature sYS.DM_DB_INDEX_PHYSICAL_STATS Returns the percentage of fragmentation in the column aVG_FRAGMENTATION_IN_PERCENT. If the value in this column exceeds 25%, then to restore the initial performance parameters, it is recommended to defragmentation / reindexing of this index.

Even easier, absolutely a report:

Set up backup

Do not forget to configure automatic for your database. It is significantly cheaper than not to make a backup and without nerves :). At least once a week, check that it was not over the place and restore the backup to a copy of the base for checking and whether backup will save, "if that" ...

Reduced database size (log)

You can reduce the database or using the SQL Server Management Studio graphics interface.
The first case is performed by calling the context menu on the required database and select Tasks - SHRINK - Database.
The second case we describe several more. Below is the log script:

DBCC SHRINKDATABASE (N "BASE1C", truncate_only); / * truncation of the entire base * /

uSE DBCC SHRINKFILE (N "BASE1C_DATA", 101); / * truncation only data file up to size 101 MB * /

use DBCC SHRINKFILE (N "BASE1C_LOG", 0); / * truncation only transaction file to size 0 MB * /

Checkpoint; / * Writes all the data from the buffer cache in the current database on the disk * /

Backup log to disk \u003d n "f: \\ log \\ base1c.bak" with truncate_only / * Cleaning transaction log * /

where:
BASE1C - Database Name
F: \\ Log \\ Base1c.bak - path to log table

In most cases, for installation 1C: Enterprises 8.x In the "Client-Server" version, it is enough to launch the 1C installation program: enterprises 8.x. At the same time, the server 1C: the enterprises receive the standard values \u200b\u200bof the parameters necessary for its normal functioning.

Consider the installation of the server 1C: enterprises in more detail. In the process of installing a server 1C: Enterprises 8.x Mounting Program 1C: Enterprises 8.x performs the following actions:

* Copies the 1C server boot modules: Enterprises to the directory specified by the installation program 1C: Enterprises as a destination folder.
* If you select "Create a USR1CV81" user in the installation process, then creates a USR1CV81 user. On behalf of this user, the server is working 1C: enterprises 8.1, if it starts as a service. Only those resources that are required by the 1C server are available to him. It is important that the 1C server: Enterprises require two directory: a general directory with server data (usually "C: \\ Program Files \\ 1CV81 \\ Server") and a temporary file directory (usually "C: \\ Documents and Settings \\ usr1cv81 \\ local settings \\ Temp "or" C: \\ WinNT \\ TEMP "). User Usr1CV81 gets the rights to a general directory with server data. Catalog of temporary files is usually available to all users.
* If during the installation process is enabled "Set the 1C server: Enterprise 8.1 as a Windows service", then logs in Windows service agent service 1C: enterprises and starts it. When you first start, a 1C server cluster is created: enterprises with default settings. It has one working server and one workflow. The address of the operating server coincides with the name of the computer on which the installation is made.

User usr1cv81 or usr1cv82 and its right

Server 1C: Enterprises is the server application of which should not depend on which user entered the server computer in the interactive mode, if at all someone entered. Therefore, when installing the server 1C: enterprises It is advisable to create a special USR1CV81 user with rights minimally necessary for the 1C server: enterprises, and not intended for interactive input. Server 1C: enterprises seem windows system User usr1cv81.

Consider a Read more rights to be installed by the user usr1cv81. Server 1C: Enterprises use the following directories:

* Catalog of loading modules is in the catalog, specified program Settings 1C: Enterprises as a destination folder. It has a loading modules of Server 1C: Enterprises. User UserR1CV81 requires the rights to read data and launch programs from this directory and its subdirectories. It receives these rights implicitly, thanks to the inclusion in the User group.
* Server data directory Usually has the name "C: \\ Program Files \\ 1CV81 \\ Server". The user of the USR1CV81 requires complete rights to this directory. The 1C installation program: Enterprises when creating a USR1CV81 user gives its rights to this directory.
* The directory of temporary files usually has the name "C: \\ Documents and Settings \\ USR1CV81 \\ Local Settings \\ temp" or "C: \\ WinNT \\ TEMP", which is determined by the value of the TEMP environment of the user environment or the TEMP variable of the system environment. You can see the value of this variable in the System Properties dialog (Start -\u003e Settings -\u003e Control Panel -\u003e System -\u003e Advanced -\u003e Environment Variables). Installation program 1C: Enterprises Specifies the user with USR1CV81 full rights to this directory. Usually when windows installations The directory of temporary files is available to all users by including in its access list of the Creator Owner group. However, this is not complete access. In particular, all users are not available for searching for files in this directory. Installing a USR1CV81 user full rights to the temporary file directory allows 1C server: Enterprises to carry out all the operations you need. You can see the access list in the directory properties dialog on the Security tab. The presence of the Creator Owner group allows you to access the directory to any user who creates any files in this directory or owning any files in this directory. At the same time, a user who created the file will be recorded instead of the Creator Owner group of the Creator Owner group. Among users who are allowed to access this directory must be USR1CV81, endowed with full rights to this directory.
It is important to keep in mind that the directory of temporary files of a specific user (including the USR1CV81 user) is determined by the combination of environment variables of this user and systemic environment variables. To find out this directory, the installation program is 1C: the enterprises request the USR1CV81 user context. In this in Windows 2000, the user, on behalf of which the 1C installation program is launched: Enterprises may be required: ACT AS Part of the Operating System and BYPASS TRAVERSE Checking. You can check the user privileges in the Local Sequrity Settings utility in the Local Policies branch -\u003e User Rights Assignment. In the process of installing the new software, the installation program usually receives these privileges automatically.

Signup Server 1C: Enterprises as a Windows service

Server 1C: Enterprises are simple console Windows Application And it can be launched interactively. However, for permanent use, it is inconvenient, since it puts the launch of the server 1C: enterprises from the input of an identical user to a server computer. To eliminate this dependence, Server 1C: Enterprises can be launched as a Windows service. To do this, it must be registered in Windows Services Manager.

To view the list of Windows services and their parameters, the Component Services utility is designed (Start -\u003e Settings -\u003e Control Panel -\u003e Administrative Tools -\u003e Services). Server 1C: Enterprises are presented in the service list service "Server 1C: Enterprise 8.1". Service Settings Specify the start of the "Server 1C: Enterprise" process (RAgent), the user, on behalf of which it starts, as well as a way to restart emergency situations.

In the service properties dialog, "Server 1C: Enterprise 8.1" on the General tab, the RAGENT process startup string is shown, which is the 1C server agent: enterprises. Usually this line has the form:

It indicates that:

* The process of the server agent is the loading module "C: \\ Program Files \\ 1CV81 \\ Bin \\ ragent.exe";
* The Ragent process starts as a Windows service and must be managed by the service manager (-srvc);
* Used as a 1C server agent: enterprises (-Agent);
* When you first start the service, a cluster must be created with default parameters and the main IP port number 1541 (-Repport 1541). In this port, client applications must connect to the information bases registered in the cluster;
* The server's agent IP port must have number 1540 (-Port 1540). By this port, the cluster console must be connected to the central server to perform administrative functions;
* When you start the cluster processes on this server, they will dynamically assign IP ports from the range of 1560-1591 (-range 1560: 1591).
* General cluster data will be posted in the C: \\ Program Files \\ 1CV81 \\ Server directory (-D "C: \\ Program Files \\ 1CV81 \\ Server").

Service "Server Agent 1C: Enterprise 8.1" can be added or removed not only when installing or removing 1C: Enterprise installation program 1C: enterprises 8.1, but manually. To do this, you can execute the Ragent utility from the command line by specifying the corresponding parameters to it.

To create a service, you need to specify the -INTSRVC parameter and parameters: -USR - the username, on behalf of which the service must be running, -PWD is the password of this user. In this case, the remaining parameters will be the parameters of the string of the server of the server 1c: enterprises as a service. For example, for standard registration of the service agent service 1C: Enterprises in debug mode The set of parameters should be:

"C: \\ Program Files \\ 1CV81 \\ Bin \\ ragent.exe" -INTSRVC -USR. \\ USR1CV81 -PWD Password -Regport 1541 -Port 1540 -range 1560: 1591 -d "C: \\ Program Files \\ 1CV81 \\ SERVER" - Debug.

To delete the service, you need to specify the -RMSRVC parameter. For example:
"C: \\ Program Files \\ 1CV81 \\ Bin \\ ragent.exe" -RMSRVC

Sometimes it is possible to change the server agent launch string or other parameters of the agent service, for example, enable debug mode, or create multiple services different versions. The service property dialog does not allow you to edit the startup string of the service application and some other parameters, for example, service identifier. To edit, you will need a REGEDIT utility designed to view and edit. system registry Windows.

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

Run the Regedit utility (Open Start -\u003e Run and type Regedit) and select a branch:

Among its parameters there is an imagepath parameter, the value of which is a string of the server of the server 1C: Enterprises. Here you can add new startup string parameters or change the values \u200b\u200bof existing ones. Full list Possible parameters are given in the book "1C: Enterprise 8.1 Client-server" documentation.

If you need to register multiple independent 1C server agent services: Enterprises need to specify different boot modules, different ports and different cluster data catalogs. It is still required 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 registered service identifier. To do this: Choose 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 its identifier is also changed. To do this: Choose a branch
HKEY_LOCAL_MACHINE \\ SYSTEM \\ CURRENTCONTROLSET \\ SERVICES \\ 1C: ENTERPRISE 8.1 Server Agent
and change her name, for example on:
HKEY_LOCAL_MACHINE \\ SYSTEM \\ CURRENTCONTROLSET \\ SERVICES \\ 1C: ENTERPRISE 8.1 Server Agent Second

What can not do the 1C installation program: Enterprises?

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

To serve 1C: Enterprises have been available from other computers in local networkYou must check the network settings on the server and client computer, as well as for the network as a whole. To transfer data between client applications and 1C server: enterprises, as well as between server cluster processes, TCP / IP is used. From the correct setting it depends on the work of 1C: enterprises in the client-server version.

1C server cluster processes: Enterprises are connected to each other by addresses defined as the values \u200b\u200bof the "Computer" properties of the properties of the working servers. For a cluster, it is necessary that the "Computer" property value is either the IP address in point notation, or such a symbolic address by which the IP address can be defined using the GetHostByname function defined in the TCP protocol program interface. The definition of the IP address is performed either based on the local table of symbolic addresses (C: \\ WINNT \\ System32 \\ Drivers \\ ETC \\ HOSTS), or by addresses available in the available DNS servers. If, on the symbolic address of the working server, its IP address is not determined or determined incorrectly (for example, the IP address does not match the actual IP address this computer), then the cluster will not work. It is important that the names of the computers and their addresses defined in Windows on each of the working servers of the cluster did not contradict their names in the DNS.

On each working server, the cluster processes use the following ports: IP port of the operating server (usually 1540); IP ports from the IP ports of the workflow ports (usually 1560-1591). In addition, the cluster port is used on the central cluster server (usually 1541). If network screens are used in the system, the data transmission over these ports must be allowed. Instead of the port permission from the list of the list, you can allow the data transfer of the cluster processes (Ragent, RMNGR, RPHOST).

Connecting a client application 1C: Enterprises with a server runs in 2 stages. At first it establishes a connection to the cluster manager. This uses the address of the central server (symbolic or numeric) and cluster port (usually 1541). Next, the client application establishes a connection with one of the workflows. As its address, the value of the "Computer" properties of the corresponding working server and the port of the workflow that is selected from the IP range of the operating server ports is used. Data transfer to these ports must be enabled in all network screens on the route from the client application to the computer cluster computers 1C: enterprises. The IP address of the server processes is performed using the GetHostByname function on the client's computer. It is important that the names of the central and working servers and their addresses defined in Windows on each of the cluster servers did not contradict their names in the DNS accessible to the client's computer.

And last. Obviously, for successfully access to the 1C server: enterprises from other computers, it must be on the network and the necessary settings must be performed. Connecting to the network and the configuration methodology relate to the administration of networks based on Microsoft Windows. and are described in the relevant instructions.

Features SQL Server Settings

1C: Enterprise in the "Client-Server" version uses for storage of SQL server data. In this case, only the server 1C: enterprises are referred to the SQL server. Customers 1C: enterprises of direct access to the SQL server do not have. Installing and configuring the SQL server is described in detail in the documentation for Microsoft SQL. Server. For successful operation of the server 1C: Enterprises with a SQL server need to pay special attention to the following settings.

* Required SQL Server Components. To access the SQL server from the server side of 1C: Enterprises on a 1C server computer: Enterprises must be installed Microsoft Data Access 2.6 or later.
* SQL server authentication. SQL Server Database Access Rights are determined by the user, on behalf of which access to databases. From the computer on which the SQL server is installed, launch the SQL Server Enterprise Manager utility, we will find the Local node (Console root -\u003e Microsoft SQL Servers -\u003e SQL Server Group -\u003e (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 server: Enterprises to access the SQL server only on behalf of the USR1CV81 user, which does not allow to distinguish between access rights to various information bases served by one 1C server: enterprises. It is recommended to select SQL Server and Windows mode. In this case, the appeal to a specific information database will be executed on behalf of the user, which is specified as a SQL server user when creating this information base. It is important that this user must have not only full rights to the information base database, but also the right to create databases in the SQL server and read the Master database tables.
* Network protocols to access the SQL server. If server 1C: Enterprises and SQL server are posted on different computersIt is necessary to configure network access protocols to the SQL server. This can be done using the SQL Server Client Network Utility utility. On the General tab, you can select a list of network protocols used to access the SQL server. The fastest and most universal is to use the TCP / IP protocol. When using other protocols, it is necessary to keep in mind that some of them, such as Named Pipes, perform additional authentication. windows tools When exchanging data with a SQL server. In this case, a USR1CV81 user must be registered to successfully work with the SQL server on a computer with the SQL server. The access protocol to this SQL server can be changed on the Alias \u200b\u200btab.

In addition to the article

There is no doubt that the bunch of MS SQL Server + server "1C: Enterprise 8" - in its niche, the most popular and frequently used bunch. For its high-quality support, it is desirable to understand both products. At the same time, in practice, the support specialist is usually either specializing in the MS SQL Server administration and is not focused on the 1C: Enterprise 8 server features, or, on the contrary, specializes in administering the server "1C: Enterprise 8" and is not oriented in features MS SQL Server.

This article is written in help and other specialists, is designed to save your time and draw your attention to the most important details when sharing software products.

To facilitate the perception of information, there are cases of practices, notes and tips (allocated in it internally).

Three-star scheme

As may be already known to the reader, the database in the case under consideration has a three-star architecture:

Link 1: DBMS MS SQL Server. "Stores" and serves the database, ultimately performs all types of operations with the database. Thus, the performance of the database, the speed and parallelism of the read-write data is largely determined by the MS SQL Server performance.

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

There are only a small part of the operations that are performed at the server level "1C: Enterprise 8", without accessing MS SQL is, in particular, tracking the so-called "managed locks", read-write "session parameters". No appeals to DBMS in such cases are not required, since these operations are not made with database data, but with auxiliary server information.

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

"Wanted as better."

After reinstalling the server "1C: Enterprise 8" users complain about a sharp drop in performance. Specialist in the introduction of PP "1C: Enterprise", which made a reinstall - only surprised - they might, I wanted to start working faster ... Analysis of the situation showed that too many resources were allocated to the server: it was allocated: its Processes (see paragraph 3) RPHOST occupied 15.5 GB from the 16GB server memory, as a result, for the compliant MS SQL Server, there was practically no available RAM.

As a result, a permanent "swap", an unnecessary load on the disk subsystem, and extremely slow operations with the database - due to the fact that MS SQL Server does not have time to process requests from "overclocked" Server "1C: Enterprise 8".

Product compatibility

Actual data on MS SQL Server versions recommended for use in conjunction with "1C: Enterprise 8" should be found on the linkhttp://v8.1c.ru/requirements/.

At the time of the preparation of the article, the developers of the company "1C" recommend the following options:

1. SQL Server 2008 R2.

2. SQL Server 2008, requires installation package 1 (SP1).

3. SQL Server 2005, you need to install the update package 3 (SP3).

Technically, it is not recommended to use MS SQL Server 2000, it requires the installation of the update package 2 (SP2), and setting the update package 4 (SP4) is desirable.

It should be borne in mind that currently This version is discontinued, and also does not have a 64-bit version for the X86-64 architecture.

Note:

You must pay attention to the settings operating system: For example, for efficient operation M SQL Server 2008 under Server 2008R2, it is necessary to disable the balanced power supply mode and the translation into maximum performance mode.

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

"1C installed"

One of the customers install "1C: enterprises 8" produced a system administrator who has no experience in working with "1C: Enterprise 8". And although, according to him, he "set 1C" - there was no client part on the user computers, and on the server - server. The analysis of the situation clarified the picture - in the kit "1C: enterprises 8" there were 2 disks - setting the platform and setting database templates. The administrator did not delve into the installation order - and installed database templates, and not executable files, platform components.

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

When installing "1C: Enterprise 8", it should be borne in mind that separately installed:

full description

Configuration templates "1C: Enterprise" is a file of the internal format of the platform, with which the platform can create a clean or demonstration database of the structure that is laid in the template. Also, using the update template, you can update the structure of the existing database already filled with data.

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

Component "1C: Enterprise" may not be installed on the server (servers).

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

Note:

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

Component "Server 1C: Enterprises" is needed to connect to MS SQL Server - this is an application server that connects the link between the client workplaces and MS SQL Server.

Installation is possible in mode simple application or system service, and recommended, of course - the second option.

When installing "as a service", this component will start and executed on behalf of the selected user:

After loading the component generates several processes, like that: "Server Agent", "Server Cluster Manager", "Server Work Processes".

Database queries are executed by workflows, and the load between them distributes the server cluster manager.

The server's workflows can be managed (add, delete, set a limit on the use of RAM, declare the main or backup) if the "Administration of Server 1C: Enterprise" component is installed.

Note:

For a 32-bit version of the server, it is recommended to install workflows in such a quantity so as not to leave the operational memory unused - each of them has a noticeable restriction on the use of RAM, from 2 to 4GB, depending on the system configuration.

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

"Uboboros"

After unsuccessful optimization of the server settings "1C: Enterprise 8", users have been reigning about the extremely slow operation of the system, and the system administrator noted a permanent 100% processor load on the server.

Analysis of the situation showed the source of the problem - when configured, a too small restriction was set to the use of RAM with work processes.

And the fact is that this limitation Works as follows:

When the server cluster manager sees that the workflow exceeded the RAM limit - the operation of this process stops, it is turned off, a new workflow is created, and the connections and user requests are redistributed between work processes.

The set limit was so small (300MB) that the workflow could not fully serve even one intensively working user - as a result, the server cluster manager continuously restarts workflows and reconnected users. As soon as a new process was created and users were connected to it - the RAM limit was almost instantly achieved and caused the next restart. This accounted for 100% of the processor boot.

Component "Server 1C: Enterprises" not needed on client workstations, and will not be able to start there, as it requires the physical presence of a protection key.

In the event that the number of connected users is small (less than 50) - the application server is usually installed on the same computer where MS SQL Server works.

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

Component "Administering Server 1C: Enterprise" can be useful and on clients - for example, you can see a list of information databases connected to a given server "1C: Enterprise".

It is highly recommended on the server itself.

Access

Note:

To check what access is provided, insufficiently use the 1C Server Administration utility: Enterprises, and the more insufficient Server's presence in the "Network Surroundings"!

It is necessary on 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 security policies, for MS SQL Server applies to account windows records or authentication in account MS SQL Server.

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

and the user of the system, on behalf of which the 1C server is run: Enterprises, it is necessary to give rights to MS SQL Server, namely:

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

access to the Master database (Public role)

new base

recommended - the right to delete its database

For example, you can assign a predefined Processadmin or SYSADMIN role to the user under consideration.

Council.

If all users have access to the working database at the same time - you need to double-check the rights and roles of the user in MS SQL Server, including installed for a specific database, that is, User Mapping:

2. Server 1C: Enterprises refers to MS SQL Server through the Microsoft Data Access mechanism, so its components must be installed, and the server of the server 1C: enterprises (see the previous item) must be right to launch them.

3. Communication between clients and the server is supported by the TCP protocol, so it is necessary that this protocol is maintained by both parties. Problems are possible with comparing the server name and its IP addresses, for example, if a pener network is used. In this case, you should write compliance in the [C: \\ Windows \\] System32 \\ Drivers \\ ETC \\ HOSTS file.

Council.

In case the network is peer - to provide permanent connection to the server, create network Diskwhich addresses any of the folders of this server.

4. In the case of using the Named Pipes protocol, and if MS SQL Server and Server 1C: Enterprises are installed on different computers - the user, on behalf of which the server 1C: Enterprises, must be registered in the list of computer users on which MS SQL Server is running.

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

6. Some antiviruses can block "unwanted" network traffic, so that it may be necessary to add their exception lists.

7. Platform release "1C: Enterprise 8" must be absolutely the same on the client and on the server.

"Twins"

"One of the customers has been used two database servers, each of which had one working base. Users worked - each simultaneously with both bases. Support services performed the" 1C: Enterprise 8 "platform update on servers and clients ... and here complains To the inability to connect - then to one, then to another database. Analysis of the situation showed - updated on clients and servers did several people, and installing specialists did not recheck that they set the same release. Therefore, on one server was one platform release on one server The second is another, half of the customers - the first of these releases, on the other half - the other. It turned out that each user has access only to one of the databases.

To quickly solve the problem, I had to install each user both platform release and create separate labels to enter each database.

Initial settings MS SQL Server and database

"And so works"

MS SQL Server is distinguished by the simplicity of the initial installation, so not all administrators are puzzled additional setting - After executing the default installation, the base has earned, users in it entered - the work is performed. Such an approach almost always entails the emergence of problems in about a month or two - and, of course, suddenly in the most inconvenient moment.

For example, in case the base is intended for accounting - before passing tax reporting, it often the need arises to urgently recalculate certain data, and to recalculate the massively, say "all the 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 point that it will be found that the base with such a recalculation "hangs", or "crashes" or does not allow to work to other users.

This kind of "Law of Murphy" concerns each of the items below.

Before using MS SQL Server as a DBMS for "1C: Enterprise" recommended:

1. Set the value of the MAX DEGREE OF PARALLELISM parameter to 1.

I.e:

context menu

next select the Advanced page and edit the Max Degree of Parallelism parameter.

Otherwise, some requests generated by the server 1C: Enterprises can cause an 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 is often emerging.

The error will not appear stably, since the request plan is formed differently depending on the accumulated statistics - it will show itself on the volume and sophisticated requests, that is, in the deadlock.

2. Create a service plan (SHRINK), the TEMPDB temporary table database. Automatically the temporary table database server 1C: enterprises are not always cleaned, and sometimes, as a result of an unsuccessful query, a temporary table of size can be formed and not cleared, 50 GB. As a result, it may end the place on the disk, as a result, it is possible to accidentally complete and the client, and server part, there is also a small risk of data integrity.

That is necessary:

go to MS SQL Management Studio

after connecting to the server, disclose the section "Maintance Plans"

create a new one (or supplementable) service plan,

add to it "Execute T-SQL Statement Task" (as the "Shrink Database" task cannot be selected the tempdb base) 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.

It should be noted that the name of the temporary table database file may not be equal to "tempdev". To check this name you can use the script

1.USE TEMPDB.
2.
3.go
4.
5.exec sp_helpfile
6.
7.GO.

"Pot, not Vari"

The most common way in practice is the way to overflow TEMPDB and thereby "drop" the server is to forget to specify the condition when connecting tables.

Namely, let's say, we have two tables in the database, every 20,000 records each. Suppose, you can set an unambiguous compliance between their records, and we write a request that creates a temporary table that contains 20 thousand entries with fields of both source tables. But if we forget to specify the condition of the connection - each recording of the first table connects with each recording of the second! That is, the resulting table from 20'000 * 20'000 \u003d 400 million records will be obtained. Etc.

3. For the sake of reducing the load on the disk subsystem, it is recommended to distribute the working database and tempdb by different physical disks, logs, the system paging file.

The desired path for storing the working base files is better to set when it is created by editing the PATH column:

To change the physical location of the temporary table base files, the ALTER DATABASE command is used, that is, in MS SQL Management Studio you need to perform the following script ("New Query" command)

1.USE Master
2.
3.go
4.
5.alter Database Tempdb.
6.
7.Modify file (name \u003d tempdev, filename \u003d "New_Disk: \\ New_Catalog \\ tempdb.mdf")
8.
9.Go.
10.
11.alter Database Tempdb.

12.
13.Modify file (name \u003d templog, filename \u003d "new_disk: \\ new_catalog \\ templog.ldf")
14.
15.Go.

4. It should not be hampered by the "growth" of the working database and its log - the limitations on the size should not be, the AUTOGROWTH property must be set as a percentage, the recommended value is 10%. Otherwise, adding data to the database, recovery from the archive and other operations can be undone for a long time.

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

5. It is recommended to include in MS SQL Server support for the TCP / IP network protocol and turn off all the others, otherwise collaborating MS SQL Server and 1C server: enterprises will be less stable.

6. Ibid - Clear the Alias \u200b\u200bsection, because Its installation leads to MS SQL Server interaction errors and 1C server: enterprises.

Before starting the database, it is recommended:

1. When creating a database from "1C: Enterprises" to set the "dates" button 2000, otherwise attempting the date of the date earlier than 01/01/1753 (which is possible by virtue of the human factor) - will cause malfunctions in the database.

Attention! The dates offset can not be changed from the existing database!

2. Set recovery mode (Recovery Model) to a simple (Simple) value, or create a maintenance plan (Maintain Plan), which will create daily backup (Backup) Databases and trim the transaction log (log file). Otherwise, in some operations, the transaction log (log-file) will grow very quickly: for example, when restructuring a database, the growth of the log file may exceed the size of the database itself several times.

3. Create a service plan (Maintain Plan), which performs the following regulatory tasks at least once a week:

Create a backup (backup) database.

Updating Database Statistics and Cleaning Procedural Cache (it should be noted that the AutoPDate Statistics property does not imply clean the procedural cache).

standard operations

DBCC FreeProccache.

Reindexing database tables.

Of course, it makes sense to configure the automatic sending of emails about the successful / unsuccessful task execution.

Conclusion

Considered issues that most often cause difficulties from system administrators and implementants "1C: Enterprise 8", due to shared use MS SQL Server and client-server version "1C: Enterprise 8".

The author hopes that quite consistently and affordably illuminated "both sides of the coin."

P.S. Make backups more often!

In this article we will talk about how the installation of 1C: Enterprise 8 on MS SQL Server 2008 R2 Express.

And in order to start this setting, first of all you need to install the server 1C: enterprises.

You must run the 1centerprise 8.2.msi file, which is in the 1C distribution: enterprises.

There may be an assumption that 1C: the enterprise 8 was pre-installed, but in case it is still not the case, then you need to choose the desired component for this. In fact, this is the very component mentioned above, and we will consider it below.

In order to essentially facilitate myself a robot with the installation of the server 1C: Enterprises, follow the instructions of the settings wizard. This installation is divided into several stages:

1. By selecting "Edit", go to the selection of the component, it must be the 1C server: Enterprises, see the picture.

If you already have installed option Servers 1C: Enterprise 8.1 and you need it to work as before, find out how you can run the server agent 1C: Enterprise 8.1 and server agent 1C: Enterprise 8.2 simultaneously on the same computer.

2. At this stage, you need to specify that you need to install the 1C server: enterprises, as a Windows service.

Note! If you want to create a new user or use the wound created, then you need to make sure that the user has all the rights to (server role) sysadmin on MS SQL server. How to do this, you will be able to learn from the subtitle Wednesday SQL Server Management Studio. But of course it is recommended to do this after installing Microsoft SQL Server 2008 R2 Express.

In the event that you create a new user, and in turn it has no right, then you still have an error, with the result that the user will not be created.

Microsoft SQL Server 2008 R2 Express - Installation

In order to start the installation, you need to download Microsoft SQL Server 2008 R2 Express from the official site of Microsoft. In this distribution, there is already a SQL Server Management Studio application.

After that, install MS SQL Server 2008, using the parameters that you see in the figure.

Important! The sort parameter must be Cyrillic_General_ci_as.

Mode to authenticate must be mixed (Mixed).

Adding a server base in 1C

Now you have everything prepared to add a base in 1C: Enterprise.
We run 1C: Enterprise, a list of databases appears before you, you click on the "Add" button.
Select "Creating a new information base." And click the "Next" button.
In the window that appears in front of you, select Two "Creating an Information Base without configuration ...". Click the "Next" button.
In the next window, it will be third in the account, you need to specify the name, for example: "server base".

Select "On Server 1C: Enterprise". Further.

In the database server item: "Specify the name of your computer on which the Microsoft SQL Server 2008 R2 Express is installed and then with the symbol inverse Slash, the name of the SQLEXPRES server, of course, if during the installation process, you did not change it. All other items can be left unchanged.

After the robots you have done, you need to click on the "Finish" button.

If the setpoint has been completed correctly, then 1C will create a base in Microsoft SQL Server 2008 R2 Express. If there is no, then you need to check whether the user is sufficiently rights on the Microsoft SQL Server 2008 R2 Express server, which you have previously selected when installing the 1C server: enterprises. How to do it you can learn from the SQL Server Management Studio environment.

To download your updated database, you need to open the configurator and in "Administration - download the information base ..." Specify the path to your previously unloaded database. To have a file previously unloaded, you need to know how to make a backup of the information base.

SQL Server Management Studio

In order to check the user rights (server role), you need to open the "SQL Server Management Studio" application.

Connect with the server using this application.

Next in the object browser, you need to choose and allocate necessary user, (For example: HOMEPC \\ usr1cv82) and by pressing the right mouse button on it, you call the context menu. Choose "Properties". If there is no needed user in the list, then you need to create it and assign it a SysAdmin server role.

You will find a login name property dialog. In the left window, you need to select the "server role" item. And now in the right window, you will be able to get acquainted with what roles are assigned to the user you need.

Installing the technological platform and applied solution on a local computer or laptop is simple and understandable. To do this, you need to purchase a licensed version of one of the configurations, which includes an installation disk with step-by-step wizards of the platform and directly the user's program.

Installing the technological platform and configuration on the local disk

Since no 1C applied solution can work without its engine - platform - installation on a local computer is divided into two principal steps:

Step-by-step guide, how to set 1C, is described in detail in the documentation attached to each licensed version of the typical or sectoral configuration. The exact following of its instructions will allow you to install yourself and without difficulty.

Installing Server 1C: Enterprise on MSSQL

To understand the following above, it is necessary to clarify two concepts:

client - personal Computer or software, a network unit that generates requests to the server in order to obtain or process any data;
the server is a powerful computer with significant production capabilities or software that generates answers to customer requests.

Client-server architecture involves the interaction of the client application with the information database server by means special applications - Servers 1C: Enterprises.

Installing MSSQL to the server machine is described in many manuals with possible variability depending on the SQL version and version of the server operating system. In the general case, set 1C to the server, following step-by-step actions:

In the existing distribution, you need to start the setup.exe installation file (for continuation, Microsoft.Net Framework and Operational windows Installer Installer):
Several options for the further development of events are offered: primary installation, modification of already installed components, update or their search. At this stage, the program checks compliance with the minimum MSSQL requirements to the operating system:

You must enter the product key received when purchasing a licensed program. After attentive and thoughtful reading the license agreement on english language It is necessary to establish its consent in the form of a check mark:

The transition to the next step is carried out when the Next button is pressed ("Next").

When you click on the Install button, a variety of settings are offered, checkboxes in which are installed as needed: You can select all (Select All) or only the most necessary (DatabasengineServices, ClientToolSConnectivity, ManagementToolBasic with the inclusion of subordinate components):
Settings on subsequent steps It is recommended to leave the default, the transition is carried out by the Next button:
Select user data storage directories (SQL 1C base), logs, temporary files and files reserve copy Information bases (backups).
The next step adds current userwho will have the rights administrator rights (Add Current User button):
Settings on subsequent pages remain default if the situation does not require otherwise. When you press the Install button, the direct installation process starts: the color ruler shows the dynamics of the procedure performed. After the installation is successfully completed, the appropriate message will be issued and only to close the installer by pressing the Close button (close).

If the installation of the server part with such a plurality of settings seems sufficiently difficult, then to perform it, contact or the LAN system administrator.

Installation of the 1C technological platform: Enterprise to the server

Installing the required platform configuration, in fact, much different from the usual, then only the exception that at a certain step it is necessary to select the setting of the component: "Server 1C: Enterprise" and "Server Administration 1C: Enterprise":

On the following page of the installer, you must select the default interface language, select the existing or create a new user to identify when the service is started. Connection security is guaranteed by entering a password with its confirmation.

When you click on the "Set" button, the installation process "1C: Enterprise" to the server will be launched:

In the future, the need to install the protection driver in the case of purchasing delivery with hardware key or activation will be requested. soft license. In case of successful installation of the 1C technology platform: the enterprise 8 will be issued a system message.

Creating an information database on the SQL server

Creating a new information base in the launch window is no different from creating a base on local computer Up to the time of choosing its location. For server you need to choose:

Next, you should fill out all the necessary fields of the settings of the information database: Database name and its name in the server cluster, IP cluster of 1C servers: enterprises and database servers, database management system (DBMS), etc.:

Following the following steps with default settings, the final action will be created on the SQL information database server.

Installing the client program

To be able to work with an information SQL-base, it is necessary to correctly connect it on client stations. Sequence of this connection:

If the information database creation settings are correctly listed on the 1C server: the enterprises are correctly configured to configure 1C client when you start a program from the client workstation to connect to the server machine.