Hot answers tagged architecture
23
As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services.
If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM ...
12
I don't use blobs -- mostly from a backup and restore perspective, as I don't want the blob data slowing down my backups.
I don't store a full URL, however ... I only store the filepath below a certain point, and build the path as I have more than one way in which people & programs access my files (FTP, HTTP, local directory, NFS mounted directories).
...
12
The reason to use BLOBs is quite simply manageability - you have exactly one method to back and restore up the database, you can easily do incremental backups, there is zero risk of the image and its meta data stored in DB tables ever getting out of sync, you also have one programming interface to run queries or load/save images, so you don't need to give ...
10
CRUD is meant to define the characteristics necessary for a database as it relates to persistent storage. It is not meant to describe everything that could be done by a database engine.
To make a comparison, fundamentally a vehicle is a device used for transport. While true, this definition certainly doesn't include all the detail entailed in a modern ...
8
I'm a big fan of storing the "reference" copy of the image in the database -- from a managability/disaster recovery standpoint this is really the way to fly.
Now, you can still do lots of things to serve the image out of the filesystem for most applications so you are not putting that much pressure on the database server itself to do things it doesn't ...
7
If you are working with linux, storing the images in the filesystem and not in the database has significant better performance, see this excerpt of Brad Ediger's book Advanced Rails.
7
None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?
Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference
You lose:
data integrity
restore integrity (data in DB7 will be later then DB1)
You gain complexity:
...
6
If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to ...
5
I would say the term Database Administrator covers the full gamut of database duties: design, development, production support, performance tuning, report writing, OLAP, etc.
A Database Architect is involved in the design and development of large or complicated database solutions. However, the Architect is usually not involved in the day-to-day operations of ...
4
Some databases can run many engines, depending on the best fit for the job. For example, many of my applications use InnoDB for most data (key constraints and row level locking), MyISAM for session data (fast, less processing) and ArchiveDB for audit trail (compressed and insert/select only, no update/delete).
The "rest" of the database software provides a ...
4
Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion.
Almost all lookup tables. You read city and state list already. Why go to database again.
Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it.
Slow queries ...
4
The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational ...
4
As Aaron Bertrand mentioned in his comment, AlwaysOn is only designed to support offloading of read-only operations, in addition to its (really neat) redundancy capabilities.
If all you're looking for is a place where your developers can check out the production data without putting load on the production server, then perhaps it's a technology that might be ...
3
Except in the most formal of companies, specific titles are largely meaningless and DBA can mean nearly anything. Some DBAs are operationally focused, some are development focused, some are analytics focused. In reality, except in very large enterprises where titles are strictly controlled and people have siloed jobs, most DBAs will cross several areas of ...
3
These two terms have been used interchangeably by many. I look at them distinctly.
DATABASE ADMINISTRATOR (DBA)
The DBA does just what the name suggests...administrator of the database. Based on the specific RDBMS being managed, the role DBA involves implementing the following aspects:
user authentication
backup strategies
DB Metrics (Recording and ...
3
Thought experiment: Instead of dividing your database into seven pieces, split it instead into 7,000 pieces. What is the likelihood that a hardware failure is going to impact your application? If there is a 0.1% chance that any particular server may die on a particular day, are your chances better or worse that you're going to be impacted by a hardware ...
3
Was this a smart move?
If it was a smart move or not, it depends of your specific case:
If the file location is directly affecting any url structure or if you're storing full file addresses in the database (bad), I can say it was a bad move since you will have trouble in case somebody move or rename some directory.
But as But if your application is ...
3
I'm not much of a fan of storing images in the database. In a small app with a few users, it seems like an easy solution, but as you start to scale, it makes things more difficult.
My preference is to start out storing images in a folder on the web server, but keep the path in an easily accessible configuration so that when I need to, I can quickly move ...
3
I would recommend keeping everything in one database. It will be easier to manage and administer. If you need to add more domains/clients, you can continue to use the existing instance of the database (no need for new installations). The records that are specific to certain domains/clients will need some kind of indicator. It could be as simple as an ...
3
You can do it with multiple databases, but it will be more difficult to manage multiple schemas (rollouts, upgrades, etc) when there are changes.
The single database design is a kind of multi-tenant (now that you have the right term, you should find a lot of material about these designs) and you would need to work on the design of these grouping structures. ...
3
You might need an intermediate table to store this relationship, as it's possible that a student has more than one parent/gaurdian (a mother and a father) and it's possible that a parent/gaurdian has more than on student.
student
-------
id
gaurdian
--------
id
student_gaurdian
----------------
student_id
gaurdian_id
The table student_gaurdian ...
3
"Isn't it possible to scale database servers through virtualization,
like forever?"
Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...
2
For the sake of simplicity, I recommend MySQL Circular Replication only. Here is why:
There are many technologies and topologies that are far superior to MySQL Circular Replication. My favorite, hands down, is DRBD (Distributed Replicated Block Device). However, DRBD works great when the Server Pair is in the same bulding, data center, and rack. It's even ...
2
Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances.
There are no performance optimisations available with ...
2
We have a good sized Enterprise system that has many of SSIS packages taking data in and out of the system daily.
Some of the strategies we have used are:
Only process deltas especially in large files. To figure which records are deltas we use change tracking and send that data to tables in a separate database, so the process of figuring out the deltas ...
2
This sounds like a good use case for Galera.
http://codership.com/content/using-galera-cluster
You could also use something like this:
Each server is a master for it's copy of the user table (I assume the user table is sharded?). Then there could be a central server with MariaDB or MySQL + Tungsten Replicator which is a slave of all the other servers.
And ...
2
Having a separate database for every user is an extremely bad idea. It will be a nightmare to keep up.
Your database sounds teeny tiny, I would not worry about it causing problems to have one database. Databases can easily be designed to perform well with one database and can handle terrabytes of information speedily if properly designed.
2
It is tough to determine exactly what you are trying to accomplish here. If by "dev" and "QA" you mean "we only want to be able to run read-only queries against an exact copy of production" then sure, you can use a secondary/replica from your production environment to accomplish that. (Though, how "exact" depends on whether your Availability Group is set up ...
1
1GB -- not very big.
Latency -- good enough to get to the other side of the earth. How wide is your network? For example the US is less than 100ms wide.
Multiple read-only slaves, etc -- sure.
"each server should have READ-WRITE access to these tables" -- What do you mean? Perhaps you meant client?
"MySQL splitting techniques" -- Be careful! If you ...
Only top voted, non community-wiki answers of a minimum length are eligible
