| Author |
Topic |
|
yutlin
Starting Member
5 Posts |
 Posted - 2005-03-11 : 10:59:45
|
| Hey all,Does anyone know of information regarding best practices in MS SQL for when to create a whole new database vs. when to create a new table? I'm working at a new job and I'm used to an enviornment where we created a database for each new database-driven tool created. I enjoyed the organization that model provided, however, my new boss is worried about performance problems that could be generated by having multiple databases. He's especially worried with people using Crystal Reports who would likely just build straight joins between databases. The current situation is one database housing several hundred tables (which to me is a disaster).Any help or suggestions would be appreciated! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-11 : 11:09:53
|
well what to do is totally dependant on your situation but i can tell you that you can easily join tables between databases.just use dbName..TableName or dbName.OwnerName.TableName naming convention.Go with the flow & have fun! Else fight the flow  |
 |
|
|
yutlin
Starting Member
5 Posts |
Posted - 2005-03-11 : 11:11:24
|
| Any idea on how performance is affected in a cross-db join vs. a cross-table join? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-11 : 11:15:04
|
| well cross table joins are certainly faster but not by much.but if the databases ar all on the same server the difference shouldn't be that big.Go with the flow & have fun! Else fight the flow |
|
|
|
yutlin
Starting Member
5 Posts |
Posted - 2005-03-11 : 11:18:25
|
| That's good to hear! Anyone have any links to some hard numbers or articles supporting the small performance difference? I'd like to have something to reference when I'm making a case to the boss man :) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-11 : 11:30:53
|
| well it's best if you do the test yourself. create two equal db's and then you join the tables in a single db or cross db.fill them with a million records. the difference should be preety obviuous then.don't have any good links on this. maybe someone else has.Go with the flow & have fun! Else fight the flow |
|
|
|
yutlin
Starting Member
5 Posts |
Posted - 2005-03-11 : 11:54:30
|
| spirit1,That's a good idea for a baseline analysis and I think I'll do that this afternoon. I wonder, however, if more complex linking would perform differently (when things like indexes become involved). Perhaps my boss wouldn't think of that though and I could simply convince him with a simple inner-join example :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-11 : 12:07:55
|
| Your boss is insaneWhat's he trying to save?Are all the tables related?If they're differnt applications how does the data relate so you would be doing joins...I've worked on several application that had thousand plus tables...But why comingle data from disparate sources?Just think about the transaction logs for starters...Brett8-) |
|
|
|
yutlin
Starting Member
5 Posts |
Posted - 2005-03-11 : 12:26:26
|
| X002548,I think the joins he's worried about are things like joining an employees in the staff database to jobs in the work database for example. To pieces of information that are logically separated but may need to be compared from time to time. I haven't dealt with transaction logs in SQL, are they separated by database? Also, can you think of any other reasons to strike down with avengence the current status quo here? I need ammunition here people! :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-12 : 00:44:40
|
| Only thing I have against multiple databases is that you can NOT synchronise backups - so if you restore two databases they are not going to be to the exact same point.Or are they? In which case I'll have to eat my hat ... again.Kristen |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-12 : 07:44:50
|
It's ok Kristen,You don't have to eat your hat(sorta). While you can't do backups at EXACTLY the same time, they can be started close enough together that restoring is possible; the restore point just has to be before the overlap time. As far as multiple DB's go, there will be no appreciable difference if they are all on the same server as far as processor time is concerned. You may run into network related traffic issues if the SQL ports are very busy...build two DB's on a server, connect a couple pc's to it, run Crystal on each using joined DB's, and then run performance monitor on the server and show your boss the network performance. The notwork overhead will be nil, your boss will believe what you're telling him (if he even knows what you're showing him!), and all will be good.Brett's right: the boss is nuts.There's two servers here running a total of 28 DB's(18 and 10);the smallest one is now pushing 1.75M records...if you use web based connections to your DB's, watch for a performance hit when linking big DB's ...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-12 : 07:48:16
|
| If you back up your databases at the same time (or roughly the same time) then you can restore them to the same point. Transaction log backups let you restore to a precise moment in time too.The one thing you lose with multiple databases is referential integrity. You can use triggers to provide this to some extent, but you can still end up with invalid data in your tables.Performance of JOINs on the same SERVER will not be significantly affected if you use one database or several. Having to JOIN across DIFFERENT servers will have an adverse affect and should be avoided.And if your database is designed and organized properly, several hundred tables is no great burden. You can always create views of frequently queried data to simplify your reporting needs. It's a good practice anyway, as end users should not query tables directly or be required to do complicated joins each time. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-12 : 12:11:50
|
| There are object security implications to using multiple databases that make it hard to manage.If TABLE_A is in database DB_A, then a dbo stored procedure can access TABLE_A without granting the user direct access to the table.If a user is connected to DB_A and a stored procedure uses table TABLE_B in database DB_B, the user in DB_A must have direct access to the table in DB_B.If a user is connected to DB_A with an application role, they cannot access the table in DB_B without resorting to something like openrowset with a SQL Server password hard coded, or granting access to the quest account.I see nothing wrong with having hundreds of tables in a database. I manage a number of databases with thousands of tables.I would say that the best rule is to group logically connected tables in the same database. If you can do joins between then, then they should probably be in the same database. If they are all used by the same, or closely related applications, then they should probably be in the same database.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-13 : 07:57:35
|
| Rob:So I can do a FULL backup on DB_A at 10:01, and on DB_B at 10:02, and then a transaction backup on DB_A at 11:01 and DB_B at 11:02Now I want to restore to 10:30So I restore the Full Backup, and then the Tranasaction backups - up to 10:30And there will be NO transaction mismatch between databases A and B? Do I have to specify an LSN to restore up to or somesuch to get the two DBs in sync? I'm surprised that it is that "guaranteed" - or maybe I've got the wrong end of the stick!Certainly I cannot restore just the FULL backups and expect the DBs to be in-sync, so at the very least I will have to restore to point-in-time whenever I do do a restore - e.g. to my TEST server.But I'd like to know what would happen in practice please, 'coz we do have some situations where data is "shared" between databases on the same server.Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-13 : 08:32:20
|
| When you restore the logs, you can provide a STOPAT datetime value, or STOPATMARK/STOPBEFOREMARK if you use marked transactions.You're guaranteed that your databases will be restored to their state at that point in time. What you're referring to as "in-sync" depends on whether the databases were "in-sync" when they were backed up. If you had to manage transactions between databases that must be synchronized upon restoration, then you can use marked transactions and restore both databases up to or before that mark.One thing you'd have to look out for is a transaction that occurs during a full or differential backup. If you wanted to restore to a point in time during that backup, you'd have to go back to the previous full backup and restore all the transaction logs up to that point.I've never used marked transactions, you would have to test them in your environment, but it should be easy to do. Books Online has the scoop under "RESTORE" and "BEGIN TRANSACTION". |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-03-13 : 17:02:45
|
quote:
One thing you'd have to look out for is a transaction that occurs during a full or differential backup. If you wanted to restore to a point in time during that backup, you'd have to go back to the previous full backup and restore all the transaction logs up to that point.
Right. The overlap during backups is the only time that there could be trouble. We backup most of our db's using marked transactions.ALSO!! Be careful about when you run SHRINK on the logs( don't ask me how I know this; I tried to warn the last admin); Don't put a step in your backup that shrinks the log before you are sure the next DB you are concerned with is also backed up(oooooh boy does speed kill!)AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
|
Creating a new DB vs a new Table