Showing posts with label web. Show all posts
Showing posts with label web. Show all posts

Thursday, March 22, 2012

Cluster Hardware recommendation

Hello,
I currently have 4 web servers (Windows 2003) and 4 sql servers (SQL 2000)
and would like to consolidate these into an entry level SAN. As the web
servers are running the same IIS based applications, i'd like to cluster
these to use the centralised storage and likewise with the SQL servers. I
was thinking of something like a Dell/EMC AX100 solution or maybe the
AX-100i (although is iSCSI a reliable option for clustering IIS or SQL?).
Would people agree that this would be the way to go or would there be some
other recommendations? Storage use at the moment is around 3TB growing to
around 10TB in the next 3 years. What about backups for this amount of data.
It would be good to have the data mirrored across to another storage unit
(is this feasable?).
Any help or advice with this matter is appreciated as i'm new to SANs and
any recommended intoroductory reading would be great.
Thanks - Jules.
Use the information found here -
http://www.microsoft.com/windows/cat...2-032dcb893c8b
Chuck Timon, Jr.
Microsoft Corporation
CCS Beta Engineer
This posting is provided "AS IS" with no
warranties, and confers no rights.
"Jules" <jules_espere11@.hotmail.com> wrote in message
news:uzIlQ0U2FHA.892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently have 4 web servers (Windows 2003) and 4 sql servers (SQL 2000)
> and would like to consolidate these into an entry level SAN. As the web
> servers are running the same IIS based applications, i'd like to cluster
> these to use the centralised storage and likewise with the SQL servers. I
> was thinking of something like a Dell/EMC AX100 solution or maybe the
> AX-100i (although is iSCSI a reliable option for clustering IIS or SQL?).
> Would people agree that this would be the way to go or would there be some
> other recommendations? Storage use at the moment is around 3TB growing to
> around 10TB in the next 3 years. What about backups for this amount of
> data. It would be good to have the data mirrored across to another storage
> unit (is this feasable?).
> Any help or advice with this matter is appreciated as i'm new to SANs and
> any recommended intoroductory reading would be great.
> Thanks - Jules.
>
|||What sort of performance are you needing? If you need any kind of
performance considerations from this cluster, then I would not recommend the
AX100, and would recommend using a CX500 instead. Sure the CX500 costs
more, but you'll get more speed and reliability from it.
The AX100 is great for storage, and is a great backup to disk solution, but
for a SQL cluster, even though it could do it, would not do it well.
Eric Bursley
eric at bursley dot net
Microsoft MVP
RHCE, MCSE, BCFP, EEIE-CS, ESCE-CS
GPG Signature:
Key fingerprint = CEAE CF3A 3876 7ECE 9DA7 946F DA9F DDCA C392 6DCB
"Jules" <jules_espere11@.hotmail.com> wrote in message
news:uzIlQ0U2FHA.892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently have 4 web servers (Windows 2003) and 4 sql servers (SQL 2000)
> and would like to consolidate these into an entry level SAN. As the web
> servers are running the same IIS based applications, i'd like to cluster
> these to use the centralised storage and likewise with the SQL servers. I
> was thinking of something like a Dell/EMC AX100 solution or maybe the
> AX-100i (although is iSCSI a reliable option for clustering IIS or SQL?).
> Would people agree that this would be the way to go or would there be some
> other recommendations? Storage use at the moment is around 3TB growing to
> around 10TB in the next 3 years. What about backups for this amount of
> data. It would be good to have the data mirrored across to another storage
> unit (is this feasable?).
> Any help or advice with this matter is appreciated as i'm new to SANs and
> any recommended intoroductory reading would be great.
> Thanks - Jules.
>
|||If you consider the EMC Symmetrix DMX or HP Storage Solutions, the backup
snap-copy disks are installed within the same cabinet for local backups and
both support remote mirroring solutions. The Clariion does not.
Sincerely,
Anthony Thomas

"Eric Bursley [MVP]" <ebursley at swbell dot net> wrote in message
news:eHInBRd2FHA.636@.TK2MSFTNGP10.phx.gbl...
> What sort of performance are you needing? If you need any kind of
> performance considerations from this cluster, then I would not recommend
the
> AX100, and would recommend using a CX500 instead. Sure the CX500 costs
> more, but you'll get more speed and reliability from it.
> The AX100 is great for storage, and is a great backup to disk solution,
but[vbcol=seagreen]
> for a SQL cluster, even though it could do it, would not do it well.
>
> Eric Bursley
> eric at bursley dot net
> Microsoft MVP
> RHCE, MCSE, BCFP, EEIE-CS, ESCE-CS
> GPG Signature:
> Key fingerprint = CEAE CF3A 3876 7ECE 9DA7 946F DA9F DDCA C392 6DCB
>
> "Jules" <jules_espere11@.hotmail.com> wrote in message
> news:uzIlQ0U2FHA.892@.TK2MSFTNGP12.phx.gbl...
2000)[vbcol=seagreen]
I[vbcol=seagreen]
SQL?).[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
storage[vbcol=seagreen]
and
>
|||I beg to differ. The Clariion support snapview which is a copy of first
write backup, and full cloning within the array. Very similar to the BCV's
that a Symmetrix has. In addition to that, the Clariion supports
mirrorview, sancopy, and mirrorview/A, which allows for replication between
multiple arrays. While a Symmetrix can do all of that an more, it will also
cost you a lot more as well.
Eric Bursley
eric at bursley dot net
Microsoft MVP
RHCE, MCSE, BCFP, EEIE-CS, ESCE-CS
GPG Signature:
Key fingerprint = CEAE CF3A 3876 7ECE 9DA7 946F DA9F DDCA C392 6DCB
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%237qoZxi2FHA.2268@.TK2MSFTNGP15.phx.gbl...
> If you consider the EMC Symmetrix DMX or HP Storage Solutions, the backup
> snap-copy disks are installed within the same cabinet for local backups
> and
> both support remote mirroring solutions. The Clariion does not.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Eric Bursley [MVP]" <ebursley at swbell dot net> wrote in message
> news:eHInBRd2FHA.636@.TK2MSFTNGP10.phx.gbl...
> the
> but
> 2000)
> I
> SQL?).
> some
> to
> storage
> and
>
|||So, the Clariion CX series will support Adaptive Copy, RDF/S and RDF/A?
If not, will the sancopy, mirrorview, and mirrorview/A support distanced or
stretch mirroring?
Again, if not, are we talking about degrees of distance, in which case it
would depend on this user's requirement of geographical separation? In
other words, you might have to go with the SYM DMX to get the distance, and
reduced latency, that your environment requires.
Anthony Thomas

"Eric Bursley [MVP]" <ebursley at swbell dot net> wrote in message
news:eBCbguo2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> I beg to differ. The Clariion support snapview which is a copy of first
> write backup, and full cloning within the array. Very similar to the
BCV's
> that a Symmetrix has. In addition to that, the Clariion supports
> mirrorview, sancopy, and mirrorview/A, which allows for replication
between
> multiple arrays. While a Symmetrix can do all of that an more, it will
also[vbcol=seagreen]
> cost you a lot more as well.
>
> Eric Bursley
> eric at bursley dot net
> Microsoft MVP
> RHCE, MCSE, BCFP, EEIE-CS, ESCE-CS
> GPG Signature:
> Key fingerprint = CEAE CF3A 3876 7ECE 9DA7 946F DA9F DDCA C392 6DCB
>
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:%237qoZxi2FHA.2268@.TK2MSFTNGP15.phx.gbl...
backup[vbcol=seagreen]
recommend[vbcol=seagreen]
web[vbcol=seagreen]
servers.[vbcol=seagreen]
growing[vbcol=seagreen]
of
>
|||Anthony Thomas wrote:
> So, the Clariion CX series will support Adaptive Copy, RDF/S and RDF/A?
> If not, will the sancopy, mirrorview, and mirrorview/A support distanced or
> stretch mirroring?
> Again, if not, are we talking about degrees of distance, in which case it
> would depend on this user's requirement of geographical separation? In
> other words, you might have to go with the SYM DMX to get the distance, and
> reduced latency, that your environment requires.
>
Clariion does support distance/stretched mirroring via Mirrorview.
MirrorView and MirrorView/A are similar to SRDF/S and SRDF/A
respectively. I don't believe Clariion has an Adaptive Copy equivilant,
but I'm not sure about that. As far as distance and latency, you
basically have the same set of obstacles to overcome with SRDF as you do
with MirrorView. Also, like the Symmetrix line, Clariions support
consistency groups.
Jon
|||With Sancopy / mirrorview / mirrorview/A, the Clariion is able to replicate
any distance the Symmetric can because the technology is control by FC-IP
routers, which transport FC over IP over any distance. I have assisted with
a Washington - Atlanta full sync mirror view connections, as well as New
York to LA. McData fibre channel IP routers handled the connections.
While the terms may be different, the technology is very similar. The only
real difference between the Symmetric and the Clariion is the Symmetric has
active / active FC ports, and the Clariion uses Active / Passive ports.
Eric Bursley
eric at bursley dot net
Microsoft MVP
RHCE, MCSE, BCFP, EEIE-CS, ESCE-CS
GPG Signature:
Key fingerprint = CEAE CF3A 3876 7ECE 9DA7 946F DA9F DDCA C392 6DCB
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzzYOQv2FHA.3188@.TK2MSFTNGP12.phx.gbl...
> So, the Clariion CX series will support Adaptive Copy, RDF/S and RDF/A?
> If not, will the sancopy, mirrorview, and mirrorview/A support distanced
> or
> stretch mirroring?
> Again, if not, are we talking about degrees of distance, in which case it
> would depend on this user's requirement of geographical separation? In
> other words, you might have to go with the SYM DMX to get the distance,
> and
> reduced latency, that your environment requires.
>
> Anthony Thomas
>
> --
> "Eric Bursley [MVP]" <ebursley at swbell dot net> wrote in message
> news:eBCbguo2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> BCV's
> between
> also
> backup
> recommend
> web
> servers.
> growing
> of
>
|||If you really need to expand the SAN up to 10GB, then you should have to
select at least CX300 or CX300i. The reasons are:
1. AX100/i does not support cascading storage.
2. By cascading up to 4 x CX300, you can have up to around 19GB for storage
space.
3. You still need a growth buffer, right?
For iSCSI, I consider that will not be a bad choice unless you have to
consider the performance difference between Gigabit Ethernet and 2GB FC.
However, in terms of unifying your network equipment to pure IP based, it
may be a good choice.
To choose between CX300/500, the only factor I consider is the total storage
size, since that the performance factor does not generate noticible
difference to me.
Considering iSCSI, I have implemented iSCSI-SQL server cluster solution for
testing and for customer production environment for few time already and
found no particular problem. The art is the OS and SQL level fine tuning,
instead of iSCSI. iSCSI itself, even using Microsoft iSCSI initiator, is a
very stable as I experienced.
"Jules" <jules_espere11@.hotmail.com> wrote in message
news:uzIlQ0U2FHA.892@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently have 4 web servers (Windows 2003) and 4 sql servers (SQL 2000)
> and would like to consolidate these into an entry level SAN. As the web
> servers are running the same IIS based applications, i'd like to cluster
> these to use the centralised storage and likewise with the SQL servers. I
> was thinking of something like a Dell/EMC AX100 solution or maybe the
> AX-100i (although is iSCSI a reliable option for clustering IIS or SQL?).
> Would people agree that this would be the way to go or would there be some
> other recommendations? Storage use at the moment is around 3TB growing to
> around 10TB in the next 3 years. What about backups for this amount of
> data. It would be good to have the data mirrored across to another storage
> unit (is this feasable?).
> Any help or advice with this matter is appreciated as i'm new to SANs and
> any recommended intoroductory reading would be great.
> Thanks - Jules.
>
|||Going from the EMC AX series to the CX series is a big price jump. Try this
Cluster Solution built on iSCSI.
http://www.microsoft.com/windows/cat...Han d&scope=1
Unless you have an unlimted IT budget
"Jules" wrote:

> Hello,
> I currently have 4 web servers (Windows 2003) and 4 sql servers (SQL 2000)
> and would like to consolidate these into an entry level SAN. As the web
> servers are running the same IIS based applications, i'd like to cluster
> these to use the centralised storage and likewise with the SQL servers. I
> was thinking of something like a Dell/EMC AX100 solution or maybe the
> AX-100i (although is iSCSI a reliable option for clustering IIS or SQL?).
> Would people agree that this would be the way to go or would there be some
> other recommendations? Storage use at the moment is around 3TB growing to
> around 10TB in the next 3 years. What about backups for this amount of data.
> It would be good to have the data mirrored across to another storage unit
> (is this feasable?).
> Any help or advice with this matter is appreciated as i'm new to SANs and
> any recommended intoroductory reading would be great.
> Thanks - Jules.
>
>

Tuesday, March 20, 2012

Cluster @#$ked

I inherited an MS Cluster (2 node) that needs some help. As long as node1 i
s live, it works fine, but when if fails over, the web server pointed to the
virtual server gets nothing back. As far as the cluster is concerned, ever
ything works fine and node2
can even send data to/from the SQL Server. Anyone have any ideas what is ha
ppening? If not, is there some way for me to find out if the server was ins
talled out of order or wrong?
Thanks
--
Johnnie O.
Computer Handyman
Amateur Superhero1. Can anyone connect to the SQL server when it is failed over?... If so,
that means that the SQL is working..
2. Then ensure that the Web server is truly pointing to the virtual server,
and that the web server is running..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:663C67E7-5F10-4BC3-AD89-FE6C9AECC6C7@.microsoft.com...
> I inherited an MS Cluster (2 node) that needs some help. As long as node1
is live, it works fine, but when if fails over, the web server pointed to
the virtual server gets nothing back. As far as the cluster is concerned,
everything works fine and node2 can even send data to/from the SQL Server.
Anyone have any ideas what is happening? If not, is there some way for me
to find out if the server was installed out of order or wrong?
> Thanks
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero|||We verified that the web server is pointed to the virtual server, but we can
not connect to the SQL Server. Cluster Manager reports that everything is w
orking fine, and there was nothing in the cluster.log that indicated a probl
em. Is there any way to ch
eck that the SQL Server was set-up correctly during installation?
--
Johnnie O.
Computer Handyman
Amateur Superhero
"Wayne Snyder" wrote:

> 1. Can anyone connect to the SQL server when it is failed over?... If so,
> that means that the SQL is working..
> 2. Then ensure that the Web server is truly pointing to the virtual server
,
> and that the web server is running..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org|||It sounds like the app has an IP address hard coded in there somewhere. Are
you 100% sure it is using the Virtual server's name and not an IP address?
Andrew J. Kelly SQL MVP
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:D5A6C302-9E2C-4A4F-B16E-105651B602C8@.microsoft.com...
> We verified that the web server is pointed to the virtual server, but we
cannot connect to the SQL Server. Cluster Manager reports that everything
is working fine, and there was nothing in the cluster.log that indicated a
problem. Is there any way to check that the SQL Server was set-up correctly
during installation?
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero
>
> "Wayne Snyder" wrote:
>
so,[vbcol=seagreen]
server,[vbcol=seagreen]
>sqlsql

Cluster @#$ked

I inherited an MS Cluster (2 node) that needs some help. As long as node1 is live, it works fine, but when if fails over, the web server pointed to the virtual server gets nothing back. As far as the cluster is concerned, everything works fine and node2 can even send data to/from the SQL Server. Anyone have any ideas what is happening? If not, is there some way for me to find out if the server was installed out of order or wrong?
Thanks
--
Johnnie O.
Computer Handyman
Amateur Superhero1. Can anyone connect to the SQL server when it is failed over?... If so,
that means that the SQL is working..
2. Then ensure that the Web server is truly pointing to the virtual server,
and that the web server is running..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:663C67E7-5F10-4BC3-AD89-FE6C9AECC6C7@.microsoft.com...
> I inherited an MS Cluster (2 node) that needs some help. As long as node1
is live, it works fine, but when if fails over, the web server pointed to
the virtual server gets nothing back. As far as the cluster is concerned,
everything works fine and node2 can even send data to/from the SQL Server.
Anyone have any ideas what is happening? If not, is there some way for me
to find out if the server was installed out of order or wrong?
> Thanks
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero|||It sounds like the app has an IP address hard coded in there somewhere. Are
you 100% sure it is using the Virtual server's name and not an IP address?
--
Andrew J. Kelly SQL MVP
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:D5A6C302-9E2C-4A4F-B16E-105651B602C8@.microsoft.com...
> We verified that the web server is pointed to the virtual server, but we
cannot connect to the SQL Server. Cluster Manager reports that everything
is working fine, and there was nothing in the cluster.log that indicated a
problem. Is there any way to check that the SQL Server was set-up correctly
during installation?
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero
>
> "Wayne Snyder" wrote:
> > 1. Can anyone connect to the SQL server when it is failed over?... If
so,
> > that means that the SQL is working..
> > 2. Then ensure that the Web server is truly pointing to the virtual
server,
> > and that the web server is running..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
>

Cluster @#$ked

I inherited an MS Cluster (2 node) that needs some help. As long as node1 is live, it works fine, but when if fails over, the web server pointed to the virtual server gets nothing back. As far as the cluster is concerned, everything works fine and node2
can even send data to/from the SQL Server. Anyone have any ideas what is happening? If not, is there some way for me to find out if the server was installed out of order or wrong?
Thanks
Johnnie O.
Computer Handyman
Amateur Superhero
1. Can anyone connect to the SQL server when it is failed over?... If so,
that means that the SQL is working..
2. Then ensure that the Web server is truly pointing to the virtual server,
and that the web server is running..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:663C67E7-5F10-4BC3-AD89-FE6C9AECC6C7@.microsoft.com...
> I inherited an MS Cluster (2 node) that needs some help. As long as node1
is live, it works fine, but when if fails over, the web server pointed to
the virtual server gets nothing back. As far as the cluster is concerned,
everything works fine and node2 can even send data to/from the SQL Server.
Anyone have any ideas what is happening? If not, is there some way for me
to find out if the server was installed out of order or wrong?
> Thanks
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero
|||We verified that the web server is pointed to the virtual server, but we cannot connect to the SQL Server. Cluster Manager reports that everything is working fine, and there was nothing in the cluster.log that indicated a problem. Is there any way to ch
eck that the SQL Server was set-up correctly during installation?
Johnnie O.
Computer Handyman
Amateur Superhero
"Wayne Snyder" wrote:

> 1. Can anyone connect to the SQL server when it is failed over?... If so,
> that means that the SQL is working..
> 2. Then ensure that the Web server is truly pointing to the virtual server,
> and that the web server is running..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
|||It sounds like the app has an IP address hard coded in there somewhere. Are
you 100% sure it is using the Virtual server's name and not an IP address?
Andrew J. Kelly SQL MVP
"JohnnieO" <JohnnieO@.discussions.microsoft.com> wrote in message
news:D5A6C302-9E2C-4A4F-B16E-105651B602C8@.microsoft.com...
> We verified that the web server is pointed to the virtual server, but we
cannot connect to the SQL Server. Cluster Manager reports that everything
is working fine, and there was nothing in the cluster.log that indicated a
problem. Is there any way to check that the SQL Server was set-up correctly
during installation?[vbcol=seagreen]
> --
> Johnnie O.
> Computer Handyman
> Amateur Superhero
>
> "Wayne Snyder" wrote:
so,[vbcol=seagreen]
server,
>

Wednesday, March 7, 2012

Closing all open connections/sessions

Hi all
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:

> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.

Closed conversation endpoints not being purged

We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...

Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.

In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.

Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.

We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.

However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.

I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!

We have tried restarting the SQL Server instance with no effect.

I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.

Has anybody experience this problem and, even better, have any idea how to solve it?

Daniel

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||Yes, we should be closing at both ends. However, I've not yet confirmed this 100%. I was working on the assumption that if the endpoint was in the "closed" state, we must have closed it at both ends (which is what the documentation suggests). Given that SB doesn't seem to be working as expected, perhaps the endpoint state is also suspect? I'll profile the system and see if I can trace the end conversations.|||

The endpoint states meaning is described here: http://msdn2.microsoft.com/en-us/library/ms176082.aspx. CLOSED indeed means both endpoints had issued END CONVERSATION.

What is you message exchange pattern? Who is issuing END CONVERSATION first, the initiator or the target? Is the target ever sending any replies back to the initiator?

Thanks,
~ Remus

|||

Thanks for considering this Remus. I'm a little busy and haven't yet had a chance to look into this to keep comprehensive answers; maybe I can spend more time on this later today.

We have a mixture of message exchange patterns. Most are fire and forget (no response from target to initiator) and in these situations the initiator starts a conversation sends a message and ends its side of the conversation with no delay; the target then picks up the message at some point in the future, processes it and then ends the other side of the conversation. We also have a few request/response conversations; generally we have seperate processes responsible for sending requests and processing responses; there are no pseudo-synchronous conversations.

I may have some more detailed information once I've analysed a trace of service broker activity in this database.

|||

Here are some examples of the service broker activity:

546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator DISCONNECTED_OUTBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Target DISCONNECTED_INBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator CLOSED

221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.083 Initiator STARTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target STARTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator DISCONNECTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Target DISCONNECTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator CLOSED
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:58:38.330 Target CLOSED

C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.977 Initiator STARTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target STARTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Initiator DISCONNECTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Target DISCONNECTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.853 Initiator CLOSED

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

Daniel

|||

The pattern that leaves target endpoints stranded is the one where the target never sends any message (including EndDialog message). So try not to do fire and forget patterns, they have other faults too. If you don't want/can't change this, then you should set up a task to periodically clean them up (using ENd CONVERSATION ... WITH CLEANUP). The criteria is conversation endpoints that are CLOSED and last activity is more than 30 minutes in the past.

HTH,
~ Remus

|||

Daniel Renshaw wrote:

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

I hop I'll g et this right, I'm doing it from memory:

The transition CONVERSING->DISCONNECTED_OUTBOUND is when the initiator issues END CONVERSATION.
The transition CONVERSING->DISCONNECTED_INBOUND is when the EndDialog message sent by initiator arrives at the target
The transition DISCONNECTED_OUTBOUND->CLOSED is when the acknowledgement for the initiators EndDialog message comes back to it.
The transition DISCONNECTED_INBOUND->CLOSED is when the target issues END CONVERSATION in response to the EndDialog message received from initiator.

The pattern shown here is one of the pattern that leaves the target endpoint stranded. Because it never sent any message to the initiator, it never received an acknowledgement. An ack from the initiator sent back to the target serves as positive proof that the initiator will not retry to send message 0. W/o this proof the target cannot delete itself, it has to stay around indefinetly to prevent a replay of message 0, resulting in a stranded target endpoint. That's another reason why the fire-and-forget pattern is ... unrecommended.

BTW, you should also add the EventSubclass column to the profiler view, it details what kind of conversation event we're seeing.

HTH,
~ Remus

|||

Thanks Remus, I'll implement the background cleanup for now. We may be revisting our core principles at some point in the future based on the lessons learnt over the past few months. We've found that tables may have been a more appropriate solution than the many queues we've ended up with.

For example, a message arrives and we put a record in a table as an audit/status information. This continues through the system, each message/step typically generates further records being inserted/updated in regular tables so that we can keep track of what's going on. This has caused tremendous bloating/duplication of data and massive transaction logs so we're now asking ourselves: why not put the message in that same table and forget the queues.

When we come to reconsider all that, we'll ensure fire-and-forget is removed from any service broker elements that remain.

Daniel

|||This saved me from pulling my hair out... I've added it to my blog.

http://www.nootz.net/index.php/notification-services-conversation-cleanup.html

Thanks,
Garick

Closed conversation endpoints not being purged

We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...

Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.

In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.

Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.

We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.

However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.

I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!

We have tried restarting the SQL Server instance with no effect.

I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.

Has anybody experience this problem and, even better, have any idea how to solve it?

Daniel

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Hi Daniel!

Sounds very interesting. Do you close the conversation on both ends of the dialog with an END CONVERSATION statement?
Normally the conversations should go away from sys.conversation_endpoints within 30 minutes as you have wrote...

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||Yes, we should be closing at both ends. However, I've not yet confirmed this 100%. I was working on the assumption that if the endpoint was in the "closed" state, we must have closed it at both ends (which is what the documentation suggests). Given that SB doesn't seem to be working as expected, perhaps the endpoint state is also suspect? I'll profile the system and see if I can trace the end conversations.|||

The endpoint states meaning is described here: http://msdn2.microsoft.com/en-us/library/ms176082.aspx. CLOSED indeed means both endpoints had issued END CONVERSATION.

What is you message exchange pattern? Who is issuing END CONVERSATION first, the initiator or the target? Is the target ever sending any replies back to the initiator?

Thanks,
~ Remus

|||

Thanks for considering this Remus. I'm a little busy and haven't yet had a chance to look into this to keep comprehensive answers; maybe I can spend more time on this later today.

We have a mixture of message exchange patterns. Most are fire and forget (no response from target to initiator) and in these situations the initiator starts a conversation sends a message and ends its side of the conversation with no delay; the target then picks up the message at some point in the future, processes it and then ends the other side of the conversation. We also have a few request/response conversations; generally we have seperate processes responsible for sending requests and processing responses; there are no pseudo-synchronous conversations.

I may have some more detailed information once I've analysed a trace of service broker activity in this database.

|||

Here are some examples of the service broker activity:

546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator DISCONNECTED_OUTBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Target DISCONNECTED_INBOUND
546BC38E-B300-40BC-9A54-2C6F281E928D 2006-09-27 09:35:50.920 Initiator CLOSED

221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.083 Initiator STARTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target STARTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.100 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.113 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.130 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.147 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.160 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.177 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Initiator CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.193 Target CONVERSING
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator DISCONNECTED_OUTBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Target DISCONNECTED_INBOUND
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:48:22.207 Initiator CLOSED
221E2875-FEFD-41B2-B6EF-2C75F5885E32 2006-09-27 09:58:38.330 Target CLOSED

C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.977 Initiator STARTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target STARTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:12.993 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Initiator CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:38:13.180 Target CONVERSING
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Initiator DISCONNECTED_OUTBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.837 Target DISCONNECTED_INBOUND
C1E9F3C6-B10C-4605-8E6A-2CB03D95EE2C 2006-09-27 09:54:17.853 Initiator CLOSED

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

Daniel

|||

The pattern that leaves target endpoints stranded is the one where the target never sends any message (including EndDialog message). So try not to do fire and forget patterns, they have other faults too. If you don't want/can't change this, then you should set up a task to periodically clean them up (using ENd CONVERSATION ... WITH CLEANUP). The criteria is conversation endpoints that are CLOSED and last activity is more than 30 minutes in the past.

HTH,
~ Remus

|||

Daniel Renshaw wrote:

I'm not sure this tells us much but I am suprised about the location of the DISCONNECTED_OUTBOUND messages as I thought our code typcically ended the conversation immediately after sending the message(s) without waiting for a response. Is this because I traced the service broker activity events rather than the actuall T-SQL statements issuing the service broker commands ("END CONVERSATION" etc)?

I hop I'll g et this right, I'm doing it from memory:

The transition CONVERSING->DISCONNECTED_OUTBOUND is when the initiator issues END CONVERSATION.
The transition CONVERSING->DISCONNECTED_INBOUND is when the EndDialog message sent by initiator arrives at the target
The transition DISCONNECTED_OUTBOUND->CLOSED is when the acknowledgement for the initiators EndDialog message comes back to it.
The transition DISCONNECTED_INBOUND->CLOSED is when the target issues END CONVERSATION in response to the EndDialog message received from initiator.

The pattern shown here is one of the pattern that leaves the target endpoint stranded. Because it never sent any message to the initiator, it never received an acknowledgement. An ack from the initiator sent back to the target serves as positive proof that the initiator will not retry to send message 0. W/o this proof the target cannot delete itself, it has to stay around indefinetly to prevent a replay of message 0, resulting in a stranded target endpoint. That's another reason why the fire-and-forget pattern is ... unrecommended.

BTW, you should also add the EventSubclass column to the profiler view, it details what kind of conversation event we're seeing.

HTH,
~ Remus

|||

Thanks Remus, I'll implement the background cleanup for now. We may be revisting our core principles at some point in the future based on the lessons learnt over the past few months. We've found that tables may have been a more appropriate solution than the many queues we've ended up with.

For example, a message arrives and we put a record in a table as an audit/status information. This continues through the system, each message/step typically generates further records being inserted/updated in regular tables so that we can keep track of what's going on. This has caused tremendous bloating/duplication of data and massive transaction logs so we're now asking ourselves: why not put the message in that same table and forget the queues.

When we come to reconsider all that, we'll ensure fire-and-forget is removed from any service broker elements that remain.

Daniel

|||This saved me from pulling my hair out... I've added it to my blog.

http://www.nootz.net/index.php/notification-services-conversation-cleanup.html

Thanks,
Garick

Close Enough?

I'm a VB6 / MS Access programmer trying to get up to speed in the world of
ASP.NET and SQL Server 2000. I'm on a fairly large web database project so
I'm trying to use VisualUML for management of the database schema. I expect
many generations of design as the requirements become defined in an iterate
process. Therefore a DB design tools like VisualUML is highly desirable.
When I worked through the WebMatrix tutorials I built a table in SQL Server
Enterprise Manager whose Create SQL looks like this (generated from SQL
Query Analyzer):
CREATE TABLE [wmt_Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
CONSTRAINT [PK_wmt_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I've been trying to use VisualUML to create the *same* *exact* thing.
Here's my first "maybe" try (from SQL Query Analyzer):
CREATE TABLE [wmt_Orders_2] (
[OrderID] [int] NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I noticed the lack of the CONSTRAINT on the primary key. Here are two more
tries, setting various attributes in VisualUML.
CREATE TABLE [wmt_Orders_3] (
[OrderID] [int] IDENTITY (100, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [wmt_Orders_4] (
[OrderID] [int] IDENTITY (100, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
UNIQUE NONCLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I haven't been able to figure out how to make VisualUML create a table whose
CREATE SQL is identical to that created "by hand." Are any of these "close
enough" to be completely useful? (If so I hope it's wmt_Orders_2 ! <g> )
The project I'm working on has scores of tables and quite a few 0-M:0M
(zero-Many:zero-Many) relationships so I really want to avoid future
problems caused by current ignorance. ;-)
Thanks, & please go easy on the SQL Server 2000 newbie! <g>
-- MarkHi Mark,
I learned you want to use VisualUML to create table DDL as below:
CREATE TABLE [wmt_Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
CONSTRAINT [PK_wmt_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
For this VisualUML question, you can post it into the VisualUML forum for
further support.
http://www.visualobject.com/forums/
http://www.visualuml.com/default.htm
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Friday, February 24, 2012

Client Web Browser & Remote SQL Script Error?

Hello all,

I know that this is a long shot, but I have a problem which someone
reading this group *may* just be able to shed some light on.

We have a new internal personnel planner/attendance system in place. It
uses a web interface to allow members of staff to select their site
location for any week, request leave and record absences.

The server-side scripting is composed of VB/ASP and Javascript which
ultimately queries & writes to an MS SQL Server 2000 database via an
ODBC connection between webserver (IIS6.0).

Here is the problem and I *know* that it sounds unlikely/impossible but
we carried out exhaustive tests.

-> I open a web browser on my local PC (XP Pro SP2)
-> Login to the web-based planning system
-> Update information
-> Submit Changes

At this point I get a script error:

--error message--

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ')'.

/staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108

------

Which is fair enough as it probably points to a problem with the SQL
statement being passed by the .asp page.

***BUT*** I _only_ get this message when I access the page via a browser
from certain PCs!

I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.

I have obviously ensured that it isn't any kind of browser caching issue.

I have no issues using scripted-based web pages in general from the PC
in question.

I have checked the regional settings to ensure that I am using a
standard character set.

I *can* submit database changes via web browsers from most other PCs on
our network, without incurring the error message.

Even when I get the error message, the values are written to the db and
the information is updated.

The other PCs from which I am experiencing the same difficulty are
servers running Win2000 & Win2003 respectively.

Is there any kind of OS/User Profile setting which would mean that
scripts running on a remote webserver would fall over when trying to
perform an SQL write?

Could it be some kind of character encoding issue, which means that the
parameters that are received by the .asp script and then written via the
SQL statement are mangled in any way?

I can't see that there is any kind of local setting that would influence
whether remote scripts would or would not cause an error such as this.

If anyone has any ideas, has encountered anything similar or can shed
light on a system-specific setting or feature that may cause this,
please post your thoughts.

Yours Gratefully

GuyIf the server-side code path is identical, I suggest you focus on the client
browser settings. My guess is that you have client-side script that doesn't
execute under certain configurations so data isn't posted back as expected.
This causes your server-side script to generate an invalid SQL statement
like 'INSERT INTO MyTable VALUES()' and results in the error you describe.

There are other clues in your post that you aren't following Best Practices
so you might want to revisit the items below.

Rather than ODBC, consider using the OLE DB provider directly. For Windows
Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI"

For SQL Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User
Id=MyLogin;Password=myPassword1"

Consider using parameterized SQL Statements rather than build SQL statement
strings. This is more secure, provides better performance and eliminates
the need to double-up embedded quotes and format dates. For example:

myCommand.CommandText = "INSERT INTO MyTable VALUES(?)"
Set myIntegerParameter = myCommand.CreateParameter( _
"@.MyIntegerParameter", 3, 1)
myCommand.Parameters.Append myIntegerParameter
myIntegerParameter.Value = Request("UserValue")

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Guy Debord" <laugh@.life.org> wrote in message
news:tyDIf.45673$Rw6.23215@.newsfe7-gui.ntli.net...
> Hello all,
> I know that this is a long shot, but I have a problem which someone
> reading this group *may* just be able to shed some light on.
> We have a new internal personnel planner/attendance system in place. It
> uses a web interface to allow members of staff to select their site
> location for any week, request leave and record absences.
> The server-side scripting is composed of VB/ASP and Javascript which
> ultimately queries & writes to an MS SQL Server 2000 database via an
> ODBC connection between webserver (IIS6.0).
> Here is the problem and I *know* that it sounds unlikely/impossible but
> we carried out exhaustive tests.
> -> I open a web browser on my local PC (XP Pro SP2)
> -> Login to the web-based planning system
> -> Update information
> -> Submit Changes
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
> ------
> Which is fair enough as it probably points to a problem with the SQL
> statement being passed by the .asp page.
> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!
> I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.
> I have obviously ensured that it isn't any kind of browser caching issue.
> I have no issues using scripted-based web pages in general from the PC
> in question.
> I have checked the regional settings to ensure that I am using a
> standard character set.
> I *can* submit database changes via web browsers from most other PCs on
> our network, without incurring the error message.
> Even when I get the error message, the values are written to the db and
> the information is updated.
> The other PCs from which I am experiencing the same difficulty are
> servers running Win2000 & Win2003 respectively.
> Is there any kind of OS/User Profile setting which would mean that
> scripts running on a remote webserver would fall over when trying to
> perform an SQL write?
> Could it be some kind of character encoding issue, which means that the
> parameters that are received by the .asp script and then written via the
> SQL statement are mangled in any way?
> I can't see that there is any kind of local setting that would influence
> whether remote scripts would or would not cause an error such as this.
> If anyone has any ideas, has encountered anything similar or can shed
> light on a system-specific setting or feature that may cause this,
> please post your thoughts.
> Yours Gratefully
> Guy|||Dan,

Thanks for taking the time to offer up your suggestions.

Using parameterized SQL statements looks like it may well help to
eliminate some of the errors which are easy to make using lengthy
statement strings.

Thanks for the tip.

The OLE DB versus ODBC issue is one that I have one that I will address.

As for the the client-side script suggestion, I'll dig deep and let you
know if I can find any likely suspects.

Thanks again,

Guy|||Guy Debord (laugh@.life.org) writes:
> At this point I get a script error:
> --error message--
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108

I've seen plenty of those pages on the web!

> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!

Well, rather than looking at the PCs, look at the SQL instead. Set up a
trace with Profiler, and see what is being sent to SQL Server. Include
the error events and StmtStarting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thursday, February 16, 2012

Client Printing Hangs

We are accessing SSRS reports using web service. We call the Render the reports using http://<server>/ReportServer/Pages/ReportViewer.aspx?reportName&rs:Command=Render

All reports render fine. However for one particular report when we click the print button on the report viewer in IE, Active-x pops up a message "Printing Now" and hangs eternally.

The error occurs even when I am logged in with Admin privileges to the machine and Active-X client works fine for other reports except this one.

Work-around tried: If I call the same report from Report Manager it works fine. Even on the server I have been able to print the report fine.

The report is just 1 page and the data loaded is also not very heavy. Any idea why client side report printing hangs?

hello... have you solved it yet? I got the same issue too.|||

Hi quantum,

Have you tried unstinstalling and reinstalling the Print Control?

You can set a registry key to on the client machine that will start client print logging. It creates a log file in your temp folder. Here is the key.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Reporting Services]
"LogRSClientPrintInfo"=dword:00000001

You can send me the log file and I can take a look to see if I know what the issue is. Remove the online in my email address.

|||

We are also experiencing the problem from all client PCs. The problem only occurs on one report that contains a subreport. Besides the IE browser, it also hangs when trying to print from Studio 2005.

Has anyone solved this problem yet?

|||We have the same problem. Except that the problem occurs on a report containing a matrix control and no subreports. It started happening with a new version of a report. When I deployed an old version of the same report (that printed nicely earlier), it also hangs during printing.

We have RS 2000 with SP2.|||

Folks, we've seen a similar issue in our testing but were not able to reproduce the scenario for it. Without a stable repro, it makes it close to impossible to identify and fix the issue.

Would it be possible for one/some of you to follow the instructions that Brad provided earlier in this thread and then post a bug with that information, the RDL you're using, and (if possible) a sample dataset that causes the issue on our product feedback site:

http://lab.msdn.microsoft.com/productfeedback/

Thanks for your help on this,

-Lukasz

|||

the issue seems to a problem the local machine and the rights it has to install exes. My machine our organization stop us from installing any exes from the web etc. I have the same printing issue and the browser hangs.

But when I tried the same page from a machine in the DMZ zone which does not have restrictions, the priting is working fine.

|||

We are having the same issue described above, however it only appears to effect the larger reports. The other interesting thing is that we get this error, if the client waits long enough - about 10 min.

INVALID PAGE NUMBER
The requested page does not exist.

Can someone give me some more specifics as to how to run the client logging utiltity mentioned above. I am assuming that the registry edit should be made on the server - not the client? Also, where does the log file go - exactly. If it is in a %temp% directory, is this is the directory for the user under which reporting services runs, or the IIS user, or the local user or what?

We are running:
Windows Server 2003, SP2, 64bit
SQL Server 2000, SP4, clustered, 64bit
IIS 6.0 on Windows Server 2003

|||

Hi Brad,

I couldn't find the registry keys on my machine. We are using SQL Server 2005 and Reporting Services 2005. Reports are being rendered via browser using ReportViewer.aspx with various query string parameters.

I am thinking that this is not a stable approach to render reports from Report Server. Do you know if directly accessing reports from client browser using ReportViewer.aspx is not a recommended approach?

Thanks

Uday Hegde

|||

I am having the same issue, started yesterday. All of a sudden, the report wouldn't print.

here is the data from the printlog

CRSClientPrint:Stick out tonguerint - Starting Function.
reportServerUrl = /ReportServer
reportPath = %2fAR%2fAR0006(b)+Statement+of+Accounts+-+Known+as+Name&rsTongue TiedessionID=wkawsv3n2prf1gv2hnili455
reportName = AR0006(b) Statement of Accounts - Known as Name
CResourceManager:Tongue TiedetLocaleInfo - Starting Function.
SetUICulture - Starting Function.
Attempted UI LCID = 9
Using UI LCID = 1033
SetUICulture - Ending Function.
CResourceManager::LoadResourceDLL - Starting Function.
CResourceManager::LoadResourceDLL - Ending Function: 0x00000000.
CResourceManager::CanUseCurrentLocale - Starting Function.
CResourceManager::CanUseCurrentLocale - Ending Function.
CResourceManager::LoadGDIPlus - Starting Function.
CResourceManager::LoadGDIPlus - Ending Function: 0x00000000.
Attempted culture = 1033
Using culture = 1033
Culture name = English_United States.1252
CResourceManager:Tongue TiedetLocaleInfo - Ending Function: 0x00000000.
CPrintDlg:Stick out tonguerint - Starting Function.
CPrintDlg::InitializePrintDlg - Starting Function.
CPrintDlg::GetDefaultPrinterName - Starting Function.
CPrintDlg::GetDefaultPrinterName - Ending Function: 0x00000000.
CPrintDlg:Tongue TiedetPrinterPaperSize - Starting Function.
CPrintDlg::GetDefaultPaperSetting - Starting Function.
CPrintDlg::GetDefaultPaperSetting - Ending Function: 0x00000000.
CPrintDlg:Tongue TiedetPrinterPaperSize - Ending Function: 0x00000000.
CPrintDlg::InitializePrintDlg - Ending Function: 0x00000000.
CReport:Tongue TiedetReportName - Starting Function.
CReport:Tongue TiedetReportName - Ending Function: 0x00000000.
CReport::Fetch - Starting Function.
CReport::ComparePrintInfo - Starting Function.
Value of diff = 2
CReport::ComparePrintInfo - Ending Function.
CReport::ClearPages - Starting Function.
CReport::ClearFetchedPages - Starting Function.
CReport::ClearFetchedPages - Ending Function.
CReport::ClearPages - Ending Function.
CReport::ResetNextFetch - Starting Function.
Next Fetch set to 1
CReport::ResetNextFetch - Ending Function.
CReport:Tongue TiedtartFetchThread - Starting Function.
CReport::GetPageToFetch - Starting Function.
Page to fetch = 1
CReport::GetPageToFetch - Ending Function.
CReport:Tongue TiedtartFetchThread - Ending Function: 0x00000000.
CReport::Fetch - Ending Function: 0x00000000.
CPrintPreviewDlg::CreatePreviewDialog - Starting Function.
CReport::GetPage - Starting Function.
Page = 1
CReport::FetchThreadMain - Starting Function.
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x00000000
CReport::GetPageWrapper - Ending Function.
CReport::GetPageToFetch - Starting Function.
Page to fetch = 1
CReport::GetPageToFetch - Ending Function.
CReport::FetchAllPages - Starting Function.
CReport:Stick out tongueerformFetch - Starting Function.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CResourceManager:Tongue TiedetInvariantCulture - Starting Function.
CResourceManager:Tongue TiedetInvariantCulture - Ending Function: 0x00000000.
CResourceManager::ResetCulture - Starting Function.
CResourceManager::ResetCulture - Ending Function: 0x00000000.
Url = http://10.1.1.130/ReportServer?%2fAR%2fAR0006(b)+Statement+of+Accounts+-+Known+as+Name&rsTongue TiedessionID=wkawsv3n2prf1gv2hnili455&rs:Command=Render&rs:format=IMAGE&rcSurpriseutputFormat=emfplus&rcTongue TiedtartPage=1&rc:EndPage=65535&rcStick out tongueageWidth=8.5in&rcStick out tongueageHeight=11.0in&rc:MarginTop=3.048mm&rc:MarginBottom=3.048mm&rc:MarginLeft=3.048mm&rc:MarginRight=3.048mm&rsStick out tongueersistStreams=True
CPage::CreateEnhMetaFile - Starting Function.
CPage::CreateEnhMetaFile - Ending Function.
CReport:Stick out tongueerformFetch - Ending Function.
CReport::FetchAllPages - Ending Function.
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x05E76D00
CReport::GetPageWrapper - Ending Function.
CReport::GetPageToFetch - Starting Function.
Page to fetch = 2
CReport::GetPageToFetch - Ending Function.
CReport::GetPage - Ending Function: 0x00000000.
CReport::FetchPage - Starting Function.
CReport:Stick out tongueerformFetch - Starting Function.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CResourceManager:Tongue TiedetInvariantCulture - Starting Function.
CResourceManager:Tongue TiedetInvariantCulture - Ending Function: 0x00000000.
CResourceManager::ResetCulture - Starting Function.
CResourceManager::ResetCulture - Ending Function: 0x00000000.
Url = http://10.1.1.130/ReportServer?%2fAR%2fAR0006(b)+Statement+of+Accounts+-+Known+as+Name&rsTongue TiedessionID=wkawsv3n2prf1gv2hnili455&rs:Command=Render&rs:format=IMAGE&rcSurpriseutputFormat=emfplus&rcTongue TiedtartPage=2&rc:EndPage=2&rcStick out tongueageWidth=8.5in&rcStick out tongueageHeight=11.0in&rc:MarginTop=3.048mm&rc:MarginBottom=3.048mm&rc:MarginLeft=3.048mm&rc:MarginRight=3.048mm&rs:GetNextStream=True
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CReport::GetPage - Starting Function.
Page = 1
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x05E76D00
CReport::GetPageWrapper - Ending Function.
CReport::GetPage - Ending Function: 0x00000000.
CPrintDlg:Big SmilerawFile - Starting Function.
CPrintDlg:Big SmilerawFile - Ending Function: 0x00000000.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CReport::GetPage - Starting Function.
Page = 2
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x00000000
CReport::GetPageWrapper - Ending Function.
CPage::CreateEnhMetaFile - Starting Function.
CPage::CreateEnhMetaFile - Ending Function.
CReport:Stick out tongueerformFetch - Ending Function.
CReport::FetchPage - Ending Function.
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x05EA5908
CReport::GetPageWrapper - Ending Function.
CReport::GetPageToFetch - Starting Function.
Page to fetch = 3
CReport::GetPageToFetch - Ending Function.
CReport::GetPage - Ending Function: 0x00000000.
CPrintDlg:Big SmilerawFile - Starting Function.
CReport::FetchPage - Starting Function.
CReport:Stick out tongueerformFetch - Starting Function.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CResourceManager:Tongue TiedetInvariantCulture - Starting Function.
CResourceManager:Tongue TiedetInvariantCulture - Ending Function: 0x00000000.
CResourceManager::ResetCulture - Starting Function.
CResourceManager::ResetCulture - Ending Function: 0x00000000.
Url = http://10.1.1.130/ReportServer?%2fAR%2fAR0006(b)+Statement+of+Accounts+-+Known+as+Name&rsTongue TiedessionID=wkawsv3n2prf1gv2hnili455&rs:Command=Render&rs:format=IMAGE&rcSurpriseutputFormat=emfplus&rcTongue TiedtartPage=3&rc:EndPage=3&rcStick out tongueageWidth=8.5in&rcStick out tongueageHeight=11.0in&rc:MarginTop=3.048mm&rc:MarginBottom=3.048mm&rc:MarginLeft=3.048mm&rc:MarginRight=3.048mm&rs:GetNextStream=True
CPrintDlg:Big SmilerawFile - Ending Function: 0x00000000.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CReport::GetPage - Starting Function.
Page = 3
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x00000000
CReport::GetPageWrapper - Ending Function.
CPage::CreateEnhMetaFile - Starting Function.
CPage::CreateEnhMetaFile - Ending Function.
CReport:Stick out tongueerformFetch - Ending Function.
CReport::FetchPage - Ending Function.
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x05ED4798
CReport::GetPageWrapper - Ending Function.
CReport::GetPage - Ending Function: 0x00000000.
CReport::GetPageToFetch - Starting Function.
Page to fetch = 4
CReport::GetPageToFetch - Ending Function.
CPrintDlg:Big SmilerawFile - Starting Function.
CReport::FetchPage - Starting Function.
CReport:Stick out tongueerformFetch - Starting Function.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CResourceManager:Tongue TiedetInvariantCulture - Starting Function.
CResourceManager:Tongue TiedetInvariantCulture - Ending Function: 0x00000000.
CResourceManager::ResetCulture - Starting Function.
CResourceManager::ResetCulture - Ending Function: 0x00000000.
Url = http://10.1.1.130/ReportServer?%2fAR%2fAR0006(b)+Statement+of+Accounts+-+Known+as+Name&rsTongue TiedessionID=wkawsv3n2prf1gv2hnili455&rs:Command=Render&rs:format=IMAGE&rcSurpriseutputFormat=emfplus&rcTongue TiedtartPage=4&rc:EndPage=4&rcStick out tongueageWidth=8.5in&rcStick out tongueageHeight=11.0in&rc:MarginTop=3.048mm&rc:MarginBottom=3.048mm&rc:MarginLeft=3.048mm&rc:MarginRight=3.048mm&rs:GetNextStream=True
CPrintDlg:Big SmilerawFile - Ending Function: 0x00000000.
CReport::GetPaperSize - Starting Function.
Height = 11000, Width = 8500 IsMM = 0
CReport::GetPaperSize - Ending Function.
CReport::GetPage - Starting Function.
Page = 4
CReport::GetPageWrapper - Starting Function.
Page wrapper = 0x00000000
CReport::GetPageWrapper - Ending Function.