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


Here’s an interesting question that came in to our questions line (questions@SQLskills.com – no guarantee of an answer – I check it every so often):



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.

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

  1. Hello Paul:

    I need your expertise advise. i have seen that many recommends to do split backups for faster backups and i am not convince with that unless there are multiple physical drives present to do so. its my understanding that there is one thread per physical drive and splitting multiple file backups on one drive will slow down.

    Any pointer, article or white paper is greatly appreciated.

    Thanking you in advance.
    Jay

  2. Hi Paul,

    I need advise on the issues our DBA’s (I am not a DBA) are facing. Our Database is on MS SQL 2014 with a size of approx 6.9 TB and growing on monthly basis. We have our file groups partitioned into monthly partitions. Our DBA’s complains regularly regarding DB back-up and restoration time, the time it takes to archive data, problem of maintaining indexes etc.

    The management direction is to break down the databases into yearly partitions and bases on operations needs they will decide how many years worth of data will be available on production at one time.

    What are my options in this scenario.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.