Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS in cluster Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:12 PM
Points: 214, Visits: 929


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.
Post #1473148
Posted Friday, July 12, 2013 11:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,847, Visits: 12,579
use the virtual sql server name

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

Post #1473156
Posted Friday, July 12, 2013 11:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:12 PM
Points: 214, Visits: 929
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?
Post #1473179
Posted Friday, July 12, 2013 12:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,847, Visits: 12,579
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


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

Post #1473201
Posted Sunday, July 14, 2013 2:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:12 PM
Points: 214, Visits: 929
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.
Post #1473386
Posted Monday, July 15, 2013 5:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,847, Visits: 12,579
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.


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

Post #1473588
Posted Monday, July 15, 2013 7:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:38 PM
Points: 209, Visits: 637
I could be mistaken here or just confused . 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
Post #1473652
Posted Monday, July 15, 2013 8:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 5,847, Visits: 12,579
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.


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

Post #1473679
Posted Monday, July 15, 2013 11:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:12 PM
Points: 214, Visits: 929
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.
Post #1473802
Posted Monday, July 15, 2013 11:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:38 PM
Points: 209, Visits: 637
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
Post #1473821
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse