Configuring 2005 to run SSIS packages containing DTS packages on a cluster
-
We just ran through some fun and games with this, ending [?] with a MS support call. I wrote the following summary for the company, and I figure that posting it here may help someone else some day. (Add a reply if it does!)
Philip
Recap:
- A manual failover was issued to the cluster last night, switching the active server from nodeA to nodeB. Routine operation, nothing much wrong with that.
- The early AM dataload failed, for reasons that were not at all clear
- We identified supplementary issues without reaching any kind of understanding, and so opened a case with Microsoft tech support
- They identified the issue, tested some things on their side (to ensure we could do the same mid-day on the Production system without downtime), and then walked us through applying them.
So what was it? Paraphrasing what I learned from the MS tech guy:
When installing SQL Server 2005 on a cluster, only the installation components for the database components and the analysis services components are cluster aware. That means that, when installing, these components will be installed on all nodes of the cluster.
All other SQL Server 2005 components' installation routines are not cluster aware. That means that when you install them on a cluster, they will only be installed on the node you are actually running the installation on (which will always be the active node). So if you want Notification Services, Reporting Services, Integration Services, or the Workstation Components installed on a cluster, you will have to install those components directly on each node of the cluster.
We stumbled across this when we upgraded the Production server, and we did manually install IS on the "other" node. (I'm not sure we were entirely clear why, or what was really going on.) Once we were done, the SSIS packages (we tested with the one we use for backing up our databases) would run when either node was active.
Well, actually it turns out that the "Backup Databases" package was the only one that would run on the "off" node. All of our other SSIS packages are wrappers around our old SQL 2000 DTS packages. For these kinds of packages to run on SQL Server 2005, the "SQL Server 2000 Designer Components" (an extra Microsoft backwards-compatibility download) must be installed on the server, ALONG WITH the Workstation components--and specifically, SQL Server Business Intelligence Development Studio. We had IS installed, we had the 2000 Designer Components installed... but we didn't have the workstation components installed on the cluster's "off" node, so the SSIS packages with embedded DTS components couldn't run.
We have installed the Workstation components on the "off" node, and the SSIS/DTS packages are up and running again. This problem will not recur.
Disclaimer: the above is my understanding and interpretation of the situation. While I believe it to be completely accurate, it may well not be comprehensive--there could be other details, issues, or related minutiae that will come back and haunt us later.
-
as you will have noticed, SSIS is not cluster aware. What I mean is SSIS is not member of a cluster group, unless you install it yourself.
This means, you'll have to install SSIS and the needed components (dts) yourself at both nodes.
Brian did a nice show on demonstrating how to cluster SSIS.
(http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
Other resources are :
Problem with SSIS on a cluster
Installing SQL Server Integration Services
Kirk Haselden : Making SSIS a Clustered Service
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yeah, I'd forgotten we had an ad hoc "Hit Technet find out how to cluster SSIS right now" session at 3am. Tricky how the mind works...
I also have a quick update to my first post:
I got a clarification from the MS tech guy. It appears that you only need the Workstation Components present to succesfully execute such an SSIS package--the SQL 2000 Designer Tools are only required if you want to open and review it. But they're small, and it definitely doesn't hurt to be able to open and review a package.
And again, while I'm pretty sure it's accurate, I do not claim this is the full and complete storey--but it's good enough for the time being. Now I have to go find out why all our upgraded master databases are set to a compatbility level of 80...
Philip
-
..Now I have to go find out why all our upgraded master databases are set to a compatbility level of 80...
If you've upgraded, the compatibility level has not been changed. (not even for system databases !)
You'll have to change that yourself !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
(a) ...but system databases msdb and model did get upgraded. Why?
(b) We manually upgraded our own databases, of course
(c) Since we're upgrading "our" databases, why shouldn't Microsoft upgrade "their" databases? Perhaps more importantly, why was there no mention anywhere of this? Does it even really matter--can master run as 80, or is that setting just ignored? We upgraded to have access to all the cool wifty 2005 features, so it's very disconcerting to stumble across this implication that said features are auto-disabled to a greater or lesser extent. (I mean, it's the master database that's not 2005 compatible, so what effects "carry over" to the rest of the instance?)
Parts of SQL 2005, I like. Parts, I don't like. It's a bit neck-and-neck just now...
Philip
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply