|
SQLDude
Starting Member
41 Posts |
 Posted - September 16 2009 : 23:37:01
|
I am finishing up development for a generic Database Upgrade Conversion process involving the automated conversion of Access Table, View and Index Structures in a VPASP Shopping or any other Access System to their SQL Server 2005/2008 equivalents. It runs right from your server without the need for any outside assistance from external staff or consultants.
After creating a clone of your Access tables, indexes and views in SQL Server this data migration routine will selectively migrate all the data from your Access tables into your new SQL Server 2005/2008 tables without having to give out either your actual Access Database or the Table Contents or your passwords to anyone.
Here is the Reverse Engineering part of the process running against a system with almost 200 tables and almost 300 indexes and Views which is being done as a system acceptance test. Still a work in progress, but the core pieces are in place.
http://www.21stcenturyecommerce.com/SQLDDL/ViewDBTables.asp
I do the automated reverse engineering of the Access Table DDLs (Data Definition Language) and convert them into SQL equivalent DDL Statements, because table structures and even extra tables might be slightly different for every VPASP customer and for every version of VP-ASP out there.
I am finishing the actual data conversion routine which would migrate the data from Access to SQL Server after these new SQL Tables have been created including any views or indexes. It is written entirely in ASP, with VB Scripting, the File System Object (FSO), the Dictionary Object, XML, DHTML, JavaScript right now and runs pretty quickly as you will see against a SQL Server 2008 Database just for the sake of an example.
It takes perhaps 15-20 seconds to reverse engineer almost 500 different database objects. There might be a total of over 2,000 columns involved in this example for the 170 tables and 270 indexes involved.
I have even come up with a way for you to run both VPASP systems in parallel using 2 different database connection files on the same server just to be sure that orders entered on the Access System and the SQL Server system produce the same results before actual cutover to production.
On a separate note:
Would there be any interest from any VP-ASP customers in a 100% SQL Server 2008 version of VPASP which used stored procedures instead of in-line SQL as part and parcel of this Access to SQL Server conversion process? Let me know. I have always found something between a 50% to 100% performance boost after converting from Access to SQL Server especially for larger databases for most complex ASP systems.
Access can have some performance issues after reaching the 250 MB to 400 MB levels as some of you may have discovered. Data loss due to Access Table corruption is always a possibility as well. Not quite as bad as MySQL with their data corruption and database disruption issues, but an issue nonetheless.
Are there any other issues in your possible Access to SQL Server migration which you would like addressed? There are other ways to upsize or upgrade Access to SQL Server but this method has always been the most reliable and the best that I have ever seen in actual operation.
John (a/k/a The SQL Dude)
[email protected]
(This is a VP-ASP Demo Site)
|
|
All Forums
Convert Access versions to SQL Server 2005/2008
New Topic
Printer Friendly
Topic 
TrustGuard - PCI Security Scanner
