Configure Integration Services on a Cluster

Hi

I am having an active/active cluster setup where there are 2 virtual SQL Servers one with default instance and other with named instance.

1) i need to know how to cluster integration service.

2) how are the integration packages are run, are they run by the integration
services service, or by the SQL Instance?

3) When fail over happens from one node to other will my packages continue to run?

i had setup Integration Services on a Cluster per the following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx.... but

when i do fail over or move the group to another node

... getting an error indicating:
An error occured attempting to read properties for the 'xxx IntegrationServices' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no] ...

I understand that integration service can be clustered only once.....

Should i do the clustering in Integration Services in a different Group
than sql server groups?....but then again i wont be able to acces MSDB...

Please provide advice on the matter,,,,,i am desparately waiting for a solution

Thanks in advance....

I

1 - That BOL topic is a good resource and pretty much covers it I think. Of course it doesn't cover you error which is perhaps more on an issue. Some other notes here as well- Cluster SSIS
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ClusterSSIS.html)

2 - Neither. SSIS (like DTS before it) is client side, it is just an object model really. Your packages are run by the process that calls them. So normally you use something like DTEXEC to call them, or the similar SQL Server Agent SISS job step subsystem. (Use CmdExec and DTEXEC even in jobs!). So if calling DTEXEC yourself it runs under you account on your machine. If using a scheduled job and DTEXEC/SSIS Subsystem it runs under the SQL Server Agent service (or proxy account is specified) so in that case it runs on the SQL Server machine that hosts the SQL Agent service. The SSIS service is a bit dumb and is just used to show what is running. Packages call into the service to say they are running, but the service is not the host.

3 - No. If packages are running during failover, then they will not continue to run, restart or do anything really because the SSIS service is not in control. However stopping the SSIS service will cancel a running package.

The error sounds has there is something duplicated, double check that you have followed the instructions you followed.

Which Group you choose is a matter of preference, as discussed in the documentation. The link above gives another reference you may wish to consider.

For my money, I don't see MSDB access as a big deal. I don't think using it as a package store is worth the effort, files are much easier to manage. Logging would be the only consideration, and that would sway me to keep them together. Coupled with the fact that I generally always use Agent to run/host the packages, it makes sense to keep them together for that as well.

|||

Hi

Thanks for the reply

My configuration has two virtual servers server1 and server2 in active /active mode

Server1 (default instance) is on cluster group1 and Server2 (named instance) is on cluster group2 as given below

Group1

Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)

Group2

Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2) SQL Server Agent (SQL2)
SQL Full Text (SQL2)

Both the servers have packages running.

I need to know how to do the clustering.

I tried clustering as per the Microsoft document : http://msdn2.microsoft.com/en-us/library/ms345193.aspx by Configuring Integration Services in the Same Group as SQL Server

then i had to configure it in the two groups (group 1 and Group 2)but cluster groups won't come online on one nodes when i do fail over... was getting an error An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]....

I need to know whether what i did is the correct method ... bcos the two servers were in differnt groups as above, i configured integration service on the two groups as per the document so i had integration service on group 1 as well as integration service on group 2

If i configure it only on a single group will it say group1 then will it be working fine (will group2 ie server2 have integration service running ? and when failover happens packages in both server continue to run ?)

If i go for the other option as per microsoft document that is in a different group than the SQL Server Database Engine

should i go for a third group say group 3 ?..pls tell me how to configure the group ?..wht all resources to be added ...?

Awaiting your reply

Thanks in advance

|||

So now things make sense!

You can only have 1 instance of the SSIS service running per machine. So by adding it to two virtual server groups, you risk trying to run two instances of SSIS on the same machine at the same time, hence the "object exists" or rather "stop mucking about with trying to run two copies of this service, I will not let you."

If running multi-instance cluster, also (previously) known as active/active, then just step back and think about what clustering SSIS will achieve? Since the SSIS service does not support multiple instances on the same machine, and the service is not a true cluster aware service that is installed within the virtual server container, (much the same thing really), then you should not try and cluster it as you will inevitably have clashes.

What does it give you anyway? Storing packages in MSDB or on a shared disk will mean they stay with that instance of SQL, which I'd say is the most important point. Run an instance of the SSIS service on each machine if you want, but don't bother with trying clustering them.

|||

Thanks a lot ............

one more query...

When i do installation of SQL server 2005 should i change the default location of integration services to the shared drive?

In my current I have my packages in C: drive which will not failover to the next node .

How do i go about to make this change...

awaiting your reply

Thanks in advance

|||

You can change the managed file store path after install, which I think would be the best method, and using a shared drive would make some sense.

http://msdn2.microsoft.com/en-us/library/ms137789.aspx

You can actually use any file path, it does not have to be the managed one. I never use it, and find no real need to enumerate packages in SSMS which is about all it gives you.

sqlsql

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
BradOps I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegroups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>> Hello Brad,
>> I understand that you are not able to configure cluster resource for SSIS
>> properly. As I know, SSIS is not cluster-aware, so you'll have to install
>> on all nodes. However, SSIS can be installed within a virtual server on a
>> failover cluster as described here:
>> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
>> That's much the way Analysis Services was done in 2000 -make it a generic
>> resource. It still requires installing the stuff local on each node.
>> Hope this is helpful. Please let's know if you have further questions or
>> concerns.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>>
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
BradOps I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/kn.../06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> [url]http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.[/ur
l]
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegroups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> [url]http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.[/ur
l]
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
Brad
You can only cluster it once. It is not an instanced component so the
service can only exist once on each node with the registry pointing at the
config file on the shared disk. Since you have multiple instances, follow
the instructions for "Configuring Integration Services in a Different Group
than SQL Server" to avoid dependencies on a specific instance being
available. If you store all your packages on the file system rather than
msdb this avoids any depence on any of the instances being available and you
can still share them out using clustered file shares to control access e.g.
Disk I
SSISPackages
SQL1 (Share)
SQL2 (Share)
SQL3 (Share)
SSISLogs
SQL1 (Share)
SQL2 (Share)
SQL3 (Share)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:eNXo%23ac7GHA.140@.TK2MSFTNGP05.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>
|||Would this work in the case of an active/active scenario where there
are 2 virtual SQL Servers, with one running on each of 2 cluster nodes?
When the integration packages are run, are they run by the integration
services service, or by the SQL Instance? In other words, does
Integration Services simply let you design the packages, or is it
running them?
I am finding this concept hard to grasp for some reason. I am used to
DTS in SQL 2000
I have a 2 node cluster with 2 virtual SQL servers, each with 1 named
instance. The Virtual servers can fail back and forth no problem. The
Integration Services is installed on both nodes, but gives an error
when I try and view the contents of the MSDB folder.
Jasper Smith wrote:[vbcol=seagreen]
> You can only cluster it once. It is not an instanced component so the
> service can only exist once on each node with the registry pointing at the
> config file on the shared disk. Since you have multiple instances, follow
> the instructions for "Configuring Integration Services in a Different Group
> than SQL Server" to avoid dependencies on a specific instance being
> available. If you store all your packages on the file system rather than
> msdb this avoids any depence on any of the instances being available and you
> can still share them out using clustered file shares to control access e.g.
> Disk I
> SSISPackages
> SQL1 (Share)
> SQL2 (Share)
> SQL3 (Share)
> SSISLogs
> SQL1 (Share)
> SQL2 (Share)
> SQL3 (Share)
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:eNXo%23ac7GHA.140@.TK2MSFTNGP05.phx.gbl...

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
Brad
Ops I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>
|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegro ups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>
|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Configure Distribution Wizard - unable to connect to server

I am been trying to setup replication on my local Developer edition copy of SQL Server 2005 SP1, but I keep running to into this unable to connect to server error within MS SQL Server Management Studio environment. I tried various connection settings under the configuration manager and things like adding entries to my host file with no luck. Understand that this requires the server name but failing at providing this for the connection to start the wizard. I am missing something simple here?

Darryl

what is the full error? Is this a remote distributor, and is this a machine in the same domain? If so, make sure to enable remote connections in the SAC.|||

Sorry that I didn't post the full error details on the first post. This is now posted below.

The SQL Server is running locally on the same PC in which I am using MS SQL Server Management Studio. The PC is part of a domain and I am logged using a domain account of which I am the admin on PC. I am able to connect/ open query windows using my domain windows account or SQL Server sa account. This the error below is when to 'right click' on Replication folder and select Configure Distribution. The wizard screen does not appear, just the error. No replication is setup on this server, but the Compact connections tools (IIS setup) were run on this same PC.

Darryl

===================================

SQL Server is unable to connect to server 'DUTTONDARRYL1'. (Configure Distribution Wizard)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ConfigureWizardErrorSR&EvtID=CantConnect&LinkId=20476

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'INITIALSETUP1'. (Replication.Utilities)

Program Location:

at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.PrepareToShow()

|||

Did you rename your sql server? In the beginning of the error message it says it cannot connect to DUTTONDARRYL1, but in the end it says INITIALSETUP1. I would remove all the hostfile, aliases, etc. you modified/added, and make sure the sql server has the same name as your machine.

|||

Where does this INITIALSETUP1 name come from or where is it stored? It almost seems like something left over from a partial install. There is no INITIALSETUP1 in the host file and there are no aliases.

Darryl

|||I don't know, you tell me. Do select * from sysservers to see if the entries are correct.|||

Thanks for the select stament. Here is the result with the first 3 column

srvid srvsstatus srvname

0 1089 INITIALSETUP1

1 1184 BAMPrimaryImportSrv

2 1089 DUTTONDARRYL1

3 1089 MSSQLSERVER

The last 2 entries I added today through a store procedure (sp_addserver ?) base on reading other threads on this topic. Ok, I now see were this INITIALSETUP1 is getting stored, can I just write an update statement here against the first record or is there a better way to change the name?

Darryl

PS: Thanks for the quick followup

|||

What are the reasons for calling sp_addserver? You shouldn't be doing this unless you want to use linked servers. See topic "sp_addserver" in Books Online. If you do not need INITIALSETUP1 then you should remove it. Same with MSSQLSERVER entry. You should remove them with sp_dropserver.

|||

Ok I found the solution....

I did need to use sp_addserver, but I was using it incorrectly. As per the on-line books on sp_addserver , this is used for defining a remote server or the name of the local instance of SQL Server. The local instance is what is important to bring up the configuration distribution wizard without the error. Here are my steps:

1) Remove all the servers listed from sp_helpserver or 'select * from sysservers' (as per Greg sugguestion) that are not required or needed for linking. In my case, I removed all server by using the sp_ dropserver

2) Add in the local server using sp_addserver with the local parameter (i.e. sp_addserver @.server'DUTTONDARRYL1', @.local='LOCAL')

3) Re-start SQL Server

You can check if the local instance come up properly if the 'select @.@.servername' displays the server name.

Darryl

PS: Thanks Greg Y for helping me work through this problem

Conditional split with dependence?

I have setup a SSIS package that takes a flat file fixed width input, and stores it to two SQL server tables in the same database. The flat file contains two types of records, lets call them Type1 and Type2. The two types of records are formatted differently, and the first character determines what type the record is. I used a conditional split to send record type1 down one path, and type2 down the other. On each of those I use a derived column task to build all the fields and then store to the table with the OLE destination. I put any errors that occur (like truncation) into an error table by setting the "redirected row" feature vs "Fail Component". This all works well and I have no issues.

The dilema is as follows. Type1 is essentially a parent record and the Type2 record is a child. There is a shared primary key / foreign key relationship field. I want errors when processing type1 to cause the associated type2 to also be redirected to the error table vs being inserted.

If anyone has suggestions on how this could be done, reference articles, etc... please let me know.

Thanks.

Perhaps use a merge join on the error output of the Type1 flow together with the Type 2 data flow. Then use a conditional split to look for matches. If you have a match, you direct the Type 2 record (along with the Type1 record) down a separate error-handling flow. If you don't have a match, the Type2 records can be processed accordingly.|||

I'm trying your suggestion and I think it will work. But I am having an issue. I have my original flat file source, which I read into the SSIS package as just rows. So I do CRLF search to bring in as one column. I then send it to a derived column component after a conditional split to perform all the "substrings" to get the actual columns out of the data.

In order to do a merge join you must use sorted columns. I was able to set sorted column on the flat file data source and single column, which does me no good. I need to be able to set the sorted column on the derived columns after the data has been put into columns. Is there any way to set the sort column on a derived column? If I can do that it will solve my issues.

Thanks.

conditional relationship to multiple tables

Hello all.

I have what I think is an interesting database issue. In a nutshell, I want to know if it is possible and if so how one can setup a table to optionally relate to different tables. Let me explain, consider the following two tables (in simple psuedo-sql syntax):

Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar

Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int

Relationship:
MessageFields.PID relates to Messages.ID

These tables store information used to parse messages. They are related via a straight forward one-to-many relationship where the PID in MessageFields is the FK that relates to ID in Messages. In this simple kind of relationship, it is easy to setup referential integrity and cascaded deletes, etc...

Now, this worked fine as long as each message simply had it's fields and that was it. However, some fields can have sub-fields (if field is an array, it will have x number of subfields corresponding to each array element). Also, those sub-fields can have sub-fields. In fact, there is no set limit, although in practice it will probably only go 3 levels deep in subfields.

Anyway, the way to represent an arbirary subfield structure like this is to use a recursive table structure, where the FK field in the table (PID in this case) refers to the PK field in the same table (ID), like so:

Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int

Relationship:
MessageFields.PID relates to MessageFields.ID

I believe you can even setup referential integrity and cascaded deletes on such a self-referecing, recursive setup.

The problem is, we still need to relate the MessageFields table to the Messages table. Sooo, the only way to do this that I have come up with is a setup like this:

Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar

Table MessageFields
------
ID - Int, PK
PID - Int, FK
ParType - char(1)
Name - varchar
Type - varchar
Size - int

Relationship:
If ParType = 'M' then
MessageFields.PID relates to Messages.ID
elseif ParType = 'F' then
MessageFields.PID relates to MessageFields.ID
endif

Problem is, I don't think it is possible to setup a relationship (and referential integrity) on a condition like this.

So, my question is, is there a way to setup such a relationship? Is this even a good idea, or is there some standard, better way to setup these tables? Of course, I know I can just setup the tables this way and NOT use a defined relationship, and just be careful in the code that I'm not inserting something incorrect, but I'd rather not. One idea I did have was use a trigger to enforce my referential integrity. The trigger could check inserts into the messagefields table and test the value of partype, then test to see if the inserted row matches the appropriate column in the appropriate table. But before I go down that road, I'd like to see what someone else thinks.

Thanks much for any info/insight someone can give me on this.It's an interesting problem and one I've recently encountered. In our logical model we used subtypes. We have a 'type' of locator with 'subtypes' of physical, tele and postal. So we need to relate entities to locators (many-to-many) based on the type of locator.

entity:
entity_id

entity_locator_participation
entity_id
locator_id
locator_type

tele_locator
locator_id
phone_nbr
email_addr
etc...

postal_locator
locator_id
addr1
addr2
city
etc...

We need to be able to relate entity_locator_participation to tele_locator, postal_locator, or physical_locator depending on the value of the locator_type. For now, we just have no relationship and are maintaining it through code(stored procedures), but it's ugly. We've also thought about encapsulating the logic in triggers.

It's very similar to the probem you bring up. To the best of my knowledge, there is no way to do this, so I'm interested to see what other solutions people have come up with!

-Loach|||Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int

set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)

if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end

end

Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

Tony|||Originally posted by foxybanjo
Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:

CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int

set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)

if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end

end

Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.

Tony

I had a similar problem in my database and I used this approach:

I wanted to create some tables to hold some survey template data. The main table held the name of the template and some other general information. There were a number of other tables representing each type of template. A one-many relationship was created between the template table and each of the survey tables. A template type Id in the template table would identify which survey table was to be used and this was set in a view. The table set up looks like:

Template Table:
TemplateID - PK
TemplateName
TemplateTypeID - identifies which template table you are using

Customer Survey Template
CustomerSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other customer survey template columns}

Void Log Survey Template
VoidLogSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other void log survey template columns)

A view is used for each survey filtered by the templatetypeId . I found this to be very flexible and versatile and easy to use in the front end (in my case an Access database with a data grid)

Concurrent snapshot processing

How can i check if i have concurrent snapshot processing enabled thru TSQL ?
I dont see an option to set it during the initial setup using the GUI
Hasan,
have a look at sp_helppublication (synchronization method). This can be set
after finishing the publication wizard, and before you set up a subscriber.
Rgds,
Paul Ibison