Getting Started: Migrate a Database from SQL Server to NuoDB

The new .Net driver for NuoDb, currently in beta, allows to interact with the NuoDB database from a wide range of existing tools. One of this is the migration tool available with the Microsoft SQL Server database, that can move data between two data sources for which an ADO.NET driver is available. Today we will migrate the AdventureWorks database to NuoDb, available at the addresshttp://msftdbprodsamples.codeplex.com/

The first step is to let the migration tool know how to map the provider specific data types; for instance, SQL Server has the non-standard “money” and the “uniqueidentifier” data types.

To do that, we will create a new file (click here to download it) in the repository of the migration tool (e.g. C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles), placing in its root node the identification of the source (all the possible drivers connecting to SQL Server) and target databases (the ADO.NET driver for NuoDB)

The rest of the file can be copied from the other configuration files, e.g. the one converting from MSSQL to Oracle; for instance we will be mapping the “money” datatype to a “decimal”

and the “uniqueidentifier” to a “char(38)”, as the datatype is used to store GUIDs

Once this file is placed in the folder with the other mappings, we can start the “Import and Export Data” application from the SQL Server group of the Start menu.

The first step is selecting the source database

Then, we select the target database; as soon as the NuoDb dirver is selected, the dialog changes to let use specify the values of the properties that will build the connection string: the address of the server, the name of the database, user and password for authentication and the default schema (optional)

The third step in the wizard lets us choose if we want to migrate from the source database a set of tables, or just the result of a custom query; as we want to clone the entire database, the default choice is already correct.

We are then presented with the list of tables that are available in the source database; by clicking on the checkbox located in the header of the table we can select all of them. Clicking on the Edit Mappings button lets us specify the target schema for all of the tables in the target database.

As we are interested only in the AdventureWorks data, we can avoid importing the tables coming from the “dbo” schema

Selecting one of the target tables and clicking Edit Mappings we can verify if our mapping configuration file worked: for the Product table, for instance, it converted the “image” datatype of ThumbNailPhoto into a varbinary, the “uniqueidentifier” ofrowguid into a char(38), and the “money” of StandardCost and ListPrice into a decimal(19,4).

The conversion is almost over: just a final page to recapitulate the steps, and we are ready to start.

Done! We now have a nice database to play with in the next blog articles!