[RESOLVED] Single or multiple databases?

[ovi_gm]

Hello everybody,

I have a question regarding an application I want to develop for my work. We are an engineering company and I want to make like a project manager, organizer and parts list for our projects.

For the first part of my application I will have data of clients, employees, contact persons for each client with all their information. Also I will have a list of my projects which will contain every information regarding clients, who worked and how much, when was it delivered and so on.

For all these I am using a single database with tables for each element.

NOW, there are approximately 300 to maybe 600 projects in one year. For each project I want to create a list of parts which are very many and in many categories like screws, sensors, pneumatics, electrics and so on. They will also contain prices, suppliers, etc... so it will be pretty complex.

My question is: how would it be best to work? I was thinking that it would be best if every project has it's separate database. But can I create databases at runtime? By doing this would it be safer for backups and stuff like that? I was wondering what would be the best way to manage all these projects and databases? Thank you.

P.S.

I am using VB2010 express and SQL Server 2008R2.

Cheers

[techgnome]

one a single database... this is the kind of stuff databases are designed for. I've worked with databases that held years worth of construction project data... we're talking about tens of thousands of items being tracked... for a single project, and there were a lot of projects... I currently now deal with databases that hold all kinds of financial information... years worth of data... the "small" version of the database was only 50GB... the full version was considerable larger...

-tg

[ovi_gm]

So your advice is to work with a single database. What about backups? How is it best to deal with them? I mean, in case of a system crash, I don't want to loose anything. How should I approach this?

[Nightwalker83]

It would depend on which database you are going to use in regards to what the backup options are! Which database were you thinking of using?

[jmcilhinney]

As tg says, there's really no reason to use more than one database. An industrial-strength server like SQL Server would have no issues at all. You'd most likely just have a table for projects where each row represented one project. Your parts table would then have a ProjectID column so each part was related to the appropriate project.

As for backups, that's really up to you based on how critical the data is. You might use a SQL Server Agent job to backup the database every week or every day. I'm no DBA so I don't know whether this would be considered an abuse of the feature but you may also be able to use replication to ensure that you always have an as-close-to-up-to-the-minute copy as possible. You'd only do that for fairly critical data though.

[FunkyDexter]

you may also be able to use replication to ensure that you always have an as-close-to-up-to-the-minute copy

I was on a project that went down that route once and would advise against it (not actually my decision). It's quite handy to recover from disk head crashes etc but it does have the problem that any data corruption replicates straight into your "back up" so you can't roll that back. Also, replication comes with all sorts of pain if you want to update database structure.

If you want to achieve that sort of thing you're better off using an apropriate RAID configuration so you're not worried about disk failures. If you don't need to be quite so up to date then regular rapid incremental backups (I think a minute is the minimum time you can set) combined with a daily full back up is pretty solid.

[techgnome]

That's the scheme we used... was hourly transactional backups, followed by daily complete backups... so if something happens during the day, at most, all that's lost is an hour's worth of data.

-tg

[ovi_gm]

OK. Thank you all for your answers. I am using a service based database (mdf file).

How is this done: regular rapid incremental backups (I think a minute is the minimum time you can set) combined with a daily full back up...?

[techgnome]

I wouldn't go any more frequently than an hour... if you feel like you have to go any more often then that, then that tells me you'll want to re-evaluate your hardware, since you don't have much faith in it.

-tg

[ovi_gm]

Oh no no. Once a day for a backup it's enough. I wouldn't go further than that. But how should I do it?

[techgnome]

once you have your databse setup... setup a maintenance plan for it, which includes the backup process...

-tg

[ovi_gm]

OK? But finally how do I make this backup? Do I copy the mdf file in a backup directory? Is that enough? Or are there any other steps to take care of?

[techgnome]

No... you create a backup process... it just happens.

http://msdn.microsoft.com/en-us/library/ms175477.aspx
http://msdn.microsoft.com/en-us/library/ms187510.aspx
http://www.techrepublic.com/article/...server/1043671

https://www.youtube.com/watch?v=XZbF_VWScNE

-tg

[ovi_gm]

Ok. Lot's of info. Thank you very much. I will make some research regarding this subject.

Cheers!