May 20, 2009 at 7:57 am
Hi All
I have quite a few questions and thought instead of starting separate threads, I ask it all in one.
1) I know SQL 2005/2008 has built in maintenance tasks, but is it advisable to use of just write custom stored procedures and schedule through SQL Agent to look at things like fragmentation, re-indexing, updating stats, backup to disk etc.
2) Managing security, how do you currently do this, I like to keep everything scripted, so to grant users permissions, I already have similar scripts maybe to another user, I just copy & paste and put in the new login name. How's this stored bearing in mind that it would have passwords a times and how is it managed centrally, lets say other SA's might need access to it in an emergency.
3) I have read about certificates in SQL 2005, I'm still trying to find a practical day to day use to justify it, maybe a good way to do this would be to hear how other DBA's use certificates in SQL 2005 and in what situatio/enviroment. Any benefits etc ? Any gotchas ??
May 20, 2009 at 1:27 pm
Dean Jones (5/20/2009)
Hi AllI have quite a few questions and thought instead of starting separate threads, I ask it all in one.
1) I know SQL 2005/2008 has built in maintenance tasks, but is it advisable to use of just write custom stored procedures and schedule through SQL Agent to look at things like fragmentation, re-indexing, updating stats, backup to disk etc.
It really depends. If you have, or are acting as, the dedicated DBA in the company and you're going to take the time to customize and control the behavior of the various maintenance routines so that you get optimal performance, yeah, go with scripts. They give you a lot more control over what happens when. However, if you're only a part time DBA, you might want to consider just taking advantage of the out of the box maintenance routines for most situations.
2) Managing security, how do you currently do this, I like to keep everything scripted, so to grant users permissions, I already have similar scripts maybe to another user, I just copy & paste and put in the new login name. How's this stored bearing in mind that it would have passwords a times and how is it managed centrally, lets say other SA's might need access to it in an emergency.
Generally, I define a role within a database. I then give permissions to that role. I assign an NT group, not individuals, to that role and then I call up the network & security admins and tell them which group to add people to. If I do need to grant access to individual logins, I still only grant them through the role. That way the role is defined with the database, backed up with it, etc. and you can simply add or remove people to give, or take away, privileges.
3) I have read about certificates in SQL 2005, I'm still trying to find a practical day to day use to justify it, maybe a good way to do this would be to hear how other DBA's use certificates in SQL 2005 and in what situatio/enviroment. Any benefits etc ? Any gotchas ??
I've never used them, so I don't want to comment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply