Ubuntu上SQL Server的数据库迁移

Migrating SQL Server Databases to Ubuntu: A Step-by-Step Guide

Migrating a SQL Server database to Ubuntu involves transferring data from a source server (Windows or another Ubuntu instance) to a target Ubuntu server running SQL Server. Below are the most effective methods, detailed steps, and best practices to ensure a smooth migration.

Preparation

Before starting, complete these prerequisites to avoid errors:

1. Install SQL Server on Ubuntu:

   • Update packages: sudo apt-get update.
   • Import Microsoft’s GPG key: curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc.
   • Register the SQL Server repository (adjust for your Ubuntu version, e.g., 22.04):

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

   • Install SQL Server: sudo apt-get install -y mssql-server.
   • Configure the instance: Run sudo /opt/mssql/bin/mssql-conf setup, set the SA password (follow SQL Server’s complexity rules), and complete the setup.
   • Start the service: sudo systemctl start mssql-server and enable auto-start: sudo systemctl enable mssql-server.

2. Install Command-Line Tools:
   SQL Server tools (sqlcmd, bcp) are required for command-line operations. Install them using:

   sudo apt-get install -y mssql-tools unixodbc-dev
   echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
   source ~/.bashrc
   

   Verify installation: sqlcmd -S localhost -U SA -P '' -Q "SELECT @@VERSION".

3. Assess the Source Database:

   • Check database size (e.g., using sp_spaceused in SSMS).
   • Identify dependencies (stored procedures, triggers, users).
   • Document the schema and data structure for validation later.

Method 1: Backup and Restore (Full Database Migration)

This is the most common method for full database migration, suitable for most scenarios.

Step 1: Backup the Source Database

• On Windows (SSMS):
  1. Connect to the source SQL Server instance in SSMS.
  2. Right-click the database → Tasks → Backup.
  3. Select Full backup type, specify a path (e.g., D:\backups\MyDB.bak), and click OK.
• On Linux (Command Line):
  Use sqlcmd to create a backup:

  sqlcmd -S localhost -U SA -P '' -Q "BACKUP DATABASE [MyDB] TO DISK = N'/var/opt/mssql/backup/MyDB.bak' WITH INIT, STATS = 10"
  

  Replace [MyDB] with your database name and adjust the path.

Step 2: Transfer the Backup File to Ubuntu

Use scp (secure copy) to transfer the .bak file from the source to the Ubuntu server:

scp username@source_server:/path/to/MyDB.bak /home/ubuntu/backup/

Replace username, source_server, and paths with actual values.

Step 3: Restore the Database on Ubuntu

1. Create a new database (optional but recommended):

   sqlcmd -S localhost -U SA -P '' -Q "CREATE DATABASE [MyDB_New]"
   

2. Restore the backup using RESTORE DATABASE with MOVE to relocate data/log files (adjust paths as needed):

   sqlcmd -S localhost -U SA -P '' -Q "RESTORE DATABASE [MyDB_New] FROM DISK = N'/home/ubuntu/backup/MyDB.bak' WITH MOVE 'MyDB_Data' TO N'/var/opt/mssql/data/MyDB_New.mdf', MOVE 'MyDB_Log' TO N'/var/opt/mssql/data/MyDB_New_log.ldf', STATS = 10, REPLACE, RECOVERY"
   

   • REPLACE: Overwrites an existing database with the same name.
   • RECOVERY: Brings the database online after restore.

3. Verify the restore:

   sqlcmd -S localhost -U SA -P '' -d MyDB_New -Q "SELECT TOP 10 * FROM YourTableName"
   

   Replace YourTableName with a table from your database.

Method 2: Using bcp for Bulk Data Migration

For large databases or incremental migrations, bcp (Bulk Copy Program) is efficient for exporting/importing data in CSV or native formats.

Step 1: Export Data from the Source

• On Windows (SSMS):
  Use the Import/Export Wizard (right-click database → Tasks → Export Data) to export tables to CSV files.
• On Linux (Command Line):
  Use bcp to export a table to a CSV file:

  bcp MyDB.dbo.MyTable out /home/ubuntu/export/MyTable.csv -c -t, -S localhost -U SA -P ''
  

  • -c: Uses character data type.
  • -t,: Sets the field terminator to a comma.

Step 2: Transfer Exported Files to Ubuntu

Use scp to copy CSV files to the target Ubuntu server (if exported from Windows).

Step 3: Import Data to Ubuntu SQL Server

Use bcp to import the CSV file into the target database:

bcp MyDB_New.dbo.MyTable in /home/ubuntu/export/MyTable.csv -c -t, -S localhost -U SA -P '' -e /home/ubuntu/export/error.log

• -e: Logs errors to a file (useful for troubleshooting).
• Add -F 2 to skip the header row if your CSV has one.

Step 4: Validate Data

Run queries on the target database to compare record counts or sample data:

sqlcmd -S localhost -U SA -P '' -d MyDB_New -Q "SELECT COUNT(*) FROM MyTable"

Ensure the count matches the source table.

Method 3: Using SQL Server Integration Services (SSIS)

For complex migrations (schema transformations, data cleansing, automation), use SSIS (SQL Server Data Tools).

Steps:

1. Create an SSIS Project:
   Open Visual Studio, create a new Integration Services Project, and design a package.
2. Add Data Flow Tasks:
   • Use OLE DB Source to connect to the source database.
   • Use OLE DB Destination to connect to the Ubuntu SQL Server (configure the connection string with Ubuntu’s IP/hostname).
   • Add transformations (e.g., Derived Column, Lookup) if needed.
3. Execute and Deploy:
   Run the package in SSDT to test. Deploy it to the SSIS Catalog (on Windows) or a Linux-based SSIS runtime (if available).
4. Schedule (Optional):
   Use SQL Server Agent (Windows) or cron (Ubuntu) to automate the package execution.

SSIS is ideal for large-scale migrations with complex requirements but requires familiarity with the tool.

Post-Migration Validation

After migration, perform these checks to ensure success:

1. Data Integrity:
   Compare row counts, sample records, and checksums (e.g., CHECKSUM TABLE in MySQL—SQL Server uses HASHBYTES) between source and target databases.
2. Schema Consistency:
   Use INFORMATION_SCHEMA or third-party tools (e.g., Redgate SQL Compare) to verify tables, columns, indexes, and constraints.
3. Application Testing:
   Update the application’s connection string to point to the Ubuntu SQL Server (e.g., Server=ubuntu_ip;Database=MyDB_New;User Id=sa;Password=;).
   Test all CRUD operations (Create, Read, Update, Delete) and business logic.
4. Performance Testing:
   Monitor query performance using sys.dm_exec_query_stats or tools like SQL Server Profiler. Optimize indexes if needed.

Troubleshooting Common Issues

• Permission Denied: Ensure the SA user has sufficient permissions on the target server. Use ALTER LOGIN SA ENABLE if the account is disabled.
• File Path Errors: Verify that the paths in RESTORE DATABASE (data/log files) exist and are writable by the mssql user.
• Version Compatibility: Migrate to a SQL Server version on Ubuntu that is equal to or higher than the source version (e.g., SQL Server 2019 on Windows can migrate to SQL Server 2022 on Ubuntu).
• Connection Failures: Check firewall rules (allow port 1433), network connectivity (ping the Ubuntu server), and SQL Server configuration (sudo /opt/mssql/bin/mssql-conf set network.tcpport 1433).

By following these methods and best practices, you can successfully migrate SQL Server databases to Ubuntu while minimizing downtime and ensuring data integrity.