Database Underground | Sean McCown | June 2007

MORE ENTRIES

June 2007

June 29, 2007 | Comments: (0)

I stay amazingly perplexed and annoyed at the state of DBs in this world. By now I'm sure it's no shock to many companies that DBs exist and that they can be used to store data. What I'm also very sure of is that a lot of them just don't get it. They look at DBs as a repository for things they'll never need again or something, I guess, instead of using them to make their business run smoother and their customers happier with their experience.

To illustrate...

1. Why does my cell phone company have me enter my phone# while I'm on hold if the person I get on the phone is just going to ask me for it anyway? Doesn't that system talk to hers? If not, then what's the purpose of having me give my #? And if I'm calling from my cell phone, why can't that system talk to the other one and just pick up my #? They know it's me calling because it shows up on my bill.

2. Why is it when I go to the store and pay with my card I have to tell the cashier that it's a debit card? I already pushed the debit button on the card reader... can't these systems talk to each other?

3. When I go to the Dr's office, why do I still have to fill out my address and other info every single time I go in, so they can attach it to my chart? Are you kidding me? You have my info in the computer... why can't you pull it from there?

4. When I take on a new job... why do I have to put all my info in 30 different places? Are you seriously telling me that you can't write a quick little something in say InfoPath or some equal technology so I can put all my info down once and then all the forms read from it and print out? Is this really such a foreign concept? I've been complaining about this for years, and I have yet to see anyone do it. There are so many technologies that can do this type of thing it's just ridiculous that it isn't the standard.

So far I think I've seen one company that really uses its DBs effectively... at least from a customer's point of view. And that's Discount Tire. You can go to ANY Discount Tire anywhere and they'll have your entire history available. They don't bother you with making you give your info again, or ask you stupid questions about your phone # or address. If you need to update your info, just tell them and they'll do it right there. It's excellent.

I know there are other companies out there that use their DBs well, and their customers even benefit from it, but there are far more who don't. I'm just wondering... why do you even have a DB if you're not going to use it to make your business more professional?

Posted by Sean McCown on June 29, 2007 08:19 AM

June 26, 2007 | Comments: (0)

Some Code you might Enjoy

I wrote this code the other day that disables all user logins in SQL Server 2000. If you have the need to be the only one on the server in SQL for doing maint, troubleshooting, etc., then you'll find this useful. I've given you both the adhoc version and the SP version. Hope someone can use it... because even sa can be kept out of the server this way.

When you use this, don't forget to put yourself in the exclude list in that NOT IN clause. Otherwise you'll lock everyone out and you'll need to restore master to get everything back.

And since this hits the system table directly, I'm not responsible for any damage you do to your system with this code. Use it at your own risk.

sp_configure 'allow updates', 1 GO RECONFIGURE WITH override GO --Disable users. UPDATE sysxlogins SET xstatus = 1|xstatus WHERE name NOT IN ('user1', 'user2') --Enable users. UPDATE sysxlogins SET xstatus = 1^xstatus WHERE name NOT IN ('user1', 'user2') sp_configure 'allow updates', 0 GO RECONFIGURE WITH override GO sp_helptext syslogins ------------------------Turn into SP----------------------------------- -- --Create Procedure dbo.spDisableAllUsers -- --@Users varchar(3) -- --as -- --/* --Disables all user accts on the server. --Author: Sean McCown --Date: 6/25/2007 -- --*/ -- --DECLARE @sql nvarchar(1000) -- --SET @sql = 'sp_configure ''allow updates'', 1; reconfigure with override;' -- --EXEC (@sql) -- -- --IF @Users = 'OFF' -- BEGIN -- -- update master..sysxlogins -- set xstatus = 1|xstatus -- where name NOT in ('user1', 'user2') -- -- END -- --IF @Users = 'ON' -- BEGIN -- update master..sysxlogins -- set xstatus = 1^xstatus -- where name NOT in ('user1', 'user2') -- --END -- --SET @sql = 'sp_configure ''allow updates'', 0; reconfigure with override;' -- --EXEC (@sql)

OK, so it's worth mentioning that this code doesn't work in Yukon. Not only can you disable accts in Yukon with 'alter login', you'd have to be able to touch the system tables directly, which is just too much trouble. The one thing that you can't do in Yukon, that you can do with the code above is disable windows groups. BOL says that's by design, and I suppose I can see that, but it would be really handy to be able to throw your server into a mode where only certain admins can connect, etc. Now, to get around this problem, all you have to do is make sure that no matter what you do, you don't give anyone dbo in any of your DBs. Then you can just throw all of your DBs into dbo only mode before you start your script. Another method would be to take your DBs offline if you don't need them. So if you're performing maint on several DBs one at a time, then disable all DBs except the one you're working on, and then disable it when you're done, and enable the next one in dbo only mode. This way, you can't control users connecting to the box, but they won't be able to do anything.

Now, all this has come about because MS decided to not allow us to disable windows groups. Now, I'm not saying that's a good thing or a bad thing... I'm just saying it's the reason we have to do it this way.

Posted by Sean McCown on June 26, 2007 08:02 AM

June 22, 2007 | Comments: (0)

Riding the Winds of Change

Sometimes it can be really difficult to get changes made in your company. And most of the time the more important the change is, the harder it is to make it happen. However, there can be a way to get things done that lights a fire under their butts.

One technique I like to employ when the opportunity arises is to wait until something big happens and then ride the skirt of the aftermath to get my change made. Here's a recent example... I wanted to reduce the rights of this one generic accout in our DB so that we could put the DB in dbo only mode during our maintenance. The problem was that we can't get our maintenance done because processes keep logging on to do run reports. They were ruluctant, and it was a fight, but I finally got it done. Now, now even 2wks later, we had problems with our ETL and it was running several hours behind. We're actually doing our best to get it done, so we've got the DB in dbo only mode again... and guess what... that generic account won't be interfering with ETL because I took those rights away. Currently, everyone is thrilled that we don't have to worry about the extra usage on the box while we're trying to get ETL finished. So misison accomplished.

To that end, I've also wanted to limit the access of all the users. Everyone has way more access than they should, and I've been trying to find a way to bring that up for serious conversation. Well, since I was right about this one, then if I strike while the iron's hot, I should be able to get them to listen to me about the others too. I've got good solid logic behind me, so I'm not going in there unprepared. But timing is everything.

Another good technique is to use data collection. Whenever you have a production issue, you have to determine the root cause. Well, record that info with dates, and details. Now, whenever you're ready to go to management with your change, you have actual numbers to back you up. I've documented that 45% of our production issues come from people making unauthorized changes in our DB, so if those rights go, we should see a dramatic decrease in production issues. You can't argue with numbers. Don't get me wrong... I know they still do, and those are the battles you just can't win. Sometimes all you can do is all you can do... but at least you'll have more ammo than you would otherwise.

So collect your info, and hang off the skirts of disasters and you'll have a lot more success.

Posted by Sean McCown on June 22, 2007 07:35 AM

June 21, 2007 | Comments: (0)

SSRS and Dundas

In case you missed it, MS just acquired the Dundas products for use in future versions of SSRS. Those would be the maps, guage, charts, etc that made Dundas famous. And while Dundas is still currently selling those products, there's no telling what may happen in the future. However, I want to make it perfectly clear that MS did not buy Dundas... just their products.

Posted by Sean McCown on June 21, 2007 07:24 AM

June 19, 2007 | Comments: (0)

New RSS Feed is Ready

OK, I've been promising this for a long time now, but I finally got the RSS ready for IT Bookworm. So you guys go on and subscribe and I'll update it with book and video reviews as well as press releases for new books and videos. I may even put some training discounts up there if they come my way.

Posted by Sean McCown on June 19, 2007 07:26 PM

June 18, 2007 | Comments: (0)

An Excellent Publishing Idea

I just got a press release that O'Reilly publishing is now offering PDF versions of their books in single-chapter downloads. I love this idea because so often I've only wanted one or 2 chapters of a book, but had to buy the entire thing just to get what I wanted.

However, it does make me wonder about the specifics of the plan. What happens if there's a book with a lot of chapters, and I purchase a few of them, and then decide to get the entire book? Will I be able to put those previous purchases towards the purchase of the entire book? Because if you've got a title with a lot of chapters, you could exceed the price of the entire book if you bought them one at a time, and say I've bought 5 chapters, and now I want to buy the entire book... now I've paid that much more for it when it would've been cheaper for me to just get the whole book to begin with.

All of that aside though, it's an excellent idea and I'm sure these details will work themselves out as the program matures.

So rather than give you all the details myself, I'll just paste the press release I received. I will say though that these chapter downloads are something those of us on Safari have enjoyed for quite some time. So I'm glad to see they've expanded the program.

Sebastopol, CA--In today's Web 2.0 driven publishing marketplace, it takes new and creative strategies to get authors and their work noticed by web savvy readers. But even when it is noticed, today's readers increasingly want content in new and convenient ways that suit their digital lifestyles. Along with traditional print formats, they want content they can read on computers, PDAs, and cell phones. For this reason O'Reilly Media--the pioneering publishing company that coined the term Web 2.0--has recently launched several innovative publishing programs aimed at delivering content in formats the tech generation craves.

Starting this month, O'Reilly Media customers have the option to purchase book content by the chapter in PDF format for $3.99.

"As with iTunes, our customers now have the choice of buying the whole book or just the part they want," notes Allen Noren, project leader and O'Reilly's director of online marketing. "I love the usability of these independent chapters. Each has its own table of contents and index.
They're bookmarked and searchable. And they're infinitely portable."

O'Reilly initially rolled out this new feature on 714 books. All these titles are also presently part of the Copyright Clearance Center RightsLink project, which gives customers the option to purchase reuse rights of book content for their Intranets, newsletters, course packs, and websites.

For a real time understanding of the many new choices available for O'Reilly readers, check this example. Readers interested in Scott Raymond's "Ajax on Rails" can find many appealing options when they click on this online catalog link:

http://www.oreilly.com/catalog/9780596527440/
http://www.oreilly.com/catalog/9780596527440/
Along the right hand column there are clearly marked links to:

-- Purchase the printed book for $39.99

-- Purchase the entire book in a PDF format for $27.99

-- Purchase a chapter for $3.99 each

-- Purchase reprint rights for portions of the book

-- Purchase a site license for the entire book or a portion of it

-- And a link to read it online through Safari, an electronic reference library for programmers and IT professionals. A Safari subscription gives you complete access to the electronic versions of over 3,600+ of the latest and greatest books from more than a dozen publishers, including O'Reilly Media, Addison-Wesley, Adobe Press, and Peachpit Press.

While most publishers understand that technology is shifting the ground on which modern publishing thrived for nearly a century, few have the knowledge to implement the immediate and long-term challenges and opportunities presented by these innovations. O'Reilly's new offerings of convenient and reader friendly formats is helping to build a successful future for publishers and authors in a digital era.

Posted by Sean McCown on June 18, 2007 06:33 PM

June 18, 2007 | Comments: (0)

Managing Jobs in SQL Server

OK, from time to time this topic comes up so I thought I'd publish this info in the hopes that someone will find it useful.

The problem is with getting non-admins the ability to admin jobs in SQL2K. It's widely known that only SAs can do this, but there's a special DB role in msdb that lets you do this w/o having to be an admin. Rather than give you a big explanation, here's the code so you can test it yourself. This will let anyone in this group admin SQL jobs just like they were SA.

use msdb
exec master..sp_addlogin 'testme', 'testme', 'msdb'
exec msdb..sp_adduser 'testme', 'testme', 'TargetServersRole'
grant execute on sp_add_job to TargetServersRole
grant execute on sp_add_jobstep to TargetServersRole
grant execute on sp_update_job to TargetServersRole
grant execute on sp_add_jobserver to TargetServersRole
grant execute on sp_add_jobschedule to TargetServersRole
grant execute on sp_delete_jobschedule to TargetServersRole
grant execute on sp_delete_job to TargetServersRole
grant execute on sp_start_job to TargetServersRole
grant execute on sp_stop_job to TargetServersRole

I hope this helps somebody. If you want to give the ability to admin only specific jobs though, you'll still have to use one of the intermediate steps you did before, or make the user the owner of the job. Too bad groups can't own jobs.

Posted by Sean McCown on June 18, 2007 12:17 PM

June 14, 2007 | Comments: (0)

What is Quest Doing?

For a long time now I've been hearing that Quest was going to release a LiteSpeed for Oracle. Actually, to tell the whole truth, I've been hearing it since the Imceda days for those of you who can remember back that far. After the Oracle release, they were then to release DB2, MySQL, Sybase, etc. LiteSpeed was going to take over the entire backup market.

Now I hear that LiteSpeed for Oracle has been cancelled. There are lots of rumors going around as to why, and when you get down to it, it really doesn't matter. I happen to know that a lot of their customers have been waiting for this for a long time. The pitch for almost 3yrs now has been that LiteSpeed was a full enterprise database backup solution because it was going to bring your entire enterprise together under one database backup umbrella. I've even preached that many times myself because I was being told from very high up that it was going to happen. It was actually exciting. Just think about it. No matter what your platform, you can manage all of your database backups from one single repository. You could get all of your reports in a single place, manage all your licenses in a single place, and even push out all your installs from a single place. But that's not going to happen now.

It's not like the product doesn't exist. I've actually played with an early beta of LiteSpeed for Oracle. So it's not like they couldn't figure out how to do it.

One of the more substantial rumors I've heard is that Oracle is in the process of developing the same technology and Quest didn't feel like competing with them. Hey, you know what... whatever. Even if Oracle did develop backup compression technology they still wouldn't offer you the same thing LiteSpeed would have, and that's unified backup management for all your platforms.

Frankly, I've seen Quest pump out some real crap and push it on us like we're stupid. TOAD for SQL Server comes to mind. There's also their Change Director for SQL Server... don't even get me started on that one. Maybe I'll talk about that one soon. And then the really good ideas like LiteSpeed for Oracle and Coefficient get tabled. Now, Coefficient is still out there, and you can still buy it, but to my knowledge it hasn't gone through any significant revision since Quest bought Imceda.

So if any of their sales people contact you and give you the song and dance about LiteSpeed unifying your different DB backup platforms, tell them I said they're full of it.

Basically we're right back where we started though... without a way to take care of all of our DB platforms in one place. And what are we going to do, turn to Backup Exec or ArcServe? I DON'T THINK SO. I'd rather manage my backups by hand than get involved with either of those products.

Anyway, if you look at LiteSpeed for SQL Server sales as any kind of an estimate on what your LiteSpeed for Oracle sales would be, then Quest is making a BIG mistake. Imceda was the only player in the market for SQL Server backup compression and they mopped up. Even after Idera and Red-Gate came along, LiteSpeed still sold (and is still selling) like hotcakes. It just goes to show that there's enough market to go around for everyone. But Quest wouldn't have to share it with everyone. They'd have the entire market all to themselves. And even if Oracle did get in the game, Oracle has this way of making everything more difficult than it needs to be, so there'd still be a benefit to LiteSpeed. And again, there's still that whole unified management thing it would bring you.

This is just a really good example of Quest not listening to their customers because I know for a fact that many of their customers have been begging for this. And how many people do you know who would tell you that they don't want 85% compression on their backups and drastically reduced backup times? Nobody that I've ever seen.

So again, what is Quest doing?

Posted by Sean McCown on June 14, 2007 05:36 PM

Technology White Papers

InfoWorld Technology Marketplace

» Technology White Papers Library

Technology White Papers by Topic

Technology White Papers E-mail Alert