Nov 20, 2016

How to Migrate Databases to SQL Server on Red Hat or Ubuntu Linux


You can migrate your databases and data to SQL Server vNext CTP1 running on Linux. The method you choose to use depends on the source data and your specific scenario. The following sections provide best practices for various migration scenarios.






Migrate from SQL Server on Windows

If you want to migrate SQL Server databases on Windows to SQL Server vNext on Linux, the recommended technique is to use SQL Server backup and restore.

  1. Create a backup of the database on the Windows machine.
  2. Transfer the backup file to the target SQL Server Linux machine.
  3. Restore the backup on the Linux machine.
It is also possible to export your database to a BACPAC file (a compressed file that contains your database schema and data). If you have a BACPAC file, you can transfer this file to your Linux machine and then import it to SQL Server.

Migrate from other database servers

You can migrate databases on other database systems to SQL Server vNext on Linux. This includes Microsoft Access, DB2, MySQL, Oracle, and Sybase databases. In this scenario, use the SQL Server Management Assistant (SSMA) to automate the migration to SQL Server on Linux.

Migrate structured data

There are also techniques for importing raw data. You might have structured data files that were exported from other databases or data sources. In this case, you can use the bcp tool to bulk insert the data. Or you can run SQL Server Integration Services on Windows to import the data into a SQL Server database on Linux. SQL Server Integration Services enables you to run more complex transformations on the data during the import.


Restore a SQL Server database from Windows to Linux

SQL Server's backup and restore feature is the recommended way to migrate a database from SQL Server on Windows to SQL Server vNext CTP1 on Linux. This topic provides step-by-step instructions for this technique. In this tutorial, you will:

  • Download the AdventureWorks backup file on a Windows machine
  • Transfer the backup to your Linux machine
  • Restore the database using Transact-SQL commands
Note
This tutorial assumes that you have installed SQL Server vNext CTP1 and the SQL Server Tools on your target Linux server.

Download the AdventureWorks database backup

Although you can use the same steps to restore any database, the AdventureWorks sample database provides a good example. It comes as an existing database backup file.
Note
To restore a database to SQL Server on Linux, the source backup must be taken from SQL Server 2014 or SQL Server 2016. The backup SQL Server build number must not be greater than the restore SQL Server build number.
On your Windows machine, go to 

https://msftdbprodsamples.codeplex.com/downloads/get/880661 and download the Adventure Works 2014 Full Database Backup.zip.

Tip
Although this tutorial demonstrates backup and restore between Windows and Linux, you could also use a browser on Linux to directly download the AdventureWorks sample to your Linux machine.
Open the zip file, and extract the AdventureWorks2014.bak file to a folder on your machine.

Transfer the backup file to Linux

To restore the database, you must first transfer the backup file from the Windows machine to the target Linux machine.

  1. For Windows, install a Bash shell. There are several options, including the following:
  2. Open a Bash shell (terminal) and navigate to the directory containing AdventureWorks2014.bak.
  3. Use the scp (secure copy) command to transfer the file to the target Linux machine. The following example transfers AdventureWorks2014.bak to the home directory of user1 on the server named linuxserver1.
sudo scp AdventureWorks2014.bak user1@linuxserver1:./
In the previous example, you could instead provide the IP address in place of the server name.
There are several alternatives to using scp. One is to use Samba to setup an SMB network share between Windows and Linux. For a walkthrough on Ubuntu, see How to Create a Network Share Via Samba. Once established, you can access it as a network file share from Windows, such as \\machinenameorip\share.

Move the backup file

At this point, the backup file is on your Linux server. Before restoring the database to SQL Server, you must place the backup in a subdirectory of /var/opt/mssql.

Open a Terminal on the target Linux machine that contains the backup.
Enter super user mode.
    sudo su
    Create a new backup directory. The -p parameter does nothing if the directory already exists.

    mkdir -p /var/opt/mssql/backup
    Move the backup file to that directory. In the following example, the backup file resides in the home directory of user1. Change the command to match the location of AdventureWorks2014.bak on your machine.

    mv /home/user1/AdventureWorks2014.bak /var/opt/mssql/backup/
    Exit super user mode.

    exit

    Restore the database backup

    To restore the backup, you can use the RESTORE DATABASE Transact-SQL (TQL) command.
    Note
    The following steps use the sqlcmd tool. If you haven’t install SQL Server Tools, see Install SQL Server on Linux.
    In the same terminal, launch sqlcmd. The following example connects to the local SQL Server instance with the SA user. Enter the password when prompted or specify the password with the -P parameter.

    sqlcmd -S localhost -U SA
    After connecting, enter the following RESTORE DATABSE command, pressing ENTER after each line. The example below restores the AdventureWorks2014.bak file from the /var/opt/mssql/backup directory.

    RESTORE DATABASE AdventureWorks
    FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
    WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
    MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
    GO
    You should get a message the database is successfully restored.

    Verify the restoration by first changing the context to the AdventureWorks database.

    USE AdventureWorks
    GO
    Run the following query that lists the top 10 products in the Production.Products table.

    SELECT TOP 10 Name, ProductNumber FROM Production.Product ORDER BY Name
    GO

    Export and import a database on Linux with sqlpackage

    This topic shows how to the sqlpackage tool to export and import databases to SQL Server vNext CTP1 on Linux.

    Overview

    The sqlpackage command line utility can be used to perform a number of operations on databases such as:
    • export a database (database schema and user data) to a .bacpac file
    • import a database (database schema and/or user data) from a .bacpac file
    • create a database snapshot (.dacpac) file for a database
    • incrementally update a database schema to match the schema of a source .dacpac file
    • create a Transact-SQL incremental update script that updates the schema of a target database to match the schema of a source database
    You can use the sqlpackage command line utility with Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and from Azure SQL Database. The sqlpackage command line utility is particularly useful when you wish to import a database from Azure SQL Database to SQL Server on Linux or vice-versa.

    sqlpackage location

    The sqlpackage command line utility is in the following location after you install SQL Server on Linux. Running sqlpackage with no command line parameters displays help about the available parameters:

    /opt/mssql/bin/sqlpackage
    You can specify command line parameters for a number of actions along with action specific parameters and properties as follows:

    /opt/mssql/bin/sqlpackage {parameters}{properties}{SQLCMD Variables} 
    /opt/mssql/bin/sqlpackage /Action:Extract /SourceServerName:tcp: /SourceDatabaseName: /TargetFile:
    /SourceUser: /SourcePassword:
    Command line parameters can be specified using their short forms:

    /opt/mssql/bin/sqlpackage /a:Export /ssn:tcp: /sdn: /tf:
    /su: /sp:
    Export a database (schema and user data) to a .BACPAC file

    Use the following command to export database schema and user data to a .BACPAC file:

    /opt/mssql/bin/sqlpackage /a:Export /ssn:tcp: /sdn: /su: /sp: /tf:

    Import a database (schema and user data) from a .BACPAC file

    Use the following command to import database schema and user data from a .BACPAC file:

    /opt/mssql/bin/sqlpackage /a:Import /tsn:tcp: /tdn: /tu: /tp: /sf:






    Export and import a database on Linux with SSMS

    This topic shows how to use SQL Server Management Studio (SSMS) to export and import a database on SQL Server vNext CTP1 on Linux. SSMS is a Windows application, so use SSMS when you have a Windows machine that can connect to a remote SQL Server instance on Linux.

    You should always install and use the most recent version of SQL Server Management Studio (SSMS) as described in Use SSMS on Windows to connect to SQL Server on Linux

    Export a database with SSMS

    • Start SSMS by typing Microsoft SQL Server Management Studio in the Windows search box, and then click the desktop app.
    SQL Server Management Studio

    • Connect to your source database in Object Explorer. The source database can be in Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and Azure SQL Database or Azure SQL Data Warehouse.
    • Right-click the source database in the Object Explorer, point to Tasks, and click Export Data-Tier Application...
    • In the export wizard, click Next, and then on the Settings tab, configure the export to save the BACPAC file to either a local disk location or to an Azure blob.
    • By default, all objects in the database are exported. Click the Advanced tab and choose the database objects that you wish to export.
    • Click Next and then click Finish.
    The *.BACPAC file is successfully created at the location you chose and you are ready to import it into a target database.

    Import a database with SSMS


    • Start SSMS by typing Microsoft SQL Server Management Studio in the Windows search box, and then click the desktop app.
    SQL Server Management Studio

    • Connect to your target server in Object Explorer. The target server can be Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and Azure SQL Database or Azure SQL Data Warehouse.
    • Right-click the Databases folder in the Object Explorer and click Import Data-tier Application...
    • To create the database in your target server, specify a BACPAC file from your local disk or select the Azure storage account and container to which you uploaded your BACPAC file.
    • Provide the New database name for the database. If you are importing a database on Azure SQL Database, set the Edition of Microsoft Azure SQL Database (service tier), Maximum database size, and Service Objective (performance level).
    • Click Next and then click Finish to import the BACPAC file into a new database in your target server.
    The *.BACPAC file is imported to create a new database in the target server you specified.

    Use SSMA to migrate databases to SQL Server on Linux

    This topic introduces SQL Server Migration Assistant (SSMA) that helps you easily migrate databases to SQL Server vNext CTP1 on Linux from Microsoft Access, DB2, MySQL, Oracle and Sybase. SSMA is a Windows application, so use SSMA when you have a Windows machine that can connect to a remote SQL Server instance on Linux.

    SSMA supports a variety of source databases including Oracle, MySQL, Sybase, DB2 and Microsoft Access to SQL Server vNext CTP1 on Linux and helps automate migration tasks such as:
    • assess your source database
    • convert the source database schema to Microsoft SQL Server schema
    • migrate the schema
    • migrate the data
    • test the migration
    To get started, download SQL Server Migration Assistant (SSMA) for your source database from the list below:

    Bulk copy data with bcp to SQL Server on Linux

    This topic shows how to use the bcp command line utility to bulk copy data between an instance of SQL Server vNext CTP1 on Linux and a data file in a user-specified format.

    You can use bcp to import large numbers of rows into SQL Server tables or to export data from SQL Server tables into data files. Except when used with the queryout option, bcp requires no knowledge of Transact-SQL. The bcp command line utility works with Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and Azure SQL Database and Azure SQL Data Warehouse.

    This topic will show you how to:
    • Import data into a table using the bcp in command
    • Export data from a table uisng the bcp out command

    Install the SQL Server command-line tools

    bcp is part of the SQL Server command-line tools, which are not installed automatically with SQL Server on Linux. If you have not already installed the SQL Server command-line tools on your Linux machine, you must install them.

    Import data with bcp

    In this tutorial, you will create a sample database and table on the local SQL Server instance (localhost) and then use bcp to load into the sample table from a text file on disk.

    Create a sample database and table

    Let's start by creating a sample database with a simple table that will be used in the rest of this tutorial.

    1. On your Linux box, open a command terminal.
    2. Copy and paste the commands below into the terminal window. These commands use the sqlcmd command line utility to create a sample database (BcpSampleDB) and a table (TestEmployees) on the local SQL Server instance (localhost). Remember to replace the username and  as necessary before running the commands.
    Create the database BcpSampleDB:

    sqlcmd -S localhost -U sa -P  -Q "CREATE DATABASE BcpSampleDB;"
    

    Create the table TestEmployees in the database BcpSampleDB:

    sqlcmd -S localhost -U sa -P  -d BcpSampleDB -Q "CREATE TABLE TestEmployees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"


    Create the source data file

    Copy and paste the command below in your terminal window. We will use the built-in cat command to create a sample text data file with 3 records save the file in your home directory as ~/test_data.txt. The fields in the records are delimited by a comma.

    cat > ~/test_data.txt << EOF
    1,Jared,Australia
    2,Nikita,India
    3,Tom,Germany
    EOF
    You can verify that the data file was created correctly by running the command below in your terminal window:

    cat ~/test_data.txt
    

    This should display the following in your terminal window:

    1,Jared,Australia
    2,Nikita,India
    3,Tom,Germany

    Import data from the source data file

    Copy and paste the commands below into the terminal window. This command uses bcp to connect to the local SQL Server instance (localhost) and import the data from the data file (~/test_data.txt) into the table (TestEmployees) in the database (BcpSampleDB). Remember to replace the username and  as necessary before running the commands.

    bcp TestEmployees in ~/test_data.txt -S localhost -U sa -P  -d BcpSampleDB -c -t  ','
    

    Here's a brief overview of the command line parameters we used with bcp in this example:

    • -S: specifies the instance of SQL Server to which to connect
    • -U: specifies the login ID used to connect to SQL Server
    • -P: specifies the password for the login ID
    • -d: specifies the database to connect to
    • -c: performs operations using a character data type
    • -t: specifies the field terminator. We are using comma as the field terminator for the records in our data file
    Note
    We are not specifying a custom row terminator in this example. Rows in the text data file were correctly terminated with newline when we used the cat command to create the data file earlier.
    You can verify that the data was successfully imported by running the command below in your terminal window. Remember to replace the username and  as necessary before running the command.

    sqlcmd -S localhost -d BcpSampleDB -U sa -P  -I -Q "SELECT * FROM TestEmployees;"
    

    This should display the following results:

    Id          Name                Location
    ----------- ------------------- -------------------
              1 Jared               Australia
              2 Nikita              India
              3 Tom                 Germany
    
    (3 rows affected)

    Export data with bcp

    In this tutorial, you will use bcp to export data from the sample table we created earlier to a new data file.

    Copy and paste the commands below into the terminal window. These commands use the bcpcommand line utility to export data from the table TestEmployees in the in the database BcpSampleDB to a new data file called ~/test_export.txt. Remember to replace the username and  as necessary before running the command.

    bcp TestEmployees out ~/test_export.txt -S localhost -U sa -P  -d BcpSampleDB -c -t ','
    

    You can verify that the data was exported correctly by running the command below in your terminal window:

    cat ~/test_export.txt
    

    This should display the following in your terminal window:

    1,Jared,Australia
    2,Nikita,India
    3,Tom,Germany







    Backup and restore SQL Server databases on Linux

    You can take backups of databases from SQL Server vNext CTP1 on Linux with the same tools as other platforms. On a Linux server, you can use sqlcmd to connect to the SQL Server and take backups. From Windows, you can connect to SQL Server on Linux and take backups with the user interface. The backup functionality is the same across platforms.

    Backup with sqlcmd

    In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb.

    sqlcmd -H localhost -U SA -Q "BACKUP DATABASE [demodb] TO DISK = N'var/opt/mssql/data/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
    

    When you run the command, SQL Server will prompt for a password. After you enter the password, the shell will return the results of the backup progress. For example:

    Password:
    10 percent processed.
    21 percent processed.
    32 percent processed.
    40 percent processed.
    51 percent processed.
    61 percent processed.
    72 percent processed.
    80 percent processed.
    91 percent processed.
    Processed 296 pages for database 'demodb', file 'demodb' on file 1.
    100 percent processed.
    Processed 2 pages for database 'demodb', file 'demodb_log' on file 1.
    BACKUP DATABASE successfully processed 298 pages in 0.064 seconds (36.376 MB/sec).

    Backup log with sqlcmd

    In the following example, sqlcmd connects to the local SQL Server instance and takes a tail-log backup. After the tail-log backup completes, the database will be in a restoring state.

    sqlcmd -H localhost -U SA -Q "BACKUP LOG [demodb] TO  DISK = N'var/opt/mssql/data/demodb_LogBackup_2016-11-14_18-09-53.bak' WITH NOFORMAT, NOINIT,  NAME = N'demodb_LogBackup_2016-11-14_18-09-53', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5"
    

    Restore with sqlcmd

    In the following example sqlcmd connects to the local instance of SQL Server and restores a database.

    sqlcmd -H localhost -U SA -Q "RESTORE DATABASE [demodb] FROM  DISK = N'var/opt/mssql/data/demodb.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5"
    

    Backup and Restore with SQL Server Management Studio (SSMS)

    You can use SSMS from a Windows computer to connect to a Linux database and take a backup through the user-interface.

    Note
    Use the latest version of SSMS to connect to SQL Server. To download and install the latest version, see Download SSMS.
    The following steps walk through taking a backup with SSMS.

    1. Start SSMS and connect to your server in SQL Server vNext CTP1 on Linux.
    2. In Object Explorer, right-click on your database, Click Tasks, and then click Back Up....
    3. In the Backup Up Database dialog, verify the parameters and options, and click OK.
    SQL Server completes the database backup.

    Restore with SQL Server Management Studio (SSMS)

    The following steps walk you through restoring a database with SSMS.
    1. In SSMS right-click Databases and click Restore Databases....
    2. Under Source click Device: and then click the ellipses (...).
    3. Locate your database backup file and click OK.
    4. Under Restore plan, verify the backup file and settings. Click OK.
    5. SQL Server restores the database.


    Conclusion

    The above steps covered migration, backup and restore process of databases to SQL Server you have installed and managed on Red Hat or Ubuntu Linux

    Post a Comment

     
    TECH SUPPORT © 2012 - Designed by INFOSBIRD