question

soulkeeper avatar image
soulkeeper asked

Secondary SQL Server plan

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
sql server 2012high-availabilitysecondary
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
It sounds like you need to look at "SQL Server AlwaysOn Availability Groups". Although be warned - you might not have the right edition of SQL Server to do that properly. See [MSDN overview of AO AGs][1]. If you don't have the right version, then your options are more limited - you could do something like [logshipping or mirroring][2], or even [replication][3], but those would require intervention to complete a failover. What these things ultimately boil down to, though, is how much data can you afford to lose (Recovery Point Objective, RPO), how long can you keep running without your database being available (Recovery Time Objective, RTO), and how deep are your (corporate) pockets? Oh, and the amount of data we're actually talking about... [1]: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx [2]: https://msdn.microsoft.com/en-us/library/ms190202(v=sql.110).aspx [3]: https://msdn.microsoft.com/en-us/library/ms151198(v=sql.110).aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well i have SQL Server 2012 enterprise so it will do . as for the recovery time i need it to be asap , my DB is not big around 1 gb maximum .
1 Like 1 ·
"asap" is not an actionable RTO or RPO. It makes it sound like the company has specified zero downtime, in which case they'll need to write a cheque with several commas in the amount and bring in an appropriately experienced team to set it up and maintain it as well as update the application so it can follow the failover without interruption.
1 Like 1 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.