It seems that I regularly meet people in the SQL community that are asked about moving their datastore to a NoSQL type of database. In a mature system, that might be a conversation worth having, especially if there are issues with handling the workload. I doubt there are many type of workloads that I would think are worth moving, but perhaps some exist. However, the discussion about moving is one you can have, given your understanding of the deficiencies you see in your datastore.
Often I often find is a different story for new applications. Often I hear about developers becoming enamored with a datastore, a simpler one than SQL Server or some other RDBMS, and look to MongoDB, Cassandra, Hadoop, Neo4J, etc. as the shiny, new, exciting choice. They think NoSQL offers an easier way to build their application, where they can throw and grab JSON through an API, while changing their minds on the ways in which they need to handle data.
They approach a new application like a startup would, using some exciting technology that has just proven to be successful by some hot company in the news. They look forward to developing an application that other developers will be jealous of next year. Or they just want to use something new that's being written about on blogs and spoken about at conferences. Or they just want to do something more interesting on the next project. All these might be great approaches, and certainly are ways that make one's job more engaging.
However. Just as this piece notes, choosing a new datastore because it's new and exciting, and not because it fits the data model is a mistake. The data model matters, often because this is the permanent output of your software application. The data will live on and needs to meet current and future needs, separate from the amazing Node.JS framework of the week that your users love. Actually, users don't love your framework. They love your work. Whether it's MVC, Angular, Django, or Dapper, most clients could care less. Any language/framework/platform can build an application that works well for clients. Your application just has to work.
Relational databases work really, really, really well. They handle most problem domains well, with protection against common consistency errors, and they have plenty of features that help you build a solid data model. There are tips and tricks to migrate your schema to some new form if you need to. However, if you need to migrate your schema every few days, you haven't really thought about the schema. As the article notes, you need a schema. On read, on write, or both, you're not getting away with ignoring schema.
And by the way, if you keep your schema in Python or C# or some other language, you're likely creating a set of "legacy code" that will haunt you for years and is much harder to manage than database schema migrations. Maybe it feels less risky, but it's probably way less fun to work on.
Databases are a pain to work with. I get that. It's so much more cumbersome than refactoring C# classes on the fly. Databases have a different job, and they do it well. If you have a good reason to choose some other datastore that's related to your problem domain and data model, then go ahead and choose something else. If you don't, then use SQL Server (or PostgreSQL, Oracle, etc.). Most of you won't, so just stick with relational databases. You might not thank me, but your organization and future developers will.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
Could your SQL coding be more efficient?
Data Platform MVPs and SQL Server experts share their tips on how to standardize formatting whilst stripping out the repetition of coding with SQL Prompt. Over these 20 short videos we will help you write better, shareable SQL faster. Check out the tips
Database migrations inside Visual Studio
Feeling the pain of managing and deploying database changes manually? Redgate ReadyRoll creates SQL migration scripts you can use to version control, build and release, and automate deployments. Try it free
Although it is easy to use SQL Server R Services to create R scripts that incorporate SQL Server data by passing in a T-SQL query as an argument when calling the sp_execute_external_script stored procedure, you are limited to that one query, unless you pass additional data directly between R and SQL Server via CSV files. It is simple to do, and opens up many additional opportunities for data analysis. Robert Sheldon explains how. More »
Redgate has launched a new research study that reveals the benefits different stakeholders can expect from DevOps, and provides a powerful methodology for calculating the ROI of database DevOps. More »
Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
I have loaded a data frame from a CSV with this code:
gdpvalues = read.csv("h:\\downloads\\gdp2.csv")
Unfortunately the file doesn't have a header row. Right now I see this in R Studio:
I want to add column names to the data frame. What code can I run?
Remove duplicate values
- Hi Experts,
In the below table records are grouped by ID column. If the count for ID is greater than 1 then...
Transactional Replication :: Error Creating New Publication
- Attempting to create a new publication and received this error:
TITLE: New Publication Wizard
SQL Server could not create publication 'XX_TABLES'.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.