Typically, installing MS SQL Server is not considered, since it is believed that it is quite simple - click the Next button several times and the database is installed. However, there are several nuances that can poison the life of even an experienced administrator, provided that he has not previously installed MS SQL Server for 1C.

Creating virtual servers

The first step is to create one or two virtual servers. We previously wrote about choosing the optimal virtual server configuration for 1C. With a heavy load, you will need two virtual servers - one will be used for 1C, and the second for the DBMS. In this article we will assume that the load is moderate, therefore both 1C and the DBMS will be installed on one server. The virtual server configuration is shown in the table.

Virtual server configuration under moderate load

Installing MS SQL Server for 1C

Download the latest version of MS SQL Server. We will not consider the installation process in detail - it is really simple. But we will focus only on the necessary settings.

By default, MS SQL Server is loaded onto the system drive C:. By selecting a custom installation type (Fig. 1), you can change the destination drive and directory (Fig. 2).

Rice. 1. MS SQL Server Download Tool

Rice. 2. Select the download directory

Next, you need to wait until the installer downloads the necessary files (Fig. 3), after which you will see the SQL Server Installation Center window (Fig. 4). The Installation Center not only allows you to install SQL Server, but also performs various database server maintenance operations. In our case, we're interested in installation for now, so go to the Installation section and select New installation of a stand-alone instance of SQL Server or add components to an existing installation.

Rice. 4. SQL Server Installation Center

Rice. 5. Installation section

The installer will start; the first few steps do not require comments. But we need to talk about the choice of components separately. Absolutely all components are not needed for 1C to work, two are enough - Database Engine Services And Connecting to Client Tools. Previously, you could select a component Controls, in the latest release of SQL Server (2017), management tools are installed separately - from the section Installation. So, select the two main components, set the installation directories (previously we selected the download directory) and click the button Further.

Rice. 6. Selecting server components

Click Next until you go to the Server Configuration section (Fig. 7). On the Collation Options tab, make sure the Cyrillic_General_CI_AS encoding is selected. If you chose Russian (Fig. 2), then this encoding will be selected by default, but if you prefer English on the server, then the encoding will have to be changed.

In chapter Setting up the DBMS kernel on the tab Server configuration set mixed authentication mode (Fig. 8), and also set a password for the database server system administrator account (sa). You also need to assign server administrators. If you are running under CloudAdmin or another user with administrative rights, just click the button Add current user.

Next tab - Data directories- very important. User databases and tempdb should be stored on the highest performing disk. Ideally, add an SSD drive to store databases. Although 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.

Rice. 9. Data directories (default paths)

The remaining parameters can be left as default. Click the button Further and then the button Install and wait while SQL Server installs.

Return to the Installation Center window and install SQL Server management tools (Figure 10). A browser will open with a page where you can download the controls. Download the installer file and install. It is not necessary to install management tools on the server, they can be installed on the administrator's computer, but since we have a virtual server, we will not complicate its configuration and will install SQL Server management tools on the same computer.

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

We just completed the installation of SQL Server and management tools. All that remains is to configure the server to work with 1C. Using management tools, connect to SQL Server. Right-click on the server in the list on the left and select the command Properties(Fig. 11). Make sure the encoding is Cyrillic_General_CI_AS. Otherwise, the server will have to be reinstalled.

Rice. 11. Server properties

If everything is ok with the encoding, go to the section Memory. Specify the amount of RAM available to the SQL server, otherwise the SQL server will try to recycle all available memory. If 1C is installed on the same server, then this behavior of SQL Server cannot be called optimal - for 1C you also need to leave available resources.

Close the properties window. Go to section Security, Logins. Right click on the partition Logins and select a team Create a new login. Create a new user account on behalf of which 1C will connect - using the administrator account (sa), giving 1C maximum rights, to put it mildly, is incorrect.

Enter the username, set authentication via SQL Server (Fig. 13). Next, go to the section Server roles and select dbcreator, processadmin and public (Figure 14).

Rice. 13. Creating a new user account for 1C

That's all. We installed SQL Server and management tools, performed basic server setup and created an account for 1C. In most cases, the default SQL server parameters are more than suitable for ensuring productive operation of the 1C:Enterprise server.

This article contains information about the installation procedure for 1C 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 at the MS SQL installation stage.

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.

February 2, 2015 at 4:04 pm

The most efficient server scheme in terms of speed of operation, for client-server 1C 8.x

Preface

I constantly came across statements from IT specialists “the network is loaded by 20%... the processors are at 50%... there are few queues for disks... So the network and servers are coping... see the code in 1C for problems only there.”

In fact, the following happened (the 1C and SQL servers were separated on different computers): the network was practically used to the maximum( these " 20% network interface load" = "20% useful data" + "80% processing overhead"). And accordingly, due to the small width of the channel for exchanging “useful” data, the SQL server and “1C Server” were constantly waiting for each other, which led to low utilization of CPU and disk system resources.

Maintain:First, I want to focus on what the 1C platform is?

So let's start with the main thing 1C - built on ORM(object-relational mapping) -system and the programmer in it works not directly with the relational representation, but with objects.
ru.wikipedia.org/wiki/ORM

A programmer in the 1C environment writes object logic, and the platform itself is responsible for assembling/disassembling and recording objects in a “flat form” according to database tables.

The main "+" and "-" from an ORM point of view:

"+" A programmer in an ORM environment gains an advantage in the speed of application development due to the reduction in the amount of code and its simplicity compared to purely relational program code (example SQL queries). It also frees you from writing code that works directly with records in Relational DBMS tables. * 1

"-" Difficulties for creators of ORM “platforms” and performance issues:

Using a relational database to store object-oriented data creates a "semantic gap", forcing programmers to write software that must both be able to process data in object-oriented form and be able to store that data in relational form. This constant need to convert between two different forms of data not only greatly reduces productivity, but also creates difficulties for programmers, since both forms of data impose restrictions on each other.

*1"Clarification". Despite the fact that 1C 8.x allows you to work with relational-like code (read only) in the 1C “Request” object, it is still not a directly one-to-one query to data storage tables translated into a relational DBMS, but first In total, “Object request” also does not bypass the stage of assembling and disassembling objects. Therefore, often, instead of many, thousands of line “Object queries” - the most optimal in terms of code performance and development speed - is to write object-based, non-relational-like code.

Chapter 1: Let's consider the client-server model 1C 8.x

I will note the main bottlenecks affecting performance:

1) The first bottleneck is the communication medium.
In the figure, arrows show data exchange flows, where the “red” ones are the Relational DBMS<->Object DBMS, “orange” - synchronization between Object DBMS.
Because when using separate servers for DBMS and 1C clusters - the communication environment is network connections - then there are significant delays in the transfer of data in numerous small portions - both due to the latency of the physical implementation of the interfaces itself, and due to the latency of nodes in this network.

Let's look at the example of the Ethernet Gigabit network standard. (Data transfer rate graph...below)
using the example of 1C Server running with MS SQL ( default size of communication packets is 4 kb):

The graph shows that when using DATA = 4 kb packets, the bandwidth of the considered network is only 250 Megabit/s. (as correctly noted in the commentary to the publication: these are not protocol packages For example TCP level, and DATA packets which generate applications participating in the exchange)
…
From practice: this split into two separate servers
MS SQL (server No. 1)< - Ethernet Gigabit --->"Server 1C" (server No. 1)
inferior in platform speed
by 50% MS SQL option (server No. 1)< - Shared Memory (без сети через участок памяти) --->“Server 1C” (server No. 1)… and that’s already "in one high-load user session"

2) The bottleneck is the number of individual computers in “1C clusters”, the more there are, the greater the synchronization costs and, as a result, the decrease in system performance.

3) Bottleneck - the number of separate processes of the 1c server, the more there are, the greater the cost of synchronizing them... But here, most likely, it is necessary to find a “golden mean” - to ensure stability. 2*
2* “Clarification” - for MS Windows there is the following rule:
Processes are more expensive than threads, which means in practice the following in this case: the exchange rate between two threads within one process is significantly higher than the exchange rate between threads located in different processes.

Therefore, for example, “File 1C 8.x” always exceeds the speed of single-user operation of the platform in the client-server version. Everything is simple because... in the case of “File 1C 8.x”, the “Relational DBMS” thread communicates with the “Object DBMS” thread within one single process.

4) Bottleneck – single-threaded user session, because each individual session - the user session is not parallelized by the platform into several, then its work is limited to using the resources of one CPU core => therefore, the maximum speed of each core is desirable, in this case the performance of the 1C platform, for example on a 10-core CPU at 1 GHz, will be significantly inferior to the performance platforms on a 4-core CPU at 3 GHz - of course, up to a certain number of threads.

Chapter 2(Summary): Let's consider non-scalable and scalable options - the most effective schemes for the 1C 8.x platform. for OS Windows (I believe the situation is similar for Linux)

1-Option (not scalable). Per 100 “high load user sessions”

1) a regular 2-socket server with 4 core CPUs at 3 GHz is effective.

3) MS SQL< - Shared memory -->"Server 1C"

2-Option (scalable). starting from 100"highly loaded user sessions" and onwards….
Here it is most logical to follow the path of the German 1c “Sap HANA”))
Assemble a modular “Super-computer” from SGI - consisting of “blades” on 2 socket motherboards, each blade is connected to each other by a complex topology of ultra-fast interconnect based on NUMA chips, and everything is controlled by a single OS. Those. programs inside such a server, by definition, have access to the resources of any “blade”.

1) add “blades” according to the required load... at the rate of approximately one “blade” per 100 users.

2) fast disk system on SSD

3) MS SQL< - Shared memory -->"Server 1C"

Installing the technology platform and application solution on a local computer or laptop is simple and straightforward. To do this, you need to purchase a licensed version of one of the configurations, which includes an installation disk with step-by-step installation wizards for the platform and the user program itself.

Installing the technology platform and configuration on a local disk

Since not a single 1C application solution can work without its own engine - the platform - installation on a local computer is divided into two fundamental steps:

A step-by-step guide on how to install 1C is described in detail in the documentation attached to each licensed version of a standard or industry configuration. Following his instructions exactly will allow you to complete the installation yourself and without difficulty.

Installing 1C:Enterprise server on MSSQL

To understand what is described below, two concepts need to be clarified:

• client - a personal computer or software, a network unit that generates requests to the server in order to receive or process any data;
• server – a powerful computer with significant production capabilities or software that generates responses to client requests.

Client-server architecture involves the interaction of a client application with an information database server through a special application - the 1C: Enterprise server.

Installing MSSQL on a server machine is described in many manuals with possible variability depending on the SQL version and the version of the server operating system. In general, you can install 1C on a server by following the step-by-step steps:

1. On your existing distribution, you must run the setup.exe installation file (to continue, you will need the Microsoft.NET Framework and the Windows Installer):
   
2. Several options for further development of events are offered: initial installation, modification of already installed components, updating or searching for them. At this stage, the program checks compliance with the minimum MSSQL operating system requirements:
   

You must enter the product key you received when you purchased the licensed program. After carefully and thoughtfully reading the license agreement in English, you must indicate your consent by checking the box:


The transition to the next step is carried out by clicking the Next button.

3. When you click the Install button, you are offered many settings, the checkboxes of which can be checked as needed: you can select all (Select All) or only the most necessary (DatabaseEngineServices, ClientToolsConnectivity, ManagementToolBasic with the inclusion of subordinate components):
   
4. It is recommended to leave the settings in the subsequent steps as default; click the Next button:
   
5. Directories are selected for storing user data (SQL 1C database), logs, temporary files and information base backup files (backups).
6. The next step adds the current user, who will have system administrator rights (the “Add Current User” button):
   
7. The settings on subsequent pages remain default unless the situation requires otherwise. When you click the Install button, the installation process itself starts: the color line shows the dynamics of the procedure being performed. After the installation is successfully completed, a corresponding message will be displayed and all that remains is to close the installer by clicking the Close button.
   

If installing a server part with so many settings seems quite complicated, then you should contact your local network system administrator to complete it.

Installing the 1C:Enterprise technology platform on a server

Installing the platform configuration required for operation, in fact, is not much different from the usual one, with the only exception that at a certain step you need to select the installation of the components: “1C:Enterprise Server” and “1C:Enterprise Server Administration”:

On subsequent pages of the installer, you must select the default interface language, select an existing one, or create a new user for identification when starting the service. Connection security is guaranteed by entering a password and confirming it.

When you click on the “Install” button, the process of installing the 1C:Enterprise technology platform on the server will start:

In the future, you will be asked to install a protection driver if you purchase a product with a hardware key or activate a software license. If the 1C:Enterprise 8 technology platform is successfully installed, a system message will be issued.

Creating an infobase on a SQL server

Creating a new infobase in the launch window is no different from creating a database on a local computer, right up to the moment you select its location. For the server you need to select:

Next, you should fill in all the necessary fields for setting up the information base parameters: name of the database and its name in the server cluster, IP of the 1C:Enterprise server cluster and database server, type of database management system (DBMS), etc.:

Proceeding through the following steps with the default settings left, the final action will be to create a SQL infobase on the server.

Installing the client program

To enable users to work with the SQL information database, it is necessary to connect it correctly on client stations. The sequence of such a connection:

If the settings for creating an infobase on the 1C:Enterprise server have been correctly specified and the 1C client configuration has been completed correctly, when you start the program from the client workstation, a connection to the server machine will be made.

Updating Database Statistics

(one or several times a day)

If manually, then we perform the following procedures:

Exec sp_msforeachtable N"UPDATE STATISTICS ? WITH FULLSCAN" DBCC UPDATEUSAGE (dbname)

Via GUI:

Clearing the DBMS procedural cache

(after statistics update)

We carry out the procedure

DBCC FREEPROCCACHE

Database reindexing

(once a day during non-working hours)

If manually, then

Sp_msforeachtable N"DBCC DBREINDEX (""?")"

Via GUI:

Use fragmentation analysis to reduce load

For large databases, you need to reduce unnecessary defragmentation operations for those that do not require it.

Dynamic control table function sys.dm_db_index_physical_stats returns the percentage of fragmentation in a column avg_fragmentation_in_percent. If the value in this column is greater than 25%, it is recommended that you defragment/reindex this index to restore original performance.

Even easier, using the report:

Set up backup

Don't forget to set automatic for your database. This is significantly cheaper than not making a backup and without the hassle :) . At least once a week, check that you haven’t run out of space and restore the backup to a copy of the database to check whether the backup will save you, “if anything”...

Reducing the size of the database (log) of data

You can either shrink the database using the SQL Server Management Studio GUI.
The first case is performed by calling the context menu on the required database and selecting Tasks - Shrink - Database.
Let us describe the second case in a little more detail. Below is the log reduction script:

DBCC shrinkdatabase(N"base1c", TRUNCATE_ONLY); /*truncation of the entire database*/

use DBCC SHRINKFILE(N"base1c_Data", 101); /*truncate only the data file to a size of 101 MB*/

use DBCC SHRINKFILE(N"base1c_Log", 0); /*truncate only the transaction file to 0 MB*/

CHECKPOINT; /*Writes all data from the buffer cache in the current database to disk*/

BACKUP LOG TO DISK = N"F:\log\base1c.bak" WITH TRUNCATE_ONLY /*clearing the transaction log */

Where:
base1c - database name
F:\log\base1c.bak - path to the log backup file