February 2008 - Posts

What motivates geeks?
I recently moved a project from .NET 2.0 to .NET 3.5 and discovered that a method I had used to deserialize some JSON had become obsolete. This prompted much debate with my colleagues about why the .NET framework continues to change.

The guys I was talking to both reckon that a large part of the motivation for Microsoft to keep changing the .NET Framework is just to make money. I guess in their view of the situation, the desire for change is being driven from the top, and Scott Guthrie and co. are coming up with changes to please their financially-minded masters.

I don't buy this. I can't imagine the .NET architects sitting round a table in Redmond saying "We have been told to change things for .NET 4.0 so our users have to upgrade - can anyone think of anything we can change?"

My view of the situation is that change is being driven from the bottom: the guys who actually design and build the Framework, who are passionate about creating the best development platform they can, are constantly hoping to improve things for the millions of developers out there using .NET.

The fact that I'm struggling to adapt to a change in JSON deserialization is very frustrating, but I have no doubt that the person who took the decision to deprecate the old method and create the DataContractJsonSerializer class did so because they thought it would be better than the JavaScriptSerializer.

Obviously the money men in Microsoft would have been pleased that the JSON guru decided to improve things, as it's one microscopic part of the reason that people will upgrade to 3.5, but I firmly believe that the changes are driven by people who are engrossed in the technical side of their job, and are desperate to make .NET as good as possible.

They are, after all, just another bunch of geeks. They may be more talented and more influential than your average bunch of geeks, but they are fundamentally just another team of geeks doing their jobs. In my experience, whilst geeks want to get paid just like anyone else, what they really want is to make things work better.
Programmatically Listing SQL Servers

Using the .NET Framework there are a variety of ways you can search for available SQL Servers, but no single technique does the whole job. The best approach is to use a combination of methods to ensure you get the most complete list possible.

Most methods rely on a UDP broadcast to locate servers - this is good for finding unknown servers which haven't been registered but doesn't guarantee to return the definitive list. Timeouts and firewalls blocking the relevant ports can lead to servers missing from the list and, if you're not on a network then even local servers will disappear from the list, as the UDP broadcast fails completely. In addition the list of servers is not guaranteed to be the same if you run the search twice.

The main alternative is to search the registry for registered SQL Servers. Whilst this is fine for servers you know about (and works when the network is unavailable) it doesn't allow you to discover new SQL Servers.


Method 1: UDP Broadcast

Used by:

The first two actually are actually the same under the covers: EnumAvailableSqlServers is simply an abstraction of the SqlDataSourceEnumerator class.

EnumAvailableSqlServers example

Pros:
+ Includes SQL Servers which are not registered

Cons:
- Doesn't work when there's no network connection
- Subject to firewall rules (Blocked TCP 1433 and UDP 1434)
- Doesn't find SQL Servers if SQL Browser is off
- Doesn't find SQL Servers if they are hidden
- List contents not guaranteed to be repeatable


Method 2: Reading Registered Servers from the Registry

Used by:
Microsoft.SqlServer.Management.Smo.SmoApplication.SqlServerRegistrations.RegisteredServers

RegisteredServers example

Pros:
+ Works with no network connection
+ Works regardless of firewalls
+ Shows all registered servers, regardless of SQL Browser/hidden status

Cons:
- Only shows SQL Servers which have been registered
- Will be removed in a future version of SQL Server according to MSDN


Method 3: SMO WMI

Windows Management Instrumentation provides a third way to locate SQL Servers.

WMI example

Pros:
+ Works with no network connection

Cons:
- Only lists SQL Server 2005 and later
- Only finds registered servers


Putting the methods together

If you're after a list which matches that shown in SSMS (or Enterprise Manager) then looking for registered servers will probably be an acceptable solution. If, on the other hand, you want to show all available SQL Servers (as in the SSMS "Browse for Servers" dialog), then your best bet is probably to combine one of the UDP methods with a list of registered servers. This will give you a reliable list of registered servers even when the network is down, and will include unregistered servers where possible.

One of the most user-friendly ways of doing this is to initially populate your control with the registered servers (which can be done very quickly) but allow the user to initiate a search for further network servers (which can take several seconds) in case they're looking for an unregistered server. You should always allow the user to type in a server name too, as the server may be available even though it's not returned by the UDP methods described above.

 
And another thing...

Depending on your requirements, you may need to include the instance names in the list. The methods I've mentioned all return instance names of the non-default instances (e.g. MyPC\SQLEXPRESS) but they handle them in different ways - the MSDN documentation gives full details.

Extending SMO Classes with Extension Methods and IEnumerable(T)

One of the simplest ways to audit changes to a table is to have a trigger that stores a copy of any row that's inserted/updated/deleted, together with some meta data such as who made the change and when they made it. There are limitations (such as several types which can't be used in the inserted and deleted tables) but often this method of auditing will be sufficient.

Because of the limitations, you will normally need to specify a column list in your trigger, and this list needs to be kept up to date if the table being audited is ever changed.

While building a tool to keep audit triggers and audit tables in synch with the tables being audited, I recently found myself trying to inherit from various sealed SMO classes. Having discovered that this was impossible, I tried a different tack: extending the SMO classes with .NET 3.5 extension methods.

As ScottGu's examples show, extension methods can be extremely simple.

I added a simple method to the Column class to expose whether the column's type was available in the inserted and deleted tables (i.e. whether the audit trigger could use it), then extended the Table class to expose a collection of these auditable columns.

IEnumerable Extension Method

The solution is actually extremely simple, but I found very little written about using IEnumerable or IEnumerable(T) within an extension method. That may be because it's so straightforward that no-one has written about it, but hopefully someone will find this useful...

One thing which would be nice in a future .NET release is extension properties - both the methods mentioned above would be more satisfactory if they were properties.

How to fix slow SMO performance

By default, when SMO queries the database for the properties of an object, it only loads the basic properties. This reduces the number of tables it needs to join in the query, so dramatically improves performance, as long as you don't need the other properties.

As an example, lets look at building a simple front end to display the tables in a database and the columns of the selected table.

SQL Server application

Using SMO to request the TableCollection, the query requests just the schema name and table name from sys.tables. This is perfect for our requirements: we can populate our list of tables using the names and we don't care about the other details. We've saved time because we didn't join to the other system tables we would have needed if we were interested in properties such as indexing, keys, partitioning etc.

Now we come to populate the column details. If we use SMO in its default mode then populating the ColumnsCollection of our table will use a simple query getting the column's ID and name, joining sys.tables with sys.all_columns:

SMO SQL

If we were only displaying the column name that that would be fine. However, our listview is going to show more than just the name, and we create our listviewitem objects using some other interesting properties of the column:

Creating ListItems

Running SQL Server Profiler you'll notice that SMO has lost its way a bit. We'd expect it to fire another query to get the extended properties, but it does the extended query once for each column! Given that the full query now joins 13 tables, it's no surprise that people complain about SMO being slower than DMO.

SetDefaultInitFields
The solution to this problem is to tell SMO what you're going to need in advance. Using SetDefaultInitFields you can tell SMO to load all extra properties or you can even specify a list of exactly which properties you're going to need.

SetDefaultInitFields is set at the server level. The simplest way to set it is just to tell it that all column properties should be loaded at once.

Using SetDefaultInitFields

Checking the SQL Profiler again reveals that the initial query is now the 13-table version, so is a little bit slower, but there are no further queries. If we were to specify the exact list of properties we were interested in then we could cut down the number of tables joined and make it even faster.

Bonus improvements: AddRange and BeginUpdate
If you do write an application which sticks the column details into a listview, there are two other ways to improve performance: the AddRange method of the listview adds a collection or array of listviewitems to the listview in one operation, so avoids the nasty flickering as the control is repainted between add operations. If you prefer to use add, then the other way to stop the flickering is to use BeginUpdate and EndUpdate.