I was interviewed in Redmond a couple months ago and the video was just published. I show and demo some database migration work with ReadyRoll in this video:
https://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Database-DevOps-with-RedGate-ReadyRoll/player
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.
Steve Jones
Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.
I should write about my first one, but I just copied Rob’s test, so that’s not so exciting. Instead, I decided to take his advice and write some code, then decide how I test it. This isn’t really TDD, but I need to understand how Posh returns things, so I’ll figure that out first.
I decided to work with SQL Clone, since that’s an area I’m working in a bit already. SQL Clone has a set of PoSh cmdlets that you can use to create database clones, so I thought that would be a good test.
My process is to remove a clone if it exists and then add one back. For testing, I need to consider what actually is happening here.
The result of this process means that I’ll have a Clone, no matter whether I created a new one or had to remove one and recreate a new one. Or, if I had a Clone already and my script failed. This last one is something a tester needs to be aware of.
In my case, I can determine if there is a new clone by looking at the created date of the database object. If that was later than the beginning of my test, then I could likely assume my clone was new.
To start with, I created a function that would destroy and create a simple clone. Once I had that, I could now write my Pester test.
I started with a “describe” element, in which I loaded my function and set a starting time.
Then in the “it” section, I run my function and then check the instance, getting the CreateDate property of my clone database. If my function has worked, this will be a new clone, created since my test started. I compare that to complete the test.
I ran this with Invoke-Pester, and it worked.
This wasn’t simple. I had to test my test a few times, and use PoSh commands to verify it was doing what I thought. I also changed my function with a hardcoded db clone name to ensure the test fails. Results for that one here:
Of course, I changed things back and tested again. Now, as I update my function to include adding in the instance name and image name, this test should still pass. Of course, I can add in other tests, or change this one, to allow me to test on different images and instances as well.
A simple test, maybe a silly one, but I learned a few things about my PoSh code (and how to write it cleaner) as well as Pester and adding in another unit test framework. Now I can expand this to test other PoSh items I have and practice writing better tests that will give me confidence my code works in a variety of situations.
It’s one week to SQL in the City 2017, once again streamed. I’ll be traveling this weekend to the Redgate office and getting setup. After two days of rehearsing, I’ll be ready for the live broadcast Dec 13.
Here’s a quick promo video as well:
Once again, we’ll be covering a number of topics that you will be interested in such as code analysis, performance counters, Database DevOps, and more. Perhaps a topic that most of us need to learn more about is data protection and privacy. We’ll have a bit to discuss here as well. This is a topic that is important for us as GDPR will take effect in 2018 and will impact us, as well as many of you.
You can see the full schedule, which is going to be broadcast twice. A lunch show for Europe and an evening show (for me) for the afternoon in the US. We’ll be live both times, so tune in and bring your
A long day for me, though I hope to get out to the gym between the shows. We’ll see as I’m sure I’ll have a few meetings with product groups as well.
Register today and I’ll see you online next week. Brings lots of questions and we’ll do our best to give you the answers you need to improve your database development.
