My question is wrt to SQL Server 2012 rather than 2008, but there does not seem to be a SQL Server 2012 HA sub-forum, hence asking this question here.
I am a programmer, but not a database person. I have used databases just like programmers do but I don't have anything more than a basic understanding of DB administration & configuration. We are going to be hiring a Database Expert but I am currently doing the groundwork to understand enough to ask the right questions while hiring one.
This is the requirement
One Data Center (DC) & One Disaster Recovery (DR) Site
Each site has 2 servers with SQL Server installed - so total of 4 SQL Servers.
The application would be interacting with the first SQL Server Instance in the DC. Only it crashes or something goes wrong, the 2nd one SQL Server at the DC would be needed. But to avoid down time the application should switch over to the 2nd one at the DC automatically. The application does not have the capability to do this - so this has to be achieved externally.
If the whole DC shuts down, the DR would be used - but this would be a manual step. But again in the DR site, switchover between the 1st SQL Server and 2nd should be automatic if the first one in the DR fails.
- The data in the first SQL Server in the DC should be automatically be in sync with the 2nd one. And likewise, the DR site SQL Servers should be in sync with the DC ones. How is this achieved. I have read about replication and also mirroring. I am not able to understand the difference between the two. Which feature would I need?
If I need Replication, it doesn't look like Merge Replication or Snapshot Replication is enough. I would probably need Transactional Replication and/or Peer-To-Peer Transaction Replication - I am still not able to figure out which of the 2 I would need.
If I need Mirroring - I read articles about mirroring publishing databases, distribution databases and other stuff. I really don't understand what are these different kinds of databases.
- Assuming all the data is in sync, I think that won't be enough, right - the application does not have the capability to switch from first to 2nd DB in case the first is down. I don't think even something like Microsoft NLB would help here - because NLB would switch only if the OS itself is down, not if just the SQL Server was down. Am I right here? So I would need something like OS clustering of SQL Server here, right?
About the DR site - I would essentially be replicating the same setup I have in the DC, right - just that the application won't be running. Or is there a better or easier way to do it?
And about software requirements - would SQL Server Standard Edition & Windows Server Version which supports Clustering. Or would I require any higher SKUs of SQL Server and Windows Server?
Also, my application is a Java application connecting to the Database using JDBC.