Migrate SQL Server database on Windows to SQL Server on Linux(RedHat 7)

After SQL Server VNext CTP release on Linux, one of the usecase is to migrate SQL Server database on Windows Server to SQL Server on Linux. This blog is to explain steps on migrating SQL Server windows to Linux.

Environment
Source Server Version : Windows 10, SQL Server 2016
Destination Server Version : RedHat Linux 7.3, SQL Server VNext (2017) CTP 2.1

SQL Server Backup and Restore method is one of the easiest and better way to migrate from Windows to Linux.

Backup from Windows

We dont need any additional steps for taking backup for Linux. Basic TSQL Backup command is good enough as below:

BACKUP DATABASE [TestMigration] 
	TO  DISK = N'C:\VirtualMachines\VMShare\TestMigration.bak'  --Provide your path to take backupname
	WITH NOFORMAT, NOINIT,  
	NAME = N'TestMigration-Full Database Backup', SKIP, 
	NOREWIND, NOUNLOAD,  STATS = 10

After successful execution of backup script, we need to move this backup file to linux server. I used ftp to move windows database backup to Linux server as below. After moving to ftp share, we can access ftp share from linux as below

$ ftp ipaddress
ftp> get TestMigration.bak
Restoring Database in Linux

Restore TSQL command is the same approach to restore database into Linux Server

RESTORE DATABASE [TestMigration] FROM  DISK = N'/var/opt/mssql/data/TestMigration.bak' 
	WITH MOVE 'TestMigration' TO '/var/opt/mssql/data/TestMigration.mdf',
	MOVE 'TestMigration_Log' TO '/var/opt/mssql/data/TestMigration_Log.ldf'
Points to consider
  1. We need to provide correct folder structure as in linux server. In this example I used /var/opt/mssql/data
  2. Providing correct logical file name as in the backup database
  3. We can execute this command by using SQLCMD in Linux or connecting SQL Server on Linux on SQL Server Management Studio or Visual Studio Code.

These simple steps are enough to migrate a windows SQL Server database to Linux.

Advertisements

How to stop and start SQL Server service on Red Hat Linux

Sql Server 2017/VNext CTP versions released on Linux servers. Here we go thru how to stop and start a SQL Server service in RH linux server.

Checking Service status

To check status of sql Server service we can use systemctl command as below:

$ systemctl status mssql-server

This shows the current running status of SQL Server. Currently SQL Server is loaded and not running and hence it shows ‘inactive’ status as below:

LinuxInactive

Starting SQL Server on Linux

To start the service we need to use same systemctl with start keyword as below:

$ systemctl start mssql-server

This step asks user to provide password for authentication and then it starts the service:

LinuxStart

We need to check the status as to see whether Service is started. For that we can use the same systemctl along with status keyword

Linux

Stopping SQL Server on Linux

Similarly to stop SQL Server service we need to use systemctl with stop keyword as below:

$ systemctl stop mssql-server

We can use status command to verify the service status.