Ubuntu上SQL Server怎么配置

Installing SQL Server on Ubuntu
To begin, import the Microsoft 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, 22.04). For Ubuntu 20.04:

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

Update the package list and install the mssql-server package:

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

Run the configuration wizard to set the SA password (must be at least 8 characters with uppercase, lowercase, digits, and/or symbols) and select an edition (Evaluation, Developer, or Express are free):

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

Verify the SQL Server service is running:

systemctl status mssql-server --no-pager

If remote access is needed, open the default TCP port (1433) in the firewall:

sudo ufw allow 1433/tcp

Installing SQL Server Command-Line Tools
To manage SQL Server locally or remotely, install sqlcmd (command-line query tool) and bcp (bulk data transfer tool):

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo apt-get install -y mssql-tools unixodbc-dev

Add the tools to your PATH for easy access (persistent across sessions):

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
source ~/.bash_profile

Connecting to SQL Server
Use sqlcmd to connect locally with the SA account:

sqlcmd -S localhost -U SA

Enter the SA password when prompted. You’ll enter the SQL command-line interface, where you can run queries (e.g., SELECT @@VERSION;) or type QUIT to exit.

Disabling the SA Account (Security Best Practice)
The SA account is a well-known administrative account; disable it after creating a new admin user. First, create a new login (e.g., admin_user) and add it to the sysadmin role:

CREATE LOGIN admin_user WITH PASSWORD = 'StrongPassword123!';
ALTER SERVER ROLE sysadmin ADD MEMBER admin_user;

Exit sqlcmd (QUIT) and reconnect with the new account:

sqlcmd -S localhost -U admin_user -P StrongPassword123!

Finally, disable the SA account:

ALTER LOGIN SA DISABLE;

Post-Installation Optimization

• Enable SQL Server Agent: The agent automates tasks like backups. Enable it with:

  sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
  sudo systemctl restart mssql-server
  

• Adjust Data/Log Directories: For better performance, move default directories to faster storage (e.g., SSD). Create a directory (e.g., /mnt/sql_data) and set ownership to the mssql user:

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

  Update the configuration to use the new directory (repeat for logs if needed):

  sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mnt/sql_data
  sudo systemctl restart mssql-server