Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elcake
Starting Member
3 Posts |
 Posted - 2014-07-02 : 07:09:57
|
Good Morning,I am currently looking for a good solution to set up several "AlwaysOn" environments with SQL 2012 or SQL 2014.We currently have the following situation2 virtual machines with Microsoft SQL server. Each server with a named instance and without any fail over or clustering. So pretty simple and boring.So we have 2 different big applications that are running with microsoft SQL.Now, we are planning a new set up for the SQL servers. We want to add the "AlwaysOn" feature to this setup. but we don't know how we should do it.If we keep the setup with 2 servers and 2 named instances. Can we create an "AlwaysOn" between these Instances?What we are planning to do:Server A has 2 named instances (For application A and application B). Instance A is active and Instance B passive.Server B has 2 named instances (For application A and application B). Instance A is passive and Instance B is active.Users using Application A will connect to Server A. Users using Application B will connect to Server B.If one server will fall out, the plan is that all the users will then use the second server. Until the first server is back up running agian.Question:- Is it possible to do this kind of setup with microsoft SQL ?- What is the best practise in this case?Thoughts behind this setup:If we keep both instances on 1 Server and use the second server as failover, only 1 Server will be used most of the time. And the second server will be idle and wait for a failover.Here is a little vizualisation of the setup that we would like to set up. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 12:40:49
|
| Your picture looks like a traditional failover cluster rather than an AlwaysOn cluster. With AlwaysOn Availability Groups, you don't have the shared storage. It basically mirrors the databases between the standalone instances.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
elcake
Starting Member
3 Posts |
Posted - 2014-07-03 : 16:23:37
|
| Good evening,KK. I thought the shared disk would be a new feature of the so called "AlwaysOn" concept of sql 2012. In earlier versions there was only the possibility for log shipping.But never mind. I´m quite new to this topic, that´s why im looking for some good advice on this forum.I changed the topic to "Failover SQL".Anyway, my actually question was if it would be possible to create such a structure with microsoft sql. So the hardware of both servers will be used but you still have a failover in case that a server is failing.And perhaps someone with more experience has a better idea how we should build our sql structure.Best regards, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-03 : 16:36:35
|
| Yes what you have proposed is possible. It is called an active/active SQL Server Failover Cluster. Generally only active/passive is recommended, but we've used active/active plenty. Active/passive is preferred because if you lose a server, then you don't have 2 SQL instances running on the same host. If your hardware can support the load or if you have a plan B where you cutover one of the SQL instances to a DR site, then I don't see an issue.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
elcake
Starting Member
3 Posts |
Posted - 2014-07-04 : 01:16:08
|
| Great. Sounds good.Does SQL still have a problem with multiple instances in the newer versions?Probably every source recommends to run only one instance on the sql server because of hardware issues that can occur.But I can only imagine that there will be a problem with the memory if you don't set a maximum memory for each instance,since SQL is using as much memory as possible.Are there other precautions that I should take when 2 instances are running on 1 server?The cpu and disk usage should be no problem at all.Besides, the failover is just a security mesure if something goes horribly wrong. So the system will still be running, even if it would be a little bit slower. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-05 : 16:51:44
|
quote:
Does SQL still have a problem with multiple instances in the newer versions?
No, but it would depend on the hardware/resources like you mentioned. Yes do set a max memory value for each and ensure you've left some for the OS and other processes such as tape backup.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Best practise - Failover SQL 2012/2014