Shulou(Shulou.com)06/01 Report--

Mssql-conf is a configuration script after installing SQL Server 2017 on Linux. You can use this utility to set the following parameters:

Agent

Enable SQL Server proxy

Collation

Set up a new collation

Customer feedback

Choose whether or not to send feedback to Microsoft

Database Mail Profile

Set the default database mail configuration

Default data directory

Modify the default path of the new data file

Default log directory

Modify the default path of the new log file

Default master database file directory

Modify the default path of the master database

Default master database file name

Modify the name of the master database file

Default dump directory

Modify the default path for new memory DUMP and other debug files

Defalut error log directory

Modify the new SQL Server error log file, default trace, system health session extension event, and Hekaton session extension event file

Default backup directory

Modify the default path of the new backup file

Dump type

Select the DUMP type of memory DUMP file collection

High availability

Enable availability group

Local Audit directory

Configure a directory to add local audit files

Locale

Configure the locale used by SQL Server (configure locale)

Memory limit

Configure SQL Server memory limit

TCP port

Modify the port on which the SQL Server connection listens

TLS

Configure TLS (Transport Level Security)

Traceflags

Set the tracking identity used by the service

Tips for using:

For AlwaysOn availability groups and shared disk clusters, always make the same configuration changes for each node.

For shared disk clusters, do not attempt to apply changes by restarting the mssql-server service. SQL Server runs as an application. Accordingly, take the resource offline and then go online.

You can specify the full path to run mssql-conf:/opt/mssql/bin/mssql-conf. If you navigate to that path, you can run mssql-conf:./mssql-conf in the context of the current path.

Enable SQL Server proxy

The sqlagent.enabled setting enables the SQL Server proxy. By default, the SQL Server agent is disabled. If sqlagent.enabled does not appear in the mssql.conf configuration file, then SQL Server internally believes that the SQL Server agent is disabled.

Follow these steps to modify the configuration:

1. To enable the SQL Server agent:

Sudo / opt/mssql/bin/mssql-conf set sqlagent.enabled true

two。 Restart the SQL Server service:

Sudo systemctl restart mssql-server

Modify SQL Server collation

Use the set-collation option to modify the collation to any supported collation.

1. First back up any user database on the instance.

two。 Detach the user database using sp_detach_db.

3. Run the set-collation option and follow the prompts:

Sudo / opt/mssql/bin/mssql-conf set-collation

4. Mssql-conf uses the tool to try to change to the specified collation and restart the service. If there are any errors, it rolls back the collation to the previous value.

5. Restore the user database backup.

For the list of supported collations, run the sys.fn_helpcollations function:

SELECT Name from sys.fn_helpcollations ()

Configure customer feedback

Telemetry.customerfeedback sets whether SQL Server sends feedback to Microsoft for modification. By default, this value is set to true for all versions. Run the following command to modify the value:

Important: you can't turn off the free version of customer feedback, Express and Developer.

1. For the telemetry.customerfeedback option, run the script mssql-conf with the set command as the root user. The following example turns off customer feedback by setting false.

Sudo / opt/mssql/bin/mssql-conf set telemetry.customerfeedback false

two。 Restart the SQL Server service:

Sudo systemctl restart mssql-server

Modify the default data and log directory location

The filelocation.defaultdatadir and filelocation.defaultlogdir settings modify the location where new data and log files are created. By default, this location is / var/opt/mssql/data. Use the following steps to modify the configuration:

1. Create a target directory for new data and log files. The following example creates a new / tmp/data directory:

Sudo mkdir / tmp/data

two。 Change the owner and group of the directory to mssql:

Sudo chown mssql / tmp/datasudo chgrp mssql / tmp/data

3. Use the mssql-conf script to execute the set command to modify the default data directory:

Sudo / opt/mssql/bin/mssql-conf set filelocation.defaultdatadir / tmp/data

4. Restart the SQL Server service:

Sudo systemctl restart mssql-server

5. Now the data files for the newly created database will be stored in the new location. If you want to change the log file location of the new database, you can use the following set command:

Sudo / opt/mssql/bin/mssql-conf set filelocation.defaultlogdir / tmp/log

6. This command also assumes that the / tmp/log directory exists and belongs to mssql users and groups.

Modify the default master database file directory location

The filelocation.masterdatafile and filelocation.masterlogfile settings modify the location where the SQL Server engine looks for master database files. By default, this location is / var/opt/mssql/data.

Modify these settings by following these steps:

1. Create a target directory for the new error log file. The following example creates a new / tmp/masterdatabasedir directory:

Sudo mkdir / tmp/masterdatabasedir

two。 Change the owner and group of the directory to mssql:

Sudo chown mssql / tmp/masterdatabasedirsudo chgrp mssql / tmp/masterdatabasedir

3. For master data and log files, use the set command of the mssql-conf script to modify the default master database directory:

Sudo / opt/mssql/bin/mssql-conf set filelocation.masterdatafile / tmp/masterdatabasedir/master.mdfsudo / opt/mssql/bin/mssql-conf set filelocation.masterlogfile / tmp/masterdatabasedir/mastlog.ldf

4. Stop the SQL Server service:

Sudo systemctl stop mssql-server

5. Mobile master.mdf and masterlog.ldf:

Sudo mv / var/opt/mssql/data/master.mdf / tmp/masterdatabasedir/master.mdfsudo mv / var/opt/mssql/data/mastlog.ldf / tmp/masterdatabasedir/mastlog.ldf

6. Start the SQL Server service:

Sudo systemctl start mssql-server

Note:

If SQL Server cannot find master.mdf and mastlog.ldf in the specified directory, the system database will automatically create a copy of the template in the specified directory, and SQL Server will start successfully. However, metadata such as user databases, server logins, server certificates, encryption keys, SQL agent jobs, or old SA login passwords will not be updated in the new master database. You have to stop SQL Server and move the old master.mdf and mastlog.ldf to the new designated location, and continue to start SQL Server with the existing metadata.

Modify the name of the master database file

The Filelocation.masterdatafile and filelocation.masterlogfile settings modify the location where the SQL Server engine looks for master database files. The default location is / var/opt/mssql/data. Use the following steps to modify these settings:

1. Stop the SQL Server service:

Sudo systemctl stop mssql-server

two。 Use the set command of the mssql-conf script to modify the name of the data and log files for the specified master database:

Sudo / opt/mssql/bin/mssql-conf set filelocation.masterdatafile / var/opt/mssql/data/masternew.mdf

Sudo / opt/mssql/bin/mssql-conf set filelocation.mastlogfile / var/opt/mssql/data / mastlognew.ldf

3. Modify the name of the master database data and log file:

Sudo mv / var/opt/mssql/data/master.mdf / var/opt/mssql/data/masternew.mdfsudo mv / var/opt/mssql/data/mastlog.ldf / var/opt/mssql/data/mastlognew.ldf

4. Start the SQL Server service:

Sudo systemctl start mssql-server

Modify the default DUMP directory location

The filelocation.defalutdumpdir setting modifies the default location of memory and SQL DUMP generation in the event of a failure. By default, these files are generated in the / var/log/mssql/log directory.

Use the following command to configure the new location:

1. Create the target directory for the new DUMP file. The following example creates a new / tmp/dump directory:

Sudo mkdir / tmp/dump

two。 Change the owner and group of the directory to mssql:

Sudo chown mssql / tmp/dumpsudo chgrp mssql / tmp/dump

3. Use the set command of the mssql-conf script to modify the default data directory:

Sudo / opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir / tmp/dump

4. Restart the SQL Server service:

Sudo systemctl restart mssql-server

Modify the default error log directory location

The filelocation.errorlogfile setting modifies where the new error log, default Profiler trace, system health session extension events, and Hekaton session extension event files are created. The default location is / var/opt/mssql/log. The directory set by the SQL error log becomes the default directory for other logs.

Modify these settings:

1. Create a target directory for the new error log file. The following example creates a new / tmp/logs directory:

Sudo mkdir / tmp/logs

two。 Change the owner and group of the directory to mssql:

Sudo chown mssql / tmp/logs

Sudo chgrp mssql / tmp/logs

3. Use the set command of the mssql-conf script to modify the default error log file name:

Sudo / opt/mssql/bin/mssql-conf set filelocation.errorlogfile / tmp/logs/errorlog

4. Restart the SQL Server service:

Sudo systemctl restart mssql-server

Modify the default backup directory location

The filelocation.defultbackupdir setting modifies the location where the backup file is generated. By default, these files are generated from / var/opt/mssql/data.

Use the following command to configure this new location:

1. Create a target directory for the new backup file. The following example creates a new / tmp/backup directory:

Sudo mkdir / tmp/backup

two。 Change the owner and group of the directory to mssql:

Sudo chown mssql / tmp/backupsudo chgrp mssql / tmp/backup

3. Use the set command of the mssql-conf script to modify the default backup directory:

Sudo / opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir / tmp/backup

4. Restart the SQL Server service:

Sudo systemctl restart mssql-server

Specify core DUMP settings

If an accident occurs to a SQL Server process, SQL Server creates a memory DUMP.

There are two options for controlling the type of memory DUMP collected by SQL Server: coredump.coredumptype and coredump.captureminiandfull. These relate to the two stages of core DUMP capture.

The first phase capture is controlled by the coredump.coredumptype setting, which determines the type of DUMP file generated in the event of an accident. The second phase is enabled when coredump.captureminiandfull is set. If coredump.captureminiandfull is set to true, it is generated by the DUMP file specified by coredump.coredumptype, and a mini DUMP is generated. Set coredump.captureminiandfull to false to disable the second capture attempt.

1. Use the coredump.captureminiandfull setting to determine whether to capture miniature and full DUMP.

Sudo / opt/mssql/bin/mssql-conf set coredump.captureminiandfull

The default value is false

two。 Use coredump.coredumptype settings to specify the type of DUMP file.

Sudo / opt/mssql/bin/mssql-conf set coredump.coredumptype

The default value is miniplus

The following list is a list of possible coredump.coredumptype values:

Types

Description

Mini

Mini is the smallest DUMP file type. It uses Linux system information to determine the threads and modules in the process. DUMP contains only the host environment thread stack and modules. It does not contain indirect memory references or global variables.

Miniplus

Miniplus is similar to mini, but it contains other memory. It understands the inside story of SQLPAL and host environment, adding memory regions to DUMP:

-- various global variables

-- memory above 64TB

-- all named regions found in / proc/$pid/maps

-- indirect memory from thread to stack

-- Thread information

-- related Teb's and Peb's

-- Module information

-- VMM and VAD numbers

Filtered

Filtered uses all memory locations in the subtraction-based design process to be included unless specifically excluded. The design understands the insider and host environment of SQLPAL and excludes specific areas from DUMP.

Full

Full is a complete process located at / proc/$pid/maps DUMP that contains all areas. It is not controlled by coredump.captureminiandfull settings.

Set the default database mail configuration

Sqlpagent.databasemailprofile allows you to set the default database mail configuration for mail alerts.

Sudo / opt/mssq/bin/mssql-conf set sqlagent.databasemailprofile high availability

The hadr.hadrenabled option enables availability groups on the SQL Server instance. The following command enables the availability group by setting hadr.hardenabled to 1. You must restart SQL Server for the configuration to take effect.

Sudo / opt/mssql/bin/mssql-conf set hadr.hadrenabled 1sudo systemctl restart mssql-server

Set up the local audit directory

The telemetry.userrequestedlocalauditdirectory setting enables local auditing, allowing you to locally audit the directory created by the log.

1. Create a target directory for the new local audit log. The following example creates a / tmp/audit directory:

Sudo mkdir / tmp/audit

two。 Change the owner and group to mssql:

Sudo chown mssql / tmp/auditsudo chgrp mssql / tmp/audit

3. For telemetry.userrequestedlocalauditdirectory, run the set command of the mssql-conf script as root:

Sudo / opt/mssql/bin/mssql-conf set telemetry.userrequestedlocalauditdirectory / tmp/audit

4. Restart the SQL Server service:

Sudo systemctl restart mssql-server

Modify SQL Server Localization

The Language.lcid setting modifies SQL Server to be localized to any supported language identity (LCID).

1. The following example modifies localization to French (1036):

Sudo / opt/mssql/bin/mssql-conf set language.lcid 1036

two。 Restart the SQL Server service to apply the changes:

Sudo systemctl restart mssql-server

Set memory limit

The Memory.memorylimitmb setting controls the amount of physical memory available to SQL Server. The default is 80% of physical memory.

1. Run the set command of the mssql-conf script as root on memory.memorylimitmb. The following example modifies the available memory to 3.25GB (3328MB).

Sudo / opt/mssql/bin/mssql-conf set memory.memorylimitmb 3328

two。 Restart the SQL Server service to apply the changes:

Sudo systemctl restart mssql-server

Modify TCP port

The Network.tcpport setting modifies the TCP port on which SQL Server connections listen. By default, the port is set to 1433. Run the following command to modify the port:

1. For network.tcpport, run the set command of the mssql-conf script:

Sudo / opt/mssql/bin/mssql-conf set network.tcpport

two。 Restart the SQL Server service:

Sudo systemctl restart mssql-server

3. When connecting to SQL Server now, you must specify the client port after the hostname or IP address is separated by a comma under the English input method. For example, to use a SQLCMD connection, you need to use the following command:

Sqlcmd-S localhost,-U test-P test

Specify TLS settings

The following options configure TLS for SQL Server instances running on Linux.

Option

Description

Network.forceencryption

If you force all connections to be encrypted for 1Magazine SQL Server. By default, this option is 0.

Network.tlscert

The full path of the certificate used by SQL Server for TLS. For example: / etc/ssl/certs/mssql.pem. The certificate must be accessed through a mssql account. Microsoft recommends using chown mssql:mssql; chmod 400 to restrict access to files.

Network.tlskey

The full path to the private key file used by SQL Server for TLS. For example: / etc/ssl/private/mssql.key. The certificate must be accessed through a mssql account. Microsoft recommends using chown mssql:mssql; chmod 400 to restrict access to files.

Network.tlsprotocols

SQL Server runs a comma-separated list of TLS protocols used. SQL Server always tries to negotiate the strongest allowed protocol. If the client does not support any allowed protocols, SQL Server rejects the connection attempt. For compatibility, all supported protocols are allowed by default (1.2, 1.1, 1.0). If the client supports TLS 1.2, Microsoft recommends that only TLS 1.2 be allowed.

Network.tlsciphers

Specifies the password allowed for TLS,SQL Server. The string must be formatted in OpenSSL's password list format. Usually, you don't need to modify this option.

By default, the following passwords are allowed:

ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA

Network.kerberoskeytabfile

Kerberos keytab file path

Enable / disable trace flags

The Traceflag option enables and disables trace flags for SQL Server service startup. Use the following command to enable / disable trace flags:

1. Use the following command to enable the trace flag. For example, for trace flag 1234:

Sudo / opt/mssql/bin/mssql-conf traceflag 1234 on

two。 You can enable multiple tracking flags by setting them separately:

Sudo / opt/mssql/bin/mssql-conf traceflag 2345 3456 on

3. In a similar manner, you can disable one or more enabled trace flags by setting them and adding the off parameter:

Sudo / opt/mssql/bin/mssql-conf traceflag 1234 2345 3456 off

4. Restart the SQL Server service to apply the changes:

Sudo systemctl restart mssql-server

Remove a setting

To remove the setting that uses mssql-conf set, call mssql-conf with the unset option and setting name. It cleans up settings and sets valid settings as default values.

1. The following example clears the network.tcpport option.

Sudo / opt/mssql/bin/mssql-conf unset network.tcpport

two。 Restart the SQL Server service.

Sudo systemctl restart mssql-server

View current settin

To view any configuration settings, run the following script to output the contents of the mssql.conf file:

Sudo cat / var/opt/mssql/mssql.conf

Note that any settings that use the default values are not displayed in this file.

Mssql.conf format

The following / var/opt/mssql/mssql.conf file provides an example of each setting. You can use this format to manually modify the mssql.conf file as needed. If you modify this file manually, you must restart the SQL Server service to apply. In order to use mssql.conf files in Docker, you must have Docker to persist your data. First add a complete mssql.conf file to your host directory and run the container.

[EULA] accepteula = Y [coredump] captureminiandfull = truecoredumptype = full [filelocation] defaultbackupdir = / var/opt/mssql/data/defaultdatadir = / var/opt/mssql/data/defaultdumpdir = / var/opt/mssql/data/defaultlogdir = / var/opt/mssql/data/ [Hadr] hadrenabled = 0 [language] lcid = 1033 [memory] memorylimitmb = 4096 [network] forceencryption = 0ipaddress = 10.192.0.0kerberoskeytabfile = / var/opt/mssql/secrets/mssql.keytabtcpport = 1401tlscert = / etc/ssl/certs/mssql.pemtlsciphers = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256: AES128-SHA256:AES256-SHA:AES128-SHAtlskey = / etc/ssl/private/mssql.keytlsprotocols = 1.2 traceflag0 1.1 [sqlagent] databasemailprofile = defaulterrorlogfile = / var/opt/mssql/log/sqlagentlog.logerrorlogginglevel = 7 [telemetry] customerfeedback = trueuserrequestedlocalauditdirectory = / tmp/audit [traceflag] traceflag0 = 1204traceflag1 = 2345traceflag = 3456

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.