SQL Server Preproduction Tasks

  • Comments posted to this topic are about the item SQL Server Preproduction Tasks

  • Very nicely done, Ken. Lot's o' work went into that one. Great job.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ditto Jeff's comments, Ken.

  • Excellent checklist! May I add a caveat to #1: The latest patch level supported by the front end app's picky vendor (if applicable).

  • Great article Ken. Much appreciated.

    Can I ask your views on the following - I recently had auditors in and one of their recommendations was to increase the maximum number of error log files to a value of 25000 or higher via a registry hack. In the vulnerability report they give the following reason for this:

    To prevent the loss of auditing data, it is recommended that you set this value high enough that the error logs will not be overwritten when restarting the database. Also note that there is a stored procedure, sp_cycle_errorlog, that closes an errorlog and creates a new file. An attacker could attempt to cover their tracks by overwriting files using this stored procedure. It is recommended that you set the value high enough that an attacker could not cycle the logs enough times in a reasonable amount of time to overwrite the error log containing an attack.

    Many thanks

    Steven

  • Steve,

    For auduting purpose I would recommend saving up the Default Trace log; depending on your work load, you can save that daily, or multiple times/day. And that trace log has alot more information the SQL Server error logs do.

    - Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Oops forgot to ask questions regarding the article :P.

    Point #2, Enable Successful and Failed logs ...

    I understand having that on for the first little while in a new system to make sure you know who is hitting the system. But by enabling both Successfull and Failed will it not fill up the error log alot quicker. Like for example on some of my serers there are over 3000 successful connections/day. I track successful for a while when setting up new server to make sure things are funcitonal; then I switch to Failed log ons only.

    Recommendations are both or just failed? Comments?

    Point #7, Create a maintenance database ...

    I just starting doing that recently on SQL Server 2000 environment because we needed to track access to the user/database login. And be able to report it out quickly. I am also using this for storing some Stored procedure for selective reindexing? What else can be in this database?

    Thanks for the Article, really good pointers :D.

    I will be reading the articles you posted today they look really good :D. :w00t:

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Failed logins are really the most important. It just depends on how busy your system is. It is all preference on what you would like to monitor.

    I use the maintenace database to hold procedures and tables for...

    reindexing, capturing database size for trending, capturing critical events from the error log, etc.

    Jonathan Kehayias did a real good presentation called Automating Common DBA Tasks that has a lot of other good ideas. It can be found under the live meeting recordings here.

    http://www.sqlpass.org/Community/SIGs/DatabaseAdministrationSIG/tabid/80/Default.aspx

  • Jeff Moden (11/25/2008)


    Very nicely done, Ken. Lot's o' work went into that one. Great job.

    Thanks Jeff. It really wasn't that much work. I just wanted to share some of the things I do (or at least think about doing) when setting up a server.

    I would love to hear what some other people do as well. I am always looking for new ideas to help improve performance and maintain system stability.

  • Well written. Excellent article...

  • Ken Simmons (11/26/2008)


    Failed logins are really the most important. It just depends on how busy your system is. It is all preference on what you would like to monitor.

    I use the maintenace database to hold procedures and tables for...

    reindexing, capturing database size for trending, capturing critical events from the error log, etc.

    Jonathan Kehayias did a real good presentation called Automating Common DBA Tasks that has a lot of other good ideas. It can be found under the live meeting recordings here.

    http://www.sqlpass.org/Community/SIGs/DatabaseAdministrationSIG/tabid/80/Default.aspx

    Thank-you Mr. Ken. I'll add that to my articles to read/watch ;-). Thanks again for a important article :D.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Steven Webster (11/26/2008)


    Great article Ken. Much appreciated.

    Can I ask your views on the following - I recently had auditors in and one of their recommendations was to increase the maximum number of error log files to a value of 25000 or higher via a registry hack. In the vulnerability report they give the following reason for this:

    To prevent the loss of auditing data, it is recommended that you set this value high enough that the error logs will not be overwritten when restarting the database. Also note that there is a stored procedure, sp_cycle_errorlog, that closes an errorlog and creates a new file. An attacker could attempt to cover their tracks by overwriting files using this stored procedure. It is recommended that you set the value high enough that an attacker could not cycle the logs enough times in a reasonable amount of time to overwrite the error log containing an attack.

    Many thanks

    Steven

    I am not sure I would want to use a registry hack. I would see if I could archive the logs somewhere or something. I would just make sure to test it first. I know it is a registry setting that gets changed when you script it out, but I have never tried anything over the maximum value of 99.

    It wonder if it would get reset after a service pack upgrade?

  • Nice article Ken. Makes me think I need to be doing more. I particularly like the fact that you included all the links in the article. This could/should be a must read.

  • I'd certainly agree that a registry hack is not the way forward. Unfortunately though where I work an auditors word is always taken ahead of mine! Interesting point tho whether the registry would be overwritten if a service pack were to be applied - I'll test that out and get back.

  • Steven Webster (11/26/2008)


    I'd certainly agree that a registry hack is not the way forward. Unfortunately though where I work an auditors word is always taken ahead of mine! Interesting point tho whether the registry would be overwritten if a service pack were to be applied - I'll test that out and get back.

    Maybe Ken's idea of archiving the error logs would work for you and the auditors.

    Also you could use the Default Trace or a server-side trace set to start at startup to do the auditing necessary and archive that data as well.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply