I am fairly new to SQL Server, and an IBM DB2 DBA by training. We are implementing a new project here at work and I am curious about this.
Our Legacy System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. The R/I is contained within each database. This gives us the ability to back up each business area at a time that is most convenient for that business group. This also gives us 13 separate recovery areas, so if we lose one, we can restore it while the other 12 are up and running.
I have been told that in SQL Server, we can and should do all of this with 1 database. (by a someone here at work.) They said we can separate filegroups by schema. They also said we would be able to back up and recover each individual business unit by each file group and no affect to the rest of the data within the database.
I want to confirm that is infact a good Idea. Are their short comings to this approach? Any reason we would not want to do this?