| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2005-06-21 : 19:00:23
|
| Hi, I am about to take control of 15 sql servers in a newly created position at an organisation in London. I have about 3 years experience of SQL server, but this will be the first time I am flying solo and being a new position there is no handover.I have a few ideas of what I should be looking at when I first arrive. Can anyone suggest a possible checklist in order of importance the things I should be checking when i arrive at the company. e.g. checking for a decent backup strategy was top of my list!Nervous, help requested.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-21 : 19:09:46
|
| These are the minimum set of jobs that I have for our production databases:Full backupsTransaction log backups for databases not in SIMPLE recovery modelDBCC CHECKDBDBCC DBREINDEXUPDATE STATISTICSMake sure that the backups can be restored on a test server. You should not assume that since the full backup job was successfull that you have valid backups. This should be automated.Familiarize yourself with the schemas by creating ERDs. Make sure that the foreign key columns are indexed, as it is a common mistake to think SQL Server created these for you.Run SQL Profiler to determine if you have any slow queries that need to be improved.Talk to the "people in charge" to find out what they need done.Tara |
 |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-21 : 19:19:30
|
| Heh there is this cool little tool microsoft came out with this year called the Baseline Security Analyzer. Download it and run it on each machine and it will show most of the flagrant security problems with sql and the server itself. This should score you some points. I shook some people up with it when I did it.Other than that:1. insist on windows authentication for everything2. check for blank sa passwords3. comprehensive backup and restore strategy like you said4. run profiler to find all of the bad queries.5. insist on everyone using stored procedures and purge all inline sql6. do not let the developers touch the production boxes7. use profiler traces from the production boxes to run the index tuning wizard on your development boxes.8. evaluate the results of the tuning wizard and decide wether to apply the reccomendations to the production boxes9. make sure there are development boxes and the inmates (developers) are not running the asylum. 10. If the system is real busy make sure they have seperated their OLTP and decision support systems.11. Make sure the indices that are in place are not fragmented by running DBCC SHOWCONTIG on a regular basis and then rebuild the indices where necessary.12. Check all applications for SQL Injection vulnerabilities.13. remove all guest accountsThat should keep you busy for a while.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-06-21 : 19:40:31
|
| a lot of good suggestions here, here is my list in order of importance.1. Ensure you have good backups. 1. Test your restores.that is it. also, don't try and push people around and try to make a bunch of changes right off the bat - even if you spot things that are completely shitty. Keep in mind that you will need to work with this group of people, and they can make work miserable for you.Of course you will want to make some changes, but you will need to set the foundation for being able to do that. If you don't then the job could become nightmarish. -ec |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-21 : 23:09:05
|
| 14. Encrypt all credit card data with public key encryption.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
|
|
|
drtherc
Starting Member
19 Posts |
Posted - 2005-06-22 : 16:44:59
|
| If your db's are large and your maintenance windows are small, use DBCC IndexDefrag during the week and DBCC ReIndex every weekend. DBCC SHOWCONTIG WITH ALL_INDEXES will help determine fragmentation in tables and indexes.David |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-06-23 : 03:25:20
|
here's what i did:1. benchmarked the existing systems in each server2. gathered data and analyzed the statistics per server3. presented the documentation to the boss including what needs to be done4. once i got approval, started kicking some asses - okay, so i exaggerated, what i meant was, i started to: a. dismantle the existing setup b. slowly but surely laid out the foundation (i really kicked asses  )5. when you're sure you've achieved your "ideal" setup, build from thereI suggest not going into the details yet, you have to take a general view of their setup, then conquer each problem at a time. If you're solo (which I was also), divide the areas you want to check and for your sake, make a schedule. Believe me, when you're there, there's so much you want to do in one day, you forget the basics. 1. backup all databases on all servers (store those backups away, properly labeled, don't forget to test restore, in case something "weird" happens)2. work on one server at a time (backup before starting, again)3. check for security (establish or improve) - sa passwords must be changed, sql service account passwords, old DBAs account  4. check for maintenance (establish or improve) - jobs, alerts5. check for performance (establish or improve) - applications using DBsHTH--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-06-23 : 03:28:37
|
oh i forgot, befriend the netadmin/techsupport  you'll need the help and cooperation with regards to firewall,network security and access, even help you out in doing tape backups --------------------keeping it simple... |
|
|
|
|
New Sql Server Role