SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS in cluster


SSIS in cluster

Author
Message
peacesells
peacesells
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 1492
I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these nodes have two sql server databases that I am interested in. I need to deploy SSIS packages to this cluster environment using XML configuration and file system deployment. Now in the config file i am not quite sure which node names(server names) i should use because the nodes name should switch when a failure occurs?

Please advise.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24438 Visits: 13698
use the virtual sql server name

---------------------------------------------------------------------
peacesells
peacesells
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 1492
george sibbald (7/12/2013)
use the virtual sql server name


Thanks George.

When you say virtual sql server Name - is that the cluster name or would that be names of the nodes. Where would ssis packages reside?
Also I read in an article or two that you need to modify MsDtsSrvr.ini.xml file- is that necessary?
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24438 Visits: 13698
the name of the SQL service in the cluster, i.e. the name you use to connect to the database engine.

The packages are normally imported to msdb, thats better for security and backup. You can store them on the filesystem, on storage that is part od the cluster so it fails over with the cluster.

Yes you need to amend the .ini file so the server name points to the cluster name rather than '.' (on both nodes)

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

---------------------------------------------------------------------
peacesells
peacesells
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 1492
george sibbald (7/12/2013)
the name of the SQL service in the cluster, i.e. the name you use to connect to the database engine.

The packages are normally imported to msdb, thats better for security and backup. You can store them on the filesystem, on storage that is part od the cluster so it fails over with the cluster.

Yes you need to amend the .ini file so the server name points to the cluster name rather than '.' (on both nodes)

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



Do i set up sql server agent job on both nodes? I tried to create sql agent job using ssis package store using the cluster name (it does not show up) if I use actual note name then i can see MSDB folder. Is file system deployment the only option.

i read somewhere to copy .ini file to the shared disk and make the changes there instead of changing on both the nodes-is that for different setup ?

Thanks.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24438 Visits: 13698
clusters use shared disks, there is only one copy of all the databases, connect to them using the virtual sql instance name (thats why its called virtual, it sits above the physical nodes)

Have you updated the .ini file to the virtual name? You will still be able to connect to SSIS using the node name of the live node, but you should be using the virtual name.

Leave the .ini where it is and on each node with exact same settings.

---------------------------------------------------------------------
DHeath
DHeath
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 650
I could be mistaken here or just confused w00t. Not sure how you have separate databases on NodeA and Node B IF this is an Active/Passive Cluster as both nodes should share the same databases and drives. You should have a Node A and Node B and then SQL Server A that ride on those nodes and fail back and forth between them like a pendulum as it fails back and forth(hopefully that makes sense). I have usually found that depending how far along you are in this set up..meaning production or still setting it up,its easier to completely rebuild the cluster than to make changes to the ini files (IMHO). I know my answer is not addressing the SSIS problem but the link that was sent also is very nice and compete about the SSIS in a cluster.

DHeath
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24438 Visits: 13698
DHeath (7/15/2013)
...I have usually found that depending how far along you are in this set up..meaning production or still setting it up,its easier to completely rebuild the cluster than to make changes to the ini files (IMHO)......

DHeath


Really? Its a one line change of a server name in two locations.

---------------------------------------------------------------------
peacesells
peacesells
SSChasing Mays
SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)SSChasing Mays (608 reputation)

Group: General Forum Members
Points: 608 Visits: 1492
george sibbald (7/15/2013)
clusters use shared disks, there is only one copy of all the databases, connect to them using the virtual sql instance name (thats why its called virtual, it sits above the physical nodes)

Have you updated the .ini file to the virtual name? You will still be able to connect to SSIS using the node name of the live node, but you should be using the virtual name.

Leave the .ini where it is and on each node with exact same settings.


I get that databases are shared but what about setting up the sql agent job- do i set up in each instance? Also in the ini file, i understand that you replace

<ServerName>virtual instance name </ServerName>

What about <Name>MSDB</Name> ? I see that some articles leave it as is where as some places have it as <Name>Instance1_MSDB</Name> ??

Thank You.
DHeath
DHeath
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 650
George,

You are correct it is a one line change in 2 locations....but my point was that the cluster appears to me that it is NOT doing everything that Peacesells want or expects therefore from my experience when clusters are troublesome its usually easier to start over unless you are well versed in them and totally understand what the set-up is and how its all configured together. IF the cluster is NOT in production and you have to ability to do a rebuild then do the rebuild with certainity that you are sharing disks and the heartbeat is going ..and ..and ..and.. Good Luck and hope it works out.

DHeath
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search