Post reply

SQL Server Preproduction Tasks

  • November 25, 2008 at 11:06 pm

    #215879

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

  • November 25, 2008 at 11:08 pm

    #903564

    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.

    Change is inevitable... Change for the better is not.

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

  • November 26, 2008 at 1:36 am

    #903586

    Ditto Jeff's comments, Ken.

  • November 26, 2008 at 3:09 am

    #903601

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

  • November 26, 2008 at 3:35 am

    #903612

    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

  • November 26, 2008 at 7:49 am

    #903738

    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].

  • November 26, 2008 at 7:52 am

    #903743

    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].

  • November 26, 2008 at 8:22 am

    #903789

    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

  • November 26, 2008 at 8:32 am

    #903802

    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.

  • November 26, 2008 at 8:34 am

    #903804

    Well written. Excellent article...

  • November 26, 2008 at 8:41 am

    #903811

    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].

  • November 26, 2008 at 8:43 am

    #903814

    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?

  • November 26, 2008 at 9:01 am

    #903835

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 26, 2008 at 9:15 am

    #903855

    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.

  • November 26, 2008 at 9:19 am

    #903863

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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