Most new users of PowerBuilder start with SQL Anywhere as the back-end database engine (formerly Adaptive Server Anywhere or ASA, and before that Watcom). With growth, a need to shift and adapt to a wider database platform is thrust on developers by customers. A recent exercise in this regard, besides re-emphasizing our past amateurishness, has also helped us make a clean, lean application. This article, besides providing insights on how to avoid basic mistakes, also has a few tips for our experienced colleagues on how to do some spring cleaning of established applications.

Background
Our application development started as a hobby without any formal training (see About the Authors); so our application-making approach was based on intuition. We found PowerBuilder to be the most suitable platform for our data- and report-centric needs. It was natural to use Watcom and later ASA 5 to create the database. By the time we had a real workable application ready (around two years for our own use, and six years for commercial use), the database structure created, though faulty as per the norms followed by other RDBMSs, was working well. A system analyst friend had given us ERwin case tools and some initial insight on normalization, etc.

The demand for Adaptive Server Enterprise (ASE) first arose in 2005. A previous attempt to shift to SQL Server met with partial success but wasn't completed. A similar exercise has been documented by Timothy Beck ("To Convert or Not to Convert ASA to MSSQL" http://pbdj.sys-con.com/read/42622.htm). With ASE and SQL Server sharing the same birthright, the processes involved were largely similar.

However, we do have many new insights to share over and above those mentioned in the previous article. To avoid redundancy, most of the database transfer issues already mentioned will be kept out of the discussion. So this article should be seen as an addendum to the above.

Our Application
We have 19 PBLs, 150 tables, over 1,200 objects and DataWindows including those specially made for use as DropDownDataWindows (DDDWs). We do not use PFC. Most of the initial objects were made from scratch or through the samples on the CD provided along with books on PowerBuilder. Stored procedures and triggers are used minimally and managed at the application level through the direct SQL Syntax in PowerBuilder. We feel that a good application should have a high percentage of these as they help keep data consistent and help in making the application perform better. All database-intensive tasks should be done using stored procedures.