Honeywell OLE DB Source Exception - RPC_E_SERVERFAULT

Hello,

Visual Studio is throwing an exception to an OLE DB source. The provider is Honeywell's and it allows access to its PHD system. The SQL command is:

Code Snippet

SELECT TAGNAME, TAGNO, TIMESTAMP, VALUE, CONF, UNITS
FROM IP_PHD_RAW
WHERE (TAGNAME IN ('T701.SPAVAILABILITY_DAY.BC', 'T200.F2AVAILABILITY_DAY.BC')) AND TIMESTAMP > '2006-04-01'

You can view the data Using Query Build successful return. However, Visual Studio throws this exception for everything else:

Code Snippet

The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)

If you know the cause of the problem or how to access Honeywell data in PHD, I would be appreciative for your assistance.

Visual Studio? Are your building a SSIS package? What have you done? What connection type?

Talk to Honeywell and find out what scenarios their provider is supported under, should it work with SSIS?

|||

Darren,

Using Professional Edition of visual studio to build the SSIS package and the OLE DB provider is from Honeywell. I'm able to view the data using the Query Builder with a SQL command. However, visual studio crashes when you select to see the Columns on the return trip to the OLE DB Source Editor.

I’m open to any suggestions

Thanks for taking an interest,

Ian

|||

Darren,

I think the Honeywell PHD is an OPC server (OLE for Process Control). It may be that Query Tool you are using mimics an OPC client enough for Honeywell to respond but your application doesn't. Sorry to say, my experience accessing Honeywell data is with an "off the shelf" OPC Client, not one I've written myself. However, you may find enough information at the OPC Foundations web site (http://www.opcfoundation.org/) to aid you.

On another note, the Honeywell PHD does come with an Oracle database. It stores configuration information about the Honeywell data as well as information on how well your Honeywell system is running. It is accessible via OLE DB or ODBC. But you probably already know that.

Hope this helps some.

Maineac

PI System Administrator

|||

Hello,

The Honeywell provider is only a partial implementation of OLE. They believe that a schema needed be returned as the data source has only one table, or some obscure reason. I’m guessing that Intergraded Services visual programming nature relies on a schema and crashes with no is found. My question now is, can I create a Data Source without using the visual method, define a file holding the connection and column mapping and import that into Integration Services?

.i

Homework assignment

I am in a college system development class, and we are using SQLPlus with some tables and data loaded to answer questions with SQL statements. I am stuck on a few questions. I was able to answer 30 of the 40 using the references. The questions have gotten very difficult in my opinion now. I write the statements, but I am not getting what the question asks for. Here is one that I am stuck on now. Here is the tables, so you know what fields there are to select from.

/* create AIRPORT */

Drop table airport;

create table AIRPORT
( air_code varchar2 (3)
CONSTRAINT airport_code_pk PRIMARY KEY,
air_location varchar2 (35),
elevation number (4,0),
air_phone varchar2 (12),
service_center char (1),
airpt_type_code char(2)
);

/* CREATE AIRPORT CLASS */

drop table airpt_class;

create table airpt_class
(airpt_type_code char(2)
constraint air_type_pk PRIMARY KEY,
airpt_type_descr varchar2 (25) );

/* CREATE Frequent_flyer */

drop table frequent_flyer;

create table frequent_flyer
(ff_no number(4)
constraint freq_flyer_pk primary key,
L_name varchar2(15) not null,
F_name varchar2(15) not null,
Str_Address varchar2(30),
City varchar2(25),
State char(2),
zip_code char(5),
total_miles number (8),
cur_year_miles number (6),
first_pur_date date);

/* CREATE FLIGHT */

drop table flight;

create table FLIGHT
(
flight_no number (4)
CONSTRAINT flight_flight_no_pk PRIMARY KEY,
orig varchar2 (3),
dest varchar2 (3),
orig_time date,
dest_time date,
meal varchar2 (1),
fare number (7,2),
ff_miles number (4)
);

/* CREATE RESERVATION */

drop table reservation;

create table reservation
(
confirm_no number (4),
res_date date,
res_name varchar2 (20),
res_phone varchar2 (12),
paid_flag char (1),
CONSTRAINT reservation_confirm_no_pk
PRIMARY KEY (confirm_no)
);

/* CREATE PASSENGER */

drop table passenger;

create table passenger
(
pass_name varchar2 (20)
CONSTRAINT passenger_pass_name_nn NOT NULL,
itinerary_no number (4)
CONSTRAINT passenger_itin_no_pk PRIMARY KEY,
ff_no number (4),
confirm_no number (4) NOT NULL
);

/* CREATE TICKET */

drop table ticket;

create table ticket
(
itinerary_no number (4)
CONSTRAINT ticket_itin_no_nn NOT NULL,
flight_no number (4)
CONSTRAINT ticket_flight_no_nn NOT NULL,
flight_date date,
seat varchar2 (3),
fare_charged number(7,2),
CONSTRAINT ticket_itin_fltno_fltdate_pk
PRIMARY KEY (itinerary_no, flight_no, flight_date),
CONSTRAINT ticket_itinno_fk
FOREIGN KEY (itinerary_no)
REFERENCES passenger
ON DELETE CASCADE,
CONSTRAINT ticket_fltno_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE SEAT */

drop table seat;

create table seat
(equip_type varchar2(6),
seat varchar2(3)
);

/* CREATE STAFF */

drop table staff;

create table staff
(
staff_no number (4),
staff_name varchar2 (20),
staff_type varchar2 (20),
staff_date date,
CONSTRAINT staff_staff_no_pk PRIMARY KEY (staff_no)
);

/* CREATE FLIGHT_DAY */

/* drop table */
drop table flight_day;

/* create table */
create table flight_day
(
flight_no number (4)
CONSTRAINT flight_day_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_day_flight_date_nn NOT NULL,
equip_no number (3,0),
pass_ctr integer,
depart_time date,
arrive_time date,
fuel_used integer,
status_note varchar2 (20),
CONSTRAINT flight_day_no_date_pk
PRIMARY KEY (flight_no, flight_date),
CONSTRAINT flight_day_flight_no_fk
FOREIGN KEY (flight_no)
REFERENCES flight
ON DELETE CASCADE
);

/* CREATE FLIGHT_CREW */

drop table flight_crew;

create table flight_crew
(
flight_no number (4)
CONSTRAINT flight_crew_flight_no_nn NOT NULL,
flight_date date
CONSTRAINT flight_crew_flight_date_nn NOT NULL,
staff_no number (4)
CONSTRAINT flight_crew_staff_no_nn NOT NULL,
work_hrs number (4,1),
CONSTRAINT flight_crew_fltno_date_staf_pk
PRIMARY KEY (flight_no, flight_date, staff_no),
CONSTRAINT flight_crew_fltno_date_fk
FOREIGN KEY (flight_no, flight_date)
REFERENCES flight_day
ON DELETE CASCADE,
CONSTRAINT flight_crew_staffno_fk
FOREIGN KEY (staff_no)
REFERENCES staff
ON DELETE CASCADE
);

/* create table equipment */

drop table equipment;

create table equipment
( equip_no number (3,0)
CONSTRAINT equipment_no_pk PRIMARY KEY,
equip_type varchar2 (6),
equip_date date
);

/* CREATE EQUIP_TYPE */

drop table equip_type;

create table equip_type
( equip_type varchar2 (6)
CONSTRAINT equip_type_type_pk PRIMARY KEY,
mfg varchar2 (15),
seats number (3),
fuel_cap number
);

/* CREATE NOTES */

drop table notes;

create table notes
(
confirm_no integer not null PRIMARY KEY,
note varchar2 (50),
CONSTRAINT notes_confirm_no_fk
FOREIGN KEY (confirm_no)
REFERENCES reservation
ON DELETE CASCADE
);

/* ADD FOREIGN KEYS TO FLIGHT */

alter table flight
add constraint orig_for_key
foreign key (orig) references airport;
alter table flight
add constraint dest_for_key
foreign key (dest) references airport;

The question wants - the people's name with reservations, flight numbers, reservation date, and the flight date for all reservations between 30 and 60 days in advance in 2002. The professor wants us to join the reservation.confirm_no to the ticket.Itinerary_no. Make it a select distinct query to keep it to 22 rows coming back.

I am getting the reservation date after the flight date with my statement. I wrote
SELECT DISTINCT reservation.res_date, reservation.Res_name, ticket.flight_date, ticket.flight_d
ate-30 AS "ADVANCE NOTICE"
2 FROM reservation, ticket
3 WHERE ticket.itinerary_no=reservation.confirm_no
4 AND reservation.res_date > '01-JAN-02'
5 AND reservation.res_date < '01-JAN-03';
Any pointers?Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!|||Originally posted by amesjustin
Well, I doubt anyone will do all the work for you, but if all you are looking for is pointers...

Take a look at the BETWEEN command.

good luck!

I wouldn't want anyone to the work. I have tried a between and couldn't figure how to get the statement to reflect between 30 and 60 days. How do you set up the statement to minus the thirty or sixty? I tried to To_char but my resources did not have good examples.|||I have tried a "between" but which variables would it be best to use it on or can you use two? We have had a real quick lesson on SQL. It is an Internet class so seeing the teacher is through emails. I put my statement of what I wrote at the very bottom. I was asking for pointers on my statement. I guess I get confused since there is two or three variables I am looking at. 1) all reservations in 2002 with 30 to 60 days reserved in advance, 2) setting it to be between Jan 1 and Dec 31 3) getting a number back on how many days it was too.
The "between" examples I saw usually followed the "where" and my "where" items are comparing an itinerary number and confirmation number. I like learning SQL but have noticed it is like java and any small detail can through it off like comma, period, paranthesis, and etc...

I have worked on this one problem over six hours. That is why I decided to enlist a discussion group for help. Thanks.

Home page has no tabs

Using System Role Assignment I grant a domain user account "System Administrator" & "System User" within Reporting Services.

Then I connect via IE7 to Report Server with the domain account that has been granted the above rights. The page comes up but the "Contents" and "Properties" tabs are missing. This user can use "Site Settings" and perform admin though.

If I assign the users domain account to the local Administrator group on the Win 2003 Server, then when they reconnect to Reporting Services the missing tabs appear.

We are a development shop, need some of our qa testers and developers to have admin rights within Reporting Services but don't want to grant them local Administrator rights on the server. This could also be an issue upon deployment at customer sites.

So how to get round this. I have been trying ACL's on the Reporting Services directorys, ASP.Net security etc but with no success.

PS: Everything was sweet before SP2

OK found the answer. Use "MS SQL Server Management Studio" and connect to the Reporting Services instance.

Then on "Home" right mouse and select properties. Under permissions you have to add the windows users that are to have access.

So domain\uid, and tick the box's for the access that person needs.

Hitting an Oracle view

I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.

Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.

I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?

Sorry for the basic questions, and I really appreciate any help.

Thansks,

RobI'd suggest just using the Microsoft provided drivers unless you need specific Oracle functionality. It makes life a lot simpler in the long run.

Just use Enterprise Mangler or sp_addlinkedserver to make the Oracle server a linked server from your SQL Server, and life ought to be lovely. I don't remember needing to add any drivers to make it work, but I almost always use the Enterprise Edition of MS-SQL so I'm not always aware of what ships/installs with other editions.

-PatP|||Thanks for that tip - I have looked into sp_addlinkedserver and I can probably figure it out, but using the mangler just seems easier to me. If I start up the register server wizard, it seems to only be looking for SQL servers. I don't see any way to change the provider. Is it possible that the developer edition I am using doesn't have the functionality?|||Like so:

-PatPsql

High PF Usage

The system is a Clustered x64 SQL Server 2005 SP2 Standard 32G of RAM. The task manager show the PF Usage as high as 27G. Any ideas why the usage would be so high?

Did you give the "Lock Pages in Memory" right to the SQL Server Service account? This is extremely important for 64-bit SQL Server 2005. Otherwise, the OS controls paging instead of SQL Server. I have some more details below:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

High performance system and table and index structure

Hi Folks,
I'm working on something a little different at present. Given it's a bit out
of the ordinary, and it relates to some of our indexing discussions lately,
I'd appreciate any thoughts/feedback.
It's a SQL Server box that's receiving a stream of real-time data from oil
drilling rigs all over the world. It needs to perform both fast inserts (ie
10,000 per second) and also have good performance on a fixed list of query
types. The essential part of the table data that has me most intrigued looks
like this:
Well (ID of the well involved - varchar(60))
Wellbore (ID of a specific bore at a wellsite - varchar(60))
Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
Depth (float)
Time (datetime)
DataValue (float)
This data is defined in an external xsd. The hardware/operating system
requirements are basically a non-issue ie: whatever is needed can be
obtained.
The key thing is that about 100 wellbores with up to 100 curves (of sets of
data) can be active each second, so fast insert performance is critical.
Once inserted, the messiest queries involve:
1. All values for a specific curve within a range of depths
2. All values for a specific curve within a range of times
The things I'm pondering are:
1. Whether to have this in one database or to have a database per well.
There are about 1200 wells, but about 100 active concurrently.
2. Whether to store the data in a heap or a clustered index on either depth
or time.
3. Whether to use a non-clustered index for whichever of depth or time
wasn't the clustered one OR whether to store the data twice ie: once by date
and once by time.
4. Whether to find a way to limit the range of depth values (currently
float) to store them instead as a bigint, if the performance of bigint's in
indexes and range queries, etc. is better than floats.
I'd love to hear anyone's thoughts on this. We're about to start
experimenting to see what works.
Regards,
GregGreg
Is it SQL Server 2005?
Well , if it needs perfom fast inserts I think to store the data in a
heap you can get better performance for inserting , however as you said
you need to have good performance on lits of query , so CI will be a good
candidate for range queries
Actually it is really hard to suggest something , you need to test it and
get a decision
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:udZGEsj9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit
> out of the ordinary, and it relates to some of our indexing discussions
> lately, I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts
> (ie 10,000 per second) and also have good performance on a fixed list of
> query types. The essential part of the table data that has me most
> intrigued looks like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets
> of data) can be active each second, so fast insert performance is
> critical. Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
> 2. Whether to store the data in a heap or a clustered index on either
> depth or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by
> date and once by time.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's
> in indexes and range queries, etc. is better than floats.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
> Regards,
> Greg
>|||Hi...
We had a simmilar problem just alitle bit scaled down.
We implemented like:
- use an autoid for PK -> heap, rows are appended in the pages, no other
indexes, no constrains, all collumns allows null.
- use EE version (sql2005) :)
- use snapshot isolation mode for querying the data. (decrease lockings)
- use for querying a second maschine wich is sync-ing the data each n
sec/min. (you can try a log shipping but we've got no time to checkit)
(decreasing lockings, free cpu).
Have phun,
Radu
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:udZGEsj9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit
> out of the ordinary, and it relates to some of our indexing discussions
> lately, I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts
> (ie 10,000 per second) and also have good performance on a fixed list of
> query types. The essential part of the table data that has me most
> intrigued looks like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets
> of data) can be active each second, so fast insert performance is
> critical. Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
> 2. Whether to store the data in a heap or a clustered index on either
> depth or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by
> date and once by time.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's
> in indexes and range queries, etc. is better than floats.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
> Regards,
> Greg
>|||On 23.10.2006 02:34, Greg Low [MVP] wrote:
> Hi Folks,
> I'm working on something a little different at present. Given it's a bit out
> of the ordinary, and it relates to some of our indexing discussions lately,
> I'd appreciate any thoughts/feedback.
> It's a SQL Server box that's receiving a stream of real-time data from oil
> drilling rigs all over the world. It needs to perform both fast inserts (ie
> 10,000 per second) and also have good performance on a fixed list of query
> types. The essential part of the table data that has me most intrigued looks
> like this:
> Well (ID of the well involved - varchar(60))
> Wellbore (ID of a specific bore at a wellsite - varchar(60))
> Curve (ID of a specific set of measures eg: pressures, gas levels, etc.)
> Depth (float)
> Time (datetime)
> DataValue (float)
> This data is defined in an external xsd. The hardware/operating system
> requirements are basically a non-issue ie: whatever is needed can be
> obtained.
> The key thing is that about 100 wellbores with up to 100 curves (of sets of
> data) can be active each second, so fast insert performance is critical.
> Once inserted, the messiest queries involve:
> 1. All values for a specific curve within a range of depths
> 2. All values for a specific curve within a range of times
> The things I'm pondering are:
> 1. Whether to have this in one database or to have a database per well.
> There are about 1200 wells, but about 100 active concurrently.
What about partitioning by wellid? You should at least create a setup
with allows for as much concurrent processing as possible (i.e.
separating data physically on different disks or at least file groups
etc.) to be able to stream your data into the DB as independently as
possible.
> 2. Whether to store the data in a heap or a clustered index on either depth
> or time.
> 3. Whether to use a non-clustered index for whichever of depth or time
> wasn't the clustered one OR whether to store the data twice ie: once by date
> and once by time.
If you need purging of old data and query according to time range then a
CI with timestamp as leading column is certainly a good idea.
> 4. Whether to find a way to limit the range of depth values (currently
> float) to store them instead as a bigint, if the performance of bigint's in
> indexes and range queries, etc. is better than floats.
I would start out with floats if the data is actually float. Only go
for this type of optimization if you actually need it and it is proved
that bigint is faster.
> I'd love to hear anyone's thoughts on this. We're about to start
> experimenting to see what works.
On a more general level I am thinking that Oracle might be more suited
for this scenario because it offers several features that give you
better control. For example, you do not have a single CI on a table -
basically all indexes are ordered (but you can mimic SQL Server's CI
with an IOT). Another point is partitioning of tables, Oracle is much
more flexible there (IIRC you can do only hash partitioning on SQL 2005).
Just my 0.02EUR...
Kind regards
robert

High performance isn't always an advantage...

Hello
MSSQL 2000 + SP3 on Windows 2000 AS + SP4
I've moved my databases to new server.
IO subsystem now TEN times faster!
I have the system of two jobs performing defragmentation
of my data. One of the jobs issues DBCC INDEXDEFRAG
statements and the second one monitors state of transaction
log and stops first job if log more than 70% full. SQLAgent
can run jobs only with 1 minute intervals. So I can monitor
log status only once in minute. Everything was all right at old
server. Now on new hardware log overfills in couple of seconds
and monitoring job can't catch the moment of overfilling and
correctly stop the defragmentation.
Two questions:
1. Should I increase Tlog size depending on hardware performance
2. Is there any way to monitor log state more ofter than once
in a minute?
Thanks.
Serge ShakhovYou can have a job which uses an endless loop and WAITFOR with, say, 10 seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:83ndmb.5kr.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 + SP3 on Windows 2000 AS + SP4
> I've moved my databases to new server.
> IO subsystem now TEN times faster!
> I have the system of two jobs performing defragmentation
> of my data. One of the jobs issues DBCC INDEXDEFRAG
> statements and the second one monitors state of transaction
> log and stops first job if log more than 70% full. SQLAgent
> can run jobs only with 1 minute intervals. So I can monitor
> log status only once in minute. Everything was all right at old
> server. Now on new hardware log overfills in couple of seconds
> and monitoring job can't catch the moment of overfilling and
> correctly stop the defragmentation.
> Two questions:
> 1. Should I increase Tlog size depending on hardware performance
> 2. Is there any way to monitor log state more ofter than once
> in a minute?
> Thanks.
> Serge Shakhov
>|||Hello
> You can have a job which uses an endless loop and WAITFOR with, say, 10
seconds.
That's a good idea!
But I'll try to avoid cycling process by executing every minute job wich
will last 58-59 seconds and perform checks say every 5 seconds...
Thank you Tibor.
Serge Shakhovsql

High Number of lock requests

We're running SQL2K over 2K server, there server just has one main database
besides the system' ones for our administration system
Recently we got a tool for monitoring the server and it's registered up to
5.500.000 lock requests/min, my point is, is that normal?It really depends on what you are doing but it sounds like you have a lot of
scans happening. Basically sounds like you are missing proper indexes and
reading too many rows when you select or modify.
--
Andrew J. Kelly SQL MVP
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
> We're running SQL2K over 2K server, there server just has one main
> database
> besides the system' ones for our administration system
> Recently we got a tool for monitoring the server and it's registered up to
> 5.500.000 lock requests/min, my point is, is that normal?|||Thanks Andrew
I had a look at some of the tables and as you said it seems the developers
did not index the tables properly; I just set a couple of indexes and the
reads went down straight away.
Thanks
"Andrew J. Kelly" wrote:
> It really depends on what you are doing but it sounds like you have a lot of
> scans happening. Basically sounds like you are missing proper indexes and
> reading too many rows when you select or modify.
> --
> Andrew J. Kelly SQL MVP
>
> "FJC" <FJC@.discussions.microsoft.com> wrote in message
> news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
> > We're running SQL2K over 2K server, there server just has one main
> > database
> > besides the system' ones for our administration system
> > Recently we got a tool for monitoring the server and it's registered up to
> > 5.500.000 lock requests/min, my point is, is that normal?
>
>

High Number of lock requests

We're running SQL2K over 2K server, there server just has one main database
besides the system' ones for our administration system
Recently we got a tool for monitoring the server and it's registered up to
5.500.000 lock requests/min, my point is, is that normal?It really depends on what you are doing but it sounds like you have a lot of
scans happening. Basically sounds like you are missing proper indexes and
reading too many rows when you select or modify.
Andrew J. Kelly SQL MVP
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
> We're running SQL2K over 2K server, there server just has one main
> database
> besides the system' ones for our administration system
> Recently we got a tool for monitoring the server and it's registered up to
> 5.500.000 lock requests/min, my point is, is that normal?|||Thanks Andrew
I had a look at some of the tables and as you said it seems the developers
did not index the tables properly; I just set a couple of indexes and the
reads went down straight away.
Thanks
"Andrew J. Kelly" wrote:

> It really depends on what you are doing but it sounds like you have a lot
of
> scans happening. Basically sounds like you are missing proper indexes and
> reading too many rows when you select or modify.
> --
> Andrew J. Kelly SQL MVP
>
> "FJC" <FJC@.discussions.microsoft.com> wrote in message
> news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
>
>sql

High Number of lock requests

We're running SQL2K over 2K server, there server just has one main database
besides the system' ones for our administration system
Recently we got a tool for monitoring the server and it's registered up to
5.500.000 lock requests/min, my point is, is that normal?
It really depends on what you are doing but it sounds like you have a lot of
scans happening. Basically sounds like you are missing proper indexes and
reading too many rows when you select or modify.
Andrew J. Kelly SQL MVP
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
> We're running SQL2K over 2K server, there server just has one main
> database
> besides the system' ones for our administration system
> Recently we got a tool for monitoring the server and it's registered up to
> 5.500.000 lock requests/min, my point is, is that normal?
|||Thanks Andrew
I had a look at some of the tables and as you said it seems the developers
did not index the tables properly; I just set a couple of indexes and the
reads went down straight away.
Thanks
"Andrew J. Kelly" wrote:

> It really depends on what you are doing but it sounds like you have a lot of
> scans happening. Basically sounds like you are missing proper indexes and
> reading too many rows when you select or modify.
> --
> Andrew J. Kelly SQL MVP
>
> "FJC" <FJC@.discussions.microsoft.com> wrote in message
> news:0F0E10B5-9D70-4EB1-AD64-0CA9480910DB@.microsoft.com...
>
>

High Availability solutions

Hi

We are using in our company an ERP system called iScala.

This software is using SQL server as DBMS.

We would like to use the SQL mirroring feature from SQL 2005.

The software is using the SQL server instance name to connect to databases and when the Main SQL server fails the application can't connect to the Mirror instance.

Unfortunatelly we can't change the connection string (the software is provided by another company).

Do you have a solution?

Thanks

I believe you have to contact the vendor to provide such changes, as this is application issue to talk to the mirror server in the event of failover. If the application is not db mirror aware then you have to find a route from connectivity to talk to other server in such cases.|||

Thanks

You are right.

I'm checking now some solutions for High Availability from Symantec (VERITAS Storage Foundation HA 4.3 for Windows) and Double-Take.

Do you have any information about them?

|||

You could solve this issue using a manual change to DNS.

i.e. MYLIVESQL -> 10.0.0.1 - connection string uses MYLIVESQL as the server name, in the event of a failure you update the DNS record to MYLIVESQL -> 10.0.0.2 (which is the IP address of the mirror server).

Andrew

|||

If the (former) principal instance is still up and running but the database failover occured, then SNAC will automatically learn the location of the mirror and redirect the connection to the mirror, as described here: http://msdn2.microsoft.com/en-us/library/ms366348.aspx. But this works only if the connection string specifies the database and if the principal instance is still running.

But if the vendor is not providing support for mirroring, is highly unlikely that the application will actually work with mirrored database, it may have dependencies outside the database that are not gonna failover (e.g. logins resulting in orphaned users, jobs in msdb etc)

HTH,
~ Remus

|||

Remus,

MOSS 2007 and WSS V3 fall into the category of not supporting mirroring in the connection strings, hence the need to use the cname approach. Microsoft recommend using stsadm to manually change the settings which I think is less elegant.

Andrew

High "Disk Write Bytes/sec" in Mirror server

I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....

For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.

I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?

Hi Benny,

Just curious - is the mirrored database the only thing that's running on the mirror machine, or you may have other workloads there?

Could you please run the sp_dbmmonitorresults stored proc on the principal and the mirror, and check whether the send and receive queues show something reasonable?

-- Kaloian.

|||

Hi Kaloian, in the mirror server, I find 19 row but only 1 row in the principle server, is it normal?

According to the result of sp_dbmmonitorresults, I find that the transaction delay on the mirror server is quite long. We are using hardware RAID-5 on the Principle server and normal disk (no RAID) on mirror server, will it increase the write byte /sec, because of the slower in "disk" speed, should be make it lower, am I right?

Thank you very much for your help.

This are the result for your reference:

On Principle Server:

all the counter are "0"

On Mirror Server:

database name	role	mirroring_state	witness_status	log_generation_rate	unsent_log	send_rate	unrestored_log	recovery_rate	transaction_delay	transactions_per_sec	average_delay	time_recorded	Time_behind	local_time
Mirror2	1	4	2	71	0	71	0	0	267	44	6	30:00.7	29:00.1	30:00.7
Mirror2	1	4	1	83	0	83	0	0	324	50	6	29:00.1	29:00.1	29:00.1
Mirror2	1	4	1	74	0	74	0	0	397	42	9	28:01.0	28:01.0	28:01.0
Mirror2	1	4	1	0	0	0	0	0	0	1	0	27:00.8	27:00.8	27:00.8
Mirror2	1	4	1	0	0	0	0	0	0	1	0	26:00.7	26:00.7	26:00.7
Mirror2	1	4	1	0	0	0	0	0	0	1	0	25:00.5	25:00.5	25:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	24:00.4	24:00.4	24:00.4
Mirror2	1	4	1	0	0	0	0	0	0	1	0	23:00.2	23:00.2	23:00.2
Mirror2	1	4	1	0	0	0	0	0	0	1	0	22:00.1	22:00.1	22:00.1
Mirror2	1	4	1	0	0	0	0	0	0	1	0	21:00.9	21:00.9	21:00.9
Mirror2	1	4	1	0	0	0	0	0	0	1	0	20:00.8	20:00.8	20:00.8
Mirror2	1	4	1	7	0	7	0	0	8	2	4	19:00.6	19:00.6	19:00.6
Mirror2	1	4	1	0	0	0	0	0	2	1	2	18:00.5	18:00.5	18:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	17:00.3	17:00.3	17:00.3
Mirror2	1	4	1	0	0	0	0	0	0	1	0	16:00.2	16:00.2	16:00.2
Mirror2	1	4	1	0	0	0	0	0	0	0	0	15:40.2	15:40.2	15:40.2
Mirror2	1	2	1	0	0	0	0	0	0	0	0	44:00.1	15:40.2	44:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	43:00.1	15:40.2	43:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	42:00.1	15:40.2	42:00.1

High "Disk Write Bytes/sec" in Mirror server

I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....

For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.

I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?

Hi Benny,

Just curious - is the mirrored database the only thing that's running on the mirror machine, or you may have other workloads there?

Could you please run the sp_dbmmonitorresults stored proc on the principal and the mirror, and check whether the send and receive queues show something reasonable?

-- Kaloian.

|||

Hi Kaloian, in the mirror server, I find 19 row but only 1 row in the principle server, is it normal?

According to the result of sp_dbmmonitorresults, I find that the transaction delay on the mirror server is quite long. We are using hardware RAID-5 on the Principle server and normal disk (no RAID) on mirror server, will it increase the write byte /sec, because of the slower in "disk" speed, should be make it lower, am I right?

Thank you very much for your help.

This are the result for your reference:

On Principle Server:

all the counter are "0"

On Mirror Server:

database name	role	mirroring_state	witness_status	log_generation_rate	unsent_log	send_rate	unrestored_log	recovery_rate	transaction_delay	transactions_per_sec	average_delay	time_recorded	Time_behind	local_time
Mirror2	1	4	2	71	0	71	0	0	267	44	6	30:00.7	29:00.1	30:00.7
Mirror2	1	4	1	83	0	83	0	0	324	50	6	29:00.1	29:00.1	29:00.1
Mirror2	1	4	1	74	0	74	0	0	397	42	9	28:01.0	28:01.0	28:01.0
Mirror2	1	4	1	0	0	0	0	0	0	1	0	27:00.8	27:00.8	27:00.8
Mirror2	1	4	1	0	0	0	0	0	0	1	0	26:00.7	26:00.7	26:00.7
Mirror2	1	4	1	0	0	0	0	0	0	1	0	25:00.5	25:00.5	25:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	24:00.4	24:00.4	24:00.4
Mirror2	1	4	1	0	0	0	0	0	0	1	0	23:00.2	23:00.2	23:00.2
Mirror2	1	4	1	0	0	0	0	0	0	1	0	22:00.1	22:00.1	22:00.1
Mirror2	1	4	1	0	0	0	0	0	0	1	0	21:00.9	21:00.9	21:00.9
Mirror2	1	4	1	0	0	0	0	0	0	1	0	20:00.8	20:00.8	20:00.8
Mirror2	1	4	1	7	0	7	0	0	8	2	4	19:00.6	19:00.6	19:00.6
Mirror2	1	4	1	0	0	0	0	0	2	1	2	18:00.5	18:00.5	18:00.5
Mirror2	1	4	1	0	0	0	0	0	0	1	0	17:00.3	17:00.3	17:00.3
Mirror2	1	4	1	0	0	0	0	0	0	1	0	16:00.2	16:00.2	16:00.2
Mirror2	1	4	1	0	0	0	0	0	0	0	0	15:40.2	15:40.2	15:40.2
Mirror2	1	2	1	0	0	0	0	0	0	0	0	44:00.1	15:40.2	44:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	43:00.1	15:40.2	43:00.1
Mirror2	1	2	1	0	0	0	0	0	0	0	0	42:00.1	15:40.2	42:00.1