This is a very common scenario: A customer contacts me and asks "what would it take/cost for you to port our MS-Access database application to SQL Server and maybe put a web front end on it?"
That is quite often verbatim wording.
The answer I always give, and it's really not related to my being employed as a consultant, is "It depends."
Rather than dive into some ugly minutae, I'll give you the 50,000 foot skydive overview. This is the conceptual, philosophical level, not the technical or procedural level, but it's important for both business and technical folks to understand this at a basic level:
There are two basic parts to what most people call a "MS-Access Database Application":
1. The database content (i.e. the tables, queries, and data stuffed into them)
2. The interface (forms, reports, menus, toolbars, etc. which users interact with when using the application)
In general, when you migrate an Access Database Application to SQL Server you are really only moving the data and data structures (tables, queries, etc.), while the interface aspects require redevelopment with something to make either a client/desktop application (aka "client-server") or a web application. Examples include Visual Studio, ASP.NET or even ASP or PHP (note: there are many more possibilities, but I'm trying to keep this short)
If the database "application" provides no custom interfaces and simply has the user opening tables and queries to interact with, and use, the application - it's a piece of cake. You can either (A) use the Access "Upsizing" utility to "push" the data up to a SQL Server, or (B) use Integration Services to "pull" the data from the Access database into a SQL Server. I prefer [B] because [A] sucks and is too limited and inflexible while [B] is incredibly flexible and yields better results with less effort.
If the database "application" provides extensive interfaces like dynamic drop-down lists, graphics, radio buttons, checkboxes, toolbars or ribbons, well, it will take longer, but again: it depends.
Case 1: Migrating an Employee Database
The Access ACCDB (Access 2010) database contains a simple form for entering basic employee data to create a new employee record. There are a minimum number of tables and views and only one form. The database contains 20,000 employee records (rows). Only three employees should have rights to access the application and they are all in one AD security group.
One Solution: Copy the table structures and data into an existing SQL Server host and develop a web interface for managing employee records. The IIS web site is configured for Windows Authentication, the web app folder is secured with NTFS permissions for the AD group. The SQL connections are "trusted" with explicit AD group access configured in the SQL database. Total time to implement: 4 hours.
Case 2: Migrating an Asset Inventory Management Database
The Access MDB (Access 2003) database contains multiple forms, numerous tables and queries, as well as about a dozen reports. Some employees are allowed full permissions to edit records, while a select group is allowed only read permissions. Other employees are denied access. This results in 3 distinct roles. The roles are managed within the database (application security). The forms consist of many dynamic components and tons of clever graphics.
The company owns licenses for Office 2010 but has decided to hold off until this application can be updated to remove the reliance upon Access 2003. MDOP/App-V is not an option for this customer and neither is Embedded XP or Remote Desktop Services (TS Remote, Terminal Server, Citrix, Xen or whatever). They just want this beast killed and disposed of.
One Solution: Chain the Access developers to the back of a truck and drive off down a long gravel road at 60 mph.
There. That was easy now, wasn't it? Clear as mud.
0 comments:
Post a Comment