Ubuntu如何配置SQL Server环境

Installing SQL Server on Ubuntu
Before installing SQL Server, ensure your Ubuntu system is updated:

sudo apt update && sudo apt upgrade -y

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

Register the SQL Server repository for your Ubuntu version (replace 20.04 with your distribution version if different):

curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

Update the package list and install SQL Server:

sudo apt update
sudo apt install -y mssql-server

Run the configuration tool to set the SA password (must meet complexity requirements: 8+ characters with uppercase, lowercase, digits, 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:

sudo systemctl status mssql-server --no-pager

Enable automatic startup on boot:

sudo systemctl enable mssql-server

Installing SQL Server Command-Line Tools
To interact with SQL Server via the command line, install mssql-tools (includes sqlcmd and bcp):

sudo apt install -y mssql-tools unixodbc-dev

Add the tools to your PATH for easy access (update ~/.bash_profile for login sessions or ~/.bashrc for interactive sessions):

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

Configuring Remote Access (Optional)
If you need to connect to SQL Server from another machine, open the default TCP port (1433) in the firewall:

sudo ufw allow 1433/tcp

Ensure your network allows inbound traffic on this port.

Connecting to SQL Server Locally
Use sqlcmd to connect to the local instance with the SA account:

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

Press Enter to enter the SQL command prompt (1>).

Basic SQL Commands
Create a new database:

CREATE DATABASE TestDB;
GO

List all databases:

SELECT Name FROM sys.Databases;
GO

Switch to the new database:

USE TestDB;
GO

Create a table and insert data:

CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT);
INSERT INTO Inventory VALUES (1, 'Laptop', 10), (2, 'Mouse', 20);
GO

Query the table:

SELECT * FROM Inventory;
GO

Exit sqlcmd:

QUIT

Security Best Practices

1. Disable the SA Account: Immediately create a new sysadmin login and disable SA after initial setup.

   -- Create a new login (replace 'YourPassword' with a strong password)
   CREATE LOGIN AdminUser WITH PASSWORD = 'YourPassword';
   ALTER SERVER ROLE sysadmin ADD MEMBER AdminUser;
   -- Disable SA
   ALTER LOGIN SA DISABLE;
   

2. Use Strong Passwords: Enforce complex passwords for all accounts.
3. Limit Network Exposure: Only allow trusted IPs to access the SQL Server port (1433).
4. Regular Updates: Keep SQL Server and Ubuntu packages updated to patch vulnerabilities.

For advanced configurations (e.g., performance tuning, backups), refer to the official Microsoft documentation.