Voice of the DBA

Category: Editorial

  • No State Based Deployments

    I’ve been studying deployment ideas and technologies for quite some time now, but it’s been a focus for the last year or two as my employer is interested in this area. I’ve been reading books and talking to people, comparing their experiences with my own. I’ve found that deployments for databases are much more complex than those for other software, but essentially there are two approaches that people choose: migrations and state based migrations based deployments.

    Migrations based deployments are based on tracking each change to a database in a separate script and then executing each script, in order, on your production database. This is hard to do, requires discipline, and is what most people find difficult to do. Scripts get quite numerous, ordering is hard, and deployments become scary and largely unsuccessful without a lot of care. The scripts require tweaks and fixes to work efficiently, and you need smart DBAs and developers.

    State based deployments are what many people try to use. They look at the state of production, the state of development, run some tool like SQL Compare, or a process, and determine what changes need to be made to production to get it to match development. There are variations, but this is the basis for how many people try to create a deployment process. It seems easier.

    The problem is that at some point a state based deployment process won’t work. It’s actually impossible for a state based approach to work in all situations. Simple actions, like renames, can’t be handled by examining two states. In order to properly manage changes, and keep your data intact and safe, you need to understand “what” happened during the process, not just the end result.

    In many situations, state based deployments can work, and they can be used successfully, but there is always the possibility that you’ll need to customize your state based approach to include some migrations scripting. Keep that in mind, and make sure that any process or tool you choose has that flexibility. If you understand you need to review and modify scripts at times, you should be successful.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The Express Choice

    There are a number of editions of SQL Server, each of which has different capabilities, features, and restrictions. Over the years, the mix has changed, and it can get confusing for customers trying to decide what to purchase and use. Fortunately things seem to have become simpler the last few years, but you still have to make a few choices.

    The Express Edition has the most restrictions, including a database size restriction, but in many ways, it’s a very capable database server. It’s the evolution of the “desktop database”, MSDE, that was designed to take the place of Access for desktop software that needed a database.

    Recently I ran across a discussion on using Express in production, and I was surprised that many people didn’t think it was  a version capable of acting as a production server. It’s the same code base as the other versions of SQL Server, with more restrictions. This week, I wanted to see how most of you feel.

    Would you use Express Edition for a production database?

    I would. In fact, given the way licensing costs have soared for SQL Server, I’d be tempted to use Express in many places, especially for departmental sized applications. I wouldn’t care whether they were web based or client/server. As long as the database would remain below the 10GB limit and the 1GB RAM limitation didn’t kill performance, I think Express is a fine choice.

    Of course, outgrowing Express can be quite expensive and a shock for someone using it, but if you need a more powerful server, you need one. I just prefer to defer that cost if I can.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Building Small Habits

    I was reading Kenneth Fisher’s piece on constraints recently and was struck by the fact that this is one of those small changes you make to your coding that has big benefits. In essence, rather than being lazy and writing this:

    CREATE TABLE mytable ( myID INT PRIMARY KEY, MyCol ....

    You decide to explicitly add a few more characters. You note this is a constraint and you type this:

    CREATE TABLE mytable ( myID INT CONSTRAINT PK_MyTable_MyID PRIMARY KEY, myCol

    It’s a small change, but it takes you from an object named PK__mytable__783FBF31F8293E74 to one named PK_MyTable_MyID, which is way easier to understand and debug when you have issues. More importantly, this is a DevOps move, where a developer can make it easier for operations people to understand the constructs and organization of a system that has moved to a production environment.

    There are all sorts of little coding changes you can make that improve the way you write code, and perhaps make it perform better. Aaron Bertrand has a whole series of bad coding habits, and at SQLServerCentral, we’ve published a number of pieces on different ways to code better, such as removing cursors from your code.

    By making some small improvements in your coding habits, changing to better techniques over time, you’ll slowly improve your entire codebase and help yourself and other developers code more efficiently in the future. I’d even recommend as a team that you choose a single change to make, and begin implementing it across the next month or two. Then repeat the cycle again. In a year, you’ll be amazed where you are.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Small Leadership

    I ran across Richard Branson’s top quotes on leadership. From a man who’s been quite a success in many areas, and has influenced many others, it was interesting to see what he considers good advice. He lists ten quotes from various others, some of which I’ve heard before. It’s an interesting mix, and these are certainly items that come from someone that could be considered a leader in business. I don’t know if that’s true, but certainly Mr. Branson is in charge of his company, and I believe that it is important for the management of a company to display leadership if they want to be successful.

    Most of us, however, aren’t in management positions. Most of us might not want to ever be in management positions. Plenty of technical people prefer to remain in their roles as developers, DBAs, analysts and more. However, that desire doesn’t mean that better leadership skills wouldn’t make our jobs easier, or make us more successful.

    The first quote in the story is this: “A lot of people have gone further than they thought they could because someone else thought they could.” That’s the quote of leadership that inspires, that helps others gain confidence and do more than they might otherwise. That’s the quote that shows a person who helps others is a great employee, not only because they get work done, but because they help others perform better.

    Far too many managers don’t believe in their staff, and make the clear constantly. Those managers aren’t helping themselves or their company, as they’ll ensure most of their employees don’t do the best work they can. I’d encourage all of you to think about how you treat others. Think about what you say or do to encourage others to become better at their jobs. Perhaps there’s a little more you can to do lead others, and get more work done, while celebrating the success you can have together as a group of professionals.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.