JWColby
jwcolby at colbyconsulting.com
Fri Oct 6 10:59:21 CDT 2006
David, Steve, I did something "similar" when I normalized an access database. I had to be able to allow the client to continue entering data, and yet at some point "switch over" What I did was build up a set of queries that did the data migration, and placed them in macros - one of the damned few times I ever used macros. This allowed me to build the queries, get the sequences required for the migration figured out and working (parent first, then child, grandchild etc. When the whole MIGRATION thing was working I was able to push a button and run all of the macros that ran all of the queries, in exactly the correct order to migrate the entire database, top to bottom. Once THAT was working, I could go building forms and stuff to display the new data structure, enter new data, reports etc. When I was ready and tested, I just ran the macro and the client (who had been training the users in the new system) just switched one day to the new system. It really did work that way, just one day they turned off the old and turned on the new. It was a PITA to do of course but realistically you need the real (new) data to work with to ensure that the new forms / reports etc work, so it was worth the effort. Plus no issues of "did we enter that data in the old system?". John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David Lewis Sent: Friday, October 06, 2006 10:56 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Access BE to SQL Steve: I agree with John. You have a decent start on the estimating, but in my experience (I've done similar sized projects) each of those taskes will 1) take far longer to implement, and more importantly 2) take a very substantial block of time to plan. I found that the preparation and planning took far more time than the implementation, actually. For example, designing indexes and performance tuning queries and sp's is no quick matter. It sounds as if the access app sort of grew organically. If this is the case, you have a good opportunity to give it some structure, but that is a very large job. If you approach the project as a series of pieces that need to be made to run on an sql server platform, the project may go quicker but you will be left with a maintenance nightmare -- too much of the code and logic and design philosophy will reside in the head(s) of one or a few developers. Good job security for them, bad for the company. Another thing to give consideration to is how you will keep data in sync. You will begin the transition to sql server, and in that time of several weeks presumably the access app will still run. The initial upload of all the data is a chore in itself, and it will be duplicated again when you have to bring in the final few weeks or so of data. The sequence of these imports will be important if you have referential integrity turned on. If not, and you allow identity inserts (assuming you are using autonumber id's), there are almost guaranteed to be clean-up issues. Well, it brings back memories... Enjoy the ride. D. Lewis ------------------------------ Message: 2 Date: Thu, 5 Oct 2006 16:56:01 -0500 From: "Steve Capistrant" <scapistrant at symphonyinfo.com> Subject: [dba-SQLServer] Access BE to SQL To: <dba-sqlserver at databaseadvisors.com> Message-ID: <02F5B8F3D893A845A3DF342F14D80F0103A478 at LAKATOS.Symphony.local> Content-Type: text/plain; charset="iso-8859-1" Well, looks like I'm going to be hanging out in this group a little more. My team is about to migrate an Acc/Acc (AccessFE over AccessBE) application to Acc/SQL (AccessFE to SQL Server). I'm sure this topic has been well discussed, but could I please as for some starter tips and feedback? Also, I am considering hiring out some or all of this to someone who's done it a few times, so let me know if you are interested. The Acc/Acc app is a mature 6 year old system sold commercially, but getting unwieldy in size, speed, and corruptability. Long term plan is VB.NET over SQL. Short term (1 year) is to just get the BE in place, and rewire the FE. Pushing heavy queries to the BE are expected to go a long way in improving performance, buying us time to migrate the FE later. The application has 332 tables, 940 saved queries, 239 queries dynamically defined in code, 282 reports, 119 code modules, 899 recordset manipulations, and over 25,000 lines of code. FE is 27 meg (in MDE format) and growing. Here's the essential task list as we see it (lacking experience), including an estimate of time: 1. Migrate Data to SQL. Resetting of autonumbers, referential integrity, some indexes. 332 instances @ 10 minutes = 55 hours 2. Convert queries to views. Convert high-impact queries into Views and Stored Procedures. For each of those migrated queries, redesign filtering code to pass parameters back to SQL Server. 1451 instances @ 15 minutes = 362.75 hours. Limiting to high impact queries: 300 instances @ 15 minutes = 75 hours. 3. NewID modification. Adjust the programming code in all places where a new record is created and the resulting unique ID is captured. Access and SQL behave differently - Access allows capture before posting, SQL does not. 66 instances @ 15 minutes = 16.5 hours. 4. Boolean field modification. Access and SQL both support a Boolean data type SQL allows a null option whereas Access does not. All default Boolean settings in the app must be reviewed and changed as necessary to account for this difference. 664 instances @ 5 minutes = 55 hours. 5. Paging on List Views. Currently, all list views (on forms) are structured to allow display unlimited number of records (often returning hundreds of thousands of records). To minimize the traffic hit, we want to reengineer few of the biggest ones to implement paged subsets (e.g., 100 at a time). 15 major list views @ 2.5 hours each = 37.5 hours. Targeting high impact only: 5 lists@ 2.5 hours = 12.5 hours. ------- How's that look? Missing things? Over/underestimating time? Thank you in advance. Steve Capistrant Symphony Information Services scapistrant at symphonyinfo.com www.symphonyinfo.com Main: 763-391-7400 ext 801 Toll Free: 888-357-1373 ext 801 Direct: 612-237-0075 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com