On 13-2-2019 16:58, Kevin Stanton [email protected] [firebird-support] wrote: > I’m starting to look into migrating from 1.56 to either 2.5 or directly to > 3.0. I’ve bought the Migration Guide but haven’t read it yet. > > It concerns me that I would/will have to recompile all SPs and triggers. Is > there an automated way to do this? I have 909 SPs and 785 triggers. This > will be one heck of a task with my installation base.
The choice is to either carefully and exhaustively verify if all your stored procedures and triggers continue to work correctly, and only recreate those that you know need it, or recreate the whole shebang (you will still need to carefully and exhaustively verify them). Firebird itself has no way to recreate stored procedure and triggers automatically, although it is possible to craft scripts that rebuild it based on the database metadata (assuming the source blobs haven't been cleared). I would recommend against doing that automatically and instead you should use known good and verified scripts for the actual upgrade, if only to avoid running into issue with things that are now no longer syntactically valid or unquoted use of identifiers that are now reserved words. Some people advocate that you shouldn't upgrade a database at all, but instead recreate it and pump the data over. In a situation with a lot of deployments that might also fix problems with diverging structure (but if that is the case, the migration path will be more prone to errors). Especially when taking the big step from 1.5, it might make sense to take it as an opportunity to improve your database design, and get rid of things that are no longer needed (eg use built-in functions instead of UDFs, switch from dialect 1 to dialect 3 if you are still on dialect 1), etc. and then migrate the data from the old to the new. Firebird 1.5 to 2.0 was already a big step with a lot of new features, but also some breaking changes, sometimes subtle, sometimes not. Don't expect going from 1.5 to be simply backing up and restoring, especially not with 909 stored procedures and 785 triggers, and more so if you use a lot of UDFs, or relied on some of the ambiguous behavior that is no longer valid, used unquoted identifiers that are now reserved words, etc. And realize that Firebird 2.0 was released 12 years ago. A lot of migration knowledge from 1.5 to 2.0 may have been forgotten since (or at best not easily recalled). You should really carefully study the 2.0, 2.1, 2.5 and 3.0 release notes and the Firebird 2 installation and migration guide, and as you bought it, Carlos' Migration Guide to Firebird 3. Mark -- Mark Rotteveel
