如何在Ubuntu上定制SQL Server功能 - 问答

Installing SQL Server on Ubuntu
Before customizing SQL Server, you need to install it on your Ubuntu system. Start by importing Microsoft’s GPG key to verify package authenticity:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

Next, register the SQL Server repository for your Ubuntu version (e.g., 20.04 or 22.04). For Ubuntu 20.04 and SQL Server 2022:

sudo add-apt-repository "deb [arch=amd64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2022 main"

Update your package list and install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

Run the configuration tool to set the SA password (the system administrator account) and select an edition (Developer, Express, Standard, or Enterprise—Developer is free for non-production use):

sudo /opt/mssql/bin/mssql-conf setup

Start the SQL Server service and enable it to launch at boot:

sudo systemctl start mssql-server
sudo systemctl enable mssql-server

Verify the service is running:

sudo systemctl status mssql-server --no-pager

Customizing Core Functionality

Changing Data/Log Directories

By default, SQL Server stores data and logs in /var/opt/mssql. To change these directories (e.g., to /mnt/sql_data and /mnt/sql_log), use the mssql-conf tool:

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mnt/sql_data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mnt/sql_log

Create the new directories and set ownership to the mssql user (required for SQL Server to access them):

sudo mkdir -p /mnt/sql_data /mnt/sql_log
sudo chown mssql:mssql /mnt/sql_data /mnt/sql_log

Restart the service to apply changes:

sudo systemctl restart mssql-server

Configuring Memory Limits

SQL Server on Linux uses a default memory limit of 80% of physical RAM. To adjust this (e.g., to 4 GB), use the memory.memorylimitmb setting:

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 4096

Restart the service for the change to take effect:

sudo systemctl restart mssql-server

Enabling High Availability with Always On Availability Groups

For high availability, configure SQL Server to use Always On Availability Groups (AGs). First, enable the feature:

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

Enable the AlwaysOn_health extended event session for diagnostics:

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

Create a database master key and certificate for secure communication between replicas. On the primary node:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongMasterKeyPassword';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm_certificate';
BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    ENCRYPTION BY PASSWORD = 'YourStrongPrivateKeyPassword');

Copy the certificate files to secondary nodes and create the certificate on each (replace node2_ip with the actual IP):

scp dbm_certificate.* root@node2_ip:/var/opt/mssql/data/

On secondary nodes, set ownership and create the certificate:

sudo chown mssql:mssql /var/opt/mssql/data/dbm_certificate.*

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongMasterKeyPassword';
CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user 
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = 'YourStrongPrivateKeyPassword');

Managing Users and Permissions

Creating Logins and Database Users

To create a SQL Server login (for SQL authentication), run:

CREATE LOGIN [YourLoginName] WITH PASSWORD = 'YourStrongPassword';

To create a database user linked to the login and assign it to a role (e.g., db_datareader):

USE YourDatabase;
CREATE USER [YourUserName] FOR LOGIN [YourLoginName];
ALTER ROLE [db_datareader] ADD MEMBER [YourUserName];

Encrypting Data

To encrypt a column (e.g., CreditCardNumber in YourTable), follow these steps:

Create a master key (if one doesn’t exist):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword';

Create a certificate to protect the symmetric key:

CREATE CERTIFICATE YourCertificateName WITH SUBJECT = 'Data Encryption Certificate';

Create a symmetric key:

CREATE SYMMETRIC KEY YourKeyName 
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE YourCertificateName;

Encrypt the column data:

OPEN SYMMETRIC KEY YourKeyName DECRYPTION BY CERTIFICATE YourCertificateName;
UPDATE YourTable SET CreditCardNumber = EncryptByKey(Key_GUID('YourKeyName'), CreditCardNumber);
CLOSE SYMMETRIC KEY YourKeyName;

Integrating with Command-Line Tools
Install mssql-tools to use sqlcmd (command-line query tool) and bcp (bulk data transfer utility):

sudo apt-get install -y mssql-tools unixodbc-dev

Add the tools to your PATH by editing /etc/profile (or ~/.bashrc for your user):

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | sudo tee -a /etc/profile
source /etc/profile

Verify the installation:

sqlcmd -V

Connect to SQL Server locally using sqlcmd:

sqlcmd -S localhost -U SA -P 'YourStrongPassword'

Adjusting Locale Settings
To change SQL Server’s locale (e.g., to French), use the language.lcid parameter. For French (LCID 1036):

sudo /opt/mssql/bin/mssql-conf set language.lcid 1036
sudo systemctl restart mssql-server

Enabling Local Audit Logs
To capture audit logs locally (for compliance), set the audit directory using telemetry.userrequestedlocalauditdirectory:

sudo mkdir /tmp/audit
sudo chown mssql:mssql /tmp/audit
sudo /opt/mssql/bin/mssql-conf set telemetry.userrequestedlocalauditdirectory /tmp/audit
sudo systemctl restart mssql-server

This guide covers essential customization tasks for SQL Server on Ubuntu, from installation to advanced features like high availability and encryption. Adjust parameters based on your specific requirements (e.g., memory limits, locale, or encryption needs).

0 赞

0 踩