Best Practice, Stored Procedures & Datasets

Hi,
Would be interested to hear your thoughts on whether is best to minimise or
maximise the use of stored procedures in SQL Reporting. Is it best to have
as much as possible coming from Stored Procedures or is it better to have as
little as possible. At this stage I am more concerned about management
rather than performance. However I'd love to hear arguments from all sides !
Cheers,
JayI prefer to use stored procedures as much as possible. There are two causes
for this: I think it's more efficient to let SQL Server handle the
processing needed to return the correct dataset and just use RS to do the
formatting, and it's usually easier to change the stored procedures later
than your report. But if you know you won't be able to access the SQL server
later, you should put the queries and logic in your RS report.
Kaisa M. Lindahl
"Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Would be interested to hear your thoughts on whether is best to minimise
> or maximise the use of stored procedures in SQL Reporting. Is it best to
> have as much as possible coming from Stored Procedures or is it better to
> have as little as possible. At this stage I am more concerned about
> management rather than performance. However I'd love to hear arguments
> from all sides !
> Cheers,
> Jay
>|||Yes, I would recommend using Stored Procedures as much as possible as you can
usually reuse them in other reports. I find it particualarly useful to use
SPs for returning default paramaters as they are usually the same across most
reports I build and if you need to change any logic you just do it once and
you dont even need to republish the report or anything.
If you store the sql query in the report itself this will become
unmanageable as the amount of reports increase. Any small business logic
change will mean you have to trawl through all your reports to edit your
queries and then republsh the reports. SPs will save you all this hassle.
"Kaisa M. Lindahl" wrote:
> I prefer to use stored procedures as much as possible. There are two causes
> for this: I think it's more efficient to let SQL Server handle the
> processing needed to return the correct dataset and just use RS to do the
> formatting, and it's usually easier to change the stored procedures later
> than your report. But if you know you won't be able to access the SQL server
> later, you should put the queries and logic in your RS report.
> Kaisa M. Lindahl
> "Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
> news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > Would be interested to hear your thoughts on whether is best to minimise
> > or maximise the use of stored procedures in SQL Reporting. Is it best to
> > have as much as possible coming from Stored Procedures or is it better to
> > have as little as possible. At this stage I am more concerned about
> > management rather than performance. However I'd love to hear arguments
> > from all sides !
> >
> > Cheers,
> >
> > Jay
> >
>
>

Best Practice Analyzer for SQL Server 2005?

Hi everyone,

a long time I was using a nice tool called "Best Practice Analyzer Tool for SQL Server 2000". I would be interested whether this product is or will be available for SQL Server 2005.

Does anyone knows something about it?

Regards,

StSt

indeed that a nice tool.

for security best though you can also make use

of MBSA or MS baseline analyzer whichh supports sql server 2005

http://www.microsoft.com/technet/security/tools/mbsa2/default.mspx

|||

Hi,

no I was using the Best Practice Analyzer to check on certain SQL programming "styles" not for security reasons. I.e. I know very well how to optimize my procedures or which statement I should use and which statement I should avoid but it is difficult to make sure that these bpa rules has been respected over the whole database.

Trivial example:

Avoid using SET NOCOUNT OFF ... it might be set to ON in most procedures but there might still be some procedures where it is set to OFF or set by default.

So what I really need is a tool which checks on bpa rules and tells me if there is a violation or not.

Regards,

StSt

|||

i see..

i check mbsa.

sql 2k5 is not supported either.

lets wait....

|||

Hi,

Microsoft is currently working on a BPA for SQL Server 2005.
There is no release date known (at least not public).

Just a little patience ;-)

Regards

|||

That is correct, we have not released a hard date. We are aiming for second half of 2006. For more information you can visit my team's blog... http://blogs.msdn.com/sqlrem/.

Let me know if you have any questions.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

Here is the download link for SQL Server 2005 BPA February CTP

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Best Practice Analyzer for SQL Server 2005?

Hi everyone,

a long time I was using a nice tool called "Best Practice Analyzer Tool for SQL Server 2000". I would be interested whether this product is or will be available for SQL Server 2005.

Does anyone knows something about it?

Regards,

StSt

indeed that a nice tool.

for security best though you can also make use

of MBSA or MS baseline analyzer whichh supports sql server 2005

http://www.microsoft.com/technet/security/tools/mbsa2/default.mspx

|||

Hi,

no I was using the Best Practice Analyzer to check on certain SQL programming "styles" not for security reasons. I.e. I know very well how to optimize my procedures or which statement I should use and which statement I should avoid but it is difficult to make sure that these bpa rules has been respected over the whole database.

Trivial example:

Avoid using SET NOCOUNT OFF ... it might be set to ON in most procedures but there might still be some procedures where it is set to OFF or set by default.

So what I really need is a tool which checks on bpa rules and tells me if there is a violation or not.

Regards,

StSt

|||

i see..

i check mbsa.

sql 2k5 is not supported either.

lets wait....

|||

Hi,

Microsoft is currently working on a BPA for SQL Server 2005.
There is no release date known (at least not public).

Just a little patience ;-)

Regards

|||

That is correct, we have not released a hard date. We are aiming for second half of 2006. For more information you can visit my team's blog... http://blogs.msdn.com/sqlrem/.

Let me know if you have any questions.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

Here is the download link for SQL Server 2005 BPA February CTP

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Benefit of moving Master & TempDB to diff HD

I am interested to hear if people think it would be a good idea to move
the Master & TempDB to a different HD.

Here is my DB Server's set up:
1. Processor: (1) AMD XP 2800
2. 1st HD (IDE 0) is the system & boot drive
3. (3) SCSI HD make up a hardware RAID level 0 (striped without
parity)solution - these striped drives are just for my working DBs
4. (1) SCSI HD that's not doing anything.

I want to put the Master & TempDB on the SCSI HD that's not doing
anything. Would that be the best place for it for maximum performance or
should I put in the striped array. I am leaning more towards putting on
the SCSI HD that's not doing anything. What do you all think?

Ed

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!adude (nospam@.devdex.com) writes:
> I am interested to hear if people think it would be a good idea to move
> the Master & TempDB to a different HD.
> Here is my DB Server's set up:
> 1. Processor: (1) AMD XP 2800
> 2. 1st HD (IDE 0) is the system & boot drive
> 3. (3) SCSI HD make up a hardware RAID level 0 (striped without
> parity)solution - these striped drives are just for my working DBs
> 4. (1) SCSI HD that's not doing anything.
> I want to put the Master & TempDB on the SCSI HD that's not doing
> anything. Would that be the best place for it for maximum performance or
> should I put in the striped array. I am leaning more towards putting on
> the SCSI HD that's not doing anything. What do you all think?

I would not move master.

If that idle HD is on a different controller, moving something could be
good for performance. If you have lots of action in tempdb, this could
be a candidate. You could also consider moving transaction logs to the
idle disc.

If the disk in the same controller as the rest, I think it would be better
to move disk into the stripe. If the IDE disk is slow, you couls still
move tempdn into the RAID.

I need to add the disclaimer that hardware configuration is not my best
game.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Beginner's questions

Hello,

I have two database servers that I am interested in sending messeges between them. I am not interested in security or encryption at this point, only a simple message sending and recieving.

Can someone please provide simple scripts for object setup (certificates, queues etc) and message send and recieve ?

a link to such a script will be great as well. I am just a bit lost understanding all the new concepts (certificates, service, service binding.....)

Thank you

I.

The Service Listing Manager tool from https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx can be used to set this up. It will do all the necesary settings for you, including creating and configuring the endpoints, setting up the routes and setting up security. Give it a try and see if still things look complex after you use it :)

HTH,
~ Remus

|||

I installed the tool and followed the instructions but all it did was to create a document with the service name and endpoint. what do I do from here ?

Thanks

I.

|||

Open the created document, connect to the other machine and select the 'Import' option.

HTH,
~ Remus

|||

Thanks Remus,

That was very helpful. now - I know how to send and recieve messages localy on the same instance. how do I send and recieve messages between two different instances ?

Thank you

I.

|||

The tool does it. Repeat the same steps as for local instance, but simply connect to a different instance when running the Import step. It will do all the necesary setup steps (creat an endpoint, setup endpoint security, setup transport routes in the database etc).

HTH,
~Remus

|||Thanks but I was talking about the Begin Dialog Conversation and recieve commands. is it any different than recieving or sending a message localy ?|||

No differences there. Servicve Broker enables an application designed localy to work distributed w/o any changes.

HTH,
~ Remus

|||

then I guess I have a syntax error...

I am using this code to send message from one instance to another :

BEGIN TRANSACTION ;

GO

DECLARE @.message XML ;

SET @.message = N'<message>Hello, World!</message>' ;

DECLARE @.conversationHandle UNIQUEIDENTIFIER ;

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'tcp://TCA02SQL603:4037','3771F429-0111-488C-9D0E-080226B1DD8F'

ON CONTRACT HelloWorldContract ;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE HelloWorldMessage

(@.message) ;

SET @.message = N'<message>Goodbye!</message>' ;

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE HelloWorldMessage

(@.message) ;

END CONVERSATION @.conversationHandle ;

the message doesn't arrive and in the sys.transmission_queue table I get the error :

'The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.'

|||

The error you mention in the sys.transmission_queue indicates that there is no route for the service named 'tcp://TCA02SQL603:4037'. Perhaps you used the route address instead of the service name? BEGIN DIALOG takes in the name of the target service (e.g. 'TargetService').

HTH,
~ Remus

|||

OK, I indeed used the route address instead of the service name so I replaced it :

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'TargetService','3771F429-0111-488C-9D0E-080226B1DD8F'

ON CONTRACT HelloWorldContract ;

However, I still get the same message. I must specify that in sys.transmission_queue the

to_service_name column = 'TargetService,3771F429-0111-488C-9D0E-080226B1DD8F'

and the 'to_broker_instance' is empty.

|||

This means you had a typo in the BEGIN DIALOG statement and you actually used TO SERVICE 'TargetService, 3771F...' instead of 'TargetService','3771F...'. You should see 'TargetService' in to_service_name column and '3771F..' in to_broker_instance column.

Also, make sure the row in sys.transmission_queue is the one from your lastes actual BEGIN DIALOG, and not left there from previous attempts. To clean up failed attempts, use END CONVERSATION ... WITH CLEANUP

HTH,
~ Remus

|||

Thanks Remus, that really helped.

I am getting a different error message now :

Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.

|||

I think I deleted your post by mistake.

Ilan D wrote:

ok. for some reason 'End conversation @.CoversationHandle WITH CLEANUP' doesnt clean up the sys.transmission_queue table. any suggestions ?

I tried to make sure that I use ',' between service and instance. it's really hard to tell which error message I got because the table doesn't clean up... but I believe that the error message I am getting right now is 'The private key for the security certificate bound to the database principal (ID 1) is password protected. Password protected private keys are not supported for use with secure dialogs.'

You need to cleanup the particular @.conversationHandle that shows in the conversation_handle column in sys.transmission_queue. You need to do this individually for each conversation you wish to clean up.

The error indicates that the 'dbo' certificate is encrypted by password and the database master key encryption is missing. I assume you used the Service Lsiting Manager tool to setup the certificates. I believe the problem then lieas with 'dbo' having some aditional certificates marked as 'active for begin dialog' that are not the ones deployed by the tool. Lookup in sys.certificates what other certificates are owned by 'dbo' and mark them as not active for begin dialog (ALTER CERTIFICATE [...] WITH ACTIVE FOR BEGIN_DIALOG = OFF, see http://msdn2.microsoft.com/en-us/library/ms189511.aspx)

HTH,
~ Remus

|||

I'd recommend to use certificate based authentication for the endpoints, is easier to get it right.

The error you see is because the NTLM/Kerberos authentication ends up with the anonymous login. The best document to solve these kind of issues is this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

Remeber that the accounts being authenticated are those of that are actually running the two SQL Server services. If the SQL Server instances are running as LocalSystem or NETWORK SERVICE, then you must register the SPN 'MSSQLSvc/<machinename>:<brokerport>' for Kerberos to work (use a tool like setspn.exe, available at MS download center).

HTH,
~ Remus