David Grey's Blog

Code Generation - One Ring to bind them all

Another interesting ARCast podcast I listened to recently was discussing issues of code generation and the suggestion was that code generation is a great tool to use to do all the repetitive grungy bits of coding, which it is. The interesting bit though was the suggestion that if all your teams need to do similar things across their projects (e.g. data access layers to access common databases) then using code generation with standard templates is a great way to ensure consistency of practice across your teams and suggests (but doesn't guarantee) one approach to stop them re-inventing the wheel.

The interviewee took this suggestion one step further and urged us to view code generation as a form of code 'normalisation'. In much the same way that data normalisation seeks to eliminate duplication in a database and leave only one source of truth, he urged us to encapsulate all the patterns and practices and experience of the development teams into common code generator templates which all the teams use to perform code generation of the repetitive bits of their projects. In this way the code generator templates become the repository of all that best practice and wisdom and are the one source of truth on coding certain common behaviours. I'm not sure how well this 'one Ring to bind them all' approach would actually work in practice as it requires buy-in from all teams and the discipline to use the code generator rather than rolling your own, but it was certainly an interesting idea. I'm a big fan of using code generation (CodeSmith rocks! and lets face it, VS does rather a lot of code generation on our behalf) to make my life as a developer somewhat easier by doing all the repetitive, mundane stuff and leaving me to concentrate on the more unusual or challenging aspects of the software. I encourage others to use code generation where possible so I'm quite keen to try this idea out with some of our teams. If it ever happens I'll let you know how we get on.

del.ico.us | Digg It | Technorati | StumbleUpon | Furl | reddit

Bug Free Code?

As I have a reasonably long journey to work in a morning I like pass the time by listening to stuff on my MP3 player. Sometimes I listen to music, sometimes to audiobooks and sometimes I listen to various technology podcasts.

One series of podcasts I really enjoy listening to is Ron Jacob's ARCast show on Channel 9. If you haven't listened before, I heartily recommend it. There's a great mix of general architecture and development stuff and also some good specifics on Microsoft technologies.

I was listening to an old ARCast episode this morning, an interview with Dr Neil Roodyn on building zero defect software. This wasn't some half-baked nonsense suggesting that bugs don't exist in code but a well-reasoned discussion around various issues relating to building high-quality code. The best bit for me was the psychology of the developer and the suggestion that building zero defect code is a mindset. The thesis is that if you approach development with the frame of mind that all software has bugs and my code is bound to have them  it will become a self-fulfilling prophecy and there'll be bugs in your software. If you adopt a more positive mindset of I'm going to do everything in my power to avoid bugs the software you ship is not going to be provably free of bugs but it will certainly contain far fewer of them and be of much higher quality. It really was a most fascinating discussion and well worth a listen. There's some interesting opinions on the value of bug-tracking databases too.

del.ico.us | Digg It | Technorati | StumbleUpon | Furl | reddit

Moving servers between AD containers causes authentication failures for SQL Server Merge Replication using NT Authentication

We recently deployed an application for a customer which makes use of merge replication between two SQL Server instances. Due to the nature of the Windows infrastructure in which the application is deployed we chose to use NT Authentication for the merge replication subscriber to connect to the publisher server. To make this work we created a local machine account on the publisher server and granted it a login in the publisher SQL Server. We created an account with the same username and password on the subscriber server, granted it a SQL Server login and set it as the account used to synchronise subscriptions. Using these credentials the subscription agent job on the subscriber server can authenticate against the subscriber server and against the publisher server to synchronise the subscription. As well as the local account used for subscription synchronisation, the subscriber server also has several local machine accounts, with corresponding SQL Server logins, that are not used for subscription.

All was working well until the customer decided to reorganise their Active Directory and moved the subscriber server into a different Active Directory container.  When this change was made the merge replication suddenly stopped working. Investigation of the SQL Server logs on the subscriber server revealed a number of login failures for the local machine account used for synchronising the subscription.

Login failed for user 'SERVER\User'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 11

We eventually figured out what was going on. It appears that when a server is placed in an AD container some identifying information about the server is cached by SQL Server. This information seems to be used in authenticating the subscription synchronisation account against the local SQL Server. When the subscriber server is moved to a new AD container this identifying information obviously changes and the cached copy is no longer in sync. This causes SQL Server to deny the login and produce the error message listed above.

The solution to the problem is simple

• delete the SQL Server login and associated database user for the local machine account used for the replication subscription 
• recreate the SQL Server login and associated database user for the local machine account used for the replication subscription and assign this login/user to the appropriate server and database roles

This seems to bring everything back in sync and the replication starts working again. We've noticed that a similar thing happens if the publisher server is also moved to a different AD container. In this case, the subscription synchronisation account cannot be authenticated against the publisher server and similar login failures appear in the publisher logs.

We're still not sure what information is changing/being cached by SQL Server which causes this problem to occur. However we have been able to determine that the issue only seems to affect accounts used for replication; the other local machine accounts/SQL logins on the subscriber server are unaffected when the server is moved to a new Active Directory container. If anyone can shed some more light on this for me I'd be interested to find out what's going on. If I find out any more I'll post the information here.

del.ico.us | Digg It | Technorati | StumbleUpon | Furl | reddit

Red-Gate SQL Tools

Some time ago I was given a licence for the first version of Red-Gate's SQL Prompt tool which provides Intellisense on SQL Server databases whilst using SQL Management Studio or Visual Studio. I was impressed with the tool as the intellisense support it gave was excellent but I didn't make a great deal of use of it because it had one large drawback - it was sloooooowww. On my development box I'd finished typing the Sql before SQL Prompt had brought up it's Intellisense.

Recently I've been evaluating Red-Gate's SQL Prompt bundle, which contains SQL Prompt, SQL Doc and SQL Refactor. I was keen to see how SQL Prompt had improved in its latest incarnation (version 3) and I have to say I am really, really impressed. The whole tool has been re-engineered and it is now many orders of magnitude quicker. In fact it is lighting fast and much quicker than the VS Intellisense in Visual Studio. The intellisense is also really useful and makes writing correct SQL queries simplicity itself. I can't praise this tool highly enough, it's brilliant! This kind of tool should really have shipped with the SQL Management Studio. If the folks at Microsoft have got any sense they should make Red-Gate an offer for this tool and distribute it as part of the SQL Server product offering.

I'm also really impressed with SQL Doc, a straightforward tool for documenting SQL Server databases. This is a great example of a well-built, useful software application. It has a nice straightforward user interface and you simply point it at your database, select one or two options, press the button and seconds later a well-presented set of HTML documentation exists which describes your database in great detail. If you want you can select which database objects are documented but the tool gives you very little control over the output it produces, and in my opinion, this is its strength. In the words of the advert, it does exactly what it says on the tin - produces high quality documentation of your database without fuss. I can imagine that some users may find the lack of customisation and configuration disconcerting but personally I think it's fantastic as the default behaviour is exactly what I want from a database documenting tool. Best of all, in less than 2 minutes after installing the application I'd produced my first full set of documentation for one of our production databases. It doesn't get much better than that! If you're looking for a tool that does a good job of documenting your databases then just buy SQL Doc.

del.ico.us | Digg It | Technorati | StumbleUpon | Furl | reddit