Voice of the DBA

Tag: software development

API Changes
Not the best API documentation

There’s an interesting set of predictions about the possible new APIs (application programming interfaces) that we may see in the future. It’s a look forward into 2012, and a guess that setting standard ways in which we can build systems will become more important in the future. There were two items that I found of particular interest to the data professional.

The first was the idea that we might have more data APIs in the future, designed to make the exchange of data easier. The people that work with data imports and exports have probably been hoping for his to become more prevalent, and it has gotten better. However I do think that data is becoming the most important part of the technology world. It isn’t the software, or the platform, or the device, but the data that’s available that is the most valuable part of a technology system.

It isn’t likely that any company can create and manage all this data themselves, especially in a rapidly evolving world where so much of it changes from day to day. A successful business will need to acquire data from other sources and easily integrate the information into its systems,, and an API will make this process smoother. I expect that standards like OData will become something that we work with more in the future.

The second interesting part of the article talked about the possibility of us seeing more Enterprise APIs, developed and built by individual customers so that their systems can be more easily integrated with others. That would seem to be counterintuitive, but with so much knowledge and talent available outside your company from others that want to build apps to interact with you, I expect this may catch on, at least for those companies that see a demand from partners and customers. One very valuable effect of building an API? You get more data on how your customers use your API, allowing you to better understand how your company might change to enhance its performance.

Building an API means setting a standard, one you have to support and maintain for years. It can evolve, but the core elements of it will live for a long time, so it helps to gain experience here. Think about providing APIs as you develop software inside yout company, rather than just assuming you’ll build all the functionality needed.

Steve Jones

The Voice of the DBA Podcasts

We have one version of the podcast back and are working to get the others back soon.
- Watch the Windows Media Podcast – 27.8MB WMV
- Watch the iPod Video Podcast – 22.0MB MP4
- Watch the MP3 Audio Podcast – 4.5MB MP3
February 9, 2012
Change Management

Hurry up and wait.

Hurry up and wait.

In a couple of the large enterprises I’ve worked in, that might have been the IT motto. It seemed as though the internal developers were always under pressure to get applications finished as soon as possible. However we often found that when applications were finished, there would be a delay in deploying the new code to the production servers, usually because of a strict change control process that required documentation and testing of the changes on related systems. In many smaller companies I’ve worked in, we had no change control process at all and could deploy updates at any time.

I’m not sure which of those two systems I prefer. In general, I prefer to have some change management process to ensure that I can easily determine what changes were made at any time. However I’ve found that any change management quickly becomes a bottleneck devoid of common sense and full of bureaucratic nonsense. This Friday, I decided to ask a question about change management, but not about your opinion of whether it’s good or not. The question this week is:

Do you follow a change control process 90+% of the time?

By this question, I mean is change control a habit, an ingrained sense of the way you work and deploy updates to a live environment. I’m not asking if you have a formal process, if it’s a team process, or anything about the details, but rather do you actually follow some methodology to track and manage changes?

I’d like to think most of us would, if for no other reason than to answer the common “what changed?” question that always comes up when something breaks. However I’m curious to see the results.

Steve Jones

The Voice of the DBA Podcasts

We are still having hosting issues with the podcasts. We hope to resolve this and be back to releasing the podcast versions of the editorial next week.

February 3, 2012
Should we outsource identity management

My Database Weekly editorial for Jan 30, 2012

One of the first things many people build when they’re developing an application these days on the web is a login/identity system for their users. This allows them to identify people, customize the features and functions, and separate out the different classes of users from each other. However many developers don’t really understand good, secure design, much less encryption, and end up “storing passwords hair kari in unsecured databases”, a quote from this piece on password security.

I’d hope that most developers know that passwords should be stored in a digest (one-way hash) form, but that’s like assuming most developers understand the issues with SQL Injection. It’s not true, and not necessarily going to be true until, well, it’s not likely to ever be true. Even if we had minimum standards, there are plenty of developers that would ignore them and plow forward with the arrogance that their custom method is better implemented, and more secure, than any standard.

Passwords and password security are hard. I’d hope that most people would be looking to move to passphrases instead of passwords, but I don’t see the recommendations being widely disseminated in the world. I recently rebuilt a computer, requiring my kids to implement new credentials, and my recommendation was a simple phrase they can remember with a number. I was hoping it would serve the dual purpose of instilling good security habits as well as improving their typing skills. We’ll see if it helps.

With large rainbow tables, creative social engineering, and poor application security, it’s even more important now that we use passphrases, and even develop better identity frameworks for applications. Personally I liked the Passport system Microsoft had, and I like the OAuth system even better. Perhaps we can get more of these frameworks implemented in reference applications and frameworks, as a default way of managing identity systems.

That’s the easy part; convincing developers they can’t manage identity better is probably the hard part and I would love to see some good ideas for that.

Steve Jones

January 30, 2012
A Quick Export with SQL Packager

Disclosure: I work for Red Gate Software

Someone asked me the other day if I’d ever used SQL Packager to export a table to send to another person. I hadn’t, and in fact hadn’t even ever run the tool, but this individual said it worked great.

Since Red Gate tools are designed to be simple and intuitive, I thought I should give it a try and see what happens. I went through the Start Menu and found SQL Packager in my toolbelt installation:

I documented this as I went, shooting this images as I went through the process for the first time. As soon as Packager started, it began the packing wizard.

SQL Packager is designed to help you bundle up a database, or part of a database, as a part of an installation in your application. It can produce an .exe, a C# project, or a set of scripts that you can include as a distributable item in your application installation (or upgrade). The information can be compressed, so you reduce the requirements for your customers.

In my case, I decided to just package up a table. I first signed into my local instance, and chose the AdventureWorks database.

Next, I chose just one table, the Customers table. The Red Gate tools tend to follow a similar, intuitive design, and try to do the most common things for most customers. In this case, the entire database was selected (this is a database packaging tool), so I deselected all, and then chose the Customer table.

Once I choose the table and click next, and confirm the selection, the packaging begins. I get some options as to how I might choose to build my package.

The options are shown, and in this case I choose to save the script. Once I clicked next, I had a change to see the final script. First there was the schema tab:

On this tab, all the DDL for my table is there, including a couple dependent tables, and some functions needed for defaults or computer columns. Keys and indexes were included.

On the data tab, I had the DML for the actual data.

The comment says “Add 1000 rows”, which seems like a default. However I went back and checked in SSMS, and sure enough, my table had 1000 rows.

I clicked next, and had the chance to specify a save location.

After saving, I opened the script in SSMS, just to check. Sure enough, the DDL was at the top:

and the data at the bottom:

Simple, and easy.

If you are looking for a way to move certain sections of your database for a deployment, like all the lookup tables, give SQL Packager a try.

If you need to send some stuff to a client or friend, it might be a simple way as well to export the DDL and DML into one package.

January 26, 2012