I’m in Chicago at the SQL in the City Summit. Therefore, republishing Can Auditing Fail? as I won’t be online.
Category: Editorial
-
Rolling Back Migrations
I happen to be a fan of database migrations as a way of making and deploying database changes. This is an approach that tracks each of the scripts run by developers in their working environments and the replays these scripts in production to deploy the changes. It works really well, and is the most bulletproof method I know of for ensuring the changes will work in production. That’s not to say there aren’t issues, but it’s the approach I favor. It’s a part of what SQL Change Automation from Redgate Software does, and it’s also what Microsoft and other companies see as the future of database deployments.
This is in contrast to making changes in development and then using some technology such as SQL Compare or Schema Compare in SSDT to create a script and use that to deploy changes. That works very well for many people, but I do find that most customers outgrow the technology with certain changes that don’t lend themselves to this state or model based method of script generation.
In either case, rollbacks are a concern for many DBAs and developers. After all, the database is a stateful service, as our data must be maintained over time and there are certain changes that are difficult to rollback. While many developers that might have renamed an entity or added a column might be tempted to just reverse the change, this isn’t always easy to automate, especially in most of the tools we use. Often we depend on the skill of a particular individual to manually execute the reversing DDL, and possibly determine what to do with any data that was changed.
I ran across another developer that things migrations are a better way, and that in a year of development, they never had to roll back any changes. While I agree that migrations is a more reliable process, I do think that assuming you’ll never roll back a deployment is highly optimistic. I’ve had tales of application developers being out of sync, of other applications not working with a new version of the database, and more. Some of these might be fixed with a quick roll forward, but ultimately I think that there needs to be an easy way to make rollbacks normal.
The one downside of migrations is that the reversing transactions can be complex, since each migration script might make complex changes moving forward. There isn’t much help from most migrations tools, whether that’s SQL Change Automation, FlywayDB, Entity Framework migrations, and more. These tools can certainly generate reversing code for simple changes, but any sort of complex alteration requires custom code.
My view is that a few simple rules govern how I view rollbacks. For views, procs, functions, I’d grab the previous version from our VCS and re-deploy that. I might try to run through a DevOps pipeline, but if I were in a hurry, I’d grab the code and run it. I’d also then recommit the old version as the latest one. For tables, we should write reversing migration scripts for any entities that are risky. Risky meaning this might affect my employment status. I’d be sure I had a second deployment pipeline that I could use to run these scripts in QA, staging/pre-prod, etc. after we’d verified the code we were deploying. I’d then have checks to ensure we really were reversing the changes.
The last rule I have is that I use time to make deployments easier. I would never add new columns and drop old ones in the same deployment. If I move or change data, I’d always ensure the old versions of data remained. That way I could reverse changes without problems. I can always do cleanup in a later deployment that just removes objects or data, but I want to be sure that old data is really no longer needed. That means I need to be organized and have a good calendar system to scheduling future cleanup work as well.
Migrations are really a better way to do database deployment, whether you’re working in a relational system, or you might be altering documents in a NoSQL system. Replay the changes you’ve made in development, that you are sure worked on “your” machine. You’ll be more confident they’ll work on another machine.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 6.4MB) podcast or subscribe to the feed at iTunes and Libsyn.
-
Quitting Over Training
I saw a tweet awhile back from Alex Yates that said this: “If your employer won’t provide training because they are afraid you’ll become more employable and get a job somewhere else… it’s time to quit. Immediately.”
That’s a strong statement, but one that I tend to lean towards rather than away from. I’ll disagree slightly because I don’t think that I should recommend anyone leave their job right now. You are all adults, and you should be able to decide when it’s time to leave, or what your particular circumstances dictate. Many of us have bills to pay, and we may not have a partner that works. Sometimes we have to just deal with a poor situation for some period of time.
I will agree with Alex that if your employer doesn’t believe in you, then that’s a problem. I’ll argue that if you don’t believe in yourself and make strides to improve your skills as well, that’s a problem as well. I’ve usually tried to invest in myself, through both time and money, and I expect my employer to do the same. Often showing some of my own initiative helps convince them that they should match, or exceed, my own investment. After all, they should get lots of the immediate benefits from any learning.
I’ve worked for companies that didn’t like to fund any training. In those cases, I usually make plans to move on to another employer. I’ve had companies that limited training to something that I might get every 2-3 years. That’s not the worst situation, but you will have to decide how important this is for you. I try to work with companies, understanding that spending $5k in training for every employee + time out of the office may not be feasible. At the same time, spending $0 doesn’t seem reasonable. At least, it’s not reasonable if there isn’t a lot of internal training and opportunity to experiment and learn new skills.
I say this often when I speak, but I’ll repeat it here. Your career is in your hands. An employer ought to assist you, and many do, but it’s up to you to request training, apply yourself to learn more, invest in yourself, and ultimately take advantage of opportunity. Build a plan, define measurables, and be ready to prove that you can apply some of the training in the months after you complete it.
A week at a class or conference isn’t a vacation, and if you treat it as such, I can understand why a company might be reluctant to make an effort to train you again. Time is a valuable commodity for all of us, and we shouldn’t be wasting it when we’re trying to grow our career. Focus, learn something, and share with others. You might be surprised as the benefits that come from this, including additional investment in you by your current employer.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.
-
Big Data Analytics
How large is your analytics system? Do you have more than one machine for analytics? Do you have a cluster of machines that run Hadoop in a YARN cluster to analyze your data? Are there hundreds, or even thousands, of nodes that are being used regularly? Some of you might have what you consider to be a large system, but I bet it isn’t as large as Microsoft’s cluster.
They think they have the biggest YARN cluster, with over 50,000 nodes in a single cluster. This is used to process multiple exabytes of data from their various properties and systems. I certainly haven’t heard of a system this large, and I really wonder what this costs to run. After all, I’d think a 50,000 node cluster has to be a significant cost, though perhaps in the grand scheme of Microsoft’s $100 billion in revenue and $38 billion in expenses, even 100,000 machines can’t really impact their numbers.
The cluster has essentially been running a private version of Azure Data Lake for years that their internal developers and analysts use to access a common pool of data. In fact, because of their scale needs and the desire to limit the copying of data between clusters, they have contributed back to the Apache Yarn project a number of fixes to help ensure the software can scale to tens of thousands of nodes. There is some discussion of how they’ve allowed YARN to grow to larger scales, and it’s an interesting solution that essentially allows some overbooking of resources, knowing there are always some spare cycles available for processing data. It’s a great test site for Azure Data Lake, and something that more of us might use in the future.
I doubt may of us would need to work on data sets that large, and I know I certainly wouldn’t want to be responsible for that much of a data lake, I do think these are interesting problem domains that someone should look at. Certainly there are always large organizations and governments that have ever growing pools of data that will likely end up in a data lake of some sort. And who knows, perhaps, the definition of large will continue to grow to the point where 1,000 nodes in a cluster is considered “small”, and it’s what many of our businesses might implement in the future.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.
