SQL – Migrate Database from SQL Server to NuoDB – A Quick Tutorial

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:

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.

30 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

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:

31 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

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.

32 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

Variable Name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre7

33 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

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:

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

34 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

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.

35 SQL   Migrate Database from SQL Server to NuoDB   A Quick Tutorial

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)

SQL – Quick Start with Explorer Sections of NuoDB – Query NuoDB Database

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.

14 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database 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.

15 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database 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.

18 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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.

19 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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.

25 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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

20 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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.

21 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB 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.

26 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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. 27 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB 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.

28 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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.

29 SQL   Quick Start with Explorer Sections of NuoDB   Query NuoDB Database

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)

 

SQL – Quick Start with Admin Sections of NuoDB – Manage NuoDB Database

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:

11 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

12 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB 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.

13 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database
On the success of the creating the sample database it will show the following screen.


14 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

15 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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

16 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

17 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

22 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

23 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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.

24 SQL   Quick Start with Admin Sections of NuoDB   Manage NuoDB Database

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)

SQL – Step by Step Guide to Download and Install NuoDB – Getting Started with NuoDB

Let us take a look at the application you own at your business. If you pay attention to the underlying database for that application you will be amazed. Every successful business these days processes way more data than they used to process before. The number of transactions and the amount of data is growing at an exponential rate. Every single day there is way more data to process than before. Big data is no longer a concept; it is now turning into reality.

If you look around there are so many different big data solutions and it can be a quite difficult task to figure out where to begin. Personally, I have been experimenting with a lot of different solutions which allow my database to scale immediately without much hassle while maintaining optimal database performance.  There are for sure some solutions out there, but for many I even have to learn their specific language and there is a lot of new exploration to do. Honestly, what I prefer is a product, which works with the language I know (SQL) and follows all the RDBMS concepts which I am familiar with (ACID etc.).

NuoDB is one such solution.  It is an operational NewSQL database built on a patented emergent architecture with full support for SQL and ACID guarantees. In this blog post, I will explore how one can download and install NuoDB database.

Step 1:

Follow me and go to the NuoDB download page. Simply fill out the form, accept the online license agreement, and you will be taken directly to a page where you can select any platform you prefer to install NuoDB. In my example below, I select the Windows 64-bit platform as it is one of the most popular NuoDB platforms. (You can also run NuoDB on Amazon Web Services but I prefer to install it on my local machine for the purposes of this blog).

0 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

Step 2:

Once you have downloaded the NuoDB installer, double click on it to install it on the Windows platform.

1 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

Here is the enlarged the icon of the installer.

2 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

Step 3:

Follow the wizard installation, as it is pretty straight forward and easy to do so.

3 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

I have selected all the options to install as the overall installation is very simple and it does not take up much space.

4 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

I have installed it on my C drive but you can select your preferred drive.

5 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

It is quite possible that if you do not have 64 bit Java, it will throw following error. If you face following error, I suggest you to download 64-bit Java from here.

6 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

Make sure that you download 64-bit Java from following linkhttp://java.com/en/download/manual.jsp

7 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

If already have Java 64-bit installed, you can continue with the installation as described in following image. Otherwise, install Java and start from with Step 1.

8 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

As in my case, I already have 64-bit Java installed – and you won’t believe me when I say that the entire installation of NuoDB only took me around 90 seconds.

9 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

Click on Finish to end to exit the installation.

Step 4:

Once the installation is successful, NuoDB will automatically open the following two tabs – Console and DevCenter — in your preferred browser.

10 SQL   Step by Step Guide to Download and Install NuoDB   Getting Started with NuoDB

On the Console tab you can explore various components of the NuoDB solution, e.g. QuickStart, Admin, Explorer, Storefront and Samples. We will see various components and their usage in future blog posts.

If you follow these steps in this post, which I have followed to install NuoDB, you will agree that the installation of NuoDB is extremely smooth and it was indeed a pleasure to install a database product with such ease. If you have installed other database products in the past, you will absolutely agree with me.

So download NuoDB and install it today, and in tomorrow’s blog post I will take the installation to the next level.

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

SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

emptyresult SQL SERVER   SQL Puzzle of SET ANSI NULL   Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

Giveaway:

  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 50)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

wincopteramazon SQL SERVER   SQL Puzzle of SET ANSI NULL   Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

Click to Download Scripts

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

SQL SERVER – A New Approach to Scale .NET Applications

In a previous article, I wrote about scale-up vs. scale-out architectures using SQL Server and NuoDB as examples.  NuoDB recently announced the general availability of their latest product release, 1.1, and it looks like they’ve made significant progress in improving their Microsoft support. NuoDB now supports 64-bit Windows environments, natively integrates with Visual Studio, LINQ and EntityFramework to name a few.

For those of you who haven’t had a chance to read my previous article, NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

nuodbscaleimage1 SQL SERVER   A New Approach to Scale .NET Applications

The NuoDB distributed architecture enables you to scale computational and storage resources independently. Meaning, if your application demands higher transactional throughput, simply add more transaction engines, and if you need a highly available redundant database, add more storage managers.

A typical scale-out architecture might look like this:

nuodbscaleimage2 SQL SERVER   A New Approach to Scale .NET Applications

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

Many of you have been developing, deploying and maintaining .NET applications using SQL Server and might be familiar with setting up a server farm or application cluster to improve performance. As you know, this is no easy task, from both a development and deployment perspective.  I’m not advocating that you eliminate SQL Server from your stack, but let’s entertain the idea of using NuoDB as the primary database for your application backend. Since it presents itself as a single logical database to your application regardless of how many machines it might be running on, you’re able to simplify your code and your scale-out strategy. The transaction engine layer of NuoDB uses a dynamic caching mechanism to eliminate the need to shard or federate your data. Transaction engines connect directly to client applications and are able to process all transaction types (CRUD) and maintain data consistency across the entire deployment environment. This opens up the opportunity to write applications specifically designed for a scale-up architecture and leverage NuoDB to provide scale-out capabilities. Below is an example of co-locating transaction engines on each node in a server farm.

nuodbscaleimage3 SQL SERVER   A New Approach to Scale .NET Applications

 The genius of this system is that co-located transaction engines should be the most responsive to the application. However, in the event that one or more transaction engines get tied up, the broker process automatically connects applications to the next most responsive. There is no prerequisite for the data to actually be present on the transaction engine. NuoDB uses a built-in asynchronous replication technique to get data directly from other transaction engines or storage managers.

I have been following NuoDB for some time now and with each release the technology becomes more exciting. Click here if you would like to try out the latest 64-bit version of NuoDB that includes an ADO.NET driver for Visual Studio integration. Comments welcome!

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

SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project

Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.

nuodbshard2 SQLAuthority News   Sharding or No Sharding of Database   Working on my Weekend Project

Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager.  Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.

Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?

I have downloaded NuoDB today and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.

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

SQL SERVER – Discussion – Scale-up vs Scale-out Architectures

Note: NuoDB is a complete re-think of relational databases with innovative support for the Cloud’s dynamic, asynchronous nature. Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

Scaling Up

There are many different ways of scaling SQL Server to accommodate more transactions and throughput. The general scale-up approach includes:

  • Adding more CPU to increase computational performance
  • Adding RAM to increase query and data caching
  • Adding more storage such as SSDs and partitioning various I/O processes to different physical disks

As long as larger machines are available and your organization has the means to purchase them, then scaling up your database to increase performance is the best route. However, even if you have the means to scale up your infrastructure you will always be bound by the availability of bigger and faster hardware. Given this limitation on Moore’s Law and other organizational concerns such variable workloads and applications contending for the same dataset, additional approaches are required.

Common examples of variable workloads and applications are OLTP systems that need to be reported on by various departmental stakeholders. Other examples include financial applications that are aggregating financial transactions, marketing systems that measure segmentation performance, and other CRM/ERP systems. Developers and administrators have come up with some standard, generally accepted methods for supporting these demands on data. They include:

  • Replication techniques to enable various applications to connect to replica databases and eliminate computational and I/O constraints on the master
  • Clustering configurations to handle failover and availability concerns
  • Vertical and horizontal database partitioning to optimize query performance

Scaling Out

The introduction of SQL Azure and its ability to be deployed on to the cloud makes scaling out an option. Many instances of SQL Azure databases can be federated to support a single scaled out application. However, this approach is really no different than what developers have been doing with MySQL for many years. Federation is simply a managed method of sharding your database that comes at a cost.  Developers begin with database sharding and then that leads into application level sharding.  More sharding/federating means more complexity in your application architecture. Things databases are very good at such as query processing and managing constraints now become the developer’s responsibility.

Scale Everything

Databases have come a long way and what Microsoft is doing with SQL Azure is amazing. Their integrated development and deployment architecture gives them a clear advantage among .NET developers, administrators, and architects. However, a major shift in database technology is underway. New players are emerging that are changing how applications are built and how they are scaled. Some of the new entrants include NoSQL and NewSQL databases. NoSQL technologies might be outside the scope of this article mainly for their lack of SQL and ACID support, so let’s focus on a new database called NuoDB. Although the company might not agree that they are a NewSQL database, I will broadly characterize them as such for the sake of simplicity.

NuoDB is a database company in Cambridge, MA that I have been following for sometime. Unlike many NewSQL technologies on the market, they have decided to take a fresh approach to building a cloud database. They call it an emergent architecture, roughly modeled around how emergence manifests itself in nature. The example they use is a flock of birds and how birds flock and migrate from point A to B without any individual bird responsible for the flock as a whole. Let me break it down in more salient terms.

NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

nuodbscale1 SQL SERVER   Discussion   Scale up vs Scale out Architectures

Brokers act as load balancers that connect clients to transaction engines. Transactions engines are responsible for executing SQL and dynamically cache data for read/write purposes. Transaction engines are a key component of NuoDB that enable it operate in a distributed environment without partitioning or sharding. These processes are able to asynchronously communicate with each other to fetch data that might be cached in memory, eliminating the need for sharding and replication. As updates are processed, transaction engines asynchronously broadcast changes to their peers to maintain consistency. Storage managers are responsible for serving up data to transaction engines and serializing data to disk for durability. A typical scale out architecture might look like this:

nuodbscale2 SQL SERVER   Discussion   Scale up vs Scale out Architectures

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

This article was a brief introduction to one of many new database architectures that are making their way into mainstream applications. I like NuoDB because they are actively pursuing the Microsoft community with their public .NET Beta program. If you’re like me, and are interested in new approaches to solving database problems, I’d recommend you check out NuoDB and their .NET program.

Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

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

SQLAuthority News – HP Project Moonshot Interchangeable and Interlockable Servers with Elastically Scale NuoDB

HP has recently released a new product called Project Moonshot.  For anyone interested in databases, server systems, or cloud computing, the description of this new option was intriguing.  On its most basic level, Project Moonshot is a scalable server system, available in small units that are easy to enlarge and can run a variety of databases and clouds – anything you can think up.

Update: Don’t forget to scroll all the way down and read call for action. I value your opinion about NuoDB.

Basics of Project Moonshot

nuodbatoms SQLAuthority News   HP Project Moonshot Interchangeable and Interlockable Servers with Elastically Scale NuoDB Here are the basics:  Moonshot consists of interchangeable and interlockable servers that are much smaller and more energy efficient than traditional models.  A single Moonshot unit is 4.3U tall and can hold 45 servers, which connect together with 1G ethernet (between individuals) and 10G Ethernet (for the whole system).  The systems, though, are available for purchase individually, configured with an Intel Atom S1260 processor, 8 G of memory and 200GB SSD or 500GB HDD.

Obviously this doesn’t appear to be a server to knock anybody’s sock off, but once you start scaling up, the possibilities are endless.  This system can scale up to 180 nodes in a single unit.  The end game with this kind of system is to provide different for servers for different jobs (like computation, storage, or memory).  You can put 45 units into a 4.3 rack-unit, and suddenly you have the computing power to reckon with, and it still energy efficient.

NuoDB and Project Moontshot

I’m sure a lot of minds are racing right now, thinking up ways to use this new technology.  Well, NuoDB certainly was.  They have been test driving this new system for the past few weeks, and have come up with some unique trials to best utilize and test Moonshot.  NuoDB designed tests to help showcase what they do best: scalable and cloud databases.

NuoDB took a real world situation in mind.  In the real world, hosts have multiple websites (like blogs) that all gets steady but low traffic.  However, if a blog is lucky, it will start attracting a lot of attention.  Attention = traffic.  A lot of traffic can seriously slow servers down if there is not enough computing power dedicated to them.  That is where a scalable system like Moonshot comes into play.  NuoDB also implemented some of their own techniques into the tests to show the full integration possibilities of Moonshot.

For example: hibernating and waking databases.  By keeping some computing power “in reserve,” Moonshot helps administrators take full advantage of all the database, assigning rules to govern when and how memory is used.  For high traffic sites or times, more power is parceled out for those needs, and less is used when less is needed.  The idea seems simple, but implementation required the Moonshot Project for it to become a reality.

nuoDB logo rgb 300 SQLAuthority News   HP Project Moonshot Interchangeable and Interlockable Servers with Elastically Scale NuoDB

This is a simple overview of a long-ranging project that looked at many different variables and what Moonshot was capable of under different circumstances.  The result, though, was that even when running 7200 active databases, they were only using about 70% of the system, leaving a huge percentage of power for extra growth and capacity.  Check out the NuoDB website for more details about this exciting new project!

Call for Action

Download NuoDB and try out their new innovation. The installation and download is extremely easy. I will be very interested to know what is your experience with NuoDB.

Further Reading

NuoDB team have been writing lots of interesting blog post on the subject of Moonshot. I strongly suggest you to read them.

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

SQLAuthority Contests – Get 50 Amazon Gift Cards – Experience NuoDB Starlings 1.0.2

SQL is the standard database language. It has been around since the 1970s. ACID (Atomic, Consistent, Isolated, and Durable) guarantees that the database tier handles the transaction processing in a consistent and reliable manner, freeing the application from managing these tasks (and freeing you from having to code them into the application), while guaranteeing the integrity of the data in the database.

nuoDB logo rgb 300 SQLAuthority Contests   Get 50 Amazon Gift Cards   Experience NuoDB Starlings 1.0.2

NuoDB is the world’s first and only patented, elastically-scalable, SQL database built for decentralized computing resources. It is built with a focus on the elastic scalability on the cloud with 100% ACID guarantees and SQL compliance. Another interesting point about this innovative database is that it removes administration tasks like sharding, caching, clustering and performance tuning. It has almost same syntax and interface which is familiar to any other database application.

NuoDB has recently released their a version earlier this month. The product is very robust and has many new enhencements. The recent release has over 75 bugs fixes and improved stability with Amazon S3. The best part is that this database can be installed on pretty much any platform i.e. MacOS, Windows, Amazon EC2 as well on Ubuntu. They have also recently announced availability on the Amazon AWS marketplace.

50 Amazon Gift Card

amazoncard SQLAuthority Contests   Get 50 Amazon Gift Cards   Experience NuoDB Starlings 1.0.2

NuoDB is fantastic product and the people behind the scenes are fantastic. They not only know how to build a great product they even know how to build relationships. Here is the simple contest they offered exclusively for us. There are no complex rules or difficult questions to answer. Just experience how easy and fantastic their product is.

Things to do:

1) Answer following question in comment area below:

Who is Edgar F. Codd?

2) Download NuoDB Starlings 1.0.2 before April 10, 2013

Download Now

The first 50 people who download NuoDB and answer the question above will get USD $10 Amazon Gift Card – no catch and no restriction. Just do it before April 10, 2013.

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