Voice of the DBA

Tag: Redgate

  • Speaking at SQL Saturday #389 – Huntington Beach

    I’ll be traveling to CA next month for SQL Saturday #389 – Huntington Beach as well as a Red Gate DLM training session run by Ike Ellis. I’m assisting Ike in running a Database Continuous Integration class. It’s a paid for event, but you’ll learn how to set up and run a CI process with your database.

    Come.

    CI is all the rage and companies are improving their development processes, building applications faster with it. We go into depth, using Red Gate tools, on how you can get your database development working in a CI environment, and integrate it closely with your application development work.

    I don’t have details on my SQL Saturday session, but that should be coming soon. I will do a Red Gate presentation during lunch, so if you want to know how we can help you or have questions, come by at lunch.

  • Data Generator – Limiting Values

    This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

    I needed to generate some data for some development work on the SSC database. No, I’m not allowed to change code directly, but I was looking to send some changes to the development team, already done, and then hopefully just have them test and deploy it.

    In my case, to avoid exposing any real data in case of issues, I downloaded the schema only to my laptop. The I created a database with all the objects. One of my first areas of work was on the points and scoring systems, but to do that, I needed points.

    sql-data-generator-150

    I fired up my copy of Data Generator, let it detect the objects, and pre-populate the fields and clicked “Generate Data”. That worked well, and I had a bunch of data in my system.

    pointsgenerator_c

    My first area of work was to rewrite some procedures that perform calculation. I did that, ran a simple SUM, and got this:

    Msg 220, Level 16, State 2, Line 3
    Arithmetic overflow error for data type int

    Not what I expected. I just generated some data and ran a sum. What could be the issue?

    It turns out that the default settings for integer columns are shown here:

    pointsgenerator_b

    That’s great if you want a random distribution, but it’s not so good in this case. The points values I want to store for each row should be fro 1 to 7, and randomly distributed. I’d actually like them to be weighted towards 1 and 2, but for this project, it doesn’t matter.

    I decided to fix things by first deleting all the points data. Once this was done, I could then select the table on the left, and select the column.

    pointsgenerator_d

    This changes the right panel to the specific settings for this column. I changed the values, as you can see here, to be more in line with my needs. Only values from 0 to 7 are included.

    pointsgenerator_e

    I could actually use different settings for different columns. For example, for the PointsCategory column, I used these settings, from 1 to 1,000.

    pointsgenerator_f

    With these new settings, I generated new data for this table, and then my aggregate calculations worked.

    Data generation is a very handy thing to have, especially in development environments where you don’t want live data. In my case, while I think my systems are fairly safe, I’d hate to lose my laptop, with a copy of the SQLServerCentral database and a million emails that people might not want shared.

  • Learning Continuous Integration for Databases – LA Apr 10

    I’ve been talking about continuous integration for databases for a couple years now as a part of my job. My employer, Red Gate Software, has been building tools to help database developers build better software. We (Red Gate) want to sell software, but we also really want to help people build better software. It’s a big reason why I’m employed there. Red Gate truly wants to make the world a better place while we run a business.

    I’ve gotten lots of interest and questions from my one hour talk and late last year Red Gate decided we needed to do more. We built some all day continuous delivery classes last year and they went over well. Over the winter, Grant, myself, and a few others at Red Gate got together and we’ve been working on adding depth to our instruction and have come up with 3 full days of training to help you build better database software, quicker.

    Continuous Integration in LA

    The middle class we’ve developed is an all day look at how you implement a continuous integration process with a database and application. On April 10, 2015, I’ll be coming to Los Angeles, CA, along with one of our partners, Ike Ellis, to work on a full day of training. It’s a $500 class, but you’ll walk through a couple of different ways to set up a CI process, actually perform the steps yourself, and make changes to the database that get built and tested.

    We think it’s a great way to smooth out your development process and bring more engineering to the way you deploy database updates. Ike is an expert SQL Server consultant and he’ll show you how to create your own CI process. I’ll be in the background to help out, and we think you’ll gain some in depth knowledge that you can use at your job right away.

    If you want to advance your database development, register today and join us in LA on April 10, 2015.

    register

    I’m excited to be a part of these classes, and I hope you are as well.

    We do cover a number of Red Gate tools in the classes, but the procedures and steps are applicable to other methods of accomplishing the same tasks. Even if you choose to use other tools, or write your own, the skills we teach you will be applicable.

    I wouldn’t write my own, however. I’d easily burn the cost of the tools many times over trying to iron out the bugs in my own process.

    The Continuous Delivery Process

    The whole idea of continuous delivery is that you can make changes at anytime. It isn’t sloppy work, untested development, or anything else. This is a way to bring more engineering to software development, and I really believe in it.

    It’s not easy, however, and that’s why we’ve built three days of training. We cover these items in the three days.

    • Day 1 – Version Control for Databases
    • Day 2 – Continuous Integration for Databases
    • Day 3 – Automated Deployment

    We haven’t set these up all in one week, mainly because it’s too much information for you at once. Ideally we want to tour around, running one of these classes each quarter, because it will take you a few months to implement the information and understand it.

    If you get past these courses, we’re also partnering with expert ALM consultancy shops that can help you even more.

    We’ve got a number of all day events coming up for you to consider, so check out our list of training days. We’re adding more all the time, and let us know if you want us to come to your area.

  • 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.