Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server Preproduction Tasks Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 8:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
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.


---

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.


How to ask for help .. Read Best Practices here.
Post #609171
Posted Wednesday, November 26, 2008 8:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
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?


Ken Simmons
http://twitter.com/KenSimmons
Post #609174
Posted Wednesday, November 26, 2008 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #609198
Posted Wednesday, November 26, 2008 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:25 AM
Points: 36, Visits: 259
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.
Post #609222
Posted Wednesday, November 26, 2008 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #609231
Posted Wednesday, November 26, 2008 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 3:25 AM
Points: 36, Visits: 259
Some very good pointers there guys, thanks a million. I'd imagine some kind of archiving process along with a server side trace would suffice, particularily if the trace was set up to capture more than the error logs would.

Thanks again!
Post #609253
Posted Wednesday, November 26, 2008 10:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
Very nice article! It runs where the tires meet the road with specifics that even the newest to the product can eventually digest. This is good list to keep and train with. It also in effect shows more of what a DBA does, on a level that even management can digest, and demonstrates that a database is not just a maintenance-free "bucket" some place to dump and retrieve data.
Post #609315
Posted Wednesday, November 26, 2008 12:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
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.


Isn't an Auditor asking you to hack the registry kind of like an Oxymoron anyway? Should't they be making sure we are using the reccomended settings instead of ones that are not supported?


Ken Simmons
http://twitter.com/KenSimmons
Post #609375
Posted Thursday, November 27, 2008 3:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:05 AM
Points: 4,427, Visits: 4,178
I would add two more tasks to the checklist
1. Remove Built-in admiinstrators
2. Disable or at least rename the sa account (in 2005 or higher).

About hte loging of succesful logins I would recommend to use a LOGIN TRIGGER which records the login and the last time it connected. This avoids filling up the SQL errorlog and it's much easier to search through in case you want to know when was the last time a login was used.


Markus Bohse
Post #609653
Posted Thursday, November 27, 2008 6:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
MarkusB (11/27/2008)
I would add two more tasks to the checklist
1. Remove Built-in admiinstrators
2. Disable or at least rename the sa account (in 2005 or higher).

About hte loging of succesful logins I would recommend to use a LOGIN TRIGGER which records the login and the last time it connected. This avoids filling up the SQL errorlog and it's much easier to search through in case you want to know when was the last time a login was used.


I was considering removing Bulti-In Administrators but wasn't sure if that is a good idea or not. I thought I am just being over jelouse by not wanting to give server operations any permissions on the SQL Servers.

But as for SA account I don't think it needs to be renamed because you shouldn't be using the SA for day-to-day work anyways. I tend to leave the SA account name as is, but set a strong password with at least 15 characters, caps, lower-case, number, symbols, etc.

Thanks.


---

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.


How to ask for help .. Read Best Practices here.
Post #609786
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse