Tell us a little about MYdbPAL for SQL Server.
MYdbPAL is one of a family of products under the dbPAL banner. You can have a full multi-DBMS product – Professional or Universal, or you can work with one target database – in this case for SQL Server – and still have import capabilities from all other supported DBMS types.
What we set out to do was to build a database toolkit that is independent of any particular DBMS type, yet will cater for and work with most modern database. So, instead of just flip-flopping or converting from one to another, at the core of dbPAL’s architecture is a very complex object structure that standardizes individual DBMS features, such as data types, and translates them into a universal set.
We then took the most common tasks involved in database development — designing and building schemas; modeling; reporting — and built a professional set of tools and integrated them into one package. We also looked at data handling and, especially for the development environment, decided to incorporate a set of data ETL tools that would facilitate easy movement of data between databases and make provision for tasks such as synchronization.
Then we considered the age-old problem of change management. This boils down to one simple question. How can one tell, post event, if an object (such as a column) has been re-named or is in fact a deletion and subsequent addition? There is only one answer to this, and that is to maintain a very detailed system of change management right down to the most detailed object level. So, one of the most powerful features of dbPAL is its ability to track changes and in fact to manage its entire environment using version control. So, you can for instance, export data from one version of a database and automatically pump it into another version and dbPAL will translate everything for you. You can even go backwards in version.
Finally, we took a hard look at the user interface. What I have always liked about Windows is the iconized drag and drop environment. I just hate pop-up dialogs that take you through long sets of prompts, swapping between keyboard and mouse, only to end up with a question you can’t answer or something that means you have to cancel out and start again. And I also hate having to repeat the same process time and time again. So, after four attempts at the dbPAL GUI in three years, we finally came up with a desktop design that was very visual (based upon an infant shape sorter) and relied on drag and drop techniques to build complex database access commands. We risked asking clients for a short but steep learning curve in return for absolute ease-of-use when you get there. Some people love it immediately, and some take a little longer to adjust to it.
The big benefit of dbPAL is that you can have most of the tools that a DBA or database developer might use frequently, in one integrated package that is DBMS independent. So, if your organization moves platform, the staff have a far lower re-training overhead. Often, you need to buy different packages to do the same task for different databases. Since around 70% of IT sites work with multiple DBMS types, with dbPAL, you can dramatically reduce the number of individual packages you need to buy. Typically a dbPAL investment is a 1-2 man day cost. We would expect you to get that payback time and time again.
All DBAs and SQL Server developers can become more productive. What are some of the issues (problems) that you see that prevent them from as being as productive as they could be?
Lets look briefly at the three main areas that involve ongoing systems development: systems design, software development, and finally ongoing bug-fixing and changes.
I am a firm believer in the principle that the database design must be right from the outset. The key is both to understand the user needs and to understand how best to structure a database not only to achieve good performance, but also to make it relatively easy to develop the software. Time and time again, I have seen projects get into trouble because of a lack of understanding of user needs and processes. I am fortunate in having been trained as an accountant and auditor, so I had a very good understanding of business systems before I ever saw a computer. Unfortunately, in-depth training in systems analysis and design does not necessarily teach you to learn what makes a particular business or department process tick. Another important aspect here is one of balance. A database design can be over-simple, and can be over-complex.
Productivity management for a software developer is a nightmare. After all this time in the industry, I have yet to formulate a means of forecasting just how long a job will take. The reason is that most of the time one is unable to uncover little (and sometimes large) nuances in the way a particular process needs to work. So what looks like a two hour job suddenly stretches to two days. The conservative answer is to spend more time at the design stage, probably using some design methodology or another. But that can take away the ingredient of creativity and spontaneity that can make a difference between a good program and a great one!
Another area that causes productivity loss is the question of synchronizing work between team members. Someone comes up with a need for a database design change, and that can have a serious ripple effect on the rest of the team. Distributing database design changes is one of these areas. Many a time in the past I have had to apologize to a developer and tell him to scrap his test data and start again.
Another big hit to productivity comes when the inevitable bug arises in a system you are not currently working on or familiar with. Again, I believe that much of this goes back to failing to design and build good systems in the first place. However, all systems have their moments of unusual interest.
What can a DBA or developer do to overcome these issues and become more productive?
For most of my career, I have tried to stand back from issues and see how I can improve the big picture by some automated process, or by adopting methodologies that will be not only labor-saving but also relieve tedium. Believe me, when you used to handwrite your COBOL program on coding sheets, any productivity savings were good ones.
So I advocate the use of productivity tools — from third parties where they are clearly useful, and also in-house. We are always building tiny and not-so-tiny routines that improve productivity and reduce the risk of error. Just recently with dbPAL, we have automated the product build process (which involves gathering lots of different units together and packaging them up in the installer). We now save several hours on each build, avoid manual mistakes, and give ourselves more time for quality assurance.
Improved system design and change control is also fundamental in controlling and improving productivity. I have already waxed on about the need to get the database design right (or almost right) the first time. I like to prototype. With a tool such as dbPAL, I can turn round design changes in a matter of minutes. Once I have a design together, then good basic documentation is a must. I can keep designs for years in my head, but others need to see it drawn out and written down.