3

Our Web API app is C#/MS-SQL and our DBAs want us to use two databases, one Writable, one Read-only. The database for writes will replicate to the read-only copy. This question may apply to other languages as well.

The reason for this (from the DBAs) was so performance would not be impacted if/when tables are locked during a write.

We are trying to determine the best way to have our Show/Get methods use Readonly, yet ensure if there was just a write operation, we have fresh data. One suggestion was having a ReadOnlyToWritableData connection, but then why have the Readonly database.

What are some solutions already being used? I searched around, and couldn't find anything definitive.

UPDATE: I was informed that our DB patter is an 'Always On Availability Group'. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server I may need to do more research.

  • 6
    That seems like an unusual design decision. When we look at the CAP Theorem, it seems your DBAs want to sacrifice data consistency, which SQL databases work hard to achieve. Now you're trying to reimplement consistency on top of your database? I doubt that's going to work well. (1) Do you really need the freshest data? Is outdated data wrong, or just not as useful to your users? (2) Do you really have a performance problem, or are you scaling prematurely? – amon May 17 '17 at 18:37
  • 1
    You don't. By trying to intelligently invalidate your cache, you're likely to burn through any performance improvements you might've gained and much more likely to introduce errors while doing that concurrent orchestration. – Telastyn May 17 '17 at 19:02
  • 1
    @amon, some flavors of CQRS may take advantage of having multiple databases. From the quoted article: "More sophisticated forms may have multiple databases, polyglot persistence, data denormalization for query purposes, event sourcing...". – Machado May 17 '17 at 19:16
  • 2
    If your DBA's haven't taken some measurements to see if this will even produce a performance difference, their radical design decision is premature. Have they considered simply using NOLOCK on a single database? – Robert Harvey May 17 '17 at 19:30
  • 3
    "The reason for this (from the DBAs) was so performance would not be impacted" - If you haven't actually observed poor performance in the actual system then stop right there. Assuming you have seen performance problems, would it be possible to isolate just the most time-sensitive read queries to run against the writeable database, and then read from the read-only replicated copy for everything else where a little latency won't hurt? – Graham May 17 '17 at 19:38
2

This is an availability group setup where the application writes to one instance of the database and the reads from another instance. It is database sharding, which is not an uncommon practice and is something you will see for databases with an extremely high number of read and write operations. This is how to maintain scalability for large scale web applications (ecommerce).

An example of this issue could be seen with carts. A user adds an item to their cart, it is saved to the database, then forwarded to the next page which reads the cart and shows the items. If there is a delay with the synchronization, this could be an issue. While the delay (if the environment is set up correctly) may only be a couple of milliseconds, if there is an issue on the secondary database, the read could pull the data before the new data was actually synchronized.

There are only two possibilities:

1 - Use the application intent on the database listener and the connection string. I have heard, however, that the SQL driver for .net did not reliably implement this feature (Microsoft not working well with Microsoft). I don't know if this is still true though. Application intent will automatically switch from rw to ro. However, even implementing this will not resolve the possible issue.

2 - Use a stored procedure for the times when you know you will instantly need the data. In our cart example, a procedure to add an item could then return a recordset with all of the items currently in the cart.

What the OP is asking is this: is there a way to do this in code? Has anyone done this in code (not the stored procedure answer)?

1

Instead of connecting to a specific database instance (server), the application will connect to the HA (High Availability) cluster. The cluster will automatically direct you to the instance that is available (either primary or secondary).

So, your application should be transparent to the latency. Note, if the primary goes down, there is some latency to switch over to secondary, but again your application is going against the cluster not a specific instance so the cluster will handle the re-direct for your automatically.

1

Seems to me, if you are concerned about performance, the objects that read from the database should also maintain a cache so that multiple reads will only result in one trip to the database. If you build that, then all you have to do is code the objects that write to the database to also update the cache. That way the next read operation will hit the cache and get the more recent data.

0

Databases are a shared resource. Most programmers think of them as private property. My job as a DBA for many years was to "direct trafic" at this busy data highway intersection. John Wu's answer is on the right track.

There needs to be a layer of code between the application and the database. That layer caches the data going both ways. To the application, it looks like its own private datastore. To the database, it looks like one or a very small number of reads and writes.

Ideally... the database would get 1 SELECT at the beginning of the transaction and return all the data from all the tables that the application needs. That way, the very expensive and incredibly optimised database engine can use it's intimate and continually changing knowledge of the data to retrieve that infinitesimal subset of records in the fastest and most efficient way possible.

For example, if a table had only a few thousand rows and you have the memory, cache the whole table in RAM and do full table scans. Ignore the indexes since the double fetches would slow things down.

If all the columns needed are in indexes, then ignore the data tables and read just the indexes. Since indexes are often changed by the DBAs as part of the continuous tuning process, there's no way for the application to take advantage of this though.

No programmer, anywhere, regardless of skill, has the slightest chance of doing all this better because we do not and cannot analyze the data, this minute, the way the database engine can.

So, the selected data would be cached and constitute the entire body of data the application can manipulate in this transaction. If no application server is in use to do the caching, it can, and often is done using database stored procedures.

It could also be done by the application... but that way "there be dragons". The application programmer simply does not have access to all the information needed to write an efficient and reliable "middleware" layer. It may work for small databases, but it won't scale. It will quickly become the #1 bottleneck in the system. Don't do it.

Once all the edits to the cached data are completed, this middle layer writes the new data back to the database in the most efficient way possible in order to maximize performance. It's tempting to say that it should be 1 write, but that's rarely wise... or even possible.

The approach used is highly variable. It depends on the number of tables involved, quirks and features of the database engine, current load on the system, volumetrics, and business rules for how current the data must be.

Regarding the latter, recognize that not everything has to happen right now. The most efficient way to make database changes is with overnight "batch jobs"... long running programs doing bulk INSERT, UPDATE and DELETEs of all changes from that day.

(Introduction to Smoke and Mirrors -- That 1 query done at the beginning? Under the covers it would often actually be 2 queries. One to the main database, and one to a set of transaction tables holding recent changes that will be applied to the main database by a long-running-process later when there aren't thousands of users banging on it.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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