Ubuntu如何管理SQL Server数据库

Installing SQL Server on Ubuntu
Before installing SQL Server, ensure your Ubuntu system meets the prerequisites: at least 2GB of RAM (4GB+ recommended), 6GB of disk space, and an x64 processor with 2+ cores. To install SQL Server, follow these steps:

1. 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
   

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

   sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"
   

3. Update package lists and install the mssql-server package:

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

4. Configure SQL Server by running the setup wizard. Set a strong SA password (must meet complexity requirements: 8+ characters, 3+ of uppercase, lowercase, digits, or symbols) and choose an edition (Evaluation, Developer, or Express are free):

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

5. Verify the service status to ensure SQL Server started successfully:

   systemctl status mssql-server --no-pager
   

For detailed instructions, refer to Microsoft’s official guide.

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

sudo su
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
echo "deb [arch=amd64 signed-by=/usr/share/keyrings/microsoft-prod.gpg] https://packages.microsoft.com/ubuntu/20.04/prod focal main" | sudo tee /etc/apt/sources.list.d/mssql-tools.list
exit
sudo apt-get update
sudo apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Verify the installation by running sqlcmd -?; you should see the tool’s help output.

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

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

Replace YourStrongPassword with the SA password you set during installation. To connect remotely, replace localhost with the server’s IP address and ensure the SQL Server TCP port (default: 1433) is open in your firewall.

Creating and Managing Databases
Once connected to sqlcmd, you can run T-SQL commands to manage databases. For example:

• Create a database:

  CREATE DATABASE TestDB;
  GO
  

• List all databases:

  SELECT name FROM sys.databases WHERE dbid > 4; -- Excludes system databases
  GO
  

• Switch to a database:

  USE TestDB;
  GO
  

• Create a table and insert data:

  CREATE TABLE Employees (ID INT PRIMARY KEY, Name NVARCHAR(50), Department NVARCHAR(50));
  INSERT INTO Employees VALUES (1, 'John Doe', 'IT'), (2, 'Jane Smith', 'HR');
  GO
  

• Query data:

  SELECT * FROM Employees;
  GO
  

Type EXIT to quit sqlcmd.

Configuring SQL Server
Key configuration tasks include:

• Changing the TCP port: If remote access is needed, modify the default port (1433) using:

  sudo /opt/mssql/bin/mssql-conf set network.tcpport 1433
  sudo systemctl restart mssql-server
  

• Updating data/log directories: Move default directories to a custom location (e.g., /home/d/mssql/data) for better storage management:

  sudo mkdir -p /home/d/mssql/data
  sudo chown mssql:mssql /home/d/mssql/data
  sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /home/d/mssql/data
  sudo systemctl restart mssql-server
  

• Enabling SQL Server Agent: For scheduled tasks (e.g., backups), enable the agent:

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

These changes require a service restart to apply.

Backing Up and Restoring Databases
Regular backups are critical to prevent data loss. Use sqlcmd to back up and restore databases:

• Back up a database (e.g., TestDB) to a file:

  BACKUP DATABASE TestDB TO DISK = '/var/opt/mssql/backup/TestDB.bak';
  GO
  

• Restore a database from a backup file:

  RESTORE DATABASE TestDB FROM DISK = '/var/opt/mssql/backup/TestDB.bak'
  WITH MOVE 'TestDB_Data' TO '/var/opt/mssql/data/TestDB.mdf',
       MOVE 'TestDB_Log' TO '/var/opt/mssql/data/TestDB.ldf';
  GO
  

Verify backups by listing files in the backup directory (/var/opt/mssql/backup) or querying the msdb database.

Security Best Practices

• Change the SA password regularly: Use strong, complex passwords and avoid default credentials.
• Disable the SA account when not in use (or immediately after setting up):

  ALTER LOGIN SA DISABLE;
  GO
  

• Use SQL Server Authentication or Active Directory: Avoid using SA for daily operations; create dedicated accounts with least-privilege permissions.
• Restrict firewall access: Only allow trusted IP addresses to access the SQL Server port (1433).
• Keep SQL Server updated: Regularly check for and install updates to patch vulnerabilities.