SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Availability Groups and DR (2012)
 Best practise - Failover SQL 2012/2014
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elcake
Starting Member

3 Posts

Posted - 07/02/2014 :  07:09:57  Show Profile  Reply with Quote
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 situation
2 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.


Edited by - elcake on 07/03/2014 16:16:51

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 07/02/2014 :  12:40:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

elcake
Starting Member

3 Posts

Posted - 07/03/2014 :  16:23:37  Show Profile  Reply with Quote
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,
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 07/03/2014 :  16:36:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

elcake
Starting Member

3 Posts

Posted - 07/04/2014 :  01:16:08  Show Profile  Reply with Quote
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.


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 07/05/2014 :  16:51:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000