OK,  I have a couple of questions which I'm hoping people out there can answer.

Firstly,  I currently run a forum using WWG Forums 6.11.  It's running off an Access 2000 database and has worked great up to now but I'm starting to notice a bit of slowdown.

The forum currently has :
1600 Users
Between 160 - 200 new posts/topics per day
Roughly 40000 posts/topics total
Database size is 55mb

How much of a benefit would upgrading to MSSQL be?  I've never used MSSQL so I have nothing to compare with.   Should I ( and the visitors to my forum! ) see a noticable speed increase? 

Secondly,  If I do upgrade to MSSQL :
1).How easy is it to upgrade from the Access version to the SQL version?

2).What is the best/easiest way of going about it?

3). Anyone out there gone through this and care to impart some knowledge?

4).  Will I have any hair left when this is over?

Obviously I don't want to lose any members/topics/messages/etc  and Ideally I want the forum to be down for as short a time as possible

Cheers

Al.

I.  The benfit you'll be ganing if there is a database bottleneck, in access all users would be affected, in sql not necessarily. In general you can say that a forum of you size might be the borderline. (Wild guess) might run for some more time on access but you'll never know when it crashes.

II.   1. It is very easy to upgrade. Either use DTS in SQL server to import your Access tables or use the Access upsizing wizard. (Create the sql forum before any of those options)

      2. see no. 1
      3. I did it before and it was completely uncomplicated. Run a test first if you want and if you have the resources but I never had a problem doing it.
      4. I don't know, how many hairs do you have now?

Al.

I looked at your forum and just want to say it's cool, though slow as a dying horse (pun intended  )

How much bandwidth from the forum do you got monthly?

Yes very slow.

I think MSSQL is needed soon

You have a bug in your forum.

If you try to register with no sig it errors

If you enter a sig it works perfict.

Just wanted to let you know

55Mb Access database!!!

I would upgrade to Ms SQL Server as soon as possible, I'm suprised the forum still functions at all on an access database of that size.

As Access is a flat file database, it means that each tiume the database needs to be accessed the entire file needs to be opened and loaded into memory, putting allot of strain on the web server. As Ms SQL server, is just that a server database system you will notice a big increase in performance by upgrading to it.

how many websites are on your server.

Thanks for all the replies.

I've taken the plunge and got the hosting company to add SQL server for this site.  I've set up the SQL version of the forum on the server and everything works great with the empty database.

But... Now I am running into a problem in upsizing the old Access database.

Access is able to connect to the SQL Server database,  and  can see all the tables/modify what is in them.

But when I use the upsizing wizard it works away for a while then the report shows  'table was skipped or export failed' errors.  Then for tblTopic it shows :

[Microsoft][ODBC SQL Server Driver][SQL Server]There are no primary or candidate  keys in the referenced table 'tblForum' that match the referencing column list in the foreign key 'tblTopic_FK00'.

Trying to troubleshoot,  I tried a smaller database from another site I run,  again,  I ran into the same problems using the upsize wizard.   I was able to transfer the data from the smaller database simply through copying and pasting.  This worked fine and everything was working.  My problem with doing this for the larger database is that not all the records are sequential ( User acccounts have been deleted etc )  So if I copy and paste the data, the user id's in the SQL server database no longer match what was in the Access database.

Any suggestions would be very welcome!

Finally, In answer to some of the points above :

How many sites are on the server : Quite a lot,  but most are fairly low traffic/low bandwidth. equestrianconnection.com is the busiest site on there with around 3000 uniques per day.

How Much Bandwidth for forum : Not sure,  there's no bandwidth limit on the server so I haven't concerned myself with bandwidth usage up to now.

Bug with no Sig : Cheers for pointing that out,  will fix that as I upgrade to SQL Server

Cheers!

From memory, when you use the upsizing wizard, it does not always copy across the primary and foreign keys (ie, your relationships).

After you create the tables in SQL, you might need to go through and re-assign your keys.

Then insert the data.

Then go through the necessary columns and make those that were 'autonumbers' in Access, Identity fields in SQL Server.

This was an issue a few years ago anyway. MS released an office update called the 'Access 2000 upgrade to SQL Server 2000 Readiness Update" or something similar, so make sure you have that first.

Cheers,
Andrew

Have a look at  http://forums.webwiz.net/forum_posts.asp?TID=2196&PN=1 - MSACCES, MSSQL, MYSQL  before upgrading

u kidin only 50mb ur db ? mine 1000mb+ and i m trying to upgrade for week now but keep geting errors and stuff :(

my qustion/problem is  :

i cleard the old topics and stuff ..  i compect and fix it and now my db 60mb.

now i am trying to do the Upsizing thing and it works for over 5 hours now

thats how it works ?

(ppl who can come help me little in chat /msn/icq plz send me pm  )

I have never known what the fuss is about Access. Loading a 30MB file into 2GB memory doesn't seem too much of a task for a dual processor machine.

I have used Access since Access97 and upgraded as and when and have saved absolutely thousands of pounds for my company. I wouldn't use it for a high transaction site but for info gathering, issuing and generl tasks it has to be the best thing that MS bought.

Regards
Frank

Access is a front end/IDE/distibution environment  for a data base engine. People tend to call the data base Access. The current data base used by access is Jet. It's one of many data base engines Access has been bundled with over the years.

Jet is very good for small workgroup applications built around LANs. However, it doesn't scale well for internet use. For LANs, once the capabilities of Jet are outgrown, it's a pretty good leap to MS SQL Server for most small companies.

Microsoft is phasing out the Jet Engine. Future versions of Access will be released with MSDE (MS SQL "Light"). You can use Access today to build and run applications with MSDE as the DB. The problems (IMHO) however, are that the SQL supported by Access is not optimized for the SQL supported by MSDE/MSSQL, and that the distibution methods to employ an Access/MSDE solution are cumbersome and eror prone.

Hi,

I was wondering if anyone could help me. I am currently running version 7.9 Access forum. I'm trying to decide whether to change to SQL forum or not or if i need to. The following are my forum stats:

Number of Forums: 11
Number of Topics: 759
Number of Posts: 11157
Number of Members: 1044
Number of PM's: 511
Number of Polls: 24
Number of User Groups: 7

Anyone have any advice for me?

Som time you will be force to do it, but for speed, the host has much to say: look at one I have going here:

http://thegreenpride.dk/forum7/default.asp - http://thegreenpride.dk/forum7/default.asp

I think it runs well, and it's the Access version and the size is 25MB

But I will tell you if I knew then what I know now, it would have been the SQL version.