This is kind of the part 2 of Replicating a SQL Server database & Optimization that I started earlier this week, but I believe it owes to be a different questions now that I have a clear idea of what we are trying to achieve.
Currently we have one box running our application and database server. We about to increase our capacity to two servers. One on DC1 and the other on DC2. Both will have our application server and database running.
What we need:
Not have boxes sitting around doing nothing waiting for a failure.
Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails
Sensible fail-over times (e.g. a five minute fail over is not sustainable)
We can sustain some (sensible) permanent data loss in certain areas of our application. Anything up to a few seconds is fine. If its temporary loss e.g. pointing to an db image valid ten minutes ago is ok as long as the data recorded within those ten minutes make their way to their database at some point.
- Point app server to local db. If local db is down then point to remote db. e.g. App Server A points to DB A. App Server B points to DB B. If DB A goes offline then App Server A points to DB B. If Box A goes offline then Box B continues as it is.
Reasons we like this: Does really well on criteria A, B and C
- Application servers are pointed to one combined DB. This is the combination of DB in box A and DB in box B. No sweat if one database fails.
Reasons we like this: Does well on criteria A, B and C
- Disadvantage is that we get Application Server A pointing to box B so increased latency
- Application servers are pointed to one DB, say on box A. So application server A points to DB A and application server B is pointing to DB A. We use database B for read only reporting. On DB A failure applications are automatically switching and point to DB B which is now used for everything, not just reporting.
Reasons we like this: Still does well on criteria A, B and C
What I would like to know is how are the options above implemented with a SQL Server 2012 Enterprise. Mind that the two boxes are on different data centers but IF needed we can change that. Also, if there is a better option that we haven't thought about.
Databases image size is currently in excess of 150GB. There are some very frequently updated tables (the analytics tables are inserted sometimes 50-100 times a second).
Let me know what you think