| Author |
Topic |
|
jar21
51 Posts |
 Posted - 2008-09-23 : 10:02:17
|
| This is really just a general inquiryIf you are hired as a DBA to a company that has never had a DBA before nor had any work on their databases yet what are the first steps you would take considering best practices and improving performance (assuming they had automatic full backups and transaction logs configured already)Enjoy Life This Is Not A Rehearsal. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-09-23 : 13:20:59
|
| verify them if they can restore ok._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-23 : 14:14:02
|
| I'd look for, and remove, junk tables that had accumulated over the years and that were not part of the application.I'd set up proper production/QA/text/development environments, with policies for how rollouts would be handled.Boycotted Beijing Olympics 2008 |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2008-09-30 : 16:15:52
|
| Run something like this to confrm that all DBs are getting backed up:SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate, case when type='D' then '** FULL **' when type='I' then 'DIFFERENTIAL' when type='L' then 'LOG'end as Backup_TypeFROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D' GROUP BY B.name , a.typeORDER BY B.name , LastBackupDate desc,a.typeThen start looking at security. is 'sa' password complex ?, who knows it ?, what other logins have sysadmin rights ? .... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-30 : 16:18:32
|
Second, lock down the databases as much as possible, minimize the surface.Change password for SA account.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-30 : 16:28:27
|
- Remove all maintenance plans, replace with custom procedures
- Get all SQL Servers to the latest build for the SQL Server version
- Work with the server team to get the OS patched
- Review who has server level permissions, remove people from local admin on the database servers
- Review who has sysadmin, fix if necessary
- Review who has db_owner, fix if possible
- Educate developers on best practices
- Send out DBA naming and coding standards
- Get familiar with application performance by running SQL Profiler/SQL Trace and Performance Monitor
- Check for missing indexes on foreign keys and by viewing the Performance Dashboard reports
- Add SQL Jobs as necessary
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-30 : 17:26:26
|
Tara, I think this will be an excellent material for an in-depth article or blog post!
E 12°55'05.63"N 56°04'39.26" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-10-01 : 01:32:22
|
now she just has to make time to write it  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-10-01 : 01:45:30
|
quote:
Originally posted by tkizer
Remove all maintenance plans, replace with custom procedures
Oh, come on Tara. There's nothing wrong with using the maintenance plans. You should definitely verify that existing backup and recovery strategies are working as intended, but if they are then there is no reason to make replacing them at the top of your to-do list.Boycotted Beijing Olympics 2008 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-01 : 13:49:41
|
| I completely agree with Tara regarding maintenance plan for Defrag and Reorg index? I am using maintenance plan for Backups and integrity checks? but with defrag indexes and online stuff, it sucks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-10-01 : 14:03:54
|
| I agree. I have my own script for reindexing as well. But applying such a script does require scrapping entire existing maintenance plans, and fixing something that ain't broke would not be at the top of my list.I would take issue with your statement, because it implies that any use of maintenance plans is bad practice. I disagree.Boycotted Beijing Olympics 2008 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-10-01 : 14:29:41
|
| We scrap all maintenance plans due to the large number of databases, servers, and instances that we have. Maintenance plans don't make intelligent decisions when performing backups, but it's not as bad as the defragment issue. An example of an intelligent decision in a custom backup script is when a database is newly created, doesn't have a full backup yet, the transaction log backup runs for the instance, and does a one-off full backup to prevent the job failure. The maintenance plan job will error in this scenario. It becomes an issue when you've got a few hundred SQL Server instances to support.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-10-01 : 16:30:16
|
| That is NOT the scenario outlined by the original poster.Boycotted Beijing Olympics 2008 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-10-02 : 01:09:04
|
I highly doubt that poster was imagining a scenario where he walks into a company withquote:
Originally posted by tkizera few hundred SQL Server instances to support.
, but which quote:
Originally posted by rabino67has never had a DBA before nor had any work on their databases yet
That would certainly be a messed up company.But he did say to assume quote:
Originally posted by rabino67they had automatic full backups and transaction logs configured already
, and I contend you would be doing your new employer a disservice if your first action was to dismantle and rewrite the one aspect of the system that was working. Surely, there would be higher priorities, despite your distaste for maintenance plans.Boycotted Beijing Olympics 2008 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jar21
51 Posts |
Posted - 2008-10-07 : 12:02:51
|
| thanks for all of the input, and I was looking for general practices... currently that is sortof whats happening at my current job, except there is no DBA... I'm the SA and have had to dive into learning as much about DBA as possible.... and I must say I'm beginning to like it :-) |
|
|
|
|
what would you do first?