Where Do I Want To Go Today? - From the Field

Taking a note from Microsoft's own page, I decided to share some of my wishes for where I want SQL Server to go. Today.

Well I've had great responses to this series and I'm taking a week off from my own thoughts and adding in some of the suggestions I've received. I've provided my own comments as well and I'd love to see more feedback on these as well as on my thoughts.

SQL Security (from Paul T.)

Consider what happens in the following scenarios:

- I develop a bespoke solution including as one of its components a Sql

Server database. In order to protect my database, I impose various

security constraints.

But as soon as the client copies this database to his machine (where he has full Windows Admin rights), my security constraints have vanished.

Steve:

I remember when I started one job and every stored procedure in the production system was encrypted. It seemed cool at first, a great way to protect the code. Then we realized that version control wasn't great and that we needed to learn which version was on the server.

Fortunately security wasn't all that great in v6.5 and we could "decrypt" the procedures.

Now I'm not a huge fan of closing source. I think it does lead to problems, so I'm not overly thrilled with this one, but there is an argument to be made here. Having some way to protect your intellectual property might be nice. I'd really be interested to hear comments on this one.

More SQL Security (from Paul T.)

A company's salesmen need laptop access to part of a company's Sql Server

database. Rather than create a different subset for each salesman, the

company prefers to distribute a standard subset, but wishes to constrain a

salesman's view of the data to that portion for which he is responsible.

Again, as soon as the salesman receives his copy of the database subset, he has unfettered access to the entire database subset.

Steve:

Unless you jump through some serious hoops to limit the subsets that are copied down. I think having some sort of "key" based replication strategy that can easily map a login to a key would be fantastic. Having the subsets of data able to easily port between users SECURELY would be very nice. To me this is more of an infrastructure thing than something we should be spending lots of time coding, especially with PDAs!!!

Statspack (from Joe D.)

StatsPack is a process that reads from the dictionary tables and cache to see what is running and how many resources they are taking. You set it up to run every 15 minutes to every 2-4 hours depending on how busy the system is (busier it is, more frequently you want to run it). To produce a report you pick two points in time to run it against. If you run every hour, you can produce a report every hour, and/or see how it compares to a report ran for the entire business day. It produces a report show the top 10 resource intensive processes in various categories like : most CPU, most physical reads, most logical reads, etc. It also gives you quite a few stats on hit ratios, soft and hard parses, and wait events.

Steve:

This is one I'd love to see. I hadn't gotten this deep into my list, but for sure I'd love to know how the system is behaving. Not that I want to tweak too many knobs since I agree with the SQL Server team that I would likely get myself into more trouble, but knowing what is happening would be great. The cache hit ratio is fine as a gross measure, but I want to know what's taking up that cache. What is the server doing with my data and the requests to fulfill them?

I think having this information would really help with the troubleshooting and tuning, not with the server, but architecting or changing the applications to deal with it better.

Trigger Enhancements (from Dale W.)

My wish is for triggers that can fire for each record. I really hate having

to write a whole bunch of extra code to deal with the insert or update of

multiple records.

Steve:

Be careful what you wish for. Personally I like having the trigger fire once. I'd worry about my server ingratiating trigger variables for every row. To me it could be tremendously taxing on the hardware. SQL doesn't seemed to be optimized for row operations or cursors (as I believe Oracle is) so I see this as an issue.

My vote would be better samples and documentation on set based solutions rather than this, but I'm curious how many of you agree with Dale.

Conclusion

Please feel free to comment on any of these and send in more if you've got them.

Other items in this series:

Steve Jones

Return to Steve Jones Home

Where Do I Want To Go Today? - From the Field