December 2, 2015 at 2:22 am
Is there any way to cache database, and put one server behind database server and application server? will that support invalidate caching incase of row level modification?
What do you recommend, Is it better to use any of such product or that will not work with a large system with thousands of tables and several thousands of SPs? thousands of transactions per minute.
Regards,
Shamshad
December 2, 2015 at 3:09 am
Caching's typically done either in the application server or a cache between the app server and the DB. Caching's not something you can just plug in and go, you need to have a good understanding of the app and how it uses data and what data is static or semi-static and can be cached safely. Cache invalidation is a hard problem. Too often and the caching's pointless, not often enough and the app's working on bad data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 8:17 am
Even cache databases have issues here at scale. If you can somehow shard/federate your data, then perhaps you can use multiple systems to do this, but it's not a simple process. As Gail mentioned, cache invalidation is an issue.
In the SQL world, there are a few things to do. One is more memory, to ensure that most queries hit a RAM buffer. You can also use mutliple servers, with some technology like a replication or an AlwaysOn AG that allow reads to occur on servers separate from writes. The value here depends on whether your application can help support this with redirection of queries to different servers.
You can also use messaging technologies to queue writes, and allow them to be distributed with small delays.
December 2, 2015 at 8:42 am
One of the first things you need to do is determine whether adding complexity is necessary. Thousands of transactions a minute isn't that high all things considered
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 9:44 pm
To Gail,
As an application developer, few years ago I worked in a software house where we implemented MemCache, I know it is all manual work to understand what we need to be cached and at which step we have to invalidate that cache object and fill data from db and serve to clients then. In this organization when I joined a few months ago as a DBA, they already had implemented Windows Server AppFabric Caching in their application code. But there are limitations as compared to MemCache. I have no much idea as I did not get chance to study completely thier application because it is large with its business requirements and I am to maintain the Database side only. For R&D purpose, I saw SafePeak, a link mentioned in sqlservercentral also and I installed on my development (local machine) for testing on a few test tables to understand it. However, this product is no longer supported as mentioned in their website. So here, I just posted a general question to get opinion from experts to give me suggestion if such thing is possible and how much a reliable product is available. If there is any thing possible we can implement, (I know caching implementation and invalidated rule to be well trained very first) and verify but again if there is any reliable product available right now in market today or I may left this R&D task.
To another expert,
We have successfully did some R&D and created a test environment with HA Grouping with two nodes, One we use for writing operations and other node for read data only. This will load balance on both servers.
I did a small test activity of read and write on separate connections with the help of Listener. This is working fine by testing with profiler, each read request is going to one ready only note and write operations are going to another node.
To implemented this on our application, we need two connectionstrings, both will be using Listener and user we setup for authentication. However, maintaining HA with high transaction log, we are still looking if this can be truncated at what time because the users will be hitting application 24X7.
Shamshad Ali
Shamshad
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply