| Author |
 Topic  |
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
 Posted - 07 February 2005 : 05:39:59
|
Is this the way to do for upgrading an Access 2000 db to MS SQL ?
To transfer Access tables to SQL Server
1. Open SQL Server Enterprise Manager, expand the server, and click the Databases folder.
2. On the Tools menu, point to Data Transformation Services, and then click Import Data.
3. In the Choose a Data Source dialog box, select Microsoft Access as the Data Source, and then type the path and file name of your Access database (.mdb), or use the browser to browse for the file.
4. In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, and then select the database server and the required authentication mode. In the Database dialog box, select the target database on the server.
5. In the Specify Table Copy or Query dialog box, click Copy tables and views from the source database.
6. In the Select Source Tables and Views dialog box, select the tables and queries to import.
7. In the Save, Schedule, and Replicate Package dialog box, select Run Immediately.
8. Click Finish. |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 07 February 2005 : 06:34:52
|
| No, select to do an Object Copy and leave all the options as they are, otherwise you will not get any default values or indexes created. |
 |
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 February 2005 : 07:43:20
|
Ok, just to make sure, itīs the correct way, since Iīve never done this before. I did the following.
Iīm running a heavily moded version 3.4.03 and want to upgrade the database from Access 2000 to MS SQL 2000
1. I uploaded a fresh copy of the forum.
2. Ran the setup.asp file to insert the tables in the database.
3. Ran the DBS files for the MODS
The above works fine, and I hope itīs the correct way of doing this.
HuwR, when I get home, I will try to export the data using Object Copy. |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 February 2005 : 15:40:14
|
I made it work, not impressed by the performance though, as the old Access version seems quite a bit faster than the MS SQL version. I have no idea what Iīm doing, so thatīs probably the reason.
In addition to the above post, I did the following.
Using DTS Import/Export Wizard.
4. I selected my Access DB as Data Source and connected to the MS SQL server.
5. In Specify Table Copy or Query - I selected: Copy Table(s) and View(s) from the source database.
6. Selected All and in the Transform column selected "Delete Rows in Destination Table" and "Enable Identity Insert".
Ran this, and got the forum updated to MS SQL - no errors found so far, but the speed is not impressive. Iīm sure something is wrong, but canīt find other ways to do this. I hope anyone can help. |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 07 February 2005 : 16:28:27
|
| how big was your access db ? for small databases access is often quicker, just depends on the hardware underneath it. you will probably find searching is quicker than with access |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 February 2005 : 16:53:11
|
| A little over 40MB - did I convert it correctly? |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 07 February 2005 : 18:47:33
|
| yes, you did transfer correctly |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 08 February 2005 : 01:43:51
|
Did more testing today. Only the search page is much faster than Access, everything else is slower. Pages renders in 2-3 seconds, in Access itīs always under 1 second.
I will try MySql. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 February 2005 : 05:34:08
|
quote:
Originally posted by wii
Did more testing today. Only the search page is much faster than Access, everything else is slower. Pages renders in 2-3 seconds, in Access itīs always under 1 second.
I will try MySql.
You're probably rushing your decision. An enterprise DBMS is always more complex to tweak than Access. What kind of hardware is SQL Server running on? Does it have enough RAM (I'd say at the very least 1GB, preferably 2GB)?
Have you checked how the SQL DB handles concurrent access? It should handle it better than Access?
If you try the move to MySQL you'll probably notice that several mods won't work without changes. The SQL dialect supported is a bit different. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 08 February 2005 : 05:49:52
|
My host wonīt give me details about the configuration, but Iīm on their newest web server and newest SQL server. I rarely get over 10 users online.
ruirib, you wrote a little tutorial on transferring the data, it just seems that I did in another way, since I transferred the tables and not just the data, could that be it ? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 February 2005 : 06:23:45
|
No, it looks like you did it the way recommended here. The main issue is creating the tables, using setup.asp, and it seems like you did that, right?
10 users online should be handled fine by any database. I assume you moved to SQL Server due to database size. Anyway, I would presume the specs on the database server should be good, but with hosts one never knows... I've seen too many things that left me astonished... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 08 February 2005 : 06:39:36
|
Yes, I used setup.asp to create all the default Snitz tables, and after that ran all the DBS files to create the MOD tables.
Iīm testing SQL because my host recommended me to do so, I have 996 members and 94000 posts, Access DB is a little over 40MB.
When I log in the SQL server, I can see the names of the other databases on the same server, there are not more than 10 or so.
My host says that Access should not be used for anything serious, specially not a forum. Anything over 2 users online and the DB will crash - so they say....
But the truth is, that the forum runs very well on Access - pages are generated in under 1 second everywhere, and I often have around 10 users online with no problems.
When I asked my host how they would explain why MS SQL runs slower than my Access they didnīt respond. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 February 2005 : 09:27:20
|
With those figures, I'd stay with Access for a while. 40 MB is a perfectly reasonable number and if performance is good, no reason to upgrade, IMHO.
I've read that thing about Access not being used for anything serious and when I see it used, I feel not that much respect for those who say it (or write it). Your forum (and many other people's forums and web apps) shows differently. We have a saying in portuguese that goes something like this: the worst kind of blind is the one that chooses not to see. I think it applies like a glove to this stupid statement about Access. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 08 February 2005 : 09:34:52
|
Ok, thanks a lot.
I agree with that saying...LOL |
|
|
| |
Topic |
|
Topic Locked
