Checklist to Consider Before Migrating from SQL Server to NoSQL

Here’s a checklist of items to consider before migrating to a NoSQL db from SQL Server. If you come from SQL Server land, you may be used to these features and facilities. So before shifting over to a different world, consider if the db you’re jumping into (MongoDB, CouchDB, etc.) meets your needs by analyzing these items. Keep in mind that some features may not exist in NoSQL, not because of immaturity, but because it may not apply to the nature of a NoSQL database.

Handle Load

Compare against SQL Server:

Reading

Writing

Resource Monitoring

CPU Usage

Memory Use

Disk use

Transactions

Are there software design patterns to rollback

Locking

Reading

Programming

Syntax

SELECT / UPDATE / DELETE / INSERT / JOINS

Reusable Code Modules

Compiled Stored procs

Functions

Custom Data Types

Dynamic Management Views

Indexing

Clustered

Non-Clustered

Toolset / Resources

IDE

Administration

Profiler

Excution Planner

Community

Books

Administration

Complexity

Backing up

Full

Differentials

Transaction Log

Recovery

Replication / Clustering

Snapshots

Security

C# Integration

Facilities

Full Text Search

Snapshots

Kooboo CMS First Impressions

Kooboo is a CMS based on ASP.NET MVC. Recently, I got a chance to take it for a spin, and here are some of my thoughts. Keep in mind that the drawbacks here may come just from my ignorance of the tool use. 🙂 I’ll update this as the more I learn about the inner workings.

Benefits

  • Admin Panel’s UI is intuitive for designers/programmers
    • Easy to add pages
    • Easy to add your own themes/styles
    • Easy to create your own type of content
    • Easy to add content
  • Lots of Features, more than Orchard
  • Mature, has been around for a while (2008)
  • Views are coded in Razor
  • Can connect to MongoDB and other datasource types
  • Versioning of any piece of content and view differences
  • Manage website resources easily – images/documents/etc.

Drawbacks

May not be a drawback once I figure out the “how” and get better understanding.

  • Once a site is created, when I migrated from XML to MongoDB, I lost all the website data from the XML files.
  • Admin Panel’s UI may not be intuitive to non-designers/programmers.
  • Site directory structure Kooboo generates is not the same as the traditional ASP.NET MVC.

Let’s take a look. For a site I created using Kooboo, named “batman”:

  • When a content type is created, it does not create a C# class file. (I didn’t see one at least, in the directory structure.) It does, however, create a MongoDB collection for the content, there’s just no C# class mapped to it.
  • There’s no clear way to bind a View to a model class as in traditional ASP.NET MVC since, Kooboo doesn’t create a C# class file. It doesn’t follow the traditional file/folder naming convention: for each View, you map that to a model.
  • Community not as large as other CMS communities (Orchard, Umbraco, DNN).

I’ll keep exploring, but this is what I’ve found so far.

Using Winrar from the Command Line

Winrar is a great tool used for compressing files. It also has a command line tool called “rar.exe” to compress files, in case you want to batch it up. Here’s an example of the most common switches I use.

"C:\Program Files\WinRAR\rar.exe" a -m5 -r -ep1 "C:\temp\Work\Upgrades\2010-02-06\111.rar" "C:\temp\Work\Upgrades\2010-02-06\Post-Upgrade\"

First path is the location of the rar file to create. The second path is either the location of the file(s) or directory to compress.

options used
 
  -ep1  Exclude base directory from names
  a     Add files to archive
  -r    Recurse subdirectories (will compress entire directories)
  -m5   Set compression level to maximum

You can even assign a password to it using the -p switch

Backup MySQL DB From Remote To Your Local Machine

Why do this?

In case you don’t have shell access to your server from your hosting provider. Also if the provider doesn’t let you writing files locally from a script.

If you want to backup from a remote machine to your local machine:

(Don’t use the < or > symbols when you type it, except the last > that redirects to a file.)

mysqldump --opt -Q -h <www.narutorp.net> -u <username> --password=<chocolatepizza> <database_name> > <C:\temp\mysql\backupdb1.sql>

If you want to backup from a remote machine to that same remote machine.

mysqldump --opt -Q -h <127.0.0.1> -u <username> -p <password> > <filename.sql>

You can also pipe that to gzip to compress, and schedule it as needed.

Java Pairs Well with Which Database?

In the same way there’s a tight bond between MySQL and PHP, SQL Server and ASP.NET, SQL Server and ColdFusion – what goes well with Java? Oracle? Being curious, I started searching in employment web sites. I searched for “Java” and one of these databases: Oracle, MySQL, SQL Server and PostgreSQL. (I put in “SQL Server” using quotes.) The sites used were: craigslist, Monster.com, Dice.com, and Yahoo! Hotjobs.

The numbers signify how many job entries were returned.

So it does seem Oracle goes with Java. Also I noticed how many people call “SQL Server” just “SQL.” Sort of confusing and hard to tell if they’re referring to the platform or language.

Logical vs Physical ER Diagrams

Logical diagrams are to convey requirements only. Physical diagrams represent the actual data structure to support the requirements and take into account technical scalability and speed.

Edit: I hate the way I had to format this document for this blog post. If you want this tutorial better formatted, check out the Word document.

One-to-Many Relationship


Logical

On ER/Studio, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Physical

On SQL Server, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Manager Table:

If you allow NULLs for StoreID in the Manager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked), then you’ll have to have at least one Store assigned to a Manager.

Querying

The above states that one store can have many managers. Here’s some sample data what’s in the tables:

SELECT * FROM Manager

SELECT * FROM Store

Get all manager information with for all managers that belong to a store:

SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        INNER JOIN Store ON Manager.StoreID = Store.StoreID

Get all manager information with for all managers (even if they don’t have a store):

SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        LEFT OUTER JOIN Store ON Manager.StoreID = Store.StoreID

Notice the NULL for Steamboat Willie. He doesn’t have a store, so all Store related fields show as NULL.


Many-to-Many Relationship


In order to implement this physically, you need a join table. In this case, we use StoreManager. Logically, you only need only two entities (Store and Manager).

Logical

Physical


On SQL Server, three tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID. The table StoreManager has two foreign keys:  StoredID (which is mapped to StoreID from the Store table) and ManagerID (which is mapped to the ManagerID from the Manager table).

StoreManager Table:

If you allow NULLs for StoreID and ManagerID in the StoreManager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked for both), then you’ll have to have at least one Store assigned to a Manager.

Here’s some sample data what’s in the tables:

SELECT * FROM Manager


SELECT * FROM Store


SELECT * FROM StoreManager


Get all manager information associated with his store:

SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,
        StoreManager.StoreID, StoreManager.ManagerID, Store.StoreID,
        Store.[Name],Store.Address, Store.State, Store.City, Store.Zip
FROM    Store
        INNER JOIN StoreManager ON Store.StoreID = StoreManager.StoreID
        INNER JOIN Manager ON StoreManager.ManagerID = Manager.ManagerID

WordPress Tip: Allow comments on this post by default

Being tired of checking the “Allow comments on this post” checkbox every time I make a new post, I went into the database and changed the setting. By default, it’s set to “closed” meaning you have to check it all the time. See for yourself:

1
2
select option_name, option_value
from wp_options where option_name in ( 'default_comment_status' );

Change it to “open”:

1
2
3
update wp_options
set option_value='open'
where option_name in('default_comment_status');

Now the checkbox should always be checked.

Import MySQL Data into SQL Server

Today I needed to analyze some forum data from vBulletin running MySQL. The table on MySQL had 60,000 records. Because my playing field is SQL Server and not MySQL, and I needed to slice and dice the data, I needed a way to get the data onto SQL Server. Because of some security restrictions, I could not set up a linked server on SQL Server. I don’t have remote access to the Linux box either. I tried exporting from SQLYog, but CSV data could not be properly delimited and failed when I did a database import via the SSIS import wizard (the table has a lot of flexability to use any character and is often abused by spammers). What did I do?

I only had 4 columns to import for the table. So I ran a select statement returning one column ordered by the id. Then I copied and pasted into an Excel spreadsheet. I did this for all four rows. Because Excel doesn’t use delimiters, but rather cells to separate, I didn’t have to worry about data breaking. Then after that, I did an import via the SSIS import wizard. Ta-da, I can now slice and dice my data. There are probably more efficient ways to do this, but I needed a quick solution and this did it.