December 13, 2006 at 3:50 am
Hello everyone
I have run a website for 4 years using ASP and an Access database and I have decided it's time to convert to SQL Server.
I created a small, test version of the db on my ISP's SQL Server machine and used that to test/modify my ASP scripts for the little SQL differences and that's all OK.
My problem is the actual one-time conversion of the main database. The database changes constantly with new users registering daily and buying things. The database has a Users table of around 20,000 records and a Purchases table of 90,000 records. Also there are about 800 tables (dressage tests since you ask!) which users can create and edit. So (I think) I have to shut down the operation, do the conversion and then restart.
The only tool I can find to do this is the Upsizing Wizard in Access and it is incredibly slow (at least on my PC). I estimate it would take at least a week just to do the one-off conversion. My ISP is no help - they offer NO assistance for this kind of thing.
Can anyone suggest a way to proceed?
Many thanks
John
December 13, 2006 at 5:55 am
Are you just moving the data? You can use the import wizard to create a DTS package for SQL 2000. You first do the import to a test server from a copy of the access database. IMPORTANT: save the package so that you can edit it.
I have done this several times. I can't imagine the actual import taking too long.
Russel Loski, MCSE Business Intelligence, Data Platform
December 13, 2006 at 6:29 am
Russel
Thanks for replying.
What is the "import wizard"? Do you mean the Access Upsizing Wizard?
I need some information before I can try out your idea. Mostly they are about curing my ignorance! What is a DTS package? Where do I find it?
John
December 13, 2006 at 6:40 am
You call the import wizard from SQL Server Enterprise Manager. This tool (which is a client tool for SQL Server 2000) allows you to manage the databases, etc. The import wizard will allow you to import data from various sources (Access to Oracle; text file to Excel spreadsheet). It creates a DTS (Data Transformation Services) package which it then executes. You can also save that DTS package to edit later, again with Enterprise Manager.
My understanding is that the Access Upsizing wizard moves data and changes all of the queries and forms to use SQL Server in a data access project (I'm fuzzy on this). I think that it does more than you need on much of the project (though converting queries might still be helpful).
Russel Loski, MCSE Business Intelligence, Data Platform
December 13, 2006 at 6:52 am
Russel
The mist is clearing slightly, thank you.
I don't have SQL Server Enterprise Manager - I have something called SQL Server Management Studio Express which has nothing like what you describe. I'd guess it's a cheap (free!) version of the full thing.
What you've described sounds like what I need so I guess I should get the SQL Server Enterprise Manager - but it sounds expensive!
BTW, my database is very simple in some ways. There are no queries or forms, just tables. And there are no inter-table relationships either.
Many thanks
John
December 13, 2006 at 7:03 am
Hmmm! SQL 2000 Enterprise Manager would sure save time. I don't have Management Studio Express, but I don't think that it has the import wizard.
Can you create an Access database with linked tables to your Access 2000 database and to your SQL 2000 database? You can create a bunch of insert queries (Append queries) to insert the data from the Access tables to their equivilent SQL tables.
Russel Loski, MCSE Business Intelligence, Data Platform
December 13, 2006 at 8:24 am
I think you are right, Russel. I've searched the Microsoft website for a download for the SQL Server Entererprise Manager but I can't find one. Have you any idea where it is?
I did not explain the my SQL Server database runs on a server operated by my ISP so I have no direct control over it. I do not have SQL Server on my desktop so I had to find a front end of some kind to access and manage the db.
I CAN create an "linking" Access db with linked tables to the both the SQLS db and the orginal Access db. I will explore your suggestion which seems rather neat. But I fear it would be too cumbersome with hundreds of tables involved.
Thanks again.
John
December 14, 2006 at 4:46 am
You can take a look at my http://www.keper.com/dbexplorer/conversiontool/ Read the White Paper to understand that my tool generates the tables structures and constraints to emulate MS Access' behaviour. It also succeeds to convert about 80% of al views automatically. For the remaining 20%, the manual process is supported by a adequate user interface.
If you are interested, mail me and I'll provide the latest version 4.5
Regards,
Burkhard
December 14, 2006 at 6:21 am
Well, I have a number of thoughts. First of all, my guess as to why the upsizing wizard is incredibly slow is that you are running Access on your local PC and the SQL Server is at your ISP. My suggestion here is that for development (and even the conversion itself) you really need a copy of SQL Server on your LAN or on your PC. You could look into getting MSDE (the free version of SQL Server 2000), and if you could install the Enterprise Manager client side onto your PC as well. This will allow your conversion to run at a high speed, whether you use DTS under SQL Server (which I would recommend) or the Upsizing Wizard under Access.
Then you need to decide on your approach to the conversion, and this would probably be based on how critical reducing down time would be. Another factor would be how static the various tables and data really are. For example, if it were really important to minimize downtime you could take a snapshot of the database and convert that, get a SQL Server database ready to roll and put that up, then go back and somehow "replicate" the changes made in the interim from the Access database to the SQL Server database.
Dick Schroth
December 14, 2006 at 9:46 am
Reply to Dick Schroth
Dick
Your guess sounds makes sense. However I have SQL Server Express running on my PC. Running the Upsize Wizard from Access to SQLS Express is painfully slow. Is SQLS Express the same as MSDE? Where would I get the Enterprise Manager? I would love to install it as client on my PC.
I take your point in the last para. The biggest problem is the Purchases table with 90,000 records which only gets records added. So I could envisage a one-time transfer and then (via ASP?) replicate subsequent changes. But I can't see how I would have 2 databases (the Access and SQL Server) open at the same time in ASP. Never had to do that before! Any suggestions?
Many thanks for your reply - really helpful.
John
December 14, 2006 at 9:50 am
You'll have to plan some down time while doing the final transfer. Once you get it working at resonnable speed, plan the required downtime and make the transfer. But this will take much more planning than you may think at the moment.
December 17, 2006 at 4:21 pm
Hi There
The reason you can't find the enterprise manager is that you are using SQL 2005, which dos't use the enterprise manager any more. If you create a database on SQL then right click the database in the tasks menu there is an import wizard that will allow you to create a SSIS package, which is the replacement for DTS. You should find this a lot faster than the upsizing wizard.
Good Luck
December 17, 2006 at 6:41 pm
I don't have access to "Server Management Studio Express," which is the stripped down version of the normal 2005 Server Management Studio. I don't believe that you can use the Express tool to create SSIS packages.
In fact, right after I posted this I found the following reference that suggests that SSIS does not come with Express:
http://www.synthesisone.com/article.php/sqlserver2005-express-limitations
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply