Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Preproduction Tasks


SQL Server Preproduction Tasks

Author
Message
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1312 Visits: 2614
Comments posted to this topic are about the item SQL Server Preproduction Tasks

Ken Simmons
http://twitter.com/KenSimmons
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54872 Visits: 40396
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
humbleDBA
humbleDBA
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 1508
Ditto Jeff's comments, Ken.
magarity kerns
magarity kerns
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 397
Excellent checklist! May I add a caveat to #1: The latest patch level supported by the front end app's picky vendor (if applicable).
Steven Webster-494809
Steven Webster-494809
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 329
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
Mohit K. Gupta
Mohit K. Gupta
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 1089
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.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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


How to ask for help .. Read Best Practices here.
Mohit K. Gupta
Mohit K. Gupta
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 1089
Oops forgot to ask questions regarding the article Tongue.

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

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

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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


How to ask for help .. Read Best Practices here.
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1312 Visits: 2614
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

Ken Simmons
http://twitter.com/KenSimmons
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1312 Visits: 2614
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.

Ken Simmons
http://twitter.com/KenSimmons
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6557 Visits: 1407
Well written. Excellent article...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search