This guide covers migrating a database from a Windows-based SQL Server 2016 instance to a Linux-based SQL Server 2017 instance. The target environment is a Linux server lacking resources for a dedicated Windows machine.

Installing SQL Server 2017 on Linux

Follow these steps to install SQL Server 2017 on a RHEL 7-based Linux distribution:

1. Add the Microsoft repository: ``` curl https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo > /etc/yum.repos.d/mssql-server.repo

2. Install the package: ``` yum install -y mssql-server

3. Run the setup wizard: ``` sudo /opt/mssql/bin/mssql-conf setup

   
   During setup, select option `2` for the Developer (free) edition. Confirm the end-user license agreement by selecting `Yes`, then set the `sa` password. The service will start automatically after installation.
   

4. Verify the installation: ``` rpm -qa | grep mssql

5. Enable automatic startup: ``` systemctl enable mssql-server

Note: The repository at https://packages.microsoft.com/config/rhel/7/ currently contains only SQL Server 2017 and 2019 repositories. Other versions must be sourced separately.

Enabling SQL Server Agent

SQL Server Agent is disabled by default. Enable it with:

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

Exporting Data from Windows SQL Server 2016

Two recommended methods exist for exporting data:

Method 1: SQL Script Export

SQL Server Management Studio (SSMS) can generate a script containing both schema and data. Use the following steps:

1. Open SSMS and connect to the source server.
2. Right-click the database, select Tasks > Generate Scripts.
3. In the wizard, choose Select specific database objects and pick the required tables.
4. In the Advanced options, set Script for Server Version to SQL Server 2017 and Script Data Types to Schema and Data.
5. Save the script as a .sql file.

This method may produce issues with certain data types or dependencies, so consider Method 2.

Method 2: Binary Backup (.bak) File

Creating a full database backup is often more reliable:

1. In SSMS, right-click the database, select Tasks > Back Up.
2. In the Back Up Database dialog, choose Backup type as Full.
3. Under Destination, specify a file path and name with a .bak extension.
4. Optionally, enable compression: go to Backup Options and check Compress backup. Compression typically reduces file size by 3:1 to 4:1, beneficial for long-term storage.
5. Click OK to create the backup.

Importing Data to Linux SQL Server 2017

Setting File Permissions

Ensure the backup file has correct ownership for the mssql user:

id mssql
chown -R mssql:mssql /path/to/db-20220222/

Restoring the Database

Use SSMS or sqlcmd to restore. If using SSMS:

1. Connect to the Linux SQL Server instance.
2. Right-click Databases, select Restore Database.
3. Choose Device and browse to the .bak file.
4. In Options, uncheck Take tail-log backup before restore to avoid errors like "BACKUP LOG cannot be performed because there is no current database backup."
5. Check Overwrite the existing database (WITH REPLACE) if necessary.
6. Click OK to restore.

Default Data Locations

• Data files: /var/opt/mssql/data
• Executables: /opt/mssql
• Configuration: /var/opt/mssql/mssql.conf

To migrate storage to a custom location, e.g., /data/mssql_data:

1. Set permissions: ``` chown -R mssql:mssql /data/mssql_data

2. Update configuration via mssql-conf: ``` /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /data/mssql_data /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /data/mssql_data

3. Restart the service: ``` systemctl restart mssql-server

   
   The `mssql.conf` file will contain: ```
   [sqlagent]
   enabled = true
   
   [EULA]
   accepteula = Y
   
   [filelocation]
   defaultdatadir = /data/mssql_data
   defaultlogdir = /data/mssql_data
   defaultbackupdir = /data/mssql_data
   

New databases will be created in the new location. Old databases can be removed after migration.

Bulk Exporting SQL Agent Jobs

To export all SQL Agent jobs to a script:

1. In SSMS, navigate to SQL Server Agent > Jobs.
2. Press F7 to open Object Explorer Details.
3. Select multiple jobs (using Ctrl or Shift), right-click, and choose Script Job as > CREATE To > File.

For scheduled backups, consider using Maintenance Plans under Management > Maintenance Plan Wizard.