SQL Server在Debian上的存储解决方案

Installing SQL Server on Debian
To run SQL Server on Debian, you must first add Microsoft’s official repository and install the database engine. Start by updating your system and installing dependencies:

sudo apt update && sudo apt upgrade sudo apt install -y curl gnupg apt-transport-https 

Next, import Microsoft’s GPG key and add the repository (adjust for your Debian version, e.g., debian/12 for Bookworm):

curl https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-archive-keyring.gpg echo "deb [signed-by=/usr/share/keyrings/microsoft-archive-keyring.gpg] https://packages.microsoft.com/debian/12/prod/ stable main" | sudo tee /etc/apt/sources.list.d/mssql-server.list 

Install SQL Server and run the configuration script to set the SA password:

sudo apt update sudo apt install -y mssql-server sudo /opt/mssql/bin/mssql-conf setup 

Finally, install command-line tools (sqlcmd, bcp) for management:

sudo apt install -y mssql-tools 

After installation, verify the service is running:

sudo systemctl status mssql-server 

Optimizing Storage Performance
Though SQL Server is not native to Debian, you can improve storage performance for Linux-based deployments (including SQL Server via containers or VMs) with these steps:

• Filesystem Choice: Use ext4 (stable, widely supported) or XFS (better for large files/high throughput) for SQL Server data directories.
• RAID Configuration: Implement RAID 10 (striping + mirroring) to balance performance and redundancy. Avoid RAID 5/6 for write-intensive workloads due to high write overhead.
• I/O Scheduling: Use the deadline scheduler (default in newer kernels) for balanced latency/throughput or noop (for hypervisors with hardware RAID) to reduce CPU overhead.
• Memory Management: Allocate sufficient RAM to SQL Server (min. 4GB for production) to maximize data caching and reduce disk I/O.
• Query Optimization: Minimize unnecessary data retrieval (use SELECT with specific columns), create indexes on frequently queried columns, and avoid SELECT * to reduce I/O load.

Changing Default Data/Log Directories
By default, SQL Server stores data and logs in /var/opt/mssql/data. To change this (e.g., to a dedicated disk for better performance):

1. Create a new directory (e.g., /home/d/mssql/data) and set ownership to the mssql user:

   sudo mkdir -p /home/d/mssql/data sudo chown mssql:mssql /home/d/mssql/data 

2. Use mssql-conf to update the default paths:

   sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /home/d/mssql/data sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /home/d/mssql/logs 

3. Restart SQL Server to apply changes:

   sudo systemctl restart mssql-server 

This ensures new databases use the specified location without affecting existing ones.

Enabling SSL for Secure Connections
To encrypt data between clients and SQL Server, enable SSL:

1. Obtain an SSL certificate (self-signed for testing or CA-signed for production).
2. Configure SQL Server to use the certificate by updating the mssql.conf file (located in /var/opt/mssql/mssql.conf):

   [network] tls = enabled cert = /path/to/certificate.pem key = /path/to/private.key 

3. Restart SQL Server to apply the configuration:

   sudo systemctl restart mssql-server 

For client connections, use a connection string with encrypt=true and trustServerCertificate=true (for self-signed certs):

jdbc:sqlserver://your_server_ip:1433;encrypt=true;trustServerCertificate=true; 

High Availability Options
For critical workloads, set up high availability using SQL Server Always On Availability Groups (supported on Debian via containers or VMs):

1. Prepare multiple Debian nodes with SQL Server installed and configured.
2. Create a shared storage volume (e.g., NFS, iSCSI) accessible to all nodes for storing transaction logs and backups.
3. Initialize the availability group on the primary node:

   CREATE AVAILABILITY GROUP [YourAGName] WITH (ENCRYPTION ON, LOG_ROTATION_ON_TIMEOUT OFF, MAX_LOG_FILES 2, MAX_LOG_MEMBERS 2, MAX_DATA_FILES 1024); 

4. Add databases to the group and configure secondary replicas:

   ALTER AVAILABILITY GROUP [YourAGName] ADD DATABASE [YourDatabaseName]; ALTER AVAILABILITY GROUP [YourAGName] ADD REPLICA ON 'SecondaryNodeIP' WITH (ENDPOINT_URL = 'TCP://SecondaryNodeIP:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT); 

5. Test failover to ensure redundancy:

   ALTER AVAILABILITY GROUP [YourAGName] FAILOVER; 

This setup ensures automatic failover and minimizes downtime during node failures.