SQL Server Performance

SQL Server Properties not in sp_configure configuration options

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Aug 3, 2013.

  1. Trev256b Member

    I am currently reviewing all sql server properties and configuration options for sql server 2008 R2 before doing the same for 2012. I will be documenting best practices for all settings as currently there is not a single document that handles this. I have noticed the following SQL Server properties that are useful to document are not in the sp_configure configuration options:
    a) Server authentication.
    b) Login auditing.
    c) Server proxy account.
    d) Full Text Upgrade Option.

    1) Does anyone know if they are listed in sp_configure, and if not is there an easy script to obtain this information rather than just looking at the server properties?

    2) Also, are there any other properties or configuration options that are not listed in sp_configure, but would also be useful to document?

    Let me know if you want a final link to this document and I will post this.
  2. davidfarr Member

    You might find yourself comparing apples with oranges here.

    Server properties and server configurations are different things by design, and should not necessarily exist in the same place. This is why many server properties would not be found in sp_configure.

    You make a rather bold claim that no single document exists regarding best practices.
    I am fairly certain that several such documents exist regarding properties and configurations, in so far as those that actually require a 'best practice' recommendation.

    It is important to note here that many properties and configurations have a default setting, which is determined by Microsoft, and which can be assumed to be 'best practice' if no specfic alternative is required.
    Equally true is that 'best practice' can depend entirely on the nature of the server's business purpose and on the type of databases it will host.
    As an analogy; Would 'best practice' for a paint job be a brush, roller or spray ? Depends entirely on the paint type and paint surface. Horses for courses.
    Trev256b likes this.
  3. Trev256b Member

    Yes - good point. I'm just collating a list of SQL Server properties and (other) configurable options - so I can make a thorough standards document to cover all properties/options. Please let me know if you know of any other 'properties' that are not covered by the 'options'. However, I think I have covered enough for now.

    There may well be a single document for this, however, I haven't found one and all the DBAs including microsoft consultants haven't got one. They mainly rely on the microsoft defaults which you have mentioned.

    I'm trying to create a standards document which could be used as a starting point at least, as a few Microsoft defaults are a bit too 'default' and not very suitable for most situations or 'best practice'.

    Thanks again for your views. Please let me know if there are any 'key' server properties that are not listed in the sp_configure that I could consider developing a best practice or standard for. For example, I was thinking of including the SQL Server agent properties.

Share This Page