Seperate reports database (1 Viewer)

[Pauldohert]

I Have been requested to alter a database with the issue being speed.

I have been requested to split the database into a reports database and a live data entry database.

(Two front ends and two back ends - one back end is basically a backup of the other and only to be used for reporting (SQL Server)

The theory being that the traffic in each database would fall and speed would improve?

Would this work? Would this work if the BEs where access? Is this a common solution?

Would the split of the FE only make any differance.

The split is easy enough to do, but will there be any noiceable benefits???

Thanks

 

[wizcow]

Paul

I thinks splitting the front end is worth a try. I would guess that that would speed up each machine that is using the front end.
If you split the backend, there may be some network advantages, as far as retrieving data, but the front end still has to write to both backends possible slowing it down.

Be sure to do a backup before you fiddle with it!

Tom

 

[Pauldohert]

Paul

I thinks splitting the front end is worth a try. I would guess that that would speed up each machine that is using the front end.
If you split the backend, there may be some network advantages, as far as retrieving data, but the front end still has to write to both backends possible slowing it down.

Be sure to do a backup before you fiddle with it!

Tom

Thanks Tom,
just to clarify one thing - one BE would be only to read from (only to produce reports) and would not be written to - this would be a copy of the "live" database and would be taken each day. (The fact that the data would be up to 24 hours is not a problem)

I can see a use for Access backends - say performance suffered terribly with 5+ users in a BE database - I could split 6 users 3 in the live data and 3 in the reports data.

Does this work in practice, and would SQL have a similar effect?

Ta!

 

[boblarson]

If you use SQL Server, you won't need duplicate databases. It is not as susceptible to the problems that you experience with a network use of Access.

You can even use MSDE if you want to have the benefits of SQL Server as a free solution (subject to the limitations listed here: MSDE Licensing )

There are several things you should be aware of if you go this route. There are certain things within Access that you can't use if using SQL Server and you need to change the way you do them. One example is parameterized queries.

Check the archives here for more SQL Server/MSDE info.