SSIS in cluster

Question

SSIS in cluster

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

peacesells (7/16/2013)
george sibbald (7/16/2013)
each node with two instances gives you 4 instances, and that is very much active\active, or as I believe Microsoft would officially call it, a multi-instance cluster.
You will have to put the jobs into the msdb relevant to the database the job is for, so just log onto the correct database engine and create the job via SQLAgent for that instance, using the virtual SQL name.
The link I posted shows you how to manage multi instances, its just a case of adding new xml nodes for each msdb and\or file system if you are using the file system to store the packages. NAme them so you can readily identify which is which when you open SSIs via management studio.
George,
Clearly i am not a cluster aware guy- but nonetheless very interesting subject if you can figure your way through.

don't worry, you're not the only one

I read somewhere that sql server agent was cluster aware which interprets to me that it if i need a job running in one of the instances on both nodes- will it suffice to create a job in one of the instances of the job using the virtual name and should that node fail the job is also visible on the same instance on the other node.

correct

For instance I have nodeA with (instanceD,InstanceF) ANd NodeB with (instanceD,InstanceF). Now i only need a sql server agent job running on InstanceD on both nodes. My understanding is that i don't have to create the job on both nodes for those two instances-please correct me.

correct, you only need to create the job once on the virtual instance

Since i am doing a file system deployment (my package would reside in shared disk), i do not see my packages in MSDB-should i be worried?

nope, that would be expected, you would have to physically import the package into msdb (via SSIS) to se it in msdb.

msdb is usually the better place to store packages, but its not a golden rule, you may want to investigate why the file system has been chosen.

You have a 2 instance cluster which is currently active\passive only because both instances are live on the same node. It is perfectly possible for just one instance to failover in which case you would then be active\active. So reaally its clearer to call it muti-instance.

[/quote]

---------------------------------------------------------------------

peacesells SSCrazy Points: 2882 More actions · Answer 2

[/quote]

nope, that would be expected, you would have to physically import the package into msdb (via SSIS) to se it in msdb.

msdb is usually the better place to store packages, but its not a golden rule, you may want to investigate why the file system has been chosen.

You have a 2 instance cluster which is currently active\passive only because both instances are live on the same node. It is perfectly possible for just one instance to failover in which case you would then be active\active. So reaally its clearer to call it muti-instance.

[/quote][/quote]

<?xml version="1.0" encoding="UTF-8"?>

<ServerName>sqlA\instanceA</ServerName>

</Folder> -<Folder xsi:type="SqlServerFolder">

<ServerName>sqlB\InstanceB</ServerName>

</Folder> -<Folder xsi:type="FileSystemFolder">

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

I have two named instances (sqlA\instanceA & sqlB\InstanceB) in both nodes (MachineA and MachineB) and I make the following changes in MsDtsSrvr.ini.xml file in both machines.

My deployment method is file system -storing packages on a shared disk (SAN).I made changes as shown above and restarted SSIS services on both the nodes.

However, when I login to SSIS services from Management studion using the hostname (MachineA AND MachineB ), i see packages under filesystem only in MachineA, the same folder is empty on MachineB. Shouldn't the packages be visible there as well? I see two empty MSDB folder on both machines and if try to expand it - I get the extact error as show in the below link even after making changes in the ini file- what i am missing- thanks.

onclick:if_IFCode(''http://shahfaisalmuhammed.wordpress.com/tag/msdtssrvr-ini-xml/'');

george sibbald SSC Guru Points: 104210 More actions · Answer 3

that link does not expand for me.

MachineB (presumed passive node) will not be able to see the disks when passive so will not able to see the file system. Connect with virtual server name.

---------------------------------------------------------------------