SELECT * FROM [Musings]

Database matters.

June 2006

SlashDot has a post discussing the possible security risks posed by telecommuters.

To me, the real problem is not telecommuting, but rather taking millions of live data rows off site. If you need to work with live data, a VPN connection will generally get you to the data, and generally fast enough to do your work. Failing that, there are now arriving tools to create test data that matches your data in terms of general distribution and content, but does not reveal any real information. Micorosoft's new Visual Studio for Database Professionals is one example.

The problem is especially accute when the data is loaded on portable equipment, such as a laptop. I expect that it is less likely that someone would come donw to my office and pull out the 35 pound desktop computers rather than the 8 pound laptop. Of course, even on the desktop, such data would not be secure...

Posted by Douglas Reilly at 11:22 AM  Permalink |

One of my big concerns about LINQ was a fear that it would not work and play well with Stored Procedures. Thankfully, I was wrong.

ScottGu (Scott Guthrie at Microsoft, who gave a number of really good talks at Tech Ed) posted a blog entry that points to other appropriate blog entries and explains how to use Stored Procedures with LINQ. This eliminates all concerns I had about LINQ interacting with the database of most of the applications I support.

Posted by Douglas Reilly at 03:25 PM  Permalink |

Mike Gunderloy describes in great detail a new feature in SQL Server 2005 that has been largely overlooked.

Synonyms, as Mike says here, allow you to take an ungainly name, that might include server, owner, etc., and replace it with a short name. This is actually quite a cool feature, though of course not one that I would switch over to SQL Server 2005 to get.

I hope to cover more of these little features as time goes on. At Tech Ed, I picked up a couple of new features, especially in T-SQL, that I hope to learn more about and blog about here. Stay tuned.

Posted by Douglas Reilly at 10:21 AM  Permalink |

One of the things I have discovered about myself, as I get older, is that I do not like change.

Being a software developer, this is a bad thing. In fact, in most cases I overcome this predisposition to not change, and embrace the new tools or technology. One area in which I have found this difficult is in the area of tools for managing SQL Server. Enterprise Manager and Query Analyzer for SQL Server 2000 are tools I spent the great bulk of my time with for a number of projects.

Then, along comes SQL Server 2005, a release that changed the tools more than they have been changed in a number of releases. I understood that SQL Server Management Studio was a big improvement in a lot of ways - most specifically in that it was not a modal application, meaning you could have multiple stored procedures or views open for editing at one time and easily switch from one tho the other. That said, I still kept the 2000 tools around so that I could use them. I used them when I just could not find the similar function in SQL Server Management Studio.

I went to a talk here at Tech Ed about SQL Server Management Studio (SSMS), and became aware of some new features that will likely change my point of view. One feature that will make whatever learning I need to do worthwhile is the ability to script out what the tool is doing. Creating a database? You can have SSMS script out what you are about to do. The scripting ability applies to almost anything you are doing. While I often will just type in the SQL when I need to have it as a script (for instance, when I need to send a script to a client), sometimes using SSMS and then scripting out the operations will be quicker and cleaner.

So, I expect I will try to learn where everything is in SSMS and use it almost exclusively, though I will keep the 2000 tools around, at least until that SQL Server 7 server at one of my cllient sites is retired...

Posted by Douglas Reilly at 01:07 PM  Permalink |

The new Database Professional SKU of Visual Studio was something I was looking forward to seeing. I was not disappointed...

Some of the features, such as moving around schema chages, seem like a natural extension of VS, though it is not something I neeed terribly, as I am using the wonderful Red Gate tools for that.

More interesting was the feature that allowed the system to create batches of test data. I suggest the Microsoft immediately hit up the Veterans Administration here in the US, as they have just been caught with a developer bringing live data home and having millions of veterans personal information exposed. Creating test data that looks like the live data is a very cool idea, and for me might be the single most useful feature added to the Database Pro SKU.

There are a number of other sessions on the Database Pro SKU, and I hope to make it to a number of them.

Posted by Douglas Reilly at 10:01 AM  Permalink |

Today at Tech Ed, I met with Michael Howard, co-author of the new book, The Security Development Lifecycle.

The Security Development Lifecycle is the way that Microsoft intends to create more secure applications, as well as mitigating risks when there is a security risk. SDL is a 13 step lifecycle that proposes to be a process for developing demonstrably more secure software. I think the "demonstrably more secure" is the key to that statement. For far too long, any security project has been sold by describing all the terrible things that could happen if you do not do "x", whatever "x" might be. By creating code that is clearly more secure, and in general more reliable, you can get the budget required.

Microsoft is the place where the SDL took shape, and by all accounts, it was a company most in need of such changes. Microsoft was hit by lots of problems, from the slammer virus on down, within a short period of time. Part of the problem was timing. Many of the applications and OS' that had vulnerabilities were designed for the pre-Internet era. Now, when broadband connections are virtually universally available, much of the software created in the 90's looks pretty bad.

So, what does the SDL entail? The key to implementing an SDL is developer awareness. From the book, I knew that MS requires developers to have at least once a year classes covering security. Having been to a number of employer mandated classes that made my eyes bleed from boredom, I was happily surprised to hear from Michael that compliance at Microsoft is not that difficult to obtain. The fact that BillG wrote an email on security certainly helped.

Best Practices, Risk Assessment and creating documentation is next. One problem these days is that many of the security risks are not entirely an issue of development. Administrators can do things to create security vulnerabilities. For instance, when editing the Web.Config file on an ASP.NET application, it is not uncommon for folks to rename it Web.Config.Bak. Unfortunately, when doing so, the file becomes servable, meaning that if someone requests the file, it will be streamed and displayed on the browser. This is clearly not a development issue, but developers can help by documenting what sortd of actions administrators of the system can take to expose the system to security risks.

The SDL goes on to address some specific coding issues (mostly C++ issues, often dealing with functions that should be banned). A surprise to me was how strongly Michael felt about the banning of the "n" functions in C++, such as strncpy and sprintfn. At one point, those were the more secure functions used in place of strcpy and sprintf. The problem with these functions is in the complexity and some confusion over the implementation of the functions. The book covers a number of safe string handling functions, certainly something that C++ developers should look at.

The interesting thing about The Security Development Lifecycle is that, unlike Michael's other books, this is very specifically not a code heavy book. It is a book that developers can gain from reading, but it is also something that you could hopefully get your currently security clueless manager to read. I have not read the entire book (I have read chapter 1 and parts of the chapters related to C++ functions) but thus far, it is an interesting look at an area where we for too long have generally not focused.

One database related question came up in my interview with Michael, and he assured me that the security team works hand in hand with the SQL Server folks, and that in general, SQL Server has been a success story, with very few vulnerabilities exposed for recent releases. I have always been impressed with the fit and finish of the SQL Server products, and so it was good to hear that it is not just my impression. The code is good!

Posted by Douglas Reilly at 02:26 PM  Permalink |

A little later today, I will be heading up to Boston for Tech Ed. I expect to be blogging from the event throughout the week. Stay tuned!

Posted by Douglas Reilly at 11:08 AM  Permalink |

I try not to pick on other peoples database designs, even when I am tortured by their unusual design. For this database, I will make an exception...

Here is a post from the Daily WTF Web site. The designer of this database, rather than using actual dates in all their tables, used a DateID that pointed to a date in a seperate table for all dates. Note that the problem involved was that the date table had not been updated (the date table needed to contain a date in order for it to be used).

In case the problem is not clear to you, using real dates in the database, and then having a table of special dates if need be, is a much more logical way to use dates. Of course, even here, you need to make some sort of arrangements for new dates to be added.

Posted by Douglas Reilly at 04:22 PM  Permalink |

A new site, linqinaction.net is now available, with lots of blog entries on the subject of LINQ.

Especially cool is the entry that shows how you can visualize what LINQ is doing in the background, by looking at the SQL generated behind the scenes. The query visualizer looks like a wonderful tool.

Posted by Douglas Reilly at 12:57 PM  Permalink |

From time to time, being able to discover the names of all databases on a SQL server can be very helpful. There are a number of ways to achieve this goal.

Keith Rull offers one way, using the sp_databases system stored procedure, as he describes here.

Posted by Douglas Reilly at 10:51 AM  Permalink |

Like many SQL Server users, I always knew that placing the MDF and LDF files for a SQL Server database on a compressed drive was a bad idea. But exactly why?

Here is a link to a well written, well thought out explanation of exactly why placing SQL Server data files on a compressed drive is a bad idea. Ken Henderson wrote the article, and Ken is someone who really knows a lot about SQL Server, and an awful lot about writing server-based applications.

In short, the reason why a compressed drive is a particularly bad idea is that when reading and writing to a compressed drive, Windows never honors a request for asynchronous I/O, something that slows down operations a great deal.

Posted by Douglas Reilly at 01:11 PM  Permalink |

ScottGu comes across with an amazingly complete blog entry on using LINQ and ASP.NET. LINQ is Language Inline Query, a way of using constructs inside the language to query various data sources. It seems a little strange at first, seeing SQL like syntax in your C# or VB code, but it is very neat under the covers when you discover how the seeming magic actually can be decomposed into easily understandable language statements.

Posted by Douglas Reilly at 05:50 PM  Permalink |

Here is a blog entry explaining how SQL Mobile will be able to synch with MS Access. Frankly, I do not think I would use it, but it is nice to have the option.

Posted by Douglas Reilly at 12:31 PM  Permalink |

Microsoft has announced a new SKU for Visual Studio, this one centered on database professionals. The current database tools in VS 2005 are really quite good, however, the additions planned for the new database professional targeted version sound cool. Especially neat is a refactoring tool that will allow you to rename objects and have the name change propagated throughout the database.

Several of the new features seem to compete with current third party offerings, including some tools I have found indispensible, like Red Gate's SQL Compare.

The team is blogging about it, and entries can be found on the following team member blogs.

http://blogs.msdn.com/gertd/

http://blogs.msdn.com/camerons

http://blogs.msdn.com/rwaymi

http://blogs.msdn.com/mattnunn

http://blogs.msdn.com/thomas_murphys_agile_db_blog

http://blogs.msdn.com/tsdatabl

Posted by Douglas Reilly at 10:35 AM  Permalink |