What you are looking to do is basically have two instances running that you can redirect clients between. There's no need to do this within Failover clustering as this won't help you any. Doing this simply requires that you setup two standalone instances of SQL Server, each on a separate Windows server. Then setup a CNAME in DNS and use that to point people to the correct SQL Instance.
Now for some of the problems.
- The data won't be in sync between the two instances.
- DNS takes time to replicate and update on the client machine's local cache, so you'll end up with users connected to both systems.
A better solution would be to take a snapshot of the database before doing the schema change release. If the release is successful delete the snapshot. If the release fails and you have to rollback you'll need to restore the database from the snapshot. This will be pretty quick, but there will be an outage while the rollback happens.
What you are looking for basically won't work.
You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):
Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):
CREATE PROCEDURE dbo.OptimizeInstanceMemory
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@thisNode NVARCHAR(255) = CONVERT(NVARCHAR(255),
SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
@otherNode NVARCHAR(255),
@optimalMemory INT = 12288, -- 12 GB
@sql NVARCHAR(MAX);
SET @sql = N'SELECT @OtherNode = CONVERT(NVARCHAR(255),
SERVERPROPERTY(N''ComputerNamePhysicalNetBIOS''));';
EXEC [SERVER\INSTANCE].master..sp_executesql @sql,
N'@OtherNode NVARCHAR(255) OUTPUT', @OtherNode OUTPUT;
IF @thisNode = @otherNode
BEGIN -- we're on the same node, let's make everyone happy
SET @optimalMemory = 6144;
END
SET @sql = N'EXEC sp_configure N''max server memory'', @om;
RECONFIGURE WITH OVERRIDE;';
EXEC master..sp_executesql @sql, N'@om INT', @optimalMemory;
EXEC [SERVER\INSTANCE].master..sp_executesql @sql, N'@om INT', @optimalMemory;
END
GO
EXEC [master].dbo.sp_procoption
N'dbo.OptimizeInstanceMemory', 'startup', 'true';
Of course create it again on the other instance, swapping the linked server name used.
This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.
Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.
You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.
Best Answer
Yes you need to have a separate set of disks for the new instance.
That is not a bad idea, lot of people do and we call it as multi-instance cluster. Both the instances would be on same node in your case. They can also be on different nodes but you need to look at licensing part then.
In your scenario you have 2 instances on same node, this is fine since 2nd node is purely passive. BUT if a failover happens for "an" instance on 2nd node which is not licensed then you would have an instance running both on 2nd node and 1st node, this is a license violation if you run this scenario for long time, unless you immediately move it back such that both instances are on same node. The term "immediately" here means as soon as possible, MS is not going to charge you if let us say for hour you run 2 different instances on 2 different nodes.
PS: I am not a licensing expert but you should get this verified from local licensing expert of your region.