linux配置conf_如何使用mssql-conf和其他可用工具在Linux上配置SQL Server 2017

linux配置conf

Configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

任何系统的配置在有效工作中都起着至关重要的作用。 但是，配置系统并不一定是一项艰巨的任务。 有几种工具可以帮助完成此过程。

SQL Server on Linux provides a wide range of options for configuration, management and administration.

Linux上SQL Server提供了用于配置，管理和管理的多种选项。

1. mssql-conf
   mssql配置
2. Transact-SQL
   事务处理SQL
3. SQL Server Management Studio
   SQL Server管理Studio
4. PowerShell
   电源外壳

This article outlines the use of mssql-conf in conjunction with other available tools, to manage SQL Server.

本文概述了与其他可用工具结合使用mssql-conf来管理SQL Server的方法。

On Windows, we are familiar with SQL Server Configuration Manager, the configuration tool used to manage and administer SQL Server services, configure the network protocols, and to manage network connectivity between the SQL Server and the client computers.

在Windows上，我们熟悉SQL Server配置管理器，该配置工具用于管理和管理SQL Server服务，配置网络协议以及管理SQL Server与客户端计算机之间的网络连接。

On Linux, we have a configuration tool known as mssql-conf. The primary function of this tool remains the same as that of the SQL Server Configuration Manager, but makes it easy for Oracle and Linux admins to work with SQL Server 2017 on Linux. I’d like to visit some of the key concepts of the Oracle parameter file (PFILE and SPFILE) here, with which the server configuration can be set/unset by adding/modifying entries in the configuration file.

在Linux上，我们有一个称为mssql-conf的配置工具。 该工具的主要功能与SQL Server配置管理器的主要功能相同，但使Oracle和Linux管理员可以轻松地在Linux上使用SQL Server 2017。 我想在这里访问Oracle参数文件的一些关键概念（PFILE和SPFILE），可以通过在配置文件中添加/修改条目来设置/取消设置服务器配置。

PFILE (parameter file) is a text file used to initialize the database and setting configuration parameters. This PFILE is read when the instance starts up, in order to get specific instance characteristics. The PFILE is text based, and can be edited in an editor like VI/VIM on UNIX/Linux or Notepad on Windows. Any changes made to the PFILE would take effect only when the database is restarted.

PFILE（参数文件）是一个文本文件，用于初始化数据库和设置配置参数。 实例启动时将读取此PFILE，以获取特定的实例特征。 PFILE是基于文本的，可以在UNIX / Linux上的VI / VIM或Windows上的记事本等编辑器中进行编辑。 只有重新启动数据库后，对PFILE所做的任何更改才会生效。

SPFILE, on the other hand, is a binary file. It is similar to sp_configure of SQL Server. T-SQL is used to adjust values or entries, or to commit them into the system without restarting the system.

另一方面，SPFILE是二进制文件。 它类似于SQL Server的sp_configure。 T-SQL用于调整值或条目，或将它们提交到系统中而无需重新启动系统。

I feel that using the Oracle configuration files as a reference in this context helps many DBAs form the analogy and understand the architecture of the SQL Server 2017 better. Also, since SQL Server 2017 has already been ported to Linux, many non-SQL-DBAs can enjoy the flexibility to play with SQL.

我觉得在这种情况下使用Oracle配置文件作为参考可以帮助许多DBA形成类比并更好地理解SQL Server 2017的体系结构。 此外，由于SQL Server 2017已经移植到Linux，因此许多非SQL-DBA都可以享受使用SQL的灵活性。

The most important point of mssql-conf is the validation rule, which adds to the flexibility, enabling the management of the configuration in a more dynamic way. The command is found at /var/opt/mssql/mssql-conf in Linux. We can also edit the file configuration file found at /var/opt/msssql/mssql.conf. Use any available text editor to edit or add the required setting(s) into the SQL configuration.

mssql-conf的最重要点是验证规则，它增加了灵活性，从而可以更动态地管理配置。 在Linux中的/ var / opt / mssql / mssql-conf中可以找到该命令。 我们还可以编辑/var/opt/msssql/mssql.conf中的文件配置文件。 使用任何可用的文本编辑器将所需的设置编辑或添加到SQL配置中。

Having control over the configuration file will help in the standardization of the installation. All these features help avoid human error associated with manually updating a file.

控制配置文件将有助于安装的标准化。 所有这些功能有助于避免与手动更新文件相关的人为错误。

Configuration Manager can be used to manage the following tasks

Configuration Manager可用于管理以下任务

1. Managing database services by configuring the startup parameter
   通过配置启动参数来管理数据库服务
2. Changing the service accounts used by the SQL Server
   更改SQL Server使用的服务帐户
3. Managing client/server protocols
   管理客户端/服务器协议

Any SQL Server configuration change registers a modification to an existing registry key value. This requires a service restart for the configuration change to take effect.

任何SQL Server配置更改都会注册对现有注册表项值的修改。 这需要重新启动服务才能使配置更改生效。

Mssql-conf file is a combination sp_configure and SSCM (SQL Server Configuration Manager).

Mssql-conf文件是sp_configure和SSCM（SQL Server配置管理器）的组合。

Mssql-conf is a configuration file for the SQL Server that gets created during the process of installation of SQL Server on Linux.

Mssql-conf是用于SQL Server的配置文件，该文件是在Linux上安装SQL Server的过程中创建的。

You can use this utility to:

您可以使用该实用程序执行以下操作：

1. Run the final installation setup process
   运行最终安装设置过程
2. • Server collation
     服务器整理
   • SQL Agent settings
     SQL代理设置
   • Database mail profile
     数据库邮件配置文件
   • Default data directory
     默认数据目录
   • Log directory
     日志目录
   • Default dump directory
     默认转储目录
   • Default backup directory
     默认备份目录
   • High Availability settings
     高可用性设置
   • SQL Server memory limit
     SQL Server内存限制
   • TCP port
     TCP端口
3. Enable or disable trace flags
   启用或禁用跟踪标志
4. Set the SA password
   设置SA密码
5. Validate the configuration
   验证配置

让我们开始 (Let’s begin )

This section deals with the configuration overview of mssql.conf and shows the set of commands to be executed to set or unset various configuration parameters using mssql-conf command.

本节介绍mssql.conf的配置概述，并显示要使用mssql-conf命令设置或取消设置各种配置参数的命令集。

1. mssql.conf mssql.conf
2. mssql-conf command reference mssql-conf命令参考
3. mssql-conf configuration file mssql-conf配置文件中设置值
4. Configuration validation
   配置验证

To display the default parameters of the mssql.conf file, use any available editor to open the file.

要显示mssql.conf文件的默认参数，请使用任何可用的编辑器打开该文件。

I’m using the VI editor to display the contents of the mssql.conf file

我正在使用VI编辑器来显示mssql.conf文件的内容

#vi /var/opt/mssql/mssql.conf

Now, execute mssql-conf to understand the available configuration options

现在，执行mssql-conf以了解可用的配置选项

$/opt/mssql/bin/mssql-conf  -h

The output gives the argument list that can be used with mssql-conf to set or unset the desired SQL Server configuration properties

输出给出了可与mssql-conf一起使用的参数列表，以设置或取消设置所需SQL Server配置属性

Let’s go ahead and display all the available configuration settings using the list parameter. It will list all the configuration attributes for the SQL Server instance

让我们继续并使用list参数显示所有可用的配置设置。 它将列出SQL Server实例的所有配置属性

#/opt/mssql/bin/mssql-conf list|more

Now, add some of the server configuration parameters using mssql-conf command. As we go through the process, after every entry, the SQL Server requires a restart to commit the changes. So let me hold off the restart part and go ahead make the changes to all the parameters I want to make changes to, and then perform a single restart the SQL Service to commit all the changes at once.

现在，使用mssql-conf命令添加一些服务器配置参数。 在进行此过程时，每次输入后，SQL Server都需要重新启动以提交更改。 因此，让我推迟重新启动部分，继续对要更改的所有参数进行更改，然后执行一次重新启动SQL Service以一次提交所有更改。

1. set command: set命令更改默认数据和日志目录：

   The properties filelocation.defaultdatadir and filelocation.defaultlogdir settings can be changed to new database and log file locations using the following command. The default file location of data and log file is /var/opt/mssql/data

   可以使用以下命令将属性filelocation.defaultdatadir和filelocation.defaultlogdir设置更改为新的数据库和日志文件位置。 数据和日志文件的默认文件位置是/ var / opt / mssql / data

   #mkdir sqlshack/data
   #mkdir sqlshack/log
   #chown mssql sqlshack/data
   #chown mssql sqlshack/log
   #chgrp mssql sqlshack/data
   #chgrp mssql sqlshack/log
   #/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /sqlshack/data
   #/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /sqlshack/log
   

2. To Change default dump directory location, use the fielocation.defaultdumpdir property with the set command

   要更改默认转储目录位置，请在set命令中使用fielocation.defaultdumpdir属性

   #mkdir sqlshack/dump
   #chown mssql sqlshack/dump
   #chown mssql sqlshack/dump
   #/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /sqlshack/dump
   

3. The property filelcoation.defaultbackupdir setting is updated to point to the new backup directory location with the set command

   使用set命令将属性filelcoation.defaultbackupdir设置更新为指向新的备份目录位置

   #mkdir sqlshack/backup
   #chown mssql sqlshack/backup
   #chown mssql sqlshack/backup
   # /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /sqlshack/backup
   

   
   
   
   

4. The property hard.hadrenabled should be set to 1. This will enable the Availability Group on SQL Server

   属性hard.hadrenabled应该设置为1。这将在SQL Server上启用可用性组。

   # /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
   

   
   
   
   

5. The property hard.hadrenabled should be set to 0. This will disable the AG on the SQL Server

   属性hard.hadrenabled应该设置为0。这将禁用SQL Server上的AG

   # /opt/mssql/bin/mssql-conf set hadr.hadrenabled  0
   

6. The memory setting is memory.memorylimitb, which represents the total memory available for SQL Server. (It’s not SQL Server Max or Min memory setting.) By default, SQL Server uses 80% of the total RAM

   内存设置为memory.memorylimitb，它表示SQL Server可用的总内存。 （这不是SQL Server的“最大”或“最小”内存设置。）默认情况下，SQL Server使用总RAM的80％

   # /opt/mssql/bin/mssql-conf set memory.memorylimitmb 2000
   

7. The network.tcpport property is used to change the default port setting; we’re changing the port from 1433 to 1435.

   network.tcpport属性用于更改默认端口设置。 我们将端口从1433更改为1435。

   #/opt/mssql/bin/mssql-conf set network.tcpport 1435
   

8. To enable or disable multiple trace flags, use space in between the traceflags values with the on clause at the end of the command, like so:

   要启用或禁用多个跟踪标志，请在命令末尾的带有on子句的traceflags值之间使用空格，如下所示：

   #/opt/mssql/bin/mssql-conf traceflag 2345 3226 3456 on
   

   
   
   
   

9. #systemctl restart mssql-server.service
   

查看当前设置 (View current settings)

To view any configured settings, use any of the editor, or the cat command; the following shows the contents of the mssql.conf file

要查看任何已配置的设置，请使用任何编辑器或cat命令。 下面显示了mssql.conf文件的内容

# cat /var/opt/mssql/mssql.conf

验证配置文件 (To validate the configuration file)

Validation helps in validating all the properties of the configuration file. It also helps identify any type errors, thereby ensuring that the SQL Server configuration is correct and ready for prime time

验证有助于验证配置文件的所有属性。 它还有助于识别任何类型错误，从而确保SQL Server配置正确并准备就绪

#/opt/mssql/bin/mssql-conf validate

Let’s make some changes to the configuration properties to see how the validation will act on the incorrect properties. Open the file and modify some contents of the configuration file. As shown in the following figure, the defaultlogdir is misspelled as defaultlogr, which is not correct. We’ve also added correct traceflags, traceflag1 and traceflag2. Run the validation command to validate the configuration.

让我们对配置属性进行一些更改，以查看验证如何对不正确的属性进行操作。 打开文件并修改配置文件的某些内容。 如下图所示，defaultlogdir拼写错误为defaultlogr，这是不正确的。 我们还添加了正确的traceflags，traceflag1和traceflag2。 运行验证命令以验证配置。

The warning message shows issues in some of the configuration settings.

该警告消息显示某些配置设置中的问题。

#/opt/mssql/bin/mssql-conf validate

输出量 (Output)

The use of default port is not functioning, since we updated the default port configuration to 1435. So, in the second attempt, we specify the custom port number, along with the server name, the parameters separated by a comma. Now the command executes successfully

默认端口的使用无法正常运行，因为我们将默认端口配置更新为1435。因此，在第二次尝试中，我们指定了自定义端口号以及服务器名称，并以逗号分隔了参数。 现在命令成功执行

The output displays the recently set traceflag details

输出显示最近设置的traceflag详细信息

结语 (Wrapping up)

We saw several tools that are available to configure MSSQL, and discussed mssql-conf in particular. I’ve covered a portion of Oracle parameter files and discussed about the similarities and the purpose of using the configuration files. Later, we had an in-depth discussion on mssql-conf tool, along with examples.

我们看到了一些可用于配置MSSQL的工具，并且特别讨论了mssql-conf。 我已经介绍了一部分Oracle参数文件，并讨论了使用配置文件的相似性和目的。 后来，我们对mssql-conf工具和示例进行了深入的讨论。

The goal of this article is to be a kick-start reference to SQL DBAs to try to set the configuration using new a tool set and configuration files. I think it’s time for SQL DBA’s to enhance their cross-technical skills as SQL Server is poised to cater to a bigger market by posting and setting new records for TPC-H benchmark.

本文的目的是作为对SQL DBA的快速参考，以尝试使用新的工具集和配置文件来设置配置。 我认为现在是SQL DBA增强其跨技术技能的时候了，因为SQL Server已准备通过发布和设置TPC-H基准新记录来迎合更大的市场。

The configuration file, as we saw, has many properties and options to tune SQL Server for optimal performance. Also, if you are a developer or an administrator of the Linux ecosystem, there is now a lot more reasons to play with SQL Server 2017. SQL Server on Linux is setting trends and benchmark to become a leading enterprise database solution for every organization and business.

如我们所见，配置文件具有许多属性和选项，可以优化SQL Server以获得最佳性能。 此外，如果您是Linux生态系统的开发人员或管理员，那么现在就有更多理由可以使用SQL Server2017。Linux上SQL Server正在设定趋势和基准，以成为每个组织和企业的领先企业数据库解决方案。 。

翻译自: https://www.sqlshack.com/configure-sql-server-2017-linux-mssql-conf-available-tools/

linux配置conf