Best Practices question:

Hi,
When I run BPA I get this message:
Database Name ANSI_NULLS ANSI_PADDING_ENABLED ANSI_WARNINGS ARITHABORT
CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIERS NUMERIC_ROUNDABORT Result
[Recommended Settings] ON ON ON ON ON ON OFF -
Axapta OFF OFF OFF OFF OFF OFF OFF Failed
Even though I have turn them ON in the server properties, and in the
database properties as well
(2 total qualifying rows in repository table
[bpa_rule_tbl!DBSQLOPTIONS!1.0] )
Copy Close
begin 666 copy.gif
M1TE&.#EA$ `0`.8``/___U&#T35)8Y.BM[+"V(JN_/S]_J[$\%R,VIFLQW.A
MWH&NY:Z_U)BKQI*P[=G=XBE4JBE4K&Z<VF".Y[_+W=+>\,?2X 25,FW^G\R-(
MEOO]_IRUYV:"I=OD]Z[0__7V^Y:FNZFYST=NNJO"[Z.RQ];>[*"Y[^OO]H6G
MZZFXSIRKP;K:_]+;\62%R2=0HMGD^9_%^I>IQ#5EQ=[BZ<_9YB!!AO?X^]?
MZBI7LW.2T9RPSK[.\/GZ_-/>]?;Y_;+)\*^^U"5-G<73\I>FN_#T^_#S^&B7
MU9NOS)JNS")%CIJMRIJNREMRCZ*QQRM7L2=0I(*?V.KO^U5YP <_;\9"JX*:]
M[*.RR+K%U/#R^&Z7YW*:ZJK![^#F\8ZX\*_#[2-'DB%#B25*F")&CR%#BLW7
M[>;J\____P``````````````````````````````````````` ```````````
M````````````````````````````````````````````````` "'Y! $``&8`
M+ `````0`! ```>F@.&:"@.X2%9@.0$# PI5C.&@.P0`D@.!-' ^/9D"3!B!73)>&
M(0`K'A4#!R-;)@.X.+X,DDCQ890>3`"@.R4X(J`#!="PI5MEH_3F1F0P8?)UPE
M&P`%!2P3.!%/9@.,VP!)&5)-$'4)(0=A%-S06%% `&%D(`0%+8=@."]?4Y`!I1
M/3M>"5^/6@.!X!R] @.R2/I/C0<41)@.@.8QP#P2`0&"BPL9Q(RI@.:FC1T.!```[
`
end
begin 666 arrow.gif
M1TE&.#EA'@.`9`/<``,# P !P``!O``&1`0)\`@.)Y`@.2?! 6?!05^!0BC" JM
M"@.=\!PZQ#@.VD#0I^"@.N "P^C#PU_#1.R$Q2O%!6D%1BV&!&#$0]O#Q*"$AJS
M&AFL&1BB&!-]$QRQ'!RC'!F-&2&U(1B!&!=Y%Q9T%A^>'QAX&"2O)!EW&1^,
M'QIV&B2>)!U_'2*1(B61YZ'B:9)B.,(R%^(2F:*2F7*2J7* B-^(R-](RN8
M*RJ1*BR6+"V5+2Z4+C"9,#"8,"^4+R^3+S&8,3"5,#BJ.#.9, S:<-C6:-3FA
M.3><-SB<.#J=.CN>.T"J0#R?/#V?/4&H03^@./T*F0C^?/T&A04.B0T*@.0D*?
M0D:G1D6E14:C1D6B14FG24BD2$FD24BB2$JD2DRG3$NE2TRE3 $ZH3DZG3DRC
M3$^G3U*H4E&F45.I4U*F4E2I5%:K5E2H5%JL6EFK65NM6UJK6 EVN75ZO7EZM
M7F&O86"N8&*P8F.Q8V.P8V6R96BT:&>S9V>R9VJT:FRV;&NU: W"X<&^W;W:[
M=G>[=WF\>7J]>H# @.(S&C(W&C7^P?X6WA8J[BJ/6HYW/G;+8LK39M/___^;F
MY@.``````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $`````+ `````>`!D`0 C_``$('$BPH,&#
M!B/)*00(#YPTD"(.X='#AXU(DA 6C/0AQY$F3(PP@.F2B@.P0%!@.I@.U,BRI4N!
MD6+*G#GS)<PH:"+JW F)1(B5-B4)'4ITJ,VC2",-:G2($* ^>>S$63-&BH\3
M0%M&*G.'CILS70T?F"1HRL+"/EH *FS!<K2W1FF #A`5J-D11=&)$B10D1
M'"(X6$ @.0**[2!,K7LP8821'@.O:\45/&"Q@.L3Y0`@.;$HDLU(?Q 9"L2GSB-(
M8;A(21+$!>*#D=#XT3.'#9DJ.F?<T%'C=<(L;7CR;+'"]\8B8,R@.$:/%B1"=
M&C9@.,$XP$@.LD4[9<@.31 4&"1!0'Q\8R0(*'#]VY)"AP@..%!@.<&"!A/GJ;]
*F!D;Z]\_," `.P``
`
end
Is there some client connection that's chaging tehse settings? You could
run a profiler for some time to check.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Best Practices Analyzer contradicts BOL for SQLSERVERAGENT

The 2005 Best Practices Analyzer (July 2007) contradicts Books Online about what account to run SQLSERVERAGENT under.

BPA says use Network Service Account, BOL explicitly says not to use Network Service Account:

BPA gives a warning saying "SQL Server Service [MY-PC\SQLSERVERAGENT] In Unrecommended Account On Host [MY-PC]. We recommend that the service [SQLSERVERAGENT] on host [MY-PC] be run under Network Service Account. Currently it is designated to run under the account [SOMEDOMAIN\someaccount]."

BooksOnline says "Security Note: Because multiple services can use the Network Service account, it is difficult to control which services have access to network resources, including SQL Server databases. We do not recommend using the Network Service account for the SQL Server Agent service."

So who's right ? BPA or BOL ?

Books online is correct - a non-system account provides more control over access, security.

Just curious...which version of BPA are you running?

-Sue

sql

Best Practices Analyzer

Anyone know if there is a BPA coming for SQL 2005?
Thank you
Hey Clint,
I'm the Program Manager responsible for the next version of Best Practices
Analyzer which will support SQL Server 2005. We are very early in the
development process and would like to get some customer input. If you'd
like to put in your two cents, go to the SQL Relational Engine Manageability
Team's blog and leave a comment on this post:
http://blogs.msdn.com/sqlrem/archive...06/570292.aspx
Thanks,
Paul A. Mestemaker II
Program Manager
Microsoft Corporation
SQL Server Relational Engine Manageability Team
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:A801888E-6B53-42E2-AF09-C9C9F0D17377@.microsoft.com...
> Anyone know if there is a BPA coming for SQL 2005?
> Thank you

Best Practices Analyzer

Why do I keep getting a login error box when I try to login to the BPA? I am
logging in with the same ID as I used to create the repository.
Hi Jim
BPA just tunnels through the connection information.
Can you connect from query analyzer?
Can you please provide more info on the error that you are getting?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
> Why do I keep getting a login error box when I try to login to the BPA? I
am
> logging in with the same ID as I used to create the repository.
|||Yes I can get in with Query Analyzer. All I get is a pop up window that says Login Error with a red x and a button to click ok.
-- Christian Kleinerman [MS] wrote: --
Hi Jim
BPA just tunnels through the connection information.
Can you connect from query analyzer?
Can you please provide more info on the error that you are getting?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
> Why do I keep getting a login error box when I try to login to the BPA? I
am
> logging in with the same ID as I used to create the repository.
|||From your post seems that repository database was created successfully
during setup. This would tell me that your managed stack is working well.
(i.e. SqlClient)
Can you try to login with profiler attached capturing logon and error
events, and verify 1) whether a connection is being established and 2)
whether any error is being reported?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:DB5F277F-6FFC-4C3B-902B-E1885A884A4C@.microsoft.com...
> Yes I can get in with Query Analyzer. All I get is a pop up window that
says Login Error with a red x and a button to click ok.
> -- Christian Kleinerman [MS] wrote: --
> Hi Jim
> BPA just tunnels through the connection information.
> Can you connect from query analyzer?
> Can you please provide more info on the error that you are getting?
> Thanks,
> - Christian
>
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "JimW" <anonymous@.discussions.microsoft.com> wrote in message
> news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
BPA? I
> am
>
>
|||This is what I get from profiler with all error possibilities and other things.
10NULLNULLNULLNULLNULLNULLNULL2004-05-19 08:53:32.880NULLNULLNULL
211exec sp_reset_connectionjweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.940NULLNULLNULL
310exec sp_reset_connectionjweber0SQL Best Practices AnalyzerWPSR\jweber5102004-05-19 08:53:36.940000
440select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' or
name jweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.940NULLNULLNULL
5114select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' o
r name jweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:36.957NULLNULLNULL
612select * from sysobjects where name = N'bpa_active_connections' or name =N'bpa_bestpractices' or name =N'bpa_files' or name =N'bpa_instance_results' or name =N'bpa_managed_types' or name =N'bpa_properties' or name =N'bpa_bestpractice_sqlservers' or
name jweber0SQL Best Practices AnalyzerWPSR\jweber51162004-05-19 08:53:36.940374016
715NULLjweber0SQL Best Practices AnalyzerWPSR\jweber511929232004-05-19 08:50:24.033413016
814-- network protocol: TCP/IP set quoted_identifier on set implicit_transactions off set cursor_close_on_commit off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set language us_english set dateformat mdy set
datefjweber0SQL Best Practices AnalyzerWPSR\jweber51NULL2004-05-19 08:53:37.003NULLNULLNULL
95NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
-- Christian Kleinerman [MS] wrote: --
From your post seems that repository database was created successfully
during setup. This would tell me that your managed stack is working well.
(i.e. SqlClient)
Can you try to login with profiler attached capturing logon and error
events, and verify 1) whether a connection is being established and 2)
whether any error is being reported?
Thanks,
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"JimW" <anonymous@.discussions.microsoft.com> wrote in message
news:DB5F277F-6FFC-4C3B-902B-E1885A884A4C@.microsoft.com...
> Yes I can get in with Query Analyzer. All I get is a pop up window that
says Login Error with a red x and a button to click ok.[vbcol=seagreen]
> BPA just tunnels through the connection information.
> Can you connect from query analyzer?
> Can you please provide more info on the error that you are getting?
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
rights.[vbcol=seagreen]
> news:E9D0888C-B01E-426A-9A74-E27E3C4113FE@.microsoft.com...
BPA? I[vbcol=seagreen]
> am

best practice analyzer feedback

Hi I've been running the BPA for a few weeks now and have followed all the
recommendations to correct my databases, but I consistently get the following
2 Non Compliance errors.
1. One or more of the databases failed the scan. It is recommended that data
and log files be kept on separate drives.
I've tried to move the log files but it won't let me even when nobody is
using the databases.
2. One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT
setting! It is generally recommended to explicitly set NOCOUNT option to ON
at the beginning of a trigger.
Here is the trigger reffered to:-
CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
FOR INSERT
AS
IF @.@.ROWCOUNT=0
RETURN
SET NOCOUNT ON
INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
FROM Inserted
As can be seen the SET NOCOUNT ON is included and it won't work if it's at
the beginning of the trigger.
The rest of the tool seems to work OK.
1 - Try to detach database first then move log file, then attach database
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> Hi I've been running the BPA for a few weeks now and have followed all the
> recommendations to correct my databases, but I consistently get the
following
> 2 Non Compliance errors.
> 1. One or more of the databases failed the scan. It is recommended that
data
> and log files be kept on separate drives.
> I've tried to move the log files but it won't let me even when nobody is
> using the databases.
>
> 2. One or more triggers is either setting NOCOUNT to OFF or missing
NOCOUNT
> setting! It is generally recommended to explicitly set NOCOUNT option to
ON
> at the beginning of a trigger.
> Here is the trigger reffered to:-
> CREATE TRIGGER trEnterRelatedEntitlement ON [dbo].[tblEmployee]
> FOR INSERT
> AS
> IF @.@.ROWCOUNT=0
> RETURN
> SET NOCOUNT ON
> INSERT INTO dbo.tblEntitlement (strLogonName, intYear)
> SELECT strlogonname, YEAR(CURRENT_TIMESTAMP)
> FROM Inserted
> As can be seen the SET NOCOUNT ON is included and it won't work if it's at
> the beginning of the trigger.
> The rest of the tool seems to work OK.
>
>
|||Thanks t, detach and attach worked great. Do you have any idea about the
NOCOUNT problem?
jez
"t" wrote:

> 1 - Try to detach database first then move log file, then attach database
> "jez123456" <jez123456@.discussions.microsoft.com> wrote in message
> news:2A879E25-DA67-4A0E-9CC8-A06A3D396B43@.microsoft.com...
> following
> data
> NOCOUNT
> ON
>
>
|||Hi
It is an interesting pattern... I'll open a tracking item to see if we can
relax the check for the next release.
Thanks for the feedback.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"jez123456" <jez123456@.discussions.microsoft.com> wrote in message
news:D67E5CBA-C13E-4272-A100-B613BD7F4E82@.microsoft.com...[vbcol=seagreen]
> Thanks t, detach and attach worked great. Do you have any idea about the
> NOCOUNT problem?
> jez
> "t" wrote:
database[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
is[vbcol=seagreen]
to[vbcol=seagreen]
it's at[vbcol=seagreen]

Best Practice Analyzer & Custom Best Practices

Hi All,
The BPA is a great tool that can be used to audit an environment for
adherenace to proper configuration and basic T-SQL coding best practices.
Does anyone know if the BPA tool can be customized to include custom
best-practice rules that are in-addition to the standard best practices
included with the tool? For example, say for example if an orgainzation
would like to add custom rules that look for the existance of specific
UserID, specific SQLAgent Jobs, etc.
Thanks in advance for your assistance.
Dave
This is a feature request that the SQL team are well aware of but as you can
imagine with SQL2005 nearing the end of a long road (near being a relative
term!) it might be a while before a version of SQLBPA is released that
supports user defined rules.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:405E1559-6CB7-4371-822B-CE4C523A5EF3@.microsoft.com...
> Hi All,
> The BPA is a great tool that can be used to audit an environment for
> adherenace to proper configuration and basic T-SQL coding best practices.
> Does anyone know if the BPA tool can be customized to include custom
> best-practice rules that are in-addition to the standard best practices
> included with the tool? For example, say for example if an orgainzation
> would like to add custom rules that look for the existance of specific
> UserID, specific SQLAgent Jobs, etc.
> Thanks in advance for your assistance.
> Dave