1s enterprise 8.2 sql installation and configuration.

Description

Benefits of using 1C:Enterprise based on Microsoft SQL Server

When using file versions of the system, with an increase in the number of jobs or complication of operations (large amounts of data for reporting or the formation of registers, document journals, a large number of simultaneous requests for generating reports, etc.), work productivity decreases: more and more time is required to complete those the same tasks. At the same time, an increase in server resources or network bandwidth does not give a tangible increase in performance. The solution is to switch to the client-server version of 1C:Enterprise.

You can use the following arguments in favor of SQL Server when talking with technical experts:

Better performance through the use of indexing and table partitioning in the DBMS
Automatic use of hardware resources as the load grows, parallel execution of queries
Consolidation of disk space with the ability to compress data in the SQL Server database up to 50% of the original volume - less need to purchase new media as the amount of data stored grows
Increased reliability through SQL Server fault tolerance and data backup technologies.

Additional Information:

SQL Server continues to evolve, with a new cloud-optimized information platform offering more options for working with data. Now there are all the necessary tools for conducting deep data analysis and using cloud solutions for the individual needs of various companies.

SQL Server makes it easier and more cost-effective to develop high-performing mission-critical applications, big data enterprise assets, and business intelligence solutions so employees can make informed decisions faster. These products can be deployed on-premise, in the cloud, or in a hybrid environment. They are managed using a familiar set of tools.

Mission Critical Performance

SQL Server accelerates mission-critical applications with new in-memory OLTP technology that delivers up to 10x performance on average and up to 30x performance in transaction processing. In terms of data storage, the new in-memory updatable data column store processes queries 100 times faster than traditional solutions. For 5 years in a row, SQL Server has confirmed its status as the most secure database.

Fast analysis of any data

Get insights faster with a business intelligence platform that accelerates the access, analysis, cleansing, and shaping of internal and external data. SQL Server and Power BI for Office 365 make it easy for users to access the data they need so they can make informed decisions faster.

Hybrid cloud platform

SQL Server is designed to be used in a hybrid environment that includes both on-premises and cloud resources, and includes new tools that make it easy to create backup and disaster recovery solutions using Microsoft Azure. These tools enable rapid migration of SQL Server databases to the cloud from on-premises resources, allowing customers to leverage the existing skills and benefits of Microsoft's global data centers.

Consider the installation and configuration of MS SQL Server for 1C.

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

Depending on this, the following options can be indicated:

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 a 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 executed by users.

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

Consider the characteristics of "iron" for all three cases

If installing on a separate server:

CPU

Option 1. Not lower than 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 at least 500 GB (preferably SSD)
Option 3: At least 1TB SAS (SSD recommended)

Network connection

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

In the case of a joint installation with the 1C Enterprise server:

CPU

Option 1. At least 2x2GHz with at least 2 cores
Option 2. At least 4x3GHz 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 at least 500 GB (preferably SSD)
Option 2: At least 1TB SSD
Option 3. It is strictly forbidden to do this

Network connection

Option 1. Preferably 1 Gb/s
Option 2. Preferably 1 Gb/s
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 go directly to the process of installing 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 installation distribution kit for MS SQL Server for 1C on the server's local hard drive.

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

In the window that opens, select "Install" in the right pane.

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 that, you need to select the server role by selecting "Install MS SQL Server Components".

The next step is to select which 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 “sa” user and set its password (you can also assign a Windows user, but the “sa” user is considered more secure).

On the other two tabs, you can set up new directories for storing user databases (but it's better not to change them).

Now you have installed MS SQL Server for 1C

"Open" our MS SQL Server for 1C. To do this, go to applications and find there SQL Server 2014 Management Studio.

After you are connected, on the left in the tree, select "Security", and in it - "Login names". By clicking the right mouse button, select "Create a login" in the submenu. In the form that opens, specify the username and password (remember this password and this user, because you will specify it 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.

We proceed to connecting the 1C base on the 1C server

Launch the 1C:Enterprise server administration console.

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

Click on "OK". The base is connected.

You can connect the base directly from the 1C base connection dialog.

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

In the window that appears, select "Create a new infobase".

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

In the next window, set the name of the database and indicate that it will be on the 1C:Enterprise server.

On the next tab, fill in the fields in the same way as it was described in the section connecting the base from the 1C server.

SQL optimization for 1C is completed. You can start and run.

Update database statistics

(one or more times a day)

If manually, then we perform the 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 the index to restore the original performance parameters.

Even easier, using the report:

Set up a backup

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

Reducing the size of the database (log) 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's describe the second case in more detail. Below is the log reduction script:

DBCC shrinkdatabase(N"base1c", TRUNCATE_ONLY); /*truncate entire base*/

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

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

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 /*clear the transaction log */

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

The amount of data stored in the database;

The ratio of the number of read requests to write requests;

The presence of other processes using resources.

Server performance can be significantly affected by settings that control disks, etc.

For example, in order to save energy, processors can "lower" the frequency of the processor, which is acceptable for personal computers and completely unacceptable for servers with 1C.

In the server BIOS, disable all settings to save processor power.

If there is "C1E" - be sure to DISCONNECT!!

For some not very parallel tasks, it is also recommended to turn off hyper-threading in the BIOS.

In some cases (especially for HP!) you need to go into the server's BIOS and turn OFF the items there that have EIST and C1E in their names.
Instead, you need to find in the same place the items related to the processor, in the name of which there is, turn on Intel SpeedStep and turn them ON.
If the BIOS has a general indication of the power saving mode, turn it on to the maximum performance mode (it can also be called "aggressive")

note that such settings are popular, but there are exceptions when vendors implement the settings and mechanisms indicated above differently, and then it may be necessary not to turn off, but to turn on some items related to EIST, SpeedStep and Turbo Boost.

Do not forget also about the scheme settings in the operating system.

Ultimately you should not be guided by the names of these items, but by the final maximum frequencies of the processors. You can control them with the CPU-Z utility. Let's take an example:

here is a snapshot of a system based on the i7-4770 processor, clocked at 3.4 GHz (which is explicitly written in the Specification: @3.40Ghz field). In the Clocks (Core # 0) group, in the Multiplier item (multiplier), the entire range of multipliers allowed for this processor is indicated: from 8 to 39. 8 is the state of rest, and 39 is the maximum possible multiplier when loading one core. If you multiply the multiplier value by the bus speed (Bus Speed) written below, in this case 99.76 MHz, you will get the current clock frequency (Core Speed). In this case, 99.76*27 is approximately equal to 2693.57 MHz. As you can see, this is even lower than the passport clock frequency.
Let's say we made some set of changes and we want to see the difference. We go here, and we see the desired maximum multiplier:

But we are not in a hurry to rejoice, in the picture there is only an instantly fixed frequency of one of the nuclei. And what about the situation on the other cores? In new versions of CPU-Z, it became possible to monitor the multiplier and frequency for all available cores (menu Tools - Clocks)

We go there and see that the multiplier is not the maximum on all cores, some cores “jump”!

We continue to research with the settings until we see that in the absence of a maximum load of processors, the frequency of all cores is maximum for a given processor:

Now you can run the TPC test with a clear conscience and see an improvement in the result there.

Servers with architecture Intel Sandy Bridge can dynamically change processor frequencies.

To manage under Linux, we send to the redhat documentation.

Make sure that after adjusting the power supply scheme, the processor operates at the desired maximum frequency declared by the manufacturer. To do this, use the cpu-z utility to look at core speed.

Using a virtual environment means it could be 4 places where you need to check the effect of settings on processor frequencies (physical server bios, host OS power supply scheme, virtual server bios, virtual OS power supply scheme).

On 1C and MS SQL Server servers, the use of antiviruses (even the fact of installation without turning it on) will lead to a decrease in performance in the form of periodic massive slowdowns and interface freezes.

Combining the roles of the 1C server and the MS SQL Server gives greater performance, especially if you use the data exchange protocol directly through the Shared Memory.

To configure the protocol, use the article

Our "recommended practices" based on the experience of completed projects

We have completed many projects using MS SQL Server 2008 R2.

The material of the article can be discussed on the forum

IF YOU HAVE MADE ALL THE SETTINGS AND DO NOT GET THE PERFORMANCE YOU NEED, THEN

In general, setting up MS SQL Server to work with 1C of an enterprise does not differ much from its usual settings, but there are still some nuances that have been identified empirically.

Consider the most important points in the installation and subsequent configuration of the server and databases in order to optimize the work 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

For MS SQL Server to work with 1C Enterprise, it is enough to select the following set of components:

Database Engine Services
Communication means of client funds
Controls - Basic
Management tools - complete set (we will need a complete 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 the speed of operation and fault tolerance.

Server Configuration

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

Configuring the Databse Engine

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 allowing 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 the properties of the Ms SQL server to work with 1C

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

Open the server properties window and go to the Memory item. We set the maximum allowable value of memory allocation for the needs of the SQL server. If this is not done, it eats up all the free memory, because the default value is 2147483647 MB. The allowable 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 set a limit for SQL.

Let's move on to the Processor. It is also better to set the maximum number of 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 will do.

Setting up the working base 1C Enterprise

Open the properties of the custom database.

Now the most important thing is to decide on the database recovery model. They are configurable in nukta settings. Let's consider two basic recovery models.

1. Simple. It should be used in the case when you plan to make a backup once a day and it does not matter to you the possibility of restoring up to a certain point. It can be 1C Accounting or ZUP where there are not a large number of daily transactions. Make one backup every night and sleep well. No complications.

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 in the 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 settings of the transaction logs.

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

The [Journal] file type setting can be skipped if the simple recovery model is used.
If you use the full one, you need to adjust the settings. Set the auto-expansion to 50MB. It is worth paying attention to the limitation of autoexpansion and it is better to change it. the default value is greater than 2TB. With a large number of transactions, for example, retail sales in 1C Retail, the transaction log will grow very quickly and soon you will run out of free space on the 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 the limit, it is worth remembering that when the extreme value is reached, an error awaits you: “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.

However, clearing the transaction log does not reduce the size of the file itself, but only frees up space in it for new records by removing inactive completed transactions.