NoSQL Zone is brought to you in partnership with:

Seth is the CTO at NuoDB. His main areas of focus are on the administration, security and resource management models, automation and the tools that drive these pieces. Seth is a DZone MVB and is not an employee of DZone and has posted 42 posts at DZone. You can read more from them at their website. View Full User Profile

Getting Started: Migrate a Database from SQL Server to NuoDB

05.12.2013
| 3805 views |
  • submit to reddit

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)

01.<dtm:DataTypeMappings
04.     SourceType="SQLOLEDB;SQLNCLI;SQLNCLI10;System.Data.SqlClient.SqlConnection"
05.     MinSourceVersion="*"
06.     MaxSourceVersion="*"
07.     DestinationType="NuoDb.Data.Client.NuoDbConnection"
08.     MinDestinationVersion="*"
09.     MaxDestinationVersion="*">

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”

01.<dtm:DataTypeMapping>
02.  <dtm:SourceDataType>
03.    <dtm:DataTypeName>money</dtm:DataTypeName>
04.  </dtm:SourceDataType>
05.  <dtm:DestinationDataType>
06.    <dtm:NumericType>
07.      <dtm:DataTypeName>decimal</dtm:DataTypeName>
08.      <dtm:Precision>19</dtm:Precision>
09.      <dtm:Scale>4</dtm:Scale>
10.    </dtm:NumericType>
11.  </dtm:DestinationDataType>
12.</dtm:DataTypeMapping>

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

01.<dtm:DataTypeMapping>
02.  <dtm:SourceDataType>
03.    <dtm:DataTypeName>uniqueidentifier</dtm:DataTypeName>
04.  </dtm:SourceDataType>
05.  <dtm:DestinationDataType>
06.    <dtm:CharacterStringType>
07.      <dtm:DataTypeName>char</dtm:DataTypeName>
08.      <dtm:Length>38</dtm:Length>
09.    </dtm:CharacterStringType>
10.  </dtm:DestinationDataType>
11.</dtm:DataTypeMapping>

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.

DTS_NuoDb0

The first step is selecting the source database

DTS_NuoDb1

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)

DTS_NuoDb2

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.

DTS_NuoDb3

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.

DTS_NuoDb4

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

DTS_NuoDb5

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).

DTS_NuoDb6

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

DTS_NuoDb7

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

DTS_NuoDb8

Published at DZone with permission of Seth Proctor, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)