July 20, 2009 at 10:36 am
I'm looking at installing a 2nd instance of sql 2005 on a cluster. Since I haven't done this before, I started researching how, and what to be careful of.
Searching on google boiled down to either don't do it, or have each instance on it's own separate pair of nodes.
Searching thru microsoft.com reveals pretty much the opposite. I pretty much derive from there that the only things to be aware of is that the drives for the sql need to be separate from the other instance, and the sharing of memory (restrict each instance to an allowable portion of the total memory).
With what I've read at microsoft.com, I'm leaning towards setting up an active-active cluster on two nodes, where each of the nodes is running one of the sql instances.
Does anyone have any experience in setting up a multiple-instance of sql 2005 on a cluster? How did you do it? Is there anything else to be aware of in the installation and/or configurations?
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 20, 2009 at 12:02 pm
I too am looking for some good guidance on this..
CEWII
July 20, 2009 at 3:32 pm
Hi Wayne,
In our cluster environment we are currently having around 5 instances running on a single cluster node. You can always have multiple instances on cluster node if you don't have enough harware and other resources. But multiples instances on a single cluster node is not usually recommended. Each SQL Server instance has its own SQL Server resource group which contains dedicated network name, I.P Address, Disk resources and SQL Server services.
When you have multiple instances on a single cluster node, there will be a huge competition among the SQL Server instances for the system resources. In that case you must setup the maximum memory for each SQL Server instance. Usually its recommended to leave 2-3 GB of the total available memory for the O.S and the rest to be shared among the instances. Also, its not advisable to run any other applications on that cluster.
Here is a very good article on the memeory management if you have multiple instances.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299068_mem1,00.html
Another major problem with this multiple instances approach is that suppose if you have 5 instances on node A and 5 instances on node B and if node A fails, then all the 10 instance will be running on a single node in which case you will have a huge and drastic performance hit. So you should always design a cluster with failover scenario in mind. i.e if you have 32 GB of RAM on node A, then it should be shared amoung the 10 cluster instances and the O.S in case of a failover. Clearly, this is a very poor design. I am telling this only to give you a picture of the process.
The installation is pretty much similar to installing multiple instances on a stand alone system. But during the installation, you should have an unique and dedicated Virtual name(Network name), I.P Address and disk resources for each of the SQL Server instances. Also, all these resources will be part of the resource group and at any time only one SQL Server instance can own that resource group i.e those resources in the resource group cannot be shared among multiple instances.
Thank You,
Best Regards,
SQL Buddy.
July 21, 2009 at 5:22 am
Hi SqlBuddy,
But multiples instances on a single cluster node is not usually recommended.
Do you happen to have any microsoft (preferred, or any other) links / white papers saying this? and why it's not recommended?
As far as supporting all instances on one node... these are all "pre-prod" databases, not heavily hit. I'm only talking about 2 total instances. I don't think that this will really be an issue.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 21, 2009 at 8:52 am
Hi Wayne,
Here is an article which says that it's not recommended to have more than one instance installed on a single cluster node.
http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
Since you are saying that you have just 2 instances and that too being pre-prod databases, I stongly believe that you should not have any problems or issues with that implementation. To let you know, we have both Development and Production clusters with multiple instances sitting on each cluster node.
Thank You,
Best Regards,
SQLBuddy.
July 22, 2009 at 6:23 am
The Books Online have a lot of information on this topic.
We've been running ACTIVE/ACTIVE clustering for years and even have a cluster with SQL2005 and SQL2000. It's totally supported by Microsoft.
When running an ACTIVE/ACTIVE cluster what you have to always keep in mind during the design is one day both INSTANCES may indeed run on one node because of a failover, so you must design the cluster and configure SQL Server with that in mind. Will you have enough resources to run on one node? You may need to purchase more RAM to meet the needs of both instances.
As mentioned above, you'll want to set MAX_SERVER_MEMORY to half of total memory for each instance, therefore when failover occurs you're not running into memory pressure between the two instances.
July 23, 2009 at 7:51 am
sqlbuddy123 (7/21/2009)
Hi Wayne,Here is an article which says that it's not recommended to have more than one instance installed on a single cluster node.
http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
Since you are saying that you have just 2 instances and that too being pre-prod databases, I stongly believe that you should not have any problems or issues with that implementation. To let you know, we have both Development and Production clusters with multiple instances sitting on each cluster node.
Thank You,
Best Regards,
SQLBuddy.
I don't agree with everything it says in that article, especially since it doesn't make the case for why it recommends what it does. What I do agree with is that you should do plenty of planning.... and if you come to the conclusion that your resources are sufficient to support your desired configuration, then go ahead and implement it.
John
July 23, 2009 at 9:26 am
We're running 2 instances on each node in a cluster (and I wish we weren't). Between trying to balance the disk space (no SAN either) and the memory, its a PITA. They won't give us enough memory to run things the way they should be, so I'm trying to write some procs that will rebalance memory on the fly in the event of a failover. The main thing I need to figure out is how to get the physical server that the instance is running on. So far, all I can get is the virtual server name, which doesn't tell me what I need. Anybody know how to get the actual physical server name? If I can get that, I'm pretty sure I can utilize more than half the memory on each node.
July 23, 2009 at 9:59 am
Is this a two node cluster? Just to understand, you're running 4 instances, two per node. If that's the case then you probably would have each server instance at 25% memory. You would really need some resources in this case, as you said.
How are you planning to rebalance on they fly? You're going to run "sp_configure" after the failover to set "max_server_memory"?
"select * from sys.dm_os_cluster_nodes" will get you the node names but not if the instance is running on it.
July 23, 2009 at 11:08 am
Steve (7/23/2009)
Is this a two node cluster? Just to understand, you're running 4 instances, two per node. If that's the case then you probably would have each server instance at 25% memory. You would really need some resources in this case, as you said.How are you planning to rebalance on they fly? You're going to run "sp_configure" after the failover to set "max_server_memory"?
"select * from sys.dm_os_cluster_nodes" will get you the node names but not if the instance is running on it.
That's the config.
That was my thought on failover anyway. I figured I can set a proc to run on startup that will check where the instance is vs. where it should be and query the other instances as to where they are and rebalance accordingly, probably with a table to tell it what to set itself to.
July 24, 2009 at 1:36 am
Look up the SERVERPROPERTY function in Books Online. One of the options tells you which physical node you're running on.
John
August 11, 2009 at 9:32 am
If anybody's interested, I figured out how to rebalance the memory on a cluster. I can use almost all the memory on each node, and still handle a failover. I've got a master table that tells each instance where it should be running and how much memory it can use. When a failover occurs, each instance checks the table, and if any instance is not running on its "home" node, all instances will rebalance themselves so that a single node can run all of them. Once all instances are back on their home node, they will all go back to full memory utilization.
August 11, 2009 at 9:47 am
I'm curious. Please post.
August 11, 2009 at 10:16 am
Hi Mdevore,
We are eager to listen!
Whenever you share your knowledge with others, your knowledge will increase by manifold.
Thats really a great virtue! Keep it up!
Yours
SQLBuddy
August 11, 2009 at 10:30 am
This takes one table in master, two stored procedures, and one job that is set to execute on startup and calls the first stored procedure ([master].[dbo].[auto_MemoryReconfigure]). I set the default memory to use all but 3 gb on each node. That allows enough for the OS, and for a failover to start. Once the first failover is detected, the memory on all instances gets rebalanced to use in total only what each server has. You will also need linked servers set up on each instance in the cluster to all other instances on the cluster. This written for a 2-node cluster, but could be used as a framework for more than that. If you've got a memory-constrained cluster like I do, it REALLY helps out.
Table Definition:
CREATE TABLE [dbo].[ClusterMemory](
[Instance] [varchar](50) NOT NULL,
[DefaultNode] [varchar](50) NOT NULL,
[DefaultMemory] [int] NOT NULL,
[FailoverMemory] [int] NOT NULL,
[Failed] [bit] NOT NULL,
CONSTRAINT [PK_ClusterMemory] PRIMARY KEY CLUSTERED
(
[Instance] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[ClusterMemory] ADD CONSTRAINT [DF_ClusterMemory_Failed] DEFAULT ((0)) FOR [Failed]
This procedure should be automatically executed at startup, either automatically (I couldn't get it to work right) or by a job that executes at Agent startup.
ALTER proc [dbo].[auto_MemoryReconfigure]
as
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
/****************************************************************************/
/* Procedure to reconfigure memory on a clustered instance. */
/* Procedure will automatically execute at startup by a job. */
/* Expects: No Parameters */
/* Requires table master.dbo.ClusterMemory */
/* Calls stored procedure master.dbo.MemoryReconfigure */
/* */
/* Written by: Murray DeVore */
/* Date Written:07/29/2009 */
/* Revision Log */
/* Revised By Date Comments */
/* ------------- ---------- --------------------------------------------*/
/****************************************************************************/
declare @InstanceName as nvarchar(128)
declare @ServerName as nvarchar(128)
declare @DefaultNode as nvarchar(128)
declare @MemoryToUse as int
declare @MemoryInUse as int
set @InstanceName = (select @@SERVERNAME)
set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))
set @DefaultNode = (select DefaultNode from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)
set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')
/*Check to see if instance is running where it is supposed to and set memory accordingly*/
IF @DefaultNode = @ServerName
BEGIN
set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)
UPDATE [].master.dbo.ClusterMemory SET Failed = 'False' WHERE Instance = @InstanceName
print 'Default Node'
END
ELSE
BEGIN
set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)
UPDATE [].master.dbo.ClusterMemory SET Failed = 'True' WHERE Instance = @InstanceName
print 'Fail Node'
END
IF @MemoryInUse @MemoryToUse
BEGIN
exec sp_configure 'max server memory (MB)',@MemoryToUse
RECONFIGURE
print @InstanceName+' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)
END
ELSE print @InstanceName+' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)
/*Send email/page alert */
declare @sub as varchar(100)
declare @msg as varchar(1000)
declare @to as varchar(100)
set @msg = 'Instance '+@InstanceName+' has restarted and is now running on node '+@ServerName+'. Its default node is '+@DefaultNode+'.'
SET @to = (select pager_address from msdb..sysoperators where name = 'SQL Server DBA')
--set @to = (select email_address from msdb..sysoperators where name = 'SQL Server DBA')
set @sub = 'WARNING: Instance '+@InstanceName+' has restarted.'
exec msdb.dbo.sp_send_dbmail @recipients = @to
, @subject = @sub
, @body = @msg
/*Tell the other instances on the cluster to check themselves*/
DECLARE @sql varchar(1000)
DECLARE curInstance CURSOR
READ_ONLY
FOR select 'EXEC '+QUOTENAME(Instance)+'.master.dbo.MemoryReconfigure' from [].master.dbo.ClusterMemory where Instance @InstanceName
OPEN curInstance
FETCH NEXT FROM curInstance INTO @sql
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
exec (@sql)
END
FETCH NEXT FROM curInstance INTO @sql
END
CLOSE curInstance
DEALLOCATE curInstance
This stored proc is called by the first one, only on each of the other instances on the cluster.
ALTER proc [dbo].[MemoryReconfigure]
as
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
/****************************************************************************/
/* Procedure to reconfigure memory on a clustered instance. */
/* Procedure is called remotely by auto_MemoryReconfigure */
/* Expects: No Parameters */
/* Requires table master.dbo.ClusterMemory */
/* */
/* Written by: Murray DeVore */
/* Date Written:07/29/2009 */
/* Revision Log */
/* Revised By Date Comments */
/* ------------- ---------- --------------------------------------------*/
/****************************************************************************/
declare @InstanceName as nvarchar(128)
declare @ServerName as nvarchar(128)
declare @DefaultNode as nvarchar(128)
declare @MemoryToUse as int
declare @MemoryInUse as int
declare @FailedCount as int
set @InstanceName = (select @@SERVERNAME)
set @ServerName = (select cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar))
set @MemoryInUse = (select cast(value_in_use as int) from sys.configurations where name = 'max server memory (MB)')
set @FailedCount = (select count(Failed) from [].master.dbo.ClusterMemory WHERE Failed = 'True')
IF @FailedCount = 0
/* Make sure memory is set to default allocation */
BEGIN
set @MemoryToUse = (select DefaultMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)
END
ELSE
/* Failover condition, reduced memory allocation */
BEGIN
set @MemoryToUse = (select FailoverMemory from [].master.dbo.ClusterMemory WHERE Instance = @InstanceName)
END
IF @MemoryInUse @MemoryToUse
BEGIN
exec sp_configure 'max server memory (MB)',@MemoryToUse
RECONFIGURE
print @InstanceName+ ' RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)
END
ELSE print @InstanceName+ ' NOT RECONFIGURED. Memory --> '+cast(@MemoryTouse as varchar)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply