Indexes and Filegroups

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried

The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

add new field int type with identity and set it as primary key

if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project