New SelectViews Show Posted
SelectViews – Brent Ozar is on the show today, talking about all sorts of SQL Server issues, things from the field and yes, opinion.
I also sat down with the good folks from XKoto to talk about some of the really cool things they’re doing for SQL Server.
[Watch the Show]
Being Proactive with your SQL Server Tasks
Sherrie wrote in with some real-world experience in doing what’s needed on systems. I wanted to pass this along – some good feedback and ideas here:
"I’m the only DBA for our shop that has 45 production instances with 225 prod databases. It is very difficult to be proactive with my DBA tasks with this many databases. My approach to this is to conduct an audit of each system by going to each server with a production SQL instance and comparing the setup with an established security policy and SQL standards. This has been an ongoing effort this year and I have not made it through all the instances yet. I started with the databases supporting the most critical applications. Obviously there are many other projects that bump this activity so I have set some goals to get a percentage done each quarter.
I do save some time by focusing on a server/instance and checking all these items during the same time period, I may not be able to resolve all issues at that time, but I do note them in a master document so I at least know the status of that environment.
Here are some of the tasks:
– Compare current setup with established security policy
This includes checking the following
MS security patches are up to date
SA password is secure
SA is not being used for application connections
Builtin/Admin role removed for all financial databases
Check use of sysadmin role
Check use of datareader and datawriter
Check who has remote access to server
– Gather perf mon stats for a 24 hour period with system and sql-related counters.
This is used to establish a benchmark and to compare with target values.
– Perform a test database recovery
– Confirm integrity check is running and reporting if there are problems
– Confirm that any needed index optimization is performed"