Tag Archives: sql server

Let’s Talk Top 7 Options for Database Gumbo

When one starts to dig into databases things get really complex really fast. There’s not only a whole plethora of database companies and projects, but database types, storage engines, and other options and functionality to choose from. One place to get a start is just to take a look at the crazy long list of databases on db-engines. In this post I’m going to take a look at a few of the top database engines to create a starting point – which I’ll reference – for future video streaming coding sessions (follow me @ twitch.tv/adronhall).

My Options for Database Gumbo

  1. Apache Cassandra / DataStax Enterprise
  2. Postgresql
  3. SQL Server
  4. Elasticsearch
  5. Redis
  6. SQLite
  7. Dynamo DB

The Reasons

Ok, so the list is as such, and as stated it’s my list. There are a lot of databases, and of course some are still more used such as Oracle. However here’s some of the logic and reasoning behind my choices above.

Oracle

First off I feel like I need to broach the Oracle topic. Mostly because of their general use in industry. I’m not doing anything with Oracle now, nor have I for years for a long, long, LONG list of reasons. Using their software tends to be buried in bureaucratic, oddly broken and unnecessary usage today anyway. They use predatory market tactics, completely dishonorable approach to sales and services, as well as threatening and suing people for doing benchmarks, and a host of other practices. In face to face experiences, Oracle tends to give off experiences, that Lawrence from Office Space would say, “naw man, I think you’d get your ass kicked for that!” and I agree. Oracle’s practices are too often disgusting. But even from the purely technical point of view, the Oracle Database and ecosystem itself really isn’t better than other options out there. It is indeed a better, more intelligently strategic and tactical option to use a number of alternatives.

Apache Cassandra / DataStax Enterprise

This combo has multiple reasons and logic to be on the list. First and foremost, much of my work today is using DataStax Enterprise (DSE) and Apache Cassandra since I work for DataStax. But it’s important to know I didn’t just go to DataStax because I needed a job, but because I chose them (and obviously they chose me by hiring me) because of the team and technology. Yes, they pay me, but it’s very much a two way street, I advocate Cassandra and DSE because I personally know the tech is top tier and solid.

On the fact that Apache Cassandra is top tier and solid, it is simply the remaining truly masterless distributed database that provides a linear path of scalability on the market that you can use, buy support for, and is actually actively and knowingly maintained not just by DataStax but by members of the community. One could make an argument for MongoDB but I’ll maybe elaborate on that in the future.

In addition to being a solid distributed database there are capabilities inherent in Apache Cassandra because of the data types and respective the CQL (Cassandra Query Language) that make it a great database to use too. DataStax Enterprise extends that to provide spatial (re: GIS/Geo Data/Queries), graph data, analytics engine, and more built on other components like SOLR and related technology. Overall a great database and great prospective combinations with the database.

Postgresql

Postgres is a relational database that has been around for a long time. It’s got some really awesome features like native JSON support, which I’m a big fan of. But I digress, there’s tons of other material that lays out thoroughly why to use Postgres which I very much agree with.

Just from the perspective of the extensive and rich data types Postgres is enough to be put on this list, but considering there are a lot of reasons around multi-tenancy, scalability, and related characteristics that are mostly unique to Postgres it’s held a solid position.

SQL Server

This one is on my list for a few reasons that have nothing to do with features or capabilities. This is the first database I was responsible for in its entirety. Administration, queries, query tuning, setup, and developer against with the application tier. I think of all my experience, this database I’ve spent the most time with, with Apache Cassandra being a close second, then Postgres and finally Riak.

Kind of a pattern there eh? Relational, distributed, relational, distributed!

The other thing about SQL Server however is the integrations, tooling, and related development ecosystem around SQL Server is above and beyond most options out there. Maybe, with a big maybe, Oracle’s ecosystem might be comparable but the pricing is insanely different. In that SQL Server basically can carry the whole workload, reporting, ETL, and other feature capabilities that the Oracle ecosystem has traditionally done. Combine SQL Server with SSIS (SQL Server Integration Services), SSRS (SQL Server Reporting Services), and other online systems like Azure’s SQL Database and the support, tooling, and ecosystem is just massive. Even though I’ve had my ins and outs with Microsoft over the years, I’ve always found myself enjoying working on SQL Server and it’s respective tooling options and such. It’s a feature rich, complete, solidly, and generally well performing relational database, full stop.

Elasticsearch

Ok, this is kind of a distributed database of sorts but focused more exclusively (not totally since it’s kind of expanded its roles) search engine. Overall I’ve had good experiences with Elasticsearch and it’s respective ELK (or Elastic ecosystem) of tooling and such, with some frustrating flakiness here and there over the years. Most of my experience has come from an operational point of view with Elasticsearch. I’ve however done a fair bit of work over the years in supporting teams that are doing actual software development against the system. I probably won’t write a huge amount about Elasticsearch in the coming months, but I’ll definitely bring it up at certain times.

Redis / SQLite / DynamoDB

These I’ll be covering in the coming months. For Redis and DynamoDB I have wanted to dig in for some comparison analysis from the perspective of implementing data tiers against these databases, where they are a good option, and determining where they’re just an outright bad option.

For SQLite I’ve used it on and off for many years, but have wanted to sit down and just learn it and try out some of its features a bit more.

Riak in a .NET World

Jeremiah's Demo Works, IT WORKS IT WORKS!

Jeremiah’s Demo Works, IT WORKS IT WORKS!

A few days ago Troy Howard, Jeremiah Peschka and I all traveled via Amtrak Cascades up to Seattle. The mission was simple, Jeremiah was presenting “Riak in a .NET World”, I was handling logistics and Troy was handling video.

So I took the video that Troy shot, I edited it, put together some soundtrack to it and let Jeremiah’s big data magic shine. He covers the basics around RDBMSes, SQL Server in this case but easily it applies to any RDBMS in large part. These basics bring us up to where and why an architecture needs to shift from an RDBMS solution to a distributed solution like Riak. After stepping through some of the key reasons to move to Riak, Jeremiah walks through a live demo of using CorrugatedIron, the .NET Client for Riak (Github repo). During the walk through he covers the specific characteristics of how CorrugatedIron interacts with Riak through indexs, buckets and during puts and pulls of data.

Toward the end of the video Joseph Blomstedt @jtuple, Troy Howard @thoward37, Jeremiah Peschka @peschkaj, Clive Boulton @iC and Richard Turner @bitcrazed. Also note, I’ve enabled download for this specific video since it is actually a large video (1.08GB total). So you may want to download and watch it if you don’t have a super reliable high speed internet connection.

Also for more on Jeremiah’s work check out http://www.brentozar.com/articles/riak/  and contact him at http://www.brentozar.com/contact/

Widgetz SQL Server DB + ASP.NET MVC 3 Web Application Scaffolding

This is a quick walk through of how to setup a database, create some model objects, and build a scaffold for CRUD (Create, Read, Update, and Delete) all using ASP.NET MVC 3 and Visual Studio 2010.

Open Visual Studio 2010 and start a database project as shown.

SQL Server 2008 Database Project in Visual Studio 2010 (Click for full size image)

SQL Server 2008 Database Project in Visual Studio 2010 (Click for full size image)

Next create an ASP.NET MVC 3 Web Application in this solution.

ASP.NET MVC 3 Web Application (Click for full size image)

ASP.NET MVC 3 Web Application (Click for full size image)

Now open the Server Explorer in Visual Studio 2010 (You might have to click on View to find it if it isn’t already available in the main IDE). Right click on the Data Connections node in the Service Explorer Tree. Select Create Database and provide a name for the database.

Create a New SQL 2008 Database

Create a New SQL 2008 Database

New Database Dialog

New Database Dialog

Next in the Server Explorer, right click on the newly created database and select New Query. Then in the query window paste this SQL create script in and run it by right clicking and selecting execute.

CREATE TABLE [dbo].[Widgetz] (
    [Id]           UNIQUEIDENTIFIER NOT NULL,
    [Name]         NVARCHAR (50)    NOT NULL,
    [Description]  NTEXT            NULL,
    [Created]      DATETIME         NOT NULL,
    [Terminated]   DATETIME         NULL,
    [Addon]        BIT              NOT NULL,
    [Active]       BIT              NOT NULL,
    [Cost]         MONEY            NULL,
    [ChildWidgetz] INT              NULL
);

ALTER TABLE [dbo].[Widgetz]
    ADD CONSTRAINT [PK_Widgetz] PRIMARY KEY CLUSTERED ([Name] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

ALTER TABLE [dbo].[Widgetz]
    ADD CONSTRAINT [DF_Widgetz_Created] DEFAULT (getdate()) FOR [Created];

ALTER TABLE [dbo].[Widgetz]
    ADD CONSTRAINT [DF_Widgetz_Active] DEFAULT ((1)) FOR [Active];

ALTER TABLE [dbo].[Widgetz]
    ADD CONSTRAINT [DF_Widgetz_Addon] DEFAULT ((0)) FOR [Addon];

Once the table is created bring to focus the Solution Explorer and right click on the Database Project, selecting the Import Database Objects and Settings.

Import Database Objects and Settings...

Import Database Objects and Settings...

Next select the correct database connection, and leave everything else as it is set.

Selecting the appropriate database, options, and click next... (Click for the full size image)

Selecting the appropriate database, options, and click next... (Click for the full size image)

Next the wizard will finish importing the database objects.

Finished (Click for full size image)

Finished (Click for full size image)

If you look through the folder tree of the project you’ll find individual files for the table, defaults, and other object settings.

Now right click on the Models directory of the ASP.NET MVC 3 Project that was created earlier. Select add and then new item. In that dialog add a new ADO.NET Entity Data Model.

Yup, just when you think it had gone away...  ADO.NET (Click for full size image)

Yup, just when you think it had gone away... ADO.NET (Click for full size image)

A dialog will appear, select the option to Generate from database and click next.

Entity Data Model Wizard (Click for full size image)

Entity Data Model Wizard (Click for full size image)

Next select the correct database connection again, then click on Yes to save the sensitive data (which if you’re using windows authentication it isn’t really going to save anything dangerous). Make sure the entities are named appropriately and click Next.

Options for Creating the ADO.NET Entity Model

Options for Creating the ADO.NET Entity Model

The next screen, which may take a moment or two to display the list of tables and objects, will appear. Select the table that has been created and click on

Entity Data Model Wizard (Click for full size image)

Entity Data Model Wizard (Click for full size image)

Once done and finish is clicked, the entity model file will show the display view of the entity that was just created.

Entity Model

Entity Model

Now right click on the controller folder and select to create a new controller.

Add Controller (Click for full size image)

Add Controller (Click for full size image)

Be sure to click the advanced options and select the layout page.

Advanced Options (Click for full size image)

Advanced Options (Click for full size image)

Now click on Ok and then Add. Visual Studio 2010 will not generate the scaffolding for the controller and views around CRUD operations.

The Scaffolding in ASP.NET MVC 3

The Scaffolding in ASP.NET MVC 3

This might seem like an absurdly easy thing to do, and you’d be right. There are however many steps to turn this into a feasible, well design user interface, and provide a solid and intelligent user experience (UX) to the end user. Also, this is arguably a not so maintainable bit of work. If the end user ONLY wants to operate on the data with CRUD, then this is great. But the minute something else is needed a very different, testable, and maintainable architecture should be utilized instead of this generated scaffolding. Which in coming blog entries I will be covering some of the ways to create testable, maintainable, and better designed architecture around more than CRUD.  🙂

CODE: All the code for this project is available in the Lesson 1 – ScaffoldGeneratedWidgetz.

A SQL Server .NET ASP.NET MVC RESTful Web Services Facade – Part I

Did I get enough of the acronyms and key words in the header?  It looks like soup!  :O

This is a somewhat messy project to build a prototype layer around SQL Server. The reason for this, shockingly, is to allow for a SQL Server to be used by frameworks and systems that normally don’t or can’t access the database directly. In my particular scenario we’re working on getting Ruby on Rails running with JRuby in a Windows Environment. Because we will need to utilize a lot of SQL Server Databases, it seemed like a great idea to build out a layer over the SQL Server (or Servers) so that a Ruby on Rails Web App, ASP.NET MVC, or even a PHP or pure Javascript Application could access the data in the database. What better way to do that then to create a RESTful Web Services Facade over the database.

Some of you might be thinking “Why not use RIA Services?!?!?! Are you mad!!” Well, there is a big problem, RIA Services doesn’t work against SQL 2000 or SQL 2005, which is the database technology that this particular requirement dictated. Well, now that you have context, I’ll dig straight in to what I did building this prototype out.

Kick Out a SQL Server Database Project

I need some data, and a database, with just some of the standard junk you’d expect in a production database. One of the best ways to throw together a database in a really short amount of time, with data, is to use a SQL Server Database Project.

New Database Project (Click for larger image)

New Database Project (Click for larger image)

You might see this and think, “But you said that the facade is against a SQL Server 2000 or 2005 database!” Well, it is, but to get a database running locally and have this project type work, I’m using my local SQL Server 2008 Express installation. However, I’m limiting myself to data types primarily available to SQL Server 2000 and 2005. So no worries, this works just fine against those archaic databases.  😛

First I ran the following script to create the database and some sample tables with various data types.

DROP DATABASE SomeExistingOrMigratedDatabase
GO
CREATE DATABASE SomeExistingOrMigratedDatabase
GO
USE SomeExistingOrMigratedDatabase
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person] DROP CONSTRAINT [FK_Person_Village]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeFlatDenormalizedDataTable]') AND type in (N'U'))
DROP TABLE [dbo].[SomeFlatDenormalizedDataTable]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Village]') AND type in (N'U'))
DROP TABLE [dbo].[Village]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Village]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Village](
	[Id] [uniqueidentifier] NOT NULL,
	[Village] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Village] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeFlatDenormalizedDataTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SomeFlatDenormalizedDataTable](
	[Id] [uniqueidentifier] NOT NULL,
	[StarzDate] [datetime] NOT NULL,
	[Numerals] [int] NULL,
	[Numberals] [int] NULL,
	[Monies] [decimal](14, 4) NOT NULL,
	[Day] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Year] [int] NOT NULL,
	[BigNonsense] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Flotsam] [float] NULL,
	[Jetsam] [float] NULL,
	[SmallishText] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BiggishText] [nvarchar](2999) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SomeFlatDenormalizedDataTable] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[VillageId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Village] FOREIGN KEY([VillageId])
REFERENCES [dbo].[Village] ([Id])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Village]

Once the database and tables are created, import the database into the database project. To do this select the “Import Database Objects and Settings…” by right clicking the context menu on the Database Project.

Import Database Objects and Settings...

Import Database Objects and Settings...

Select the database just created and click on start. Once the script generation is done, navigate into the project directories and you will see the following scripts have been created.

Generated Scripts (click for larger image)

Generated Scripts (click for larger image)

Next create a new data generation plan in the Data Generation Plans folder (notice I already cheated and have one in the above image).

Creating a Data Generation Plan

Creating a Data Generation Plan

Open up the file this creates (I called mine BuildSomeData.dgen). In the file, note I selected the relationship between the Village and People Tables, and set the ratio to 60:1. When you change the data in the Village table it then automatically updates how much data will be generated for the People Table.

Data Generation Plan

Data Generation Plan

When all that is done, hit F5, select the database and the data will be generated. That gets us a database with data to use as an existing source. From here I’ll jump into creating the actual Facade Layer.

NOTES: Once you generate data, depending on how much you decided to generate, you may want to see how big your database is by using the sp_dbhelp stored procedure. I am however, unsure which versions of SQL Server this stored procedure is available in.

Code for this project is available here: https://github.com/Adron/ExistingSqlServerProject