Hoe to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?sql

Hoe to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

Ho to run Enterprise Manager on another PC

Hi,
I want to let another user access Enterprise Manager from his PC. How do I
do this? I tried to setup MMC but there is no SQL snap-in to add.
Thanks a lot, Alex.You need to install the client tools from the SQLServer CD. You are entitled
to run the client tools on any device that is licensed with a CAL or any
device that connects to a processor-licensed server.
--
David Portas
--
Please reply only to the newsgroup
--sql

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing
Try tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
XiaobingTry tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
XiaobingTry tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobingsql

Hierarchy Parent-Child

Hi.

I have a parent-child relationship in a table, by making use of an ID, and Parent ID.

Firstly how do I manually change, or setup this hierarchy.

Secondly, how do I do a rollup, along this hierarchy, sothat I can retrieve the aggregate values, using MDX?

I thank you in advance for your feedback.

Hi. Sounds like you should start with the basics in Analysis Services about parent-child hierarchies. Here's a link to MSDN on-line which explains how parent-child hierarchies work in SSAS 2005. The subsequent links at the bottom of the page can guide you to more detail on creating the parent-child hierarchy in BI Studio.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agdimensions_5ab7.asp

Your second question, "how do I do a rollup, along this hierarchy, so that I can retrieve the aggregate values, using MDX?". There is a simple answer to your question. The answer is "nothing". The normal aggregation behavior of Analysis Services will aggregate the measures up your parent-child hierarchy. If you have a measure such as 'sales' and it has an aggregtion type of SUM, then the 'sales' values are naturally summed as you roll up the parent-child hierarchy. There is no action required on your part unless you want to change the SUM aggregation of the 'sales' measure.

Hope this helps - PaulG

|||

Hi. After a more thorough look, I determined that BI Studio seem to have sucessfully determined the hierarchies.

I can say this, based on the 'levels', that is shown under the parent view. The problem that I now have, is that for two tables, of exactly the same structure, BI is showing me the ID's of one table, and the 'name' values for another. I

have already tried to set the 'NameColumn' property of the parent id, as well as the source id, but it does not seem to work. Is there any place, where I can get good articles, on how to change these hierarchies. Or send a sample to someone?

The reason why I feel that my hierachy isnt rolling up, is the following. When I run the MDX query:

select {[Measures].[People]}on columns,{[FAFA Location].[FAFA Location].[Africa]} on rows

I get 'null', although if I do a level mdx, with 'africa' included, I get a rollup value back.

Regards.

|||

Hi. For your first problem, getting a "name" to display instead of the ID, you need to manage the properties of the child attribute which is usually the primary key for your dimension. Set the NameColumn property of child attribute to the "name" column from your parent-child table.

Second problem, getting null when querying, I can't say what the problem is without debugging your model. The most common problem is that the dimension is NOT linked properly to the fact data. What is the result when you execute a query against leaf-level descendant members of [Africa] in the dimension (I'm assuming that [Africa] is a non-leaf level member)?

Hope this Helps. Paul Goldy

Hierarchy Parent-Child

Hi.

I have a parent-child relationship in a table, by making use of an ID, and Parent ID.

Firstly how do I manually change, or setup this hierarchy.

Secondly, how do I do a rollup, along this hierarchy, sothat I can retrieve the aggregate values, using MDX?

I thank you in advance for your feedback.

Hi. Sounds like you should start with the basics in Analysis Services about parent-child hierarchies. Here's a link to MSDN on-line which explains how parent-child hierarchies work in SSAS 2005. The subsequent links at the bottom of the page can guide you to more detail on creating the parent-child hierarchy in BI Studio.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agdimensions_5ab7.asp

Your second question, "how do I do a rollup, along this hierarchy, so that I can retrieve the aggregate values, using MDX?". There is a simple answer to your question. The answer is "nothing". The normal aggregation behavior of Analysis Services will aggregate the measures up your parent-child hierarchy. If you have a measure such as 'sales' and it has an aggregtion type of SUM, then the 'sales' values are naturally summed as you roll up the parent-child hierarchy. There is no action required on your part unless you want to change the SUM aggregation of the 'sales' measure.

Hope this helps - PaulG

|||

Hi. After a more thorough look, I determined that BI Studio seem to have sucessfully determined the hierarchies.

I can say this, based on the 'levels', that is shown under the parent view. The problem that I now have, is that for two tables, of exactly the same structure, BI is showing me the ID's of one table, and the 'name' values for another. I

have already tried to set the 'NameColumn' property of the parent id, as well as the source id, but it does not seem to work. Is there any place, where I can get good articles, on how to change these hierarchies. Or send a sample to someone?

The reason why I feel that my hierachy isnt rolling up, is the following. When I run the MDX query:

select {[Measures].[People]}on columns,{[FAFA Location].[FAFA Location].[Africa]} on rows

I get 'null', although if I do a level mdx, with 'africa' included, I get a rollup value back.

Regards.

|||

Hi. For your first problem, getting a "name" to display instead of the ID, you need to manage the properties of the child attribute which is usually the primary key for your dimension. Set the NameColumn property of child attribute to the "name" column from your parent-child table.

Second problem, getting null when querying, I can't say what the problem is without debugging your model. The most common problem is that the dimension is NOT linked properly to the fact data. What is the result when you execute a query against leaf-level descendant members of [Africa] in the dimension (I'm assuming that [Africa] is a non-leaf level member)?

Hope this Helps. Paul Goldy

Hiding data in replicated tables

Hello,
I have a merge replication setup between two sql 2000 servers. It works
fine, but there is some data in one table
that I wish to hide - at the subscriber database.
This data is in 4 columns in one table. It would be best if I could exclude
the table altogether from the replication process
but this cannot be done because the table is referenced by a foreign key
constraint with another table required in the replication process.
Also I cannot vertically filter out the columns because the columns are not
nullable. I did try selecting out the data by using the 'where' clause
and trying to eliminate the data that I wish to hide from the result set,
however this did not work.
Am not sure how I can go about hiding this data because it should not be
seen at the subscriber.
Any help will be appreciated.
Thanks,
Marise
How about hiding the data by using a view on the subscriber?
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
I am not too sure what you mean by hiding data using a view on the
subscriber...please explain some more how this can be done.
Because this is data hiding at the database level. i.e. anyone who logs
into the database and runs a select query should not be able to see these
four columns in this one table.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u4Xlhg$dFHA.3712@.TK2MSFTNGP12.phx.gbl...
> How about hiding the data by using a view on the subscriber?
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nothing special here - as long as they don't have select rights on the
table, the only way they'll be able to get to see any data is through the
view.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the response. The replicated database sits away from our location
and the user name that we use to do replication has been assigned to us - we
do not have the capability to alter database permissions. I wanted to find a
way to restrict the data itself being copied over at all.
Please let me know if there is a way.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u2kqR4AfFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Nothing special here - as long as they don't have select rights on the
> table, the only way they'll be able to get to see any data is through the
> view.
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>