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

concatenation?

I have a field named "changes" it is nvarchar (150) it is in a table called changes_log.

A new row will be inserted everytime that an update statement occurs on another table and it should describe what was updated. I want to essential check if each new value is = to old value and if it is add that to a string that will become the value of "changes" for the new row.
It might contain 1 field name or many.

Here is the SQL code I am trying to work with inside a stored procedure:

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
END

But it only adds "ROUTER_NAME" even when both are changed. Maybe I'm going about this all wrong, can anyone offer me and tips on how to log what fields were changed in the database, by who and when?Your UPDATE statments have no WHERE clause. Other than that, I am not clear on exactly what you are trying to do.|||You are right! I totally forgot and left that out!

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
WHERE ITEM_NAME = @.NAME
END
select * from changes_log

output (using a different or changed name and serial_num so that both if statements would be true:

ITEM_NAME _DATE USER_ID CHANGES
------------------------------
NEW 10/17/2003 12:17:33 PM LOUMAS\NCL4504S ROUTER_NAME

Where Changes should contain "ROUTER_NAME SERIAL_NUM"

I might be approaching this all wrong but here is what the requirement is:

KEEP A LOG OF EACH ITEM THAT HAS BEEN EDITED, WHO EDITED IT, WHEN DID THEY DO IT, WHAT FIELDS WERE CHANGED.

NOTE: these items are always edited through a stored procedure. there many field unique for different types of items (from different tables) that could be edited. that is why I attempted to simply capture the field name of field that was changed.

Any ideas or help is welcome, I can change the structure of the Changes_Log table (above) if necessary. I can start from scratch if necesary.|||This is incorrect:

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

|||thanks for the reply. I made your suggested change and the result is "changes" in the new row = NULL|||try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = IsNull(CHANGES,'') + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

I gather CHANGES defaults to NULL, and so NULL + anything is NULL. IsNull returns the leftmost non-null argument, and thus should work.|||If I understand correctly you want one record in changes_log per update with a concatenated list of changes in the changes field indicating which fields in another table have been edited.

Why don't you declare a @.Changed varchar and assemble that before making any inserts to changes_log? Do a single insert at the end instead of an insert and two updates.

Are you launching this from a before trigger?
Where does @.Router come from?

Glenn|||YES IT WORKED! THANKS SO MUCH!|||OK, you do understand what I am trying to do correctly.

I'm not sure what you are getting at with declaring a @.Changed varchar? Is this a temporary variable to do a single insert? If so that sounds like a good idea, I just wanted to get something to work first. Actually there will be way more than 2 updates when complete, unless I do it this way.

I am not using any triggers.

@.Router is a value passed to the stored procedure that identifies the item by it's original name even if the name has been changed. @.Name would be the new name if name has been changed, otherwise @.Name = @.Router.

Thanks.|||Ok I changed my code as you suggested and things were working fine, until after adding all the fields I realized that if the current value of a field is NULL the IF statement returns false. Logically I think

IF NOT (@.NEW_VALUE = (SELECT NAME FROM TABLE WHERE ID = @.ID) )

where the @.NEW_VALUE is not NULL and the NAME value returned is null, should return true but it must not compare the same for NULL or something because only those statements do no execute.

Here is my code: note that I tried checking if the value returned from select is null on some items but it still did not work.

ALTER PROCEDURE sp_UPDATE_ROUTER
(
@.ROUTER nvarchar(50),
@.NAME nvarchar(50) = @.ROUTER, /*IF NO NEW NAME IS PASSED DEFAULT = CURRENT ROUTER NAME*/
@.SERIAL nvarchar(25) = NULL,
@.MODEL nvarchar(30) = NULL,
@.IOS_VER nvarchar(15) = NULL,
@.IOS_BASED nvarchar(3) = NULL,
@.BOOT_VER nvarchar(15) = NULL,
@.NETWORK nvarchar(3) = NULL,
@.FIREWALL nvarchar(16)= NULL,
@.REACH nvarchar(60) = NULL,
@.DIAL nvarchar(30) = NULL,
@.FAC_ID nvarchar(20) = NULL,
@.FAC_NAME nvarchar(40) = NULL,
@.STREET nvarchar(60) = NULL,
@.CITY nvarchar(40) = NULL,
@.STATE nvarchar(2) = NULL,
@.ZIP nvarchar(10) = NULL,
@.CONTACT nvarchar(60) = NULL,
@.CONTACT2 nvarchar(60) = NULL,
@.PO nvarchar(20) = NULL,
@.MO nvarchar(20) = NULL,
@.COMMENTS nvarchar(50) = NULL,
@.USER_ID nvarchar(50)
)
AS
DECLARE @.TEMP nvarchar(300)
IF NOT @.NAME = @.ROUTER
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
IF NOT @.MODEL = (SELECT MODEL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MODEL '
END
IF NOT @.IOS_BASED = (SELECT IOS_BASED FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_BASED '
END
IF NOT @.IOS_VER = (SELECT IOS_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_VER '
END
IF NOT @.NETWORK = (SELECT NETWORK FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'NETWORK '
END
IF NOT @.FIREWALL = (SELECT FIREWALL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FIREWALL '
END
IF NOT (@.BOOT_VER = (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'BOOT_VER '
END
IF NOT (@.REACH = (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'REACHABLE_FROM '
END
IF NOT @.DIAL = (SELECT DIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'DIAL_NUM '
END
IF NOT @.FAC_ID = (SELECT FACILITY_ID FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY_ID '
END
IF (NOT @.FAC_NAME = (SELECT FACILITY_NAME FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STREET = (SELECT STREET FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.CITY = (SELECT CITY FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STATE = (SELECT STATE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.ZIP = (SELECT ZIP_CODE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER))
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY ADDR '
END
IF NOT @.CONTACT = (SELECT CONTACT FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT '
END
IF NOT @.CONTACT2 = (SELECT CONTACT2 FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT2 '
END
IF NOT @.PO = (SELECT PO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'PO '
END
IF NOT @.MO = (SELECT MO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MO '
END
IF NOT @.COMMENTS = (SELECT COMMENTS FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'COMMENTS '
END
INSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, SERIAL_NUM, USER_ID, CHANGES) VALUES(@.NAME, GETDATE(), @.SERIAL, @.USER_ID, @.TEMP)
select * from changes_log
RETURN

THANKS|||What I meant with the @.Changed varchar was what you did with @.TEMP.|||If I understand your problem, you need to check the @.Parameters if they are null before comparing them with the previous value selected from ROUTERS.

A nested IF for each value would work.

IF NOT IsNull(@.SERIAL,'') = ''
BEGIN
 IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
END

Also I think you should be able to SET @.TEMP = '' right off, and eliminate the IsNull check in your existing code. This would go back to the simpler...

 SET @.TEMP = @.Temp + 'SERIAL_NUM '

I don't know if that will finish it, but it's another step...|||well I cna tell you just to test it I used 'xyz' for every value and none are null and none currently have a value of 'xyz'|||And?
What were your results? Your changelog should've shown a change for each field right? Did it work?|||The same as above, I posted that message with the datavalues that I described. I have already thought of all that.