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 address http://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)

<dtm:DataTypeMappings
     xmlns:dtm="http://www.microsoft.com/SqlServer/Dts/DataTypeMapping.xsd"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     SourceType="SQLOLEDB;SQLNCLI*;System.Data.SqlClient.SqlConnection"
     MinSourceVersion="*"
     MaxSourceVersion="*"
     DestinationType="NuoDb.Data.Client.NuoDbConnection"
     MinDestinationVersion="*"
     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”

<dtm:DataTypeMapping>
  <dtm:SourceDataType>
    <dtm:DataTypeName>money</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
    <dtm:NumericType>
      <dtm:DataTypeName>decimal</dtm:DataTypeName>
      <dtm:Precision>19</dtm:Precision>
      <dtm:Scale>4</dtm:Scale>
    </dtm:NumericType>
  </dtm:DestinationDataType>
</dtm:DataTypeMapping>

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

<dtm:DataTypeMapping>
  <dtm:SourceDataType>
    <dtm:DataTypeName>uniqueidentifier</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
    <dtm:CharacterStringType>
      <dtm:DataTypeName>char</dtm:DataTypeName>
      <dtm:Length>38</dtm:Length>
    </dtm:CharacterStringType>
  </dtm:DestinationDataType>
</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

 

If you haven’t already downloaded the .Net Driver for NuoDB, you can find that here: NuoDB .Net Driver

JakeemBurnett
Anonymous's picture
I am very happy to read your

I am very happy to read your articles it’s very useful for me, and I am completely satisfied with your website. All comments and articles are very useful and very good. Your blog is very attention-grabbing. I am loving all of the in turn you are sharing with each one!…

Alexey
Anonymous's picture
You should change SourceType

You should change SourceType attribute in sample file to “SQLOLEDB;SQLNCLI*;System.Data.SqlClient.SqlConnection” from “SQLOLEDB;SQLNCLI;SQLNCLI10;System.Data.SqlClient.SqlConnection” to work with any SQL Server version

Thanks for pointing this out!

Thanks for pointing this out! I’ll fix the post.

Hi NuoDB,

Hi NuoDB,

I was tried this but i am getting error while choosing a Destination and i pasted the error message in below.

The Operation could not be completed.

Additional Information action:
- System.IO.IOException:No connection could be made because the target machine actively refused it 192.168.7.5:48004,server2
  at NuoDb.Data.Client.Net.CryptoSocket.ctro(String address,Int32port)
  at NuoDb.Data.Client.NuoDb ConnectionInternal.doOpen(String hostName)(NuoDb.Data.Client)

 

Thanks,

Vinay.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.