{"id":1071,"date":"2008-03-13T17:41:14","date_gmt":"2008-03-13T17:41:14","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-17-Split-a-VLDB-into-filegroups-or-smaller-databases-for-backups.aspx"},"modified":"2008-03-13T17:41:14","modified_gmt":"2008-03-13T17:41:14","slug":"search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/","title":{"rendered":"Search Engine Q&#038;A #17: Split a VLDB into filegroups or smaller databases for backups?"},"content":{"rendered":"<p><FONT face=Verdana size=2><br \/>\n<P>Here&#8217;s an interesting question that came in to our questions line (<A href=\"mailto:questions@SQLskills.com\">questions@SQLskills.com<\/A>&nbsp;&#8211; no guarantee of an answer &#8211; I check it every so often):<\/P><br \/>\n<BLOCKQUOTE dir=ltr style=\"MARGIN-RIGHT: 0px\"><br \/>\n<P><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\">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. <\/SPAN><\/P><\/BLOCKQUOTE><br \/>\n<P dir=ltr><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>My answer will always be to keep the VLDB (<U>V<\/U>ery <U>L<\/U>arge <U>D<\/U>ata<U>B<\/U>ase) as a single unit and go with filegroups if you need to. Breaking the VLDB into smaller databases has some serious issues:<\/FONT><\/SPAN><\/P><br \/>\n<UL dir=ltr><br \/>\n<LI><br \/>\n<DIV><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>Queries become more complicated as they&#8217;re now potentially cross-database. This means you need to keep all the security settings in all the databases synchronized.<\/FONT><\/SPAN><\/DIV><br \/>\n<LI><br \/>\n<DIV><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>Referential integrity becomes a big problem as you can&#8217;t create foreign key constraints across databases<\/FONT><\/SPAN><\/DIV><br \/>\n<LI><br \/>\n<DIV><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>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.<\/FONT><\/SPAN><\/DIV><br \/>\n<LI><br \/>\n<DIV><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>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 &#8211; but for changing data it&#8217;s a nightmare.<\/FONT><\/SPAN><\/DIV><br \/>\n<LI><br \/>\n<DIV><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>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&#8217;re going to need whole-instance failure protection &#8211; which means failover clustering. Then if you want to mitigate the single-point-of-failure in a failover cluster (the shared disks), you&#8217;re going to need SAN replication to a remote failover cluster too &#8211; expensive!!!<\/FONT><\/SPAN><\/DIV><\/LI><\/UL><br \/>\n<P><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>These are just the ones that spring to mind in 5 minutes &#8211; I&#8217;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, &#8230;)<\/FONT><\/SPAN><\/P><br \/>\n<P><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>So &#8211; IMHO it&#8217;s always going to be easier to backup and restore a single VLDB split into filegroups than a VLDB split into multiple databases.<\/FONT><\/SPAN><\/P><br \/>\n<P><SPAN style=\"FONT-SIZE: 10pt; FONT-FAMILY: Arial\"><FONT face=Verdana>PS If there&#8217;s something you&#8217;d like to see me do a blog post on, shoot me an email <A href=\"mailto:paul@sqlskills.com?Subject=Blog Post Suggestion\">here<\/A>.<\/FONT><\/SPAN><\/P><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Here&#8217;s an interesting question that came in to our questions line (questions@SQLskills.com&nbsp;&#8211; no guarantee of an answer &#8211; 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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,31,35,78],"tags":[],"class_list":["post-1071","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-database-maintenance","category-disaster-recovery","category-search-engine-q-and-a"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Search Engine Q&amp;A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&amp;A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Here&#8217;s an interesting question that came in to our questions line (questions@SQLskills.com&nbsp;&#8211; no guarantee of an answer &#8211; 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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-13T17:41:14+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/\",\"name\":\"Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-03-13T17:41:14+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&#038;A #17: Split a VLDB into filegroups or smaller databases for backups?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal","og_description":"Here&#8217;s an interesting question that came in to our questions line (questions@SQLskills.com&nbsp;&#8211; no guarantee of an answer &#8211; 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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/","og_site_name":"Paul S. Randal","article_published_time":"2008-03-13T17:41:14+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/","name":"Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-03-13T17:41:14+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-17-split-a-vldb-into-filegroups-or-smaller-databases-for-backups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&#038;A #17: Split a VLDB into filegroups or smaller databases for backups?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1071","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1071"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1071\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1071"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1071"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1071"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}