May 3, 2009 at 4:35 am
GilaMonster (5/3/2009)
That's great when the DB is down and you need to restore parts of it really fast so that the users can carry on going, but it's not going to allow you to restore just filegroup 6 to an existing database because someone stuffed up and deleted important data from 20 tables.
Gail,
I didn't answer this explicitly before.
Yes it does allow you to restore the data on filegroup 6 to a point in time.
Just use bcp, SSIS, or SSMS' import/export facility to fast bulk load the damaged tables from the restored filegroup(s).
I did mention that before.
Paul
May 3, 2009 at 5:02 am
Paul White (5/3/2009)
Just use bcp, SSIS, or SSMS' import/export facility to fast bulk load the damaged tables from the restored filegroup(s).I did mention that before.
Is that really a serious suggestion?
I've had to do that before, haul a couple tables out of a backup (litespeed) and transfer them over to a database. It took most of a day. If it's one or two tables, it's not a big deal. If it's a lot of tables with foreign key constraints, indexes, other constraints then it's a very labour-intensive and mistake-prone operation and it's far from quick or simple.
Can be done? Yes.
Trivial operation? Most definitely not.
Oh, and it's not filegroup backups that give you that option. In that case (restore along side, bcp over) it makes no real difference if it's one huge database with multiple filegroups or multiple smaller databases. The process (restore, copy, drop the restored DB) is the same either way.
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
May 3, 2009 at 5:34 am
GilaMonster (5/3/2009)
Is that really a serious suggestion?
Of course...! :rolleyes:
GilaMonster (5/3/2009)
I've had to do that before, haul a couple tables out of a backup (litespeed) and transfer them over to a database. It took most of a day. If it's one or two tables, it's not a big deal. If it's a lot of tables with foreign key constraints, indexes, other constraints then it's a very labour-intensive and mistake-prone operation and it's far from quick or simple.
We've *all* had to do that before.
You lose nothing by using filegroups, and gain significantly: with a database restore, you *have* to restore everything. If the database is 1TB, that's going to take a while. Even using LiteSpeed (as I do) there is still 1TB of data to be physically written.
Let's take another example. A single table has been damaged by a physical error or an accidental update. What do you do? Restore the entire database? Restore the database somewhere else and copy the damaged table over...? 🙂
You assume that (a) the restore is large; and (b) that a restore to a point-in-time is required.
If the damage was physical, a filegroup restore to the current state of the database is clearly the win, and much faster with a filegroup restore.
If a point-in-time recovery is required, either copy the data from a database snapshot, or from the scenario I outlined.
In either case, re-creating the table(s) and bulk loading *while the rest of the database stays online* seems preferable in all but the most edgy of edge cases.
You can still restore the entire database if that is what you want.
As far as foreign keys are concerned - this is why I said that there is case for putting inter-dependent tables on their own filegroups.
Sure, you won't always be able to avoid some manual script work, but the DBA pain and inconvenience for the users is minimized with the online restore.
Re-creating indexes and table constraints doesn't seem too hard?
Super-large tables are likely to be partitioned on separate filegroups too.
Even if the entire table (all filegroups) need to be restored - I'm restoring less data from multiple backup files in parallel while your full database restore chugs along.
GilaMonster (5/3/2009)
Can be done? Yes.Trivial operation? Most definitely not.
If you want trivial, restore the database. If you want fast and available, go with the filegroup method.
GilaMonster (5/3/2009)
Oh, and it's not filegroup backups that give you that option. In that case (restore along side, bcp over) it makes no real difference if it's one huge database with multiple filegroups or multiple smaller databases. The process (restore, copy, drop the restored DB) is the same either way.
Of course it makes a difference!
The *process* may be the same, but restoring the 1TB database versus a 50MB filegroup?
I'll be home first, I reckon 😉
I never said that filegroup backups give you the bcp/SSIS/SSMS import option...why would you say that?
Paul
May 3, 2009 at 6:13 am
Paul White (5/3/2009)
You lose nothing by using filegroups, and gain significantly: with a database restore, you *have* to restore everything. If the database is 1TB, that's going to take a while. Even using LiteSpeed (as I do) there is still 1TB of data to be physically written.
You gain significantly over having multiple separate databases?
I don't argue that no filegroups vs multiple filegroups on a 1TB database is not a question. Filegroups obviously.
The question here though is 1 large DB with multiple filegroups or multiple smaller databases. Restoring primary + 1 filegroup of a huge database vs restoring an entire database that's the same size as one of those filegroups.
Same amount of data to be physically written.
Let's take another example. A single table has been damaged by a physical error or an accidental update. What do you do? Restore the entire database? Restore the database somewhere else and copy the damaged table over...? 🙂
Single table, probably the latter. 20 or so tables with complex relationships, constraints, etc possibly the former, but it depends on the type of physical error (are you talking physical corruption or dropped table?), how long ago the incident was, what my restore time will be and how much time I'm allowed by the business constraints to get that table back.
As far as foreign keys are concerned - this is why I said that there is case for putting inter-dependent tables on their own filegroups.
But if you're bcping data in, it doesn't matter where the foreign key constraints are, you still have to ensure that you load data in the right order, that you load only valid data if some tables are being loaded and some not. Or you have to recreate all the constraints afterwards and ensure that no invalid rows got it, which may happen if some tables are being reloaded and some not.
Re-creating indexes and table constraints doesn't seem too hard?
No, but it adds to the complexity and it adds to the time.
GilaMonster (5/3/2009)
Oh, and it's not filegroup backups that give you that option. In that case (restore along side, bcp over) it makes no real difference if it's one huge database with multiple filegroups or multiple smaller databases. The process (restore, copy, drop the restored DB) is the same either way.Of course it makes a difference!
The *process* may be the same, but restoring the 1TB database versus a 50MB filegroup?
I'll be home first, I reckon 😉
Read what I said please.
I said there's no difference for the restore/bcp option between 1 huge database with multiple filegroups (where you would restore primary + filegroup) or multiple smaller databases (where you would restore the full database)
One 500MB filegroup of a 1 TB database vs one 500 MB database?
I never said that filegroup backups give you the bcp/SSIS/SSMS import option...why would you say that?
GilaMonster (5/3/2009)That's just showing that you can restore 1 or 2 filegroups of a DB that has more. That's just partial database availability. That's great when the DB is down and you need to restore parts of it really fast so that the users can carry on going, but it's not going to allow you to restore just filegroup 6 to an existing database because someone stuffed up and deleted important data from 20 tables.
I didn't answer this explicitly before.
Yes it does allow you to restore the data on filegroup 6 to a point in time.
Emphasis yours and mine.
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
May 3, 2009 at 6:57 am
GilaMonster (5/3/2009)
You gain significantly over having multiple separate databases?
Yes. Take the OP's example where he has 13 databases.
You seem to be assuming that 13 DB2 databases -> 13 SQL Server filegroups, and suggesting that 13 SQL Server databases would be just as good.
Read what I said please. 😀
Each large table (or partition), or group of tables, or whatever - can be on a separate filegroup.
So 13 DB2 databases -> as many filegroups as are required to give flexibility and speed of recovery.
It is easy to see that this has many advantages over 13 monolithic databases: the proportion of each database that can stay online, the size of the files to restore, the throughput of the restore operation, and so on and so on. Each individual database could be 1TB was my point. Unless you are proposing to re-create the filegroup strategy with up to 32,767 databases...!
GilaMonster (5/3/2009)
20 or so tables with complex relationships, constraints, etc possibly the former, but it depends on the type of physical error (are you talking physical corruption or dropped table?), how long ago the incident was, what my restore time will be and how much time I'm allowed by the business constraints to get that table back.
Of course it depends. It always does, but the big picture is that filegroups give you everything separate databases do, plus a great deal more. In any given restore scenario, your options are greater with the filegroup method.
GilaMonster (5/3/2009)
But if you're bcping data in, it doesn't matter where the foreign key constraints are, you still have to ensure that you load data in the right order, that you load only valid data if some tables are being loaded and some not. Or you have to recreate all the constraints afterwards and ensure that no invalid rows got it, which may happen if some tables are being reloaded and some not.
If all related tables in a PK-FK relationship can be restored together from one or more filegroups, it's a quick win.
If not - you have lost nothing. There will always be a judgement to make. Sometimes a filegroup restore will be optimal, sometimes you will just restore the whole database. Point is: you have a choice. In my experience, most restore operations are either relatively small or a full restore. So we choose the best tool for the job.
GilaMonster (5/3/2009)
Re-creating indexes and table constraints doesn't seem too hard?
No, but it adds to the complexity and it adds to the time.
Yes it does, obviously. That would be part of the judgement call. In many restore scenarios, this will not be important, in others it may.
GilaMonster (5/3/2009)
I said there's no difference for the restore/bcp option between 1 huge database with multiple filegroups (where you would restore primary + filegroup) or multiple smaller databases (where you would restore the full database)One 500MB filegroup of a 1 TB database vs one 500 MB database?
You wrote:
GilaMonster
Oh, and it's not filegroup backups that give you that option. In that case (restore along side, bcp over) it makes no real difference if it's one huge database with multiple filegroups or multiple smaller databases. The process (restore, copy, drop the restored DB) is the same either way.
Which really doesn't say the same thing at all, unless one filegroup = one of the multiple smaller databases - I don't know where you gained that impression.
For example, my demo script explicitly restored one table on its own filegroup to illustrate the difference - at no stage have I said that 1 filegroup = 1 of the 13 databases.
The demo had three tables on three separate filegroups.
GilaMonster (5/3/2009)
Emphasis yours and mine.
I have looked at this long and hard, and still have no idea what point you are trying to make.
You said "but it's not going to allow you to restore just filegroup 6 to an existing database because someone stuffed up and deleted important data from 20 tables".
I replied "Yes it does allow you to restore the data on filegroup 6 to a point in time."
Because it does...?! Please explain.
Paul
May 3, 2009 at 7:09 am
Gail,
To distill that down a bit.
The whole point of the discussion is that I am challenging Michael's statement that creating separate databases was the only way to meet the OP's requirements.
The fact is, that given the separation of data in the existing databases, it makes sense to convert those 13 DB2 databases to 13 SQL Server databases. Of course, each of those 13 databases could take advantage of filegroups and full file backups to give the benefits I have outlined.
But that is not the point.
I am simply explaining that a solution could be created in a single SQL Server database, and that solution would have advantages over a separate database solution that did not also implement the filegroup/full file backup strategy outlined.
The key answer to your points is that I have not said that 13 DB2 databases = 13 SQL Server filegroups, and that was never the proposal.
Paul
May 3, 2009 at 8:43 am
Interesting debate, and reading through it, I'm not sure I have things underfoot, but I wanted to chime in.
Currently the business units are separated into 13 databases, so moving this over to SQL Server will work fine. If there's a good reason to do so, I think there are some good reasons to do things this way.
- scale, if you need to scale out, you can.
- backup is simpler. Most DBAs struggle a bit with filegroup restores, especially with the point in time. I know it's not really that much more complicated, but people think it is.
- fits the current model.
One DB, you have some good ideas listed above with regards to recoverability, and I'd agree this could work. It also makes coding simpler.
Personally I'd lean towards separate dbs, especially as things get large. It becomes easier to move to new servers, and administer (to me). But that's me.
There are a lot of unknowns that we don't know, so we can easily come up with reasons why separate dbs v one db is better. Both could work, but which would you recommend to a junior DBA you had to leave in charge of this?
May 3, 2009 at 2:36 pm
Steve Jones - Editor (5/3/2009)
Interesting debate, and reading through it, I'm not sure I have things underfoot, but I wanted to chime in.-snip-
Personally I'd lean towards separate dbs, especially as things get large. It becomes easier to move to new servers, and administer (to me). But that's me.
There are a lot of unknowns that we don't know, so we can easily come up with reasons why separate dbs v one db is better. Both could work, but which would you recommend to a junior DBA you had to leave in charge of this?
Hey Steve,
Yes, I agree completely - from what we know (the OP is new to SQL Server and the databases are currently separate) I'd definitely go with the separate database solution - in this case.
As I said just now, I am just debating the viability of the full-file backup / on-line restore in the general case.
The full-file solution is much more complex to administer, and many third party tools fail to support it at all, so definitely not one for someone just starting out.
Cheers,
Paul
P.S. If anyone would rather to continue the debate via email, or PM, just let me know.
May 4, 2009 at 11:23 am
Great responses, Looks like I found the right forum to post questions. Just for clarification, I wanted to add a few things.
Our entire system is currently DB2 Z/OS (mainframe) and DB2 on a Windows Server. We put out a bid for an entire new system. The selected new vendor is coming in with Sql Server and a .net solution. The vendor supplied a DBA who is very sharp, but his lead is making a lot of "executive" decisions. SQL Server isn't the leads forte. I am one of the DBA's that will be here to maintain the new system when vendor leaves.
When I spoke of the 13 business units, they are completely separate areas. They each have their own hours, own location, personnel and business function. They do share data access between some areas, but R/I never crosses these areas. Back up and recovery are just a small part of the equation. Security, logging, reorgs, availabitlity, performance and ease of administering are other parts. Each area (Database) has 20 - 100 tables. We have around 600 tables in our entire production system. Around 200 gig total.
I've seen many advantages listed to using multiple databases vs a single db. That seems to be the consensus. I appreciate that it is possible to utilize just one database. What are the advantages to using 1 vs. 13? I've seen simplified coding and the comment about 10% performance hit. Is that all?
May 4, 2009 at 11:34 am
Basically, one database is just easier. You only have one backup schedule to wory about, one security access list, etc. That said, it's not 13 times easier, just somewhat easier.
[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 4, 2009 at 11:59 am
Great debate. My vote goes for 13 separated databases!
* Noel
May 4, 2009 at 12:22 pm
My vote would go for separate databases, based mainly on your statement that each database represents a different business unit, with its own hours, etc. If you try to put all that information into a single database, think about what would need to take place to schedule a restore of that one database across all business units, as opposed to having to do that for one single business unit.
I'm not sure there's a single "right" answer to the question; you're going to have to make a decision based on your judgement.
May 4, 2009 at 3:06 pm
I'd still vote for 13 separate databases, and turn Barry's comment around. It's harder than one database, but not 13x harder. Just a little harder.
May 4, 2009 at 6:28 pm
Steve Jones - Editor (5/4/2009)
I'd still vote for 13 separate databases, and turn Barry's comment around. It's harder than one database, but not 13x harder. Just a little harder.
Heh... and I'm not so sure it's harder. It makes life a whole lot easier than trying to reindex a single tera-byte database without any interference. In a crisis, restores are going to be a lot quicker, as well. Haven't had to do it, but you might even be able to get away with parallel restores which just wouldn't be possible on 1 huge database.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 7:23 pm
Jeff Moden (5/4/2009)
Steve Jones - Editor (5/4/2009)
I'd still vote for 13 separate databases, and turn Barry's comment around. It's harder than one database, but not 13x harder. Just a little harder.Heh... and I'm not so sure it's harder. It makes life a whole lot easier than trying to reindex a single tera-byte database without any interference. In a crisis, restores are going to be a lot quicker, as well. Haven't had to do it, but you might even be able to get away with parallel restores which just wouldn't be possible on 1 huge database.
Right. It's easier, unless there is something that you cannot manage correctly or adequately in a single database. Then it's not only not easy, it's also not correct.
And I still maintain that if the multi-catalog design was correct on DB2 than I have not heard nor am I aware of any reason why it would be wrong on SQL Server and you should go to a muilt-schema design instead. It's just crazy to re-architect your application just to fit someone's idea of what's "typical" on SQL Server. Re-architecting could easily add a 50% hit to your migration project.
[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]
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply