Best way to solve a DB problem

[brewmonkey]

I am working on a project where the sites are set up on several different servers each using a different DB. One is using Access, one is MS-SQL and the other is MySQL and each database basically contains much of the same information.

Now anytime there is a change to be made I have to change each page as well as the DB and it is a pain in the butt.

It is not possible to move all the sites to one server/DB as while they are all part of the same larger organization they belong to subsidiary sites and due to the way they are chartered we are stuck doing it this way. Of course when all the projects began there was no project manager or coordinator so you had 4 developers who did things the way they each liked best. So that means in addition to the DB issue we have a language issue. Some of the sites are done in ASP/.NET while the others are done in PHP.

What I would like to do is create one main DB on one server and then just access it from the others. Permissions should not be an issue on any of them but which DB should I use? I had thought about going with an Access DB and then just uploading it to each server but one of the servers will not work with Access.

If you had a similar situation which would you use?

 

[netzilla]

Boy you are definitely in a pickle there. It sounds like you have a mix of linux and windows bases servers. I would personally lean away from using MS Access. It works fine for small-mid size databases but I have personally experienced problems with it gets large or receives a lot of traffic. I think in your situation I would go with MySQL. Its was developed primarily for the open source enviornment but there is a distribution for windows also which I have used and it seems to work just fine. Hope that helps.

 

[brewmonkey]

It does help. I got some good PM's and emails about this as well and all basically said the same thing I was thinking, go with the MySQL.

As for the types of servers, yes it is a mix. That's what happens when you let people run amok without any type of oversight. The school has been wasting several hundred dollars a year on services that should have been much less had they just gone with a dedicated server plan.

I have been thinking of perhaps getting another box on my own and moving the sites to there while they figure out what they are going to do. While it would be money out of my own pocket I would be able to write it off at the end of the year as I would be donating the space to the school which is run by the church so it is a charitable contribution. It would save them some money and make my life a bit easier.

 

[netzilla]

I guess that is the importance of establishing standards early on. It sounds like they are lucky to have you aboard. Maybe moving to one box would be the best idea to starting towards a standard. Good luck, keep us posted.

 

[brewmonkey]

I am certainly learning a ton by taking on this project and at some point in the future it will most definitely be something to put on the resume should I decide to move that route.

Now I just need to learn how to create graphics and skins and I will be good to go.

 

[tgo]

You don't necessarily need a dedicated server, just a dedicated database. Most large sites will use 2-3 web servers, while having 1 database server to handle all the database stuff. This allows you to access the same database from many different sources.

Mysql will work with ASP.net and PHP. Just create a standard DB by combining all the others fields. Remove all duplicates. Then just access the databases with code. Access canot be accessed via PHP.

Moving to a new server is the best but can cost a bit. If you can get that money back its even better. Good luck with your choice.