Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help wanted healthchecking new DB's/Servers

Author  Topic 

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-04-24 : 07:53:54
Hi all,

I'm starting a new contract later on this week, and I have absolutely no idea what state the client's SQL databases are in. From what I understand I'm the first DBA they've ever had, I presume up to now they've simply installed their RDBMS and DB's in "click this button" style according to the instructions of their 3rd party vendors and the like. So, although not strictly a 'greenfield' site, it is indeed a green one (literally as well as figuratively, but I digress!!)

Any of you guys know of a good script that I can run to give me a snapshot of the health of their databases/server config settings, listing frequency/type of backups, performance stats, trans log utilisation, security etc????

Of course I could perform this audit manually, one DB and one server at a time, and will likely perform an exhaustive one anyway this week, but I'd rather know frome Day ONE whether there are any glaring issues that urgently need to be addressed.

Cheers,


AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-24 : 08:29:15
Work backwords from the one with the most danger to you staying in this job.
a) backups
b) security
c) performance
hardware
db implementation
d) resiliance (fault-tolerance)

As you knock off each successive objective, the paycheck will settle longer and the ability to react to the next objective becomes easier.
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-04-24 : 08:59:41
quote:
c) performance
hardware
db implementation
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-24 : 09:19:54
Besides other items mentioned, I would run these at the first opportunity on each database:
DBCC CHECKCATALOG ( 'database_name' ) 
DBCC CHECKDB ( 'database_name' )

If there is any data corruption you will want to find it right away, if for no other reason than making sure you don't get blamed for it.




CODO ERGO SUM
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-04-24 : 09:56:25
quote:
Originally posted by AndrewMurphy

Work backwords from the one with the most danger to you staying in this job.
a) backups
b) security
c) performance
hardware
db implementation
d) resiliance (fault-tolerance)

As you knock off each successive objective, the paycheck will settle longer and the ability to react to the next objective becomes easier.



That was pretty much my intended order of priorities, the Backup/Performance/hardware I'm fairly familiar with, but is there a refresher article on SQL Security Auditing? As well as tempdb/trans log sizing?? It's been a few years since I configured security at my last job and really haven't kept fresh on it!

Cheers,

Jaybee.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-24 : 12:12:53
from some vague memory of my role as DBA

security...
account that runs the services
change the password after you've checked dependencies

before changing SA password, verify if there are tasks dependent on it like replication or linked servers

check for builtin administrator, users with sa or dbo rights

checked if there are audit tables/database, triggers, linked servers, dts packages, jobs, seal them off one by one




--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 15:27:09
There is the SQL Best Practices thingie-tool that MS offer for download. That might give you a nice 900 page report that you can pretend that you wrote and send in a large bill for ... email me for my Swiss bank account for my 10% please

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-24 : 16:12:20
quote:
Originally posted by Kristen
...a nice 900 page report that you can pretend that you wrote and send in a large bill for...

Do you work for Accenture?




CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-24 : 17:08:45
No, but one of my old office juniors works for Avanade - he's done OK with my basic training course - you know, two Ferrari's, big country house, that sort of thing ...

Kristen
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-04-27 : 06:22:06
[quote]Originally posted by Kristen

No, but one of my old office juniors works for Avanade - he's done OK with my basic training course - you know, two Ferrari's, big country house, that sort of thing ...

Hi, a few questions on Security - my weakest area of SQL - if I may;

Builtin\Admin

I've noticed that they are still present on the servers, are they still a threat in 2k as they were in 7.0, and more crucially, is there a way to check if anything depends on them? Secondly, I remember from the dim and distant past, every time I dropped the Builtin account, I was no longer able to schedule a bean. Is this still the case?

SA password

I'm logging on to with EM using only my Windows login (which is also a member of the NT Admin group). The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?

NT Admins

I want to be able to seal off the NT Admins group and have myself as King of the SQL servers - what would be best practice here?

Users

Is there a programmatic way to check which users have Admin rights to the database?

Processes

Is there a way (programmatic or otherwise) to check which process depend on what logins?

Cheers,


Jaybee
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 06:53:02
"The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?"

Just try to connect with a blank password.

CODO ERGO SUM
Go to Top of Page

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-04-27 : 07:39:18
quote:
Originally posted by Michael Valentine Jones

"The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?"

Just try to connect with a blank password.

CODO ERGO SUM



Well, yeah, but it's not prompting me for my password, I just get validated!

Jaybee.
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2006-04-27 : 08:46:55
I think Michael means that if you can connect to the db as SA with a blank password then the password is blank. If you get a "incorrect password" message then it isn't.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-04-27 : 16:47:23
There's a security checklist on www.sqlsecurity.com. They also have free analysis and tools sections. I really like the checklist personally.

I would also use the best practices tool from MS. I run that for all my clients, look at the backups, run through the security checklist, perform basic perfmon and profiler checks, look for maintenance routines, and create an executive summary report of all the findings with basic recommendations. It's a good way to start a relationship. Sometimes, they don't like the recommendations, but that's life.


quote:

Builtin\Admin

I've noticed that they are still present on the servers, are they still a threat in 2k as they were in 7.0, and more crucially, is there a way to check if anything depends on them? Secondly, I remember from the dim and distant past, every time I dropped the Builtin account, I was no longer able to schedule a bean. Is this still the case?



You should setup an AD group with your approved SQL Server administrators. Add that to the instances with sa privilege and drop the BUILTIN\administrator account. Do it in that order, or you'll be in trouble. You can schedule things just fine still.

quote:

SA password

I'm logging on to with EM using only my Windows login (which is also a member of the NT Admin group). The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?



Login to the instance with Query Analyzer. Change it to use SQL Server authentication and the sa username. Leave the password blank. If you can login, they are idiots.


quote:

NT Admins

I want to be able to seal off the NT Admins group and have myself as King of the SQL servers - what would be best practice here?



Follow the advice in the BUILTIN\Administrator section. Additionally, there's no way to practically isolate themselves from the physical server. Auditing is a good thing though.

quote:

Users

Is there a programmatic way to check which users have Admin rights to the database?



Follow the advice above. Write scripts to check for groups or users who are part of the server level roles, have dbo rights in the databases, or have elevated object level permissions. You can find out all the information on the system tables in Books Online. I think Nigel Rivett or Vyas might have some scripts for this online. I would check there and the script library on sqlcentral.com.

quote:

Processes

Is there a way (programmatic or otherwise) to check which process depend on what logins?



Can you explain what you mean by processes?? If you mean Windows services, it's easy. If you mean processes like scheduled jobs, you can check through system tables. Describe a little more about what you want.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -