| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-06 : 11:57:56
|
| hommer writes "The company I am working for is migrating into SQL Server 2000 from three different directions at the same time. First is their package application FS Financial and Manufacturing. That used to be in the vendor’s proprietary database Titanium. Second one comes from another vendor Order Entry/Order Processing application called SQ with backend in SyBase, and the third one is a group of home grown MS Access applications and a database for their web site.My question is on what is the better way to organize/design SQL server. Solution 1:SQL Group 1Database1_FSFinanceDatabase2_FSManufacturingDatabase3_SQOrderProcessingDatabase4_AccessAppsSolution 2:SQL Group 1Database1_FSFinanceDatabase2_FSManufacturingSQL Group 2Database1_SQOrderProcessingDatabase2_AccessAppsOr just dump every thing into one Database, and give different application group a unique table prefix assuming capacity is not an issue. " |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-06 : 13:08:06
|
| what are the application uptime requirements? What kind of server hardware you putting this on? How large are these existing databases? These are all questions that will factor into how you configure your databases.I would definitely rule out putting everything in the same DB. That doesn't work well with SQL Server.-ec |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-07-06 : 16:17:13
|
| Uptime min 7 AM to 2 AM next day, or max 5 hours downtime from 2 AM to 7 AM.SQL Server 2000 standard edition, is running on Windows Server 2003 standard edition. The machine is HP Proliant DL 360 with 2 72.8 GB 10k Ultra 320 SCSI drives. They use SAN network storage module to manage storage.FS_Finance is 11 g,FS_Manufacturing is 25 g,the rests are in between.Also, where is the best place to put in development counterparts? In its own server instance group, or as several databases mirroring each of their own twin brothers?This is a such critical and interesting topics, yet I did not find many people talk about it. There are tons of table design, database design, but not "server design". And in the past, when I walked in, the "house" is already there, I just needed to go to the rooms to have my jobs done. This is the first time, I am asked how to architect the house. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-06 : 16:21:10
|
| These should definitely not be put in the same database. Put all of them in their own databases. Whether or not you put them on the same server is another story. You could put them on different instances.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-06 : 16:34:34
|
| One big benefit of using multiple instances on the same box is that you can control the memory allocation for each instance. That way, you don't have a database that monopolizes the entire buffer cache of your system. With win2k3 you can also manage how much CPU each instance gets in a little more granular way than with win2k. Your uptime requirements for each application are a big factor in determining if you want to put all of your databases on one physical server. Also, down the road you can always relocate a database to another server if it turns out that putting everything on one box has overburdened it. SQL Server makes this a very easy process.-ec |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2004-07-06 : 16:40:33
|
| I would definitely split you development out to at least a different instance of SQL or more preferably a separate server this way it is easier to do performance testing and you can test operating system service packs etc outside of SQL without having to upgrade on dev and production at the same time. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-07-06 : 17:32:42
|
| The machine is HP Proliant DL 360 with 2 72.8 GB 10k Ultra 320 SCSI drives.Assumming this is Raid 1 then the write performance is really going to be horrible because of the crappy hp built in controller.Suggest that you split your data, log, and tempdb to separate raid 10 arrays of 4 small 18GB 15KRPM each which should run about 2,400 dollars and a nice raid controller card with lots of cache for around 600 and an enclosure I think you can get one for around 1,000. Also if you are doing any dw/olap make sure you stock up on lots of memory. Keep your app server, database server, and backserver on gigabit backbone for high throughput of 50-90MB/s versus 8MB/s.Run database maintenance on your database, indexing, defraging, partioning, statistics, integrity(checkdb) during your maintenance window. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-06 : 17:44:12
|
| They do have the worst internal RAID cards ever made. lol If he's using SAN storage, that shouldn't be too much of an issue except for the poor paging file. RAM, RAM, RAM. Although, the standard edition really limits you.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-06 : 17:55:37
|
quote:
Originally posted by derrickleggett
They do have the worst internal RAID cards ever made. lol If he's using SAN storage, that shouldn't be too much of an issue except for the poor paging file. RAM, RAM, RAM. Although, the standard edition really limits you.
It is funny that you guys mention this, because I have noticed that the DL360 is much slower than the DL380 in roughly equivalent configs. Our DL380 boxes running 2.8Ghz are much faster to start up then our DL360 3.2Ghz boxes. Both systems were running the same exact disk (15K drives) and RAID controller w/Battery backed write cache installed. We haven't done any scientific tests or anything, we all just collectively shrugged and thought you get what you pay for. Maybe HP cut some corners on these boxes, or there are chipset differences or something. But they are definitely slower than the DL380s.Sorry to go off on a tangent, but I think we have actually answered the initial question..-ec |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-07-06 : 18:01:42
|
| I can certainly appreciate the benefits of separating them out for the ease of managing them. That is primary a concern of a dba. But from a db developer standpoint, it is harder to code a select statement if the tables are scattered all over the databases and server groups. It will take more effort to keep unique id unique, as well as to keep data in synch.I guess like many things in life, the art of the trade is to balance every factor. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-06 : 18:21:51
|
quote:
Originally posted by Hommer
I can certainly appreciate the benefits of separating them out for the ease of managing them. That is primary a concern of a dba. But from a db developer standpoint, it is harder to code a select statement if the tables are scattered all over the databases and server groups. It will take more effort to keep unique id unique, as well as to keep data in synch.I guess like many things in life, the art of the trade is to balance every factor.
You are confusing the physical database design with the logical. you don't need to worry about the physical design from the SQL SELECT standpoint. SQL Server presents the database to you in the same way regardless of where your datafiles reside. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-06 : 20:50:50
|
quote:
Originally posted by tduggan
These should definitely not be put in the same database. Put all of them in their own databases.
What's the rational for that please Tara?Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-07 : 12:00:28
|
| They are different packages. Why would you put them in the same database? Each database should contain the objects for one package/application.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-07 : 12:29:09
|
| Only real reason to put them in one DB, in my mind, is so that a backup is "atomic" for them all. But I don't know if that is important in this case.The fact that they are moving them all into SQL suggests there is either considerable cross-over, or they just want easier maintenance/hosting/etc. If the cross-over between them all is sufficient I would consider a One-DB approach, but I'm only thinking Backup=Atomic and I don't know if that's sufficient reason.Kristen |
|
|
|
|
better way to organize/design SQL server