Hi I have MS SQL Server 2012 hosted in Win 2012 r2 and running a database . our server got now a second domain controller in order to stay online in case of server 1 failure . Since im really noob to SQL Server i was wondering is it possible to install SQL Server on the second server and make it sync or work as a split brain server so that if server 1 is down 2 can take over and db users wont feel any change ? is there a strategy for that or someone who will be kind enough to explain me in details how to achieve that ? Regards
If you decide to use SQL Server AlwaysOn Availability Groups (AG), I would strongly recommend getting some external help when setting things up. Getting things up and running is not that hard (well.. even that's not entirely true, there are many details you need to get right Before getting it to work), but there are more things to monitor when you implement high availability. One example being that the cluster functionality won't automatically give you a phone call and tell you your primary node has failed over to the secondary, or that the secondary node has a problem. Don't hire a consultant to set it up for you. Hire a consultant to hold your hand and talk you through setting things up yourself the first few times (I'm assuming you'll both need to and want to practice setting these things up a few times before doing it in your production environment), and to make sure you don't end up with a false sense of security. Oh, and by the way - you should perhaps also include AlwaysOn Failover Cluster Instance (FCI) as an option. It would mean sharing disks between the nodes in the cluster, whereas AG is a "share nothing"-technology. Last time I was involved in such decisions, we sat down for an hour and a half with a DBA-consultant. My sketched plan was to implement AG but we ended up implementing mirroring instead, because it is a lot simpler to setup and, perhaps more important, easier to maintain.