May 1, 2009 at 3:00 pm
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 DB2 Z/OS System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. All 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.
Secondly, it sounds as though SQL Server has performance issues trying to join tables between databases (even though they reside on the same server.)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
May 1, 2009 at 3:05 pm
troy.hodge (5/1/2009)
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 DB2 Z/OS System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. All 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.
Secondly, it sounds as though SQL Server has performance issues trying to join tables between databases (even though they reside on the same server.)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
Hi Troy,
you could go a couple of different ways:
- one large database, separate filegroups. You could backup / restore each one independently.
- separate database, which could then obviously be backed up / restored separately.
Assuming indexes are correct, etc., joining from a table in database A to a table in database B, on the same server, will have no performance penalty.
May 1, 2009 at 3:16 pm
With the multiple database solution, you have to be sure that users are "synced" between databases. Using separate schemas and filegroups may actually be easier. To give a better answer we'd probably need more information about what you are trying to accomplish.
May 1, 2009 at 4:46 pm
troy.hodge (5/1/2009)
Our Legacy DB2 Z/OS System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. All 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.
You pose some very interesting questions, in part because their perspective seems quite unusual to most of us. For instance, why is having 13 different backup schedules a good thing? Having "the ability to back up ... at a time most convenient for that business group" seems like a very odd thing to say. Most production sites have log backups (effectively incremental backups) running at regular intervals throughout the business day and then a full backup say once a day. Would this truly be "inconvenient" if two or more of these groups were on the same database? It just seems like an odd perspective because that's not usually a serious priority.
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.)
That's a very complex decision that usually has more to do with the applications and data design and their interrelationships than anything else. I have never heard backup schedules come as part of this decision. (more below)
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.
Uhhm, that seems fraught with peril. I am not 100% sure that your friend is wrong, but I am about 95% sure that it does not work quite the way that it is presented here. Hopefully one of the experts on this (like Gail) will come along and comment on this.
Secondly, it sounds as though SQL Server has performance issues trying to join tables between databases (even though they reside on the same server.)
The rule of thumb on this is about 10% overhead. I've never seen it be a show-stopper and I woudln't worry about it if I were you. If it does come, then there are things that you can do, but your performance issues will almost certainly and entirely come from basic bt significant differences in how DB2 and SQL Server perform for different SQL features.
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
There are tons of potential shortcomings, starting with namespace collisions and distinctions. If you use schema names to address this, then in all likelihood you will have to change all of your SQL code to reflect it.
I'll be back in a minute with more...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 5:12 pm
As I stated above, this is a very complex question and neither backup schedules nor cross-database performance usually even makes the top 10 issues to take into consideration for it. The issues that you usually look at are:
- How distinct/common are the users/members?
- How distinct/common is the data?
- How much cross-linking is necessary?
- How much true RI interdependency is there? (this one is really important, there is no point to being able to restore a DB independently if it invalidates the validity of the other DBs/data)
- How often would apps have to cross databases?
- Can the data-divisions truly run without each other? and for how long?
- How independent does their SW maintenance & upgrades have to be?
Generally separate databases is a desirable goal because it gives you so many more choices and freedom to manage. But it's normally very hard to achieve unless built-in from the start because of the aforementioned issues.
The reasons to want to go to fewer databases are usually either 1) easier/less to manage and don't need all of these freedoms, or 2) the separate DBs actually seriously fail one or more of the above criteria (and thus are running at jeopardy) and the best fix is to merge them back together.
You seem to have already achieved this independence on DB2. There's nothing about SQL Server that suddenly makes that a bad design or decision. I would keep it the same.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 5:14 pm
Hey Troy,
I think the article below will answer your questions. It will take a bit of reading, and if you have any specific questions afterward, please post back.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 5:17 pm
troy.hodge (5/1/2009)
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 DB2 Z/OS System has 13 databases broken up into logical business areas. In no situation do we have R/I spanning between databases. All 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.
Secondly, it sounds as though SQL Server has performance issues trying to join tables between databases (even though they reside on the same server.)
I want to confirm that is infact correct and a good Idea. Are their short comings to this approach? Any reason we would not want to do this?
You should use separate databases if you want to be able to restore data independently.
Backup and restore by filegroup is just a way to spread out the backup processing, and is useful mainly for very large databases.
You can backup and restore by filegroup, but you have to bring the database to a transactionaly consistant state by applying transaction logs to be able resume processing, so that effectively means it is all or nothing within a database.
May 1, 2009 at 5:31 pm
It sounds like you have some fairly complex requirements. I think this could be done in 1 or multiple databases, but my first thought is that you should engage a SQL consultant to spent some time looking directly at your system.
May 1, 2009 at 5:40 pm
Michael Valentine Jones (5/1/2009)
You should use separate databases if you want to be able to restore data independently.Backup and restore by filegroup is just a way to spread out the backup processing, and is useful mainly for very large databases.
You can backup and restore by filegroup, but you have to bring the database to a transactionaly consistant state by applying transaction logs to be able resume processing, so that effectively means it is all or nothing within a database.
Nope.
File restore is possible in all editions of SQL Server from 2005 onward. In the Enterprise Edition of 2005 and 2008, and online fie restore is available.
See http://msdn.microsoft.com/en-us/library/ms190710(SQL.90).aspx for the 2005 version.
Paul
edit: just to clarify, the database file is the finest granularity of online restore but any filegroup which contains an in-restore file is unavailable and any attempt to access it causes an error. More details in the link, and links from there.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 5:42 pm
Michael Valentine Jones (5/1/2009)
...Backup and restore by filegroup is just a way to spread out the backup processing, and is useful mainly for very large databases.
You can backup and restore by filegroup, but you have to bring the database to a transactionaly consistant state by applying transaction logs to be able resume processing, so that effectively means it is all or nothing within a database.
(NOTE: I now know that the essence what I said here is incorrect, please ignore. --RBarryYoung)
Thanks Micheal, that confirms what I thought. The filegroups in a SQL Server database are not transactionaly independent and cannot be made to be so. Restoring one separately would just leave the DB in an invalid/incomplete/unuseable state.
Now, Oracle has has Tablespaces which do behave very much like your Tony's friend described (especially transportable tablespaces) and that may be what he was thinking of. People who have worked in both Oracle and SQL Server environments do frequently confuse the abilities of tablespaces and filegroups.
(Edit: posted incorrect info. Noted this)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 5:47 pm
Barry,
We posted together!
Script for online restore of a single read-write file in a filegroup:
http://msdn.microsoft.com/en-us/library/ms175532(SQL.90).aspx
edit:
Something I re-learned just now: a full file backup can form the base for differential file backups.
How cool is that?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 6:01 pm
Thanks Paul. Yeah I did notice that after I posted. I actually did a lot of work emulating Oracle transportable tablespaces in SQL Server and I always forget that it is only the transportable part that filegroups cannot match (and thus required a radical workaround for our client).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 6:07 pm
Cheers Barry,
Apologies to anyone offended if I came off a bit sharp there - I was posting quickly and disengaged some of the required politeness filters. Oops!
The backup/restore model is now very large in SQL Server. This brings great flexibility but also a heck of a lot of potential gotchas. The various schemes are held together by common logic, but I have yet to find an article that strings this logical thread though the options in a way I found natural.
And: Darn it - now I have to go and find out what a transportable thingy is in Oracle!
Bye bye hours of my life...{waves}
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 2, 2009 at 11:24 am
Paul White (5/1/2009)
Michael Valentine Jones (5/1/2009)
You should use separate databases if you want to be able to restore data independently.Backup and restore by filegroup is just a way to spread out the backup processing, and is useful mainly for very large databases.
You can backup and restore by filegroup, but you have to bring the database to a transactional consistent state by applying transaction logs to be able resume processing, so that effectively means it is all or nothing within a database.
Nope.
File restore is possible in all editions of SQL Server from 2005 onward. In the Enterprise Edition of 2005 and 2008, and online fie restore is available.
See http://msdn.microsoft.com/en-us/library/ms190710(SQL.90).aspx for the 2005 version.
Paul
edit: just to clarify, the database file is the finest granularity of online restore but any filegroup which contains an in-restore file is unavailable and any attempt to access it causes an error. More details in the link, and links from there.
Just in case there is still any confusion on this, this is what I was referring to (from the link you posted):
"If the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. This brings the filegroup forward to the log records in the current active log records in the log file. The recovery point is typically near the end of log, but not necessarily. "
You can restore files or filegroups independently, but as a final step, you need to apply transaction logs to bring the database forward to a transactional consistent state. The end result is no different than restoring from a full database backup and then applying transaction logs. This is obviously not what the OP had in mind, as he was referring to being able to independently restore filegroups to a point in time.
Basically, you can restore using a combination of full, differential, file, and\or filegroup backups, but you still need to reach a transactional consistent state by applying the transaction logs before the filegroups that you restored can be brought online in read-write mode.
So my original statement is correct: "You should use separate databases if you want to be able to restore data independently."
Note to Paul: I wasn't offended by your tone, but I wasn't too happy about being called wrong when I was right.
May 2, 2009 at 11:44 am
RBarryYoung (5/1/2009)
The filegroups in a SQL Server database are not transactionaly independent and cannot be made to be so.
Correct. There is no way within the native SQL tools to restore pieces of a database to different points in time. This means you can't take a filegroup backup of filegroup 1 from last week, restore it and have the DB usable with last week's data in one filegroup and data as of today in another.
Aside: This is one of the reasons why the native tools do not allow single tables to be backed up and restored independently of the rest of the DB
Main use for file/filegroup backups and restores are to spread out the backup time for large databases. Say when the downtime windows are too small to allow a full database backup. Interestingly enough you can take differential file/filegroup backups as well, just to make things really complex.
Other use for file/filegroup backups is partial database availability (Enterprise only) . Restore just the primary filegroup and then the others later. Great in a crisis situation where the primary filegroup contains the most important info and less important stuff or historical stuff is in other filegroups. Restore the primary, have the database online and usable then restore the rest later. Used properly it can really reduce downtimes when a restore is needed.
Restoring one separately would just leave the DB in an invalid/incomplete/unuseable state.
No, it'll just leave the filegroup in question in a RESTORING state (iirc) until all the log backups, including the tail log have been restored. The exception been for a filegroup that's readonly and was marked readonly at the time the backup was taken.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply