Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Linq’ Category

Audit Trails and Logging

Posted by sqlwiseguy on June 10, 2008

This week at SQLServerCentral there have been a couple of articles on Audit Trails and Logging (Part One and Part Two) that did a good job giving the basics of logging changes made to the data. In the discussion for part two someone asked about logging select statements and the returned results. While logging the selects is reasonable, logging the results of each query is, in my mind, a little out of whack. I understand why you may want to log the results as they may change over time. For example, Mary Smith married Joe Johnson and takes his last name, or hyphenates so she is now Mary Johnson or Mary Smith-Johnson. A query for LastName = ‘Smith’ the day before the change returns Mary, but the day after it does not. So in the event that data was compromised it will be more difficult to determine whose information was compromised if only the query is logged and not the data. There are several questions that come up now:

  • How much space do you have for logging and is the expense associated with it justifiable?
  • How far back do you keep the logged data?
  • Do you log queries of the logged data?
  • Who has access to the logged data?

These are questions that come up right off the top of my head and do not address the mechanics of the logging. There are no “Select” triggers in SQL Server so that eliminates triggers, which also eliminates being able to log ad-hoc queries using Access, SSMS, Query Analyzer, or any OLE DB or ODBC connection (Excel). One solution suggested in the discussion is using a server-side trace which works for logging the query and who ran it, but will not log the results. As I am a firm believer in not granting direct table access, I would start out by putting logging in the stored procedures used for data access. Of course, that can lead to some issues with developers who would prefer to use an ORM tool, like Linq To SQL or SubSonic, for the data access layer. Granted they are still able to use stored procedures through these tools, but one of the selling points is that you “never have to write another stored procedure”. So now they not only have to data access code in the DAL, but also auditing code. If the audit needs to include the data, can I do a batch insert with the ORM tool using the dataset, list, collection I already have or do I have to iterate through the results and insert one at a time? Or do I have a separate call to the database that runs the select again, like:

Insert Into audit
Select
query,
user,
data columns….
From
table(s)
Where
criteria

This means now I am storing the user and query with each row returned and I am doubling the reads on my database.

I certainly don’t have the solution to this problem. I know that for Inserts/Updates/Deletes, I like triggers because they put all the code in one place and will fire on all changes unless someone explicitly disables triggers before making a change and if this happens you have other problems.

I’d be interested in hearing what other people think.

Posted in Linq, SQL Server, SqlServerCentral | Leave a Comment »

Another Microsoft Inconsistency

Posted by sqlwiseguy on April 28, 2008

Brian Knight just did a blog post about how, with SQL Server 2005, using (local) for the server name in a connection times out. Now, his post specifically deals with DTS/SSIS, but his discovery about why is times out, (local) uses named pipes which is disabled by default in SQL Server 2005, is interesting when discussing Linq To SQL. The SQL Server team is disabling protocols to try to make SQL Server more secure, while a the same time the Linq To SQL team has designed Linq To SQL to ONLY use Named Pipes to connect to SQL Server. So while one team is attempting to close a door another is forcing that door to be open. Then again, maybe the SQL Server team doesn’t like Linq To SQL, like most DBA’s based on my reading, and shut the door for this reason.

Another note is that SSDS (SQL Server Data Services or SQL Server on the Cloud) requires developers to use Linq. As referenced in Andy Warren’s It Depends blog.

Posted in Linq, SQL Server | Leave a Comment »

Linq and Linq To SQL

Posted by sqlwiseguy on April 25, 2008

There is an interesting discussion on SQL ServerCentral.com about Linq To SQL based on this editorial by Steve Jones. You will see the DBA’s perspective on such tools and, I think, some misunderstanding, particularly about Linq and even Linq To SQL. Linq stands for Language Integrated Query and Linq To SQL is just one implementation of Linq (go here for more Linq implementations), so please when discussing Linq be specific as Mike Taulty has asked in his blog as well.

Basically Linq allows you to access objects with a common syntax something like (in C#):

var query = from p in persons
where p.LastName == “Corbett”
orderby p.Age
select p

The great part is that persons is the object you are querying and it can be a class, a list, an array, a database query, etc… You can also have Linq query that joins objects which is a lot easier to read than nested loops to find matches.

Now back to Linq To SQL. As a hybrid DBA\Developer I see some good things about Linq To SQL, but I also know, as the “protector of data”, that having to allow table level access, which you need for Linq To SQL, opens some security holes. I like the way Linq To SQL builds my .NET objects, but wish it made using stored procedures for CRUD (create, update, delete) operations simpler or the default. Since it already has to build the SQL to do the operations, why not have it create a stored procedure? I think it could be an option when building the objects and the designer could ask you for a template for naming the stored procedures.

Also mentioned in the discussion is a mapping tool called iBatis.Net, which looks interesting and, I think has a nice implementation using XML files. It would be nice if MS incorporated something like that in Linq To SQL so you could more easily see, test, and tune SQL statement. Wouldn’t it be nice to have Linq To SQL build your objects with all of the method signatures and properties and an XML file that defines the SQL for each of the methods. If it did that, then you could just edit the XML to use stored procedures.

It’s important to remember that Linq (including Linq To SQL) is a 1.0 product and that there is definitely room for improvement and we can encourage it by letting MS know what we think the improvements can be.

Remember it is a tool and just like a hammer has certain things you use it for, the same can be said of Linq and Linq To SQL. Make sure it is the right tool for the job!

Posted in Linq | Leave a Comment »

Linq To SQL Requirement

Posted by sqlwiseguy on March 26, 2008

As I was reading some Mike Taulty’s blog I came across this link to an MSDN article, http://msdn2.microsoft.com/en-us/library/bb386929.aspx, where I learned that Linq To SQL requires that the Named Pipes protocol to be enabled. Now, I normally have this and TCP enabled on my SQL Servers, but doesn’t this contradict Microsoft’s practice of reducing the surface area exposed.

Posted in Linq, SQL Server | Leave a Comment »

Orlando Code Camp, Linq, and the DateTime Datatype

Posted by sqlwiseguy on March 25, 2008

I spent last Saturday at the Orlando Code Camp and had a good time. I went to most of the Linq sessions. Since my co-worker, Rodd Harris, and I are in the process of developing a new application in C# and SQL Server, I felt like I needed to become more familiar with Linq. I really enjoyed Jim Wooley’s sessions on Linq to XML and Building Data-Driven Web Sites Using Linq. Check out his web site at ThinqLinq.com, which is built using Linq.

I am mainly a SQL Server DBA/Developer, so I have some concerns about Linq To SQL regarding security, performance, and maintainability. Especially when I hear things like, “With Linq, you’ll never have to write a stored procedure again.” Well, I would beg to differ with that comment. Stored Procedures help isolate the database from people who think they know SQL and those who know how to use Access or ODBC to get to the data. Call me paranoid, but I think that some paranoia is necessary for a good DBA. I certainly believe Linq is a useful tool to have in your toolbox, but it should be used with thought as every tool should be. Toward the end of the day I had a good discussion about it with Andy Warren from End to End Training. He and I have similar feelings about Linq To SQL, but I think Andy is a little more anti-Linq To SQL than I am.

The coolest thing I learned about Linq was that it gives developers the ability to do heterogeneous joins. So I can have a Linq to Objects query and join on a Linq to SQL query. Now that is cool! For example this code creates an xml document using the new VB support for xml literals and then joins a Linq To XML query to a Linq To Objects query (taken from Jim Wooley’s Linq To XML presentation):


Dim fileTypes = <FileTypes>
<FileType extension=".xml" description="Extensible Markup Language"/>
<FileType extension=".dtd" description="Data Type Definition"/>
<FileType extension=".htm" description="Hypertext Markup Language"/>
<FileType extension=".rtf" description="Rich Text Format"/>
<FileType extension=".txt" description="Text File"/>
<FileType extension=".wpd" description="WordPerfect Document"/>
</FileTypes>

Dim results = _
From fi In New System.IO.DirectoryInfo("C:\projects\linq\shakespeareXml").GetFiles _
Join ext In fileTypes. On fi.Extension.ToLower Equals ext.@extension _
Where fi.LastAccessTime >= Now.AddMonths(-6) _
Select FileName = fi.Name, Exten = ext.@description _
Order By FileName Descending

For Each item In results
Console.WriteLine(item)
Next

If you have the opportunity to go to these events and local user groups you should make the effort. It is worth it. You will always pick up something useful and it’s a great opportunity to network.

I also found a cool blog post over at SQLSkills.com on why SQL Server’s datetime data type starts at 1/1/1753, 1753, datetime and you.

Posted in Code Camp, Linq, SQL Server | Leave a Comment »