Featured Script
CheckErrorLog
The script shows SQL Server’s error log without messages about differential and log backup. It is usfull if you want to chec… (read more)
Database Design Feedback – a Different Perspective
David wrote in with a different take and suggestion set on the database design questions. Agree? Disagree? Have different experiences/comments? Drop me a note here.
"1) Speed: It has been my experience that queries running on a single database are faster than on separate databases. This remains true as long as the single server has the processor power and memory to execute the queries in a valid amount of time, assuming the databases are well indexed, etc. Databases spread across different systems are reduced in their communication ability to the speed of the network. If you have 10 gig network that concern is diminished. One of the biggest remaining concerns for the speed of the queries is the speed of the disk IO. I would definitely think you are looking to get a large array of SAS drives for high speed IO.
2) Resource Utilization: The resource utilization is going to be far more dependent on the quality of the queries being fed to the database than the number of users. If each user runs a query that requires most of the database tables to be scanned each time it is run, taking far more memory than the server has, then the resources will be maxed. You can always avoid table scans by making good use of indexes, but nothing will replace having good synergy between the application and the database. While it is being developed the DBA’s need to keep on top of the utilization and stop the developers when they run a query that is great on their end, but which halts the database.
Having said that there are architectural designs that can help as well. Any fairly static lists that will be needed by clients (states in the US, etc) can be loaded into memory, from the database, by a web service and served from there. The web service would only need one access to the database regardless of how many users there are. If you were to have, as an example, ads for a web page that need to be refreshed every 10 minutes this could also be done through a web service. Thousands of users would request the adds and get the same ads from the web service until new requests come in after another 10 minutes has passed, then they get new ones.
This shows the bulk of the requests being handled by the web service and the database would get hit for the ad list once every 10 minutes.
3) Database size is always a concern. Not just for storage space (a SAN helps a lot here), but also for backups (both the size of the backup and how long it takes to accomplish). If you opt for the separation of some of your data (as mentioned in 2 above) then you could easily separate it without concerns. If you do separate certain data… you can link databases to run a single query but expect it to be slower. I have seen companies split single tables across databases. This can become a real headache when it comes time to backup or restore. Keep in mind that any database where data returned can come from a single source will operate better and be easier to maintain.
You will want to be sure that your system can handle the data juggling needs. Back in 1999 I worked for a company with large concerns on their database performance. They bought a system with 4TB of RAM and 64TB of hard drive space (the system mother board had a built in capability to do 64GB stamp memory copies, as a single operation, used by the database software through a driver). When it comes to power it is better to have too much than to not have enough.
After having said all of that… MS SQL is absolutely NOT one of the fastest databases out there. Oracle beats it every time. I recently had to price Oracle and SQL Server for an ERP implementation at the last place I worked. The ERP software could use either. The ERP Vendor stated that Oracle was the fastest database it could run on, and that is you were going to have more than 500 users it was strongly recommended over SQL Server. I did the pricing and I was shocked. I told the Oracle people that we, as a company, had never used Oracle. They said they would be sure to beat Microsoft’s price. Their price was under 15k for more than we needed.
The price for MS SQL Server was over 48k and it took myself and two other company experts in Microsoft EULAs more than two weeks to figure out what we needed to be legal on the SQL Server side."