SQL – Migrate Database from SQL Server to NuoDB – A Quick Tutorial
- by Pinal Dave
Data is growing exponentially and every organization with growing data is thinking of next big innovation in the world of Big Data. Big data is a indeed a future for every organization at one point of the time. Just like every other next big thing, big data has its own challenges and issues. The biggest challenge associated with the big data is to find the ideal platform which supports the scalability and growth of the data. If you are a regular reader of this blog, you must be familiar with NuoDB. I have been working with NuoDB for a while and their recent release is the best thus far.
NuoDB is an elastically scalable SQL database that can run on local host, datacenter and cloud-based resources. A key feature of the product is that it does not require sharding (read more here). Last week, I was able to install NuoDB in less than 90 seconds and have explored their Explorer and Admin sections. You can read about my experiences in these posts:
SQL – Step by Step Guide to Download and Install NuoDB – Getting Started with NuoDB
SQL – Quick Start with Admin Sections of NuoDB – Manage NuoDB Database
SQL – Quick Start with Explorer Sections of NuoDB – Query NuoDB Database
Many SQL Authority readers have been following me in my journey to evaluate NuoDB. One of the frequently asked questions I’ve received from you is if there is any way to migrate data from SQL Server to NuoDB. The fact is that there is indeed a way to do so and NuoDB provides a fantastic tool which can help users to do it. NuoDB Migrator is a command line utility that supports the migration of Microsoft SQL Server, MySQL, Oracle, and PostgreSQL schemas and data to NuoDB.
The migration to NuoDB is a three-step process:
NuoDB Migrator generates a schema for a target NuoDB database
It loads data into the target NuoDB database
It dumps data from the source database
Let’s see how we can migrate our data from SQL Server to NuoDB using a simple three-step approach. But before we do that we will create a sample database in MSSQL and later we will migrate the same database to NuoDB:
Setup Step 1: Build a sample data
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 I am using the SQL Server AdventureWorks database to build this sample table but you can build this sample table any way you prefer.
Setup Step 2: Install Java 64 bit
Before you can begin the migration process to NuoDB, make sure you have 64-bit Java installed on your computer. This is due to the fact that the NuoDB Migrator tool is built in Java. You can download 64-bit Java for Windows, Mac OSX, or Linux from the following link: http://java.com/en/download/manual.jsp.
One more thing to remember is that you make sure that the path in your environment settings is set to your JAVA_HOME directory or else the tool will not work. Here is how you can do it:
Go to My Computer >> Right Click >> Select Properties >> Click on Advanced System Settings >> Click on Environment Variables >> Click on New and enter the following values.
Variable Name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre7
Make sure you enter your Java installation directory in the Variable Value field.
Setup Step 3: Install JDBC driver for SQL Server.
There are two JDBC drivers available for SQL Server. Select the one you prefer to use by following one of the two links below:
Microsoft JDBC Driver
jTDS JDBC Driver
In this example we will be using jTDS JDBC driver. Once you download the driver, move the driver to your NuoDB installation folder. In my case, I have moved the JAR file of the driver into the C:\Program Files\NuoDB\tools\migrator\jar folder as this is my NuoDB installation directory.
Now we are all set to start the three-step migration process from SQL Server to NuoDB:
Migration Step 1: NuoDB Schema Generation
Here is the command I use to generate a schema of my SQL Server Database in NuoDB. First I go to the folder C:\Program Files\NuoDB\tools\migrator\bin and execute the nuodb-migrator.bat file.
Note that my database name is ‘test’. Additionally my username and password is also ‘test’. You can see that my SQL Server database is running on my localhost on port 1433. Additionally, the schema of the table is ‘dbo’.
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
The above script will generate a schema of all my SQL Server tables and will put it in the folder C:\tmp\schema.sql . You can open the schema.sql file and execute this file directly in your NuoDB instance. You can follow the link here to see how you can execute the SQL script in NuoDB.
Please note that if you have not yet created the schema in the NuoDB database, you should create it before executing this step.
Step 2: Generate the Dump File of the Data
Once you have recreated your schema in NuoDB from SQL Server, the next step is very easy. Here we create a CSV format dump file, which will contain all the data from all the tables from the SQL Server database.
The command to do so is very similar to the above command. Be aware that this step may take a bit of time based on your database size.
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
Once the above command is successfully executed you can find your CSV file in the C:\tmp\ folder. However, you do not have to do anything manually. The third and final step will take care of completing the migration process.
Migration Step 3: Load the Data into NuoDB
After building schema and taking a dump of the data, the very next step is essential and crucial. It will take the CSV file and load it into the NuoDB database.
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
Please note that in the above script we are now targeting the NuoDB database, which we have already created with the name of “MyTest”. If the database does not exist, create it manually before executing the above script. I have kept the username and password as “test”, but please make sure that you create a more secure password for your database for security reasons.
Voila! You’re Done
That’s it. You are done. It took 3 setup and 3 migration steps to migrate your SQL Server database to NuoDB. You can now start exploring the database and build excellent, scale-out applications.
In this blog post, I have done my best to come up with simple and easy process, which you can follow to migrate your app from SQL Server to NuoDB.
Download NuoDB
I strongly encourage you to download NuoDB and go through my 3-step migration tutorial from SQL Server to NuoDB.
Additionally here are two very important blog post from NuoDB CTO Seth Proctor. He has written excellent blog posts on the concept of the Administrative Domains. NuoDB has this concept of an Administrative Domain, which is a collection of hosts that can run one or multiple databases. Each database has its own TEs and SMs, but all are managed within the Admin Console for that particular domain.
http://www.nuodb.com/techblog/2013/03/11/getting-started-provisioning-a-domain/
http://www.nuodb.com/techblog/2013/03/14/getting-started-running-a-database/
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Big Data, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: NuoDB
