I recently wrote a four-part series on how I started to learn about and begin my journey with NuoDB. Big Data is indeed a big world and the learning of the Big Data is like spaghetti – no one knows in reality where to start, so I decided to learn it with the help of NuoDB. You can download NuoDB and continue your journey with me as well.

• Part 1 – Install NuoDB in 90 Seconds
• Part 2 – Manage NuoDB Installation
• Part 3 – Explore NuoDB Database
• Part 4 – Migrate from SQL Server to NuoDB

…and in this blog post we will try to answer the most asked question about NuoDB.

“I like the NuoDB Explorer but can I connect to NuoDB from my preferred Graphical User Interface?”
Honestly, I did not expect this question to be asked of me so many times but from the question it is clear that we developers absolutely want to learn new things and along with that we do want to continue to use our most efficient developer tools. Now here is the answer to the question:

“Absolutely, you can continue to use any of the following most popular SQL clients.”
NuoDB supports the three most popular 3rd-party SQL clients. In all the leading development environments there are always more than one database installed and managing each of them with a different tool is often a very difficult task. Developers like to use one tool, which can control most of the databases. Once developers are familiar with one database tool it is very difficult for them to switch to another tool. This is particularly difficult when we developers find that tool to be the key reason for our efficiency.

Let us see how to install each of the NuoDB supported 3^rd party tools along with a quick tutorial on how to go about using them.

SQuirreL SQL Client

First download SQuirreL Universal SQL client. On the Windows platform you can double-click on the file and it will install the SQuirrel client. Once it is installed, open the application and it will bring up the following screen. Now go to the Drivers tab on the left side and scroll it down. You will find NuoDB mentioned there.

Now right click over it and click on Modify Driver.

Now here is where you need to make sure that you make proper entries or your client will not work with the database. Enter following values:
Name: NuoDB
Example URL: jdbc:com:nuodb://localhost:48004/test
Website URL: http://www.nuodb.com

Now click on the Extra Class Path tab and Add the location of the nuodbjdbc.jar file. If you are following my blog posts and have installed NuoDB in the default location, you will find the default path as C:\Program Files\NuoDB\jar\nuodbjdbc.jar.

The class name of the driver is automatically populated.

Once you click OK you will see that there is a small icon displayed to the left of NuoDB, which shows that you have successfully configured and installed the NuoDB driver.

Now click on the tab of Alias tab and you can notice that it is empty. Now click on the big Plus icon and it will open screen of adding an alias.

“Alias” means nothing more than adding a database to your system. The database name of the original installation can be anything and, if you wish, you can register the database with any other alternative name.

Here are the details you should fill into the Alias screen below.

Name: Test (or your preferred alias)
Driver: NuoDB
URL: jdbc:com:nuodb://localhost:48004/test (This is for test database)
User Name: dba (This is the username which I entered for test Database)
Password: goalie (This is the password which I entered for test Database)
Check Auto Logon and Connect at Startup and click on OK.

That’s it! You are done. On the right side you will see a table name and on the left side you will see various tabs with all the relevant details from respective table. You can see various metadata, schemas, data types and other information in the table.

In addition, you can also generate script and do various important tasks related to database.

You can see how easy it is to configure NuoDB with the SQuirreL Client and get going with it immediately.

SQL Workbench/J

This is another wonderful client tool, which works very well with NuoDB. The best part is that in the Driver dropdown you will see NuoDB being mentioned there. Click here to download  SQL Workbench/J Universal SQL client.

The download process is straight forward and the installation is a very easy process for SQL Workbench/J. As soon as you open the client, you will notice on following screen the NuoDB driver when selecting a New Connection Profile.

Select NuoDB from the drop down and click on OK.

In the driver information, enter following details:
Driver: NuoDB (com.nuodb.jdbc.Driver)
URL: jdbc:com.nuodb://localhost/test
Username: dba
Password: goalie

While clicking on OK, it will bring up the following pop-up. Click Yes to edit the driver information.

Click on OK and it will bring you to following screen. This is the screen where you can perform various tasks.

You can write any SQL query you want and it will instantly show you the results. Now click on the database icon, which you see right on the left side of the word User=dba. 

Once you click on Database Explorer, you can perform various database related tasks.

As a developer, one of my favorite tasks is to look at the source of the table as it gives me a proper view of the structure of the database. I find SQL Workbench/J very efficient in doing the same.

DbVisualizer

DBVisualizer is another great tool, which helps you to connect to NuoDB and retrieve database information in your desired format. A developer who is familiar with DBVisualizer will find this client to be very easy to work with.

The installation of the DBVisualizer is very pretty straight forward. When we open the client, it will bring us to the following screen.

As a first step we need to set up the driver. Go to Tools >> Driver Manager.

It will bring up following screen where we set up the diver. Click on Create Driver and it will open up the driver settings on the right side.

On the right side of the area where it displays Driver Settings please enter the following values-
Name: NuoDB
URL Format: jdbc:com.nuodb://localhost:48004/test

Now under the driver path, click on the folder icon and it will ask for the location of the jar file. Provide the path as a C:\Program Files\NuoDB\jar\nuodbjdbc.jar and click OK. You will notice there is a green button displayed at the bottom right corner. This means the driver is configured properly.

Once driver is configured properly, we can go to Create Database Connection and create a database.

If the pop up show up for the Wizard. Click on No Wizard and continue to enter the settings manually.

Here is the Database Connection screen. This screen can be bit tricky. Here are the settings you need to remember to enter.
Name: NuoDB
Database Type: Generic
Driver: NuoDB
Database URL: jdbc:com.nuodb://localhost:48004/test
Database Userid: dba
Database Password: goalie
Once you enter the values, click on Connect.

Once Connect is pressed, it will change the button value to Reconnect if the connection is successfully established and it will show the connection details on lthe eft side.

When we further explore the NuoDB, we can see various tables created in our test application. We can further click on the right side screen and see various details on the table.

If you click on the Data Tab, it will display the entire data of the table.

The Tools menu also has some very interesting and cool features like Driver Manager, Data Monitor and SQL History.

Summary

Well, this was a relatively long post but I find it is extremely essential to cover all the three important clients, which we developers use in our daily database development. Here is my question to you?
Which one of the following is your favorite NuoDB 3^rd-Party Database Client? (Pick One)

• SQuirreL SQL Client
• SQL Workbench/J
• DbVisualizer

I will be very much eager to read your experience about NuoDB. You can download NuoDB from here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

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

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.

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.

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)

This is the third post in the series of the blog posts I am writing about NuoDB. NuoDB is very innovative and easy-to-use product. I can clearly see how one can scale-out NuoDB with so much ease and confidence. In my very first blog post we discussed how we can install NuoDB (link), and in my second post I discussed how we can manage the NuoDB database transaction engines and storage managers with a few clicks (link).

Note: You can Download NuoDB from here.

In this post, we will learn how we can use the Explorer feature of NuoDB to do various SQL operations. NuoDB has a browser-based Explorer, which is very powerful and has many of the features any IDE would normally have. Let us see how it works in the following step-by-step tutorial.

Let us go to the NuoDBNuoDB Console by typing the following URL in your browser: http://localhost:8080/

It will bring you to the QuickStart screen. Make sure that you have created the sample database. If you have not created sample database, click on Create Database and create it successfully.

Now go to the NuoDB Explorer by clicking on the main tab, and it will ask you for your domain username and password. Enter the username as a domain and password as a bird. Alternatively you can also enter username as a quickstart and password as a quickstart.

Once you enter the password you will be able to see the databases. In our example we have installed the Sample Database hence you will see the Test database in our Database Hierarchy screen.

When you click on database it will ask for the database login. Note that Database Login is different from Domain login and you will have to enter your database login over here. In our case the database username is dba and password is goalie.

Once you enter a valid username and password it will display your database. Further expand your database and you will notice various objects in your database.

Once you explore various objects, select any database and click on Open.

When you click on execute, it will display the SQL script to select the data from the table. The autogenerated script displays entire result set from the database.

The NuoDB Explorer is very powerful and makes the life of developers very easy. If you click on List SQL Statements it will list all the available SQL statements right away in Query Editor. You can see the popup window in following image.

Here is the cool thing for geeks. You can even click on Query Plan and it will display the text based query plan as well. In case of a SELECT, the query plan will be much simpler, however, when we write complex queries it will be very interesting. We can use the query plan tab for performance tuning of the database.

Here is another feature, when we click on List Tables in NuoDB Explorer.  It lists all the available tables in the query editor. This is very helpful when we are writing a long complex query.

Here is a relatively complex example I have built using Inner Join syntax. Right below I have displayed the Query Plan. The query plan displays all the little details related to the query.

Well, we just wrote multi-table query and executed it against the NuoDB database. You can use the NuoDB Admin section and do various analyses of the query and its performance.

NuoDB is a distributed database built on a patented emergent architecture with full support for SQL and ACID guarantees.  It allows you to add Transaction Engine processes to a running system to improve the performance of your system.  You can also add a second Storage Engine to your running system for redundancy purposes.  Conversely, you can shut down processes when you don’t need the extra database resources.

NuoDB also provides developers and administrators with a single intuitive interface for centrally monitoring deployments.

If you have read my blog posts and have not tried out NuoDB, I strongly suggest that you download it today and catch up with the learnings with me. Trust me though the product is very powerful, it is extremely easy to learn and use.

Reference: Pinal Dave (http://blog.sqlauthority.com)

In the yesterday’s blog post we have seen that it is extremely easy to install the NuoDB database on your local machine. Now that the application is properly set up, let us explore NuoDB a bit more and get you familiar with the how it works and what the important areas of the NuoDB are that you should learn.

As we have already installed NuoDB, now we will quickly start with two of the important areas in NuoDB: 1) Admin and 2) Explorer.

In this blog post I will explore how the Admin Section of the NuoDB Console works.  In the next blog post we will learn how the Explorer Section works.

Let us go to the NuoDB Console by typing the following URL in your browser: http://localhost:8080/

It will bring you to the following screen:

On this screen you can see a big Start QuickStart button. Click on the button and it will bring you to following screen. On this screen you will find very important information about Domain and Database Settings. It is our habit that we do not read what is written on the screen and keep on clicking on continue without reading. While we are familiar with most wizards, we can often miss the very important message on the screen.

Please note the information of Domain Settings and Database Settings from the following screen before clicking on Create Database.

Domain Settings

• User: quickstart
• Password: quickstart

Database Settings

• User: dba
• Password: goalie
• Database: test
• Schema: HOCKEY

Once you click on the Create Database button it will immediately start creating sample database. First, it will start a Storage Manager and right after that it will start a Transaction Engine. Once the engine is up, it will Create a Schema and Sample Data.

On the success of the creating the sample database it will show the following screen.

Now is the time where we can explore the NuoDB Admin or NuoDB Explorer. If you click on Admin, it will first show following login screen. Enter for the username “domain” and for the password “bird”. Alternatively you can enter “quickstart”  twice for username and password.  It works as too.

Once you enter into the Admin Section, on the left side you can see information about NuoDB and Admin Console and on the right side you can see the domain overview area.

From this Administrative section you can do any of the following tasks:

• Create a view of the entire domain
• Add and remove databases
• Start and stop NuoDB Transaction Engines and Storage Managers
• Monitor transaction across all the NuoDB databases

On the right side of the Admin Section we can see various information about a particular NuoDB domain. You can quickly view various alerts, find out information about the number of host machines that are provisioned for the domain, and see the number of databases and processes that are running in the domain.

If you click on the “1 host” link you will be able to see various processes, CPU usage and other information. In the Processes Section you can see that there are two different types of processes. The first process (where you can see the floppy drive icon) represents a running Storage Manager process and the second process a running Transaction Engine process.

You can click on the links for the Storage Manager and Transaction Engine to see further statistical details right down to the last byte of the data.

There are various charts available for analysis as well. I think the product is quite mature and the user can add different monitor charts to the Admin section.

Additionally, the Admin section is the place where you can create and manage new databases. I hope today’s tutorial gives you enough confidence that you can try out NuoDB and checkout various administrative activities with the database. I am personally impressed with their dashboard related to various counters.

For more information about how the NuoDB architecture works and what a Storage Manager or Transaction Engine does, check out this short video with NuoDB CTO Seth Proctor: 

In the next blog post, we will try out the Explorer section of NuoDB, which allows us to run SQL queries and write SQL code.  Meanwhile, I strongly suggest you download and install NuoDB and get yourself familiar with the product.

Reference: Pinal Dave (http://blog.sqlauthority.com)