Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am a student, and have a question for an assignment:

What problems and challenges do database administrators face when designing systems to trade international stocks in real time and 24 hours a day, 6 days a week? Are cloud based database systems as a service a viable option?

share|improve this question
For one thing, you're looking at a very intermittent maintenance window. You may think 24 hrs / week is a good ratio, but not when that a maintenance window is a max of 6 days away. As far as the "cloud", that word has been thrown around countless times with very different definitions, so you'll have to specify exactly what you mean by "cloud". –  Thomas Stringer May 17 '12 at 1:10
The number 1 concern of course is uptime. You're looking at a very demanding SLA. –  MarlonRibunal May 17 '12 at 1:24
Your system would need to be absolutely stable and rock solid. As such, you want to avoid rapidly evolving systems that keep changing all the time, with likely frequent breaking changes. Google up "breaking changes" followed by the name of whatever cloud you mean, and see for yourself. –  AlexKuznetsov May 17 '12 at 3:07
Sorry, that is not demanding SLA. I once was administering a system with 24/7 - mainteannce 3 in the morning meant some people get service disruptions (IP Television). THAT is demanding SLA. 24/6 means you have plenty of time to do stuff like harware swapping etc. –  TomTom Jul 19 '13 at 3:53
On top, seriously, DBA's do not design this. Architects do. The database is just the last element in a large stack and uptime and scalability must be in the DNA of the whole system. The DBA can not safe that if the application does not have this in mind from the very top. –  TomTom Jul 19 '13 at 4:05
add comment

3 Answers

Because it's a system, you will have a LOT of interacting subsystems, including power, environmental controls, communications, hardware, and then on top of that the data management and everything else.

There are facets which cut across all these layers, like security, redundancy, business continuity, etc.

Just from the database side, you would need to consider the transactional load, the architecture for balancing the load, the storage requirements, the response time requirements.

Everything depends upon the requirements - of which there will be many from regulatory bodies, clients, users, business partners.

As to whether cloud-based databases, I don't think any of those can be viable if your organization has to be responsible for the system.

Any time you outsource anything, you limit your ability to be responsive to requirements. Some things you delegate are easier to mitigate than others. For instance, you decide to use a software library as a component in your system - you can always modify the library if you have the source, or rewrite it yourself or swap it out. Or picking a RDBMS like Oracle or SQL Server where you can get a vendor to do a proof of concept and have a high level of support because the sale is large.

That's not to say by relaxing your requirements, you couldn't use Google Apps BigTable or Amazon or Azure or whatever, but the simple fact is that even with a contract, you have a limited amount of control over another organization's system and you are relying on them.

All systems are built by balancing tradeoffs.

share|improve this answer
Most of those issues are not administering though. Seriously. If the DBA has to think about how to scale the app, the developers need to be hold for damages and fires. Environmental control is normally not the DBA job but the datacenter operations team. Redundancy / continuirty is also a lot more a design / architecture issue than a dba one (if not planned by architecture, the DBA can not safe it). –  TomTom Jul 19 '13 at 4:04
@TomTom I'm sorry. The question did say designing systems and not just the database subsystem. And in this case, you are right, it is more properly a subsystem, and so the responsibility is much more limited. –  Cade Roux Jul 19 '13 at 7:49
Even then, sorry, the DBA rarely gets into the game of controlling physical datacenter aspects, such asyou name with power, environmental control etc. - in any larger operation that is a separate group, as there is also one for the networking side. You say what you need, that is it. Quite often DBA's are not even allowed close to the physical machines. –  TomTom Jul 19 '13 at 8:42
add comment

Planning, testing, and executing for a disaster scenario or security break in would be a tremendous challenge. You also have to keep in mind that there would also be some form of regulatory items you have to implement into your environment.

share|improve this answer
Possibly, possibly not. These systems can be isolated on separate networks and run with relatively low security after the gateway and regulatory requirements may be none. I know I do not have any for my trading applications. –  TomTom Jul 19 '13 at 4:02
You may be surprised, but I know of a larger trading operation in the center of europe (around 1500 employees) where you have zero security on the trading network. Plug your machine in, subscribe to the feeds and you can not only enter orders but also see all positions in real time. The network is isolated - the ports protected - but the rest not deemed necessary. And that is a company maintaining europes largest trading floor, with 11 offices worldwide. Not some small shop like mine;) Zero security - just network isolation. –  TomTom Jul 19 '13 at 4:16
add comment

None. 24/6 is quite good - thee are systems where mainteannce is a real problem.

The main problem can be - and please do not edit out the blunt langauge - if some morong stupid architects have NOT designed the system for 24/6. I.e. if it does not work with replication, or needs exsessive maintenance (on the free day) so that the admin has no chance to do system level stuff there.

If it is designed for using all features of the sql server for uptime, 24/6 is a quite good and easy to work with SLA.

You also may have a small maintenance window every day - you did not say that, but I know even forex sleeps an hour per day simply because no major location is having busienss hours during this time. And forex is unregulated. It may thus be you can do some small stuff daily.

That said, a good design would likely partition the database by location (or a group code, so you can keep all european in one server, all american in one) and allow sharding - if US markets are off, the US responsible servers would not update. As "contintens" mostly share timezone (yes, I know the america cover more - but not excessively so) that gives you maintenance windows for every continent.

Cloud based likely will not work at all because cloud based is small stuff only. Try finding someone offering you a cloud storage for a medium size database and you be surprised. Hint: medium size likely is 100gb+. It also depends what "stock trading" does. Do you test strategies? Large scale? I currently do only futures trading - 22 symbols - and our simulations generate around 50gb-75gb data per day for analysis AFTER compression. 200 before. I utilize partitioning in staging (sql server) just to be able to delete bad results fast during regular maintenance - otherweise I would do multi hundred million row delete statement. The last risk management database I build for a customer (energy, not stocks - so less symbols) had a space allocation of 20 terabyte and was using an Oracle ExaData hardware cluster (in 3 copies - in 3 locations, replicating). It really depends how large the customer scale is for this.

That said, here is something really to think of: what is "stock trading"? If it is "manual" or "automated slow" then this is not too demanding - even 1000 traders wont do a TON of trades from a db perspective.

If it is HFT - High Frequenvy Trading - you may have a single algo in a single stock sometimes doing 100 trades per second. Add many markets, many symbols, many algos and you can not scale that in sql - this is why those algos write logs, keep things in memory and you then regularly import the transactions from the logs. Real time is NOT doable for HFT in SQL - especially not because it would impact the machines (slow down the trading side). When you cound millionths of a second (and yes, they work by now in microsecond scale) and use programmed hardware (FPGA) to make the price feed parsing to be faster than the next machine - you really decouple things. By the time SQL decides to commit the transaction about the purchase, the price may have moved too far ;) And no - do not say multi threading. I know some of the infrastructure. Thos algos run one thread per core, hard wired to the core, in burning mode - and endless loop checking for the position the other core has put the data. You easily may get 20.000 transactions per SECOND peak load from someone trying to do arbitrage between US stock markets alone. That is per exchange - and there are some.

As I said - this is a very complex system setup. A lot depends on the architecture.

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.