July 12, 2013 at 10:53 am
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.
July 12, 2013 at 11:20 am
use the virtual sql server name
---------------------------------------------------------------------
July 12, 2013 at 11:49 am
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?
July 12, 2013 at 12:09 pm
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
---------------------------------------------------------------------
July 14, 2013 at 2:19 pm
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.
July 15, 2013 at 5:24 am
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.
---------------------------------------------------------------------
July 15, 2013 at 7:46 am
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
DHeath
July 15, 2013 at 8:22 am
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.
---------------------------------------------------------------------
July 15, 2013 at 11:21 am
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.
July 15, 2013 at 11:57 am
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
DHeath
July 15, 2013 at 3:29 pm
my feeling at the moment is we have a misunderstanding of clustering rather than a cluster issue, seems a bit early to start advocating rebuilding it. 🙂
SQLAgent jobs are held in msdb and there is only one msdb per instance. You seem fixated on the nodes, is this a failover cluster with just one instance, or do you have two instances, one active on each node? (An active\active cluster)
---------------------------------------------------------------------
July 15, 2013 at 4:15 pm
george sibbald (7/15/2013)
@DHeathSQLAgent jobs are held in msdb and there is only one msdb per instance. You seem fixated on the nodes, is this a failover cluster with just one instance, or do you have two instances, one active on each node? (An active\active cluster)
This is a fail over cluster with two nodes - with each node having two instances of sql server. Its Active/Passive.
July 16, 2013 at 5:18 am
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.
---------------------------------------------------------------------
July 16, 2013 at 9:05 am
Much much clearer now that its known this is a active/active cluster. Had me wondering there for a few minutes but wanted to make sure it was verified and i am sure George is your man with the answers here 🙂
DHeath
DHeath
July 16, 2013 at 9:16 am
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.
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.
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.
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?
Thank You for your time-appreciate it.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply