Data is growing exponentially, and organizations are constantly seeking innovative solutions to handle the challenges of big data. One such solution is NuoDB, an elastically scalable SQL database that can run on various platforms. In this blog post, we will explore how to migrate data from SQL Server to NuoDB using a simple three-step approach.

Step 1: Build a Sample Database

Before we begin the migration process, let’s create a sample database in SQL Server. Here’s an example of how to create a sample table:

CREATE DATABASE [Test];
CREATE TABLE [Department] (
    [DepartmentID] [smallint] NOT NULL,
    [Name] VARCHAR(100) NOT NULL,
    [GroupName] VARCHAR(100) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
) ON [PRIMARY];
INSERT INTO Department
SELECT *
FROM AdventureWorks2012.HumanResources.Department;

Note that you can use any method to create the sample table, but in this example, we are using the AdventureWorks database.

Step 2: Install Java 64-bit

Before proceeding with the migration process, ensure that you have 64-bit Java installed on your computer. The NuoDB Migrator tool is built in Java, so this step is essential. You can download 64-bit Java for Windows, Mac OSX, or Linux from the official Java website. Once installed, set the JAVA_HOME environment variable to the Java installation directory.

Step 3: Install JDBC Driver for SQL Server

To connect to SQL Server, we need to install the JDBC driver. There are two options available: Microsoft JDBC Driver and jTDS JDBC Driver. In this example, we will be using the jTDS JDBC driver. Download the driver and move the JAR file to your NuoDB installation folder.

Migration Step 1: NuoDB Schema Generation

Now, let’s generate a schema for our SQL Server database in NuoDB. Open the command prompt and navigate to the NuoDB Migrator bin folder. Execute the following command:

nuodb-migrator schema --source.driver=net.sourceforge.jtds.jdbc.Driver --source.url=jdbc:jtds:sqlserver://localhost:1433/ --source.username=test --source.password=test --source.catalog=test --source.schema=dbo --output.path=/tmp/schema.sql

This command will generate a schema of all the SQL Server tables and save it in the specified output path. Make sure to create the schema in your NuoDB database before executing this step.

Migration Step 2: Generate the Dump File of the Data

Once the schema is created in NuoDB, we can proceed to generate a CSV format dump file containing all the data from the SQL Server database. Execute the following command:

nuodb-migrator dump --source.driver=net.sourceforge.jtds.jdbc.Driver --source.url=jdbc:jtds:sqlserver://localhost:1433/ --source.username=test --source.password=test --source.catalog=test --source.schema=dbo --output.type=csv --output.path=/tmp/dump.cat

This command will create a CSV file containing the data from all the tables in the SQL Server database. The file will be saved in the specified output path.

Migration Step 3: Load the Data into NuoDB

Finally, let’s load the data from the CSV file into the NuoDB database. Execute the following command:

nuodb-migrator load --target.url=jdbc:com.nuodb://localhost:48004/mytest --target.schema=dbo --target.username=test --target.password=test --input.path=/tmp/dump.cat

Make sure to create the NuoDB database with the specified name before executing this step. Also, consider using a more secure password for your database for security reasons.

Conclusion

Congratulations! You have successfully migrated your SQL Server database to NuoDB. With NuoDB’s scalability and flexibility, you can now explore and build excellent, scale-out applications. If you want to learn more about NuoDB, I encourage you to download it and follow the 3-step migration tutorial. Happy migrating!