Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups?

I have seen demonstrations where a large database being broken down into smaller ones using synonym names. I think it was used on a data warehouse and allowed smaller database backups instead of doing one large one and simulating file group backups. Is there somebody who has worked with this variation and can identify when this would be an advantage over file group backups if there is even an advantage.

My answer will always be to keep the VLDB (Very Large DataBase) as a single unit and go with filegroups if you need to. Breaking the VLDB into smaller databases has some serious issues:

• 
  
  Queries become more complicated as they’re now potentially cross-database. This means you need to keep all the security settings in all the databases synchronized.
  
  
• 
  
  Referential integrity becomes a big problem as you can’t create foreign key constraints across databases
  
  
• 
  
  You have multiple transaction logs to manage instead of one. This means you need to be doing log backups of ALL the databases, vastly increasing the number of backup files to manage.
  
  
• 
  
  Point-in-time recovery becomes very hard as you have to restore ALL the databases to a single point-in-time. Now, this may not be too much of a problem if the data in the VLDB is essentially read-only, and gets updated en-masse every so often from your OLTP system – but for changing data it’s a nightmare.
  
  
• 
  
  Implementing a high-availability solution becomes very challenging. As soon as you start to think of multiple databases that need to be in sync, you can pretty much forget about log shipping and database mirroring. You’re going to need whole-instance failure protection – which means failover clustering. Then if you want to mitigate the single-point-of-failure in a failover cluster (the shared disks), you’re going to need SAN replication to a remote failover cluster too – expensive!!!

These are just the ones that spring to mind in 5 minutes – I’m sure there are more if I sat and thought about it longer (e.g. how to create a database snapshot, run a consistency check, …)

So – IMHO it’s always going to be easier to backup and restore a single VLDB split into filegroups than a VLDB split into multiple databases.

PS If there’s something you’d like to see me do a blog post on, shoot me an email here.