| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-05-08 : 10:18:45
|
| For your everyday house-keeping tasks, 2005 is less friendlier then 2000. Everything is that many more clicks away. Whilst that may be fine for 1 or 2 servers, when you have a gazillion servers, it starts doing your head in. For example why open the logs in a new window, that I then have to expand in order to view properly? It shouldn't be fiddlier, I can see that they have designed SSMS so that it is friendlier, but it just hasn't really turned out that way.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-08 : 10:22:12
|
| If you have that many servers why are you using management studio (and why were you using enterprise manager)?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-08 : 10:24:56
|
| SSMS is a step backward in usability from EM, probably because MS tried to force administrative functionality into a GUI that was designed for application progam development. In other words, they were just being lazy in not giving us an environment dedicated to SQL Server support.e4 d5 xd5 Nf6 |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-08 : 10:28:46
|
quote:
Originally posted by nr
If you have that many servers why are you using management studio (and why were you using enterprise manager)?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Please clarify.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-08 : 11:00:09
|
| I didn't to that extent. Thanks.Still waiting on Nigels reply.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-08 : 12:24:50
|
| You can create scripts to do anything that management studio can do. It uses smo but for most things it's easier to just use t-sql and execute using a linked server or osql depending on your setup.Usually with many servers you want to perform the same sorts of tasks and monitoring on each so most people will develop a load of scripts to help them.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-08 : 12:37:53
|
I see. In the long run it may be better but it would take a long time to setup all these scripts. I'm prepared to do this, but to setup scripts for all databases on all the servers for all the housekeeping tasks will take a long time to do. Is there not that worry that one of the many scripts might not work for one reason or another one day and you not be aware? For example, you're saying "setup a script to check if any jobs on all the servers have failed" (saves me from going in and checking for red crosses). But if that script were not to work, I might not discover for a couple of days that is has been failing because I'm depending on it to communicate with me, as opposed to me going into SSMS and physically checking it...EDITAdditionally, are you using a script then, that checks the logs on all your servers?quote:
Originally posted by nr
You can create scripts to do anything that management studio can do. It uses smo but for most things it's easier to just use t-sql and execute using a linked server or osql depending on your setup.Usually with many servers you want to perform the same sorts of tasks and monitoring on each so most people will develop a load of scripts to help them.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 12:50:50
|
| "But if that script were not to work ..."Yes, that's a real world risk.For me the switch-over point came when I couldn't get what I wanted out of the Maintenance Plans, which in the case of SQL 2000 was pretty early on. We needed Differential backups, and a variable period retention plan. We also could not cope with REINDEX of tables, and not only needed DEFRAG instead but also "only tables that need it".All of which I think is now available in SQL 2005 Maint Plans.But we also have "Delete stale data" and stuff built in to our maintenance scripts. Which I suppose Point-and-click-jobie-thingie or somesuch could do, but with where I have come from, and the amount of fragile fire-and-forget stuff I've seen DEVs build with those tools, I just couldn't contemplate it any more.I expect its "Different folks, different strokes"Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-08 : 12:53:55
|
| What do you think SSMS does? Why rely on it more than something you've written?You should do it by getting the info for jobs from all servers. You check for an absence of success rather than a failure so you will get an alert if the process fails - and also be warned if the server isn't accessible.Are you really going and checking a gazillion servers individually? I usually give up on that before it gets to around 10 servers.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-08 : 12:57:02
|
quote:
Are you really going and checking a gazillion servers individually? I usually give up on that before it gets to around 10 servers.
Em, yes.Ok, thanks.Lots to do, but guess I'll have to start somewhere.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 13:02:39
|
"Why rely on it more than something you've written?"More people testing SSMS than just me ...  "Lots to do, but guess I'll have to start somewhere"You can probably profile what SQL commands SSMS is sending to the server ...Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-08 : 13:09:33
|
quote:
"Lots to do, but guess I'll have to start somewhere"You can probably profile what SQL commands SSMS is sending to the server ...Kristen
Would you recommend that? Using the very same scripts that SSMS uses? For example, I have no script that would delete backups after so many days. I would need to rely on the web to find a script, like Tara's, that would do something like that. While I'm very comfortable with SQL, I'm not a developer, and genuinely wouldn't have the time to learn to write a 100% safe script that did stuff like that. Not for the purposes of writing house-keeping scripts anyway.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 14:11:51
|
"Using the very same scripts that SSMS uses?"Sorry, wasn't really meaning that. I mean to have a snoop on what it does, and then see if that influences how you choose to do it.It took a LONG time for us to build our backup scripts. They get more bullet proof over time.What happens if SQL Maint Wizard backup runs out of disk space? Well, if you are well set up you get an email saying it failed. You may spend a while working out what the error message relates too (not tried it on SQL 2005, but under SQL 2000 I reckon you may scratch your head for a while!)What does mine do? Web report of what worked / what failed, across all servers/databases regardless of System database / User database with/without Tlogs etc. Colour highlighted. Plus a record of the free disk space going back "forever" so that you get the chance to predict when you will be out of disk space before it actually happens.And if we are going to be running out of disk space a lot  I guess we could make it auto-adjust the retention period to free up space just-in-time from older backups, as needed.But all that is just "needs" that I have reacted to over the years.But yes it TAKES A LOT OF TIME TO BUILD (read: DEBUG!). Did I say that already? Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 04:09:57
|
| It takes a long time but you are building capital. Every minute you have spent checking servers manually is a wasted minute (in so far as it serves no future purpose). Every script you write will teach you a little more (for example about the system tables, CLR, the new management views) and is in your script library for the future. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-09 : 04:36:34
|
quote:
Originally posted by pootle_flumpEvery script you write will teach you a little more (for example about the system tables, CLR, the new management views) and is in your script library for the future.
Yeh, I suppose, I could take them with me where-ever I go.There's certainly an argument for automating it, as it does my head in. So help me out here...Primarily we're looking at:1. A job running on every server that collects a. Information about failed jobs b. Keywords in Logs (say the first 2 cycles) such as "Failed"2. This info is populated into a table2. Then a DTS job (and SSIS on 2005 machines) will send info from this table to the admin server.3. This sent information is populated in a new table on the admin server, from which I can develop web reports and/or send out emails etc.See immediately, I'm hesitant. There are hundreds of error messages. What if a crucial one in the log is not in my list of keywords, like "exploding". I would not be informed about it.Indeed, one of your servers might not be backing up right now, because you haven't been informed.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 09:00:28
|
| You've got two 2's - I'll call them 2a and 2b.You need a step between them:When "harvester" finishes successfully it should add a "Done" row to the Log table."What if a crucial one in the log is not in my list of keywords, like "exploding". I would not be informed about it"I do it the other way round, I delete [well, "ignore"] messages I am happy with. That leaves behind everything else. If a "new" but "safe" message gets through I improve the filter to ignore it next time.I also, explicitly, check that I have a "Done" message for every possible job run.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 10:05:20
|
| Have a look at MOM - I don't like it much but might give you some ideas.Do you want a job running on each server to populate a local table or a task on a central server that accesses all others?Why DTS/SSIS?As to backups - if it's a critical database then you should automate test restores and dbcc checks of the database and report on the failure of that.Otherwise check the size of the backup, test restore the header and whatever else you can think of and rport on that. Report on errors of the backup command too (you don't use maintenance plans do you?).I usually collect data from the scheduler locally but that's because it might lose messages due to truncation and history gets lost when you delete jobs. I'll run something that saves jobs, history, schedules, ...I'll monitor how long each job takes and alert on changes.The jobs themselves will log start and end to another table anyway and I'd usually use that for monitoring but the agent would give more info and also show other things that have been scheduled.If there are different servers running jobs or job threads being run then I'll build a scheduler and not use the agent (save that just for backups and such).This scheduler will have dependencies between jobs and priorities during different windows (i.e. a process gets a window to comlpete a task and if it doesn't a different task - usually for a different department gets priority). These all give a result to the scheduler which logs it for the central (intranet viewable) monitor (I've done this for a number of companies and keep meaning to turn it into a product).The monitor also allows me and the users to see what is running on any server by displaying the results from sp_nrinfo on that server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-09 : 11:12:46
|
| Many thanks.I'm sure I'll be back with questions as I progress with this.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-05-09 : 19:28:59
|
quote:
Originally posted by Kristen
"But if that script were not to work ..."Yes, that's a real world risk.For me the switch-over point came when I couldn't get what I wanted out of the Maintenance Plans, which in the case of SQL 2000 was pretty early on. We needed Differential backups, and a variable period retention plan. We also could not cope with REINDEX of tables, and not only needed DEFRAG instead but also "only tables that need it".All of which I think is now available in SQL 2005 Maint Plans.But we also have "Delete stale data" and stuff built in to our maintenance scripts. Which I suppose Point-and-click-jobie-thingie or somesuch could do, but with where I have come from, and the amount of fragile fire-and-forget stuff I've seen DEVs build with those tools, I just couldn't contemplate it any more.I expect its "Different folks, different strokes"Kristen
Kristen,Funny for some reason Im not seeing the maintenance plans folder in Management studio. I went to the Management folder and opened that up but all I see is log files...and a folder "Legacy" for DTX Import / Export. Is this some sort of seperate install that the admin forgot to put on ?Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-10 : 04:25:45
|
quote:
Kristen,Funny for some reason Im not seeing the maintenance plans folder in Management studio. I went to the Management folder and opened that up but all I see is log files...and a folder "Legacy" for DTX Import / Export. Is this some sort of seperate install that the admin forgot to put on ?
Why are you askin Kristen about Maintenance Plans?? You can't create Maintenance Plans on a 2000 machine using Management Studio.Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Next Page
|
SQL 2005 more fiddley then 2000