SSIS in cluster setup

Question

Post reply

SSIS in cluster setup

Viewing 15 posts - 16 through 30 (of 39 total)

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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 1

Anthony,

You are amazing. Thanks much for the answers.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

guys,

Doubts keep creeping up at each step:-(

My cluster is default, not named. since, i connect to it using system name and not as systemname\instancename. so my assumption is that it is a default installed cluster.

"ssis connects to default intance in which the ssis service is running"... what this mean in terms of 2 node cluster?

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Still doesnt matter as it installs the xml config file as .\instance where . is the actual local machine name not the virtual sql name, so it still wont connect and you would have to modify the xml file.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 4

My xml file has the default setup like

....

this means that now it connects to default (active) server and on failover the passive becomes active and it should be the default instance now. why it is not like that?

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

. takes in the computer name not the virtual SQL computer name.

So in a 2 node setup take the following example (Node1, Node2, SQLName)

If SQLName is running on Node1 if you try to connect to . it tries to connect to a SQL server called Node1 not SQLName, same if it fails over it now looks for . as Node2 not SQLName.

Now in a standalone setup where the SQLName is the same as the Node name your ok, but in a cluster its not the same.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 6

So, it connects to the node whichver is up at the time. If that holds true, then after failover, <.> looks for Node2, which is up now and hence it should connect to that .. isnt it? Why the need to include both nodes in xml for managing ssis?

Questions would seem lame, but my head starting to spin while grasping this

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

You have a virtual name specified when setting up a SQL instance on a cluster, this may be a named instance it maybe the default instance, but it is that name which you use to connect to SQL as, you dont use the hostname.

If I went to my SQL Cluster now and tried to connect to SQL using machine name CH4N1 it will tell me to go away as no SQL server exists under that name, but it I connect to CH4SQL1 then it connect as that is the virtual cluster name for the SQL instance.

The XML file references . which is the same as me going to SSMS saying I want to connect as CH4N1, it doesnt exist but I change the XML file to connect to CH4SQL1 instead of . and it works.

You dont need to add both nodes to the XML file, just change the . to the Virtual SQL name on both nodes.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 8

Okay. Now, what i understand is, in 2 node cluster, i need to include both node names in the xml file of both nodes....so after failover, ssis can be managed from node2. If i have

NODE1NAME is SQLname of node1

NODE2NAME is SQLname of node2

TWONODECLUSTER is the cluster name, which i use to connect to that sql in general

My change would look like,

</Folder>

</Folder>

I would make same xml changes to node2 xml.

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

No, you just need to put the TWONODECLUSTER in place of the . in the XML file on each server,

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

<ServerName>TWONODECLUSTER</ServerName>

</Folder>

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 10

OMG. I need to put the cluster name in <.> in both node xml files..thats it?

But, dougbert link had this,

http://dougbert.com/blog/post/ssis-and-clustering-what-you-should-do-instead.aspx

" let’s take this concept to a cluster. For our cluster, let’s have 4 nodes names Node1, Node2, Node3, and Node4. On this, let’s install 4 instances of SQL in 4 separate resource groups. Let’s use the network names net1, net2, net3, and net4, and let’s install instances InstanceA, InstanceB, InstanceC, and InstanceD on those net names respectively so that the full names of our instances will be net1\InstanceA; net2\InstanceB; net3\InstanceC, and net4\InstanceD. Any of the 4 nodes can host any of the instances in our setup. "

and it goes on to add 4 set of instance names

<Name>InstanceA MSDB</Name>

<ServerName>net1\InstanceA</ServerName>

</Folder>

<Name>InstanceB MSDB</Name>

<ServerName>net2\InstanceB</ServerName>

Ant-Green SSC Guru Points: 113445 More actions · Answer 11

I have a 4 node cluster, with 3 SQL instance, the node names are CH4N1, CH4N2, CH4N3, CH4N4, the SQL instances are called CH4SQL1\INSTANCE01, CH4SQL2\INSTANCE02, CH4SQL3\INSTANCE03, my MsDtsSrvr.ini file looks like this

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

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

<Folder xsi:type="SqlServerFolder">

<Name>Instance01_MSDB</Name>

<ServerName>CH4SQL1\INSTANCE01</ServerName>

</Folder>

<Folder xsi:type="SqlServerFolder">

<Name>Instance02_MSDB</Name>

<ServerName>CH4SQL2\INSTANCE02</ServerName>

</Folder>

<Folder xsi:type="SqlServerFolder">

<Name>Instance03_MSDB</Name>

<ServerName>CH4SQL3\INSTANCE03</ServerName>

</Folder>

<Folder xsi:type="FileSystemFolder">

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

This is the same file on all 4 of the nodes, as you can see it references the SQL names, not the node names.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 12

Virtual name: virtual name and IP address that stays the same no matter what node in a cluster is active.

So my virtual name would be TWONODECLUSTER and i would replace<.> with TWONODECLUSTER in 2 nodes. till this i understood.

"4 node cluster, with 3 SQL instance, the node names are CH4N1, CH4N2, CH4N3, CH4N4, the SQL instances are called CH4SQL1\INSTANCE01, CH4SQL2\INSTANCE02, CH4SQL3\INSTANCE03, my MsDtsSrvr.ini file looks like this "

4 nodes: A, B, C, D

3 sql: CH4SQL1\INSTANCE01 running on A

CH4SQL2\INSTANCE02 running on B

CH4SQL3\INSTANCE03 running on C

This cluster should have a Virtual Name. So, why the virutal name is NOT given in xml?

Ant-Green SSC Guru Points: 113445 More actions · Answer 13

You dont provide the XML file with the cluster name you give it the virual sql cluster name which is the name that is used to connect to SQL when it fails between nodes.

What name do you use when you connect to SQL?

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 14

Cluster name: WINCLUSTER (which i use to connect in cluster admin)

Virtual Name: TWONODECLUSTER (which i give in SSMS to connect to sql.. doesnt matter which node is up)

This TWONODECLUSTER has 2 nodes setup NODE1(systemname) and NODE2(systemname). SQL is not named instance.

Ant-Green SSC Guru Points: 113445 More actions · Answer 15

So as you connect to SQL via SSMS as TWONODECLUSTER you put in TWONODECLUSTER in the XML file.

Remember your connecting to MSDB so you need to tell it which SQL servers MSDB you want to connect to, so use whatever you use to connect to SQL as the replacement for the <.>