A workaround to a problem when upgrading BlogEngine from 2.0 to 2.5

During the BlogEngine upgrade from 2.0 to 2.5 (one that hosts this blog) I’ve come across a problem. The problem might happen during execution of the SQL Server upgrade scripts that come with BlogEngine 2.5.

After running the scripts I’ve got an error mentioning that constraint FK_be_PostComment_be_Posts can’t be enforced. Huh? After some experimenting I’ve seen that the 2.0 database isn’t exactly well enforced with constraints and I had some comments left that don’t belong to any of the posts (I guess I’ve deleted the posts but comments were still there because database didn’t enforce the constraints and BlogEngine didn’t delete them).

Here is what I’ve did to upgrade.

1. In the upgrade script comment this statement:

       1: ALTER TABLE dbo.be_PostComment
       2:   ADD CONSTRAINT FK_be_PostComment_be_Posts FOREIGN KEY (BlogID, PostID) REFERENCES dbo.be_Posts (BlogID, PostID)
       3: GO

2. Run the upgrade script.

3. (Optional) To find if any comment is parentless execute this SELECT statement

   1: SELECT * FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)

4. Delete the orphaned comments

   1: DELETE FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)

5. Run the statement from upgrade script that you’ve commented in #1.

That’s it. I guess you could delete orphaned comments even before running the upgrade script and thus avoid first and last step.

Observation: Looks like at least database in version 2.0 wasn’t very well enforced, hopefully 2.5 rectifies this problem (it adds constrains here and there). Don’t forget, database is the last defense tier against bad data and should be as much protected as it can be.

Calling WCF services from within Sql Server

It is possible to create a managed .net stored procedure for SQL Server that acts like a WCF client. SQL Server 2005 at least is required. But I won't talk about how to. I'd rather discuss an odd error you can encounter while performing this stunt.

On development machines one can encounter this odd error when WCF client is being initialized, after managed stored procedure is being invoked through T-SQL.

Msg 6522, Level 16, State 1, Procedure CalcAdd, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "CalcAdd": System.Configuration.ConfigurationErrorsException: The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 192) System.Configuration.ConfigurationErrorsException: at System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult) at System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject) at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission) at System.C...

Note that everything is configured properly and WCF client inside stored procedure should initialize just fine. The problem is somehow connected to machine.config file or better, to some initialization of types defined there.

After few google searches I've come across this post from MS' Jason Pang. Looks like the exception is linked to WCF debugging feature, usually installed on development machines - this debugging service has problems with WCF client running under SQL Server.

Workaround: (Temporary) disable WCF debugging by invoking this command line utility:

[Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u

Now the WCF client inside SQL Server runs just fine. After you've done you can re-enable WCF debugging services by calling same exe with different parameter:

[Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -i

Note that administrator privileges are required to disable/enable it.

Minimum database permissions for using SqlDependency with SQL Server 2005

If you wonder what minimum permissions you need to use SqlDependency with SQL Server 2005 then look no further. This code project article definitely works and is the most complete one I've found. Note that setting minimum database permissions for notification isn't exactly the easiest task on the planet. Actually through experimenting I've managed to create a service_queue in sys.objects that I couldn't remove without restoring the database entirely (luckily I am using RedGate's SQL Backup 5 for backing up my databases - it makes the backup and restore process so easy, not to mention the encryption and compression capabilities).

Anyway, after restoring the database to the initial non-notification enabled point (clean state), I managed to apply proper minimum permissions following the mentioned article.

Peculiar problem involving Windows 2003, VMWare Server, SQL Server 2005 and networking

Over the weekend I've built my new server - yes, the content you are reading right now is served from it. Perhaps more about this new server in another post. Back to the point. Host OS on the server is Windows 2003 R2 x32 and there is also a SQL Server 2005 running there. I've had x64 bit before but it is just too much trouble running it since the drivers and support situation. So, if you don't need more than 4GB of RAM then I don't see a compelling reason to go with 64 bits. Why I say "host OS"? Because I am running VMWare Server on top of it. And there are two guest OS running inside virtual machines: Another Windows 2003 that serves web content (it uses SQL Server located on host) and Windows Home Server that takes care of backups. So, after I've installed everything I fired up my web virtual machine and take a took a look at my blog - it was a no go. Virtual machine was working fine, just the Community Server wasn't running. After turning off custom exception handling I ended with a asp.net exception reporting page which I was looking for. However the error was an odd one. It stated that connection with SQL Server (running on host) has timed out. Hm. I investigated further by creating a test application that reads a table from database. Running on my workstation it read the data just fine. But when running from within web server it read just first n rows (i.e. 20) and then it timed out, always at the same row - which was really puzzling. The same symptom appeared to any SQL Server client running within guest OS. So it was obviously a problem related to VMWare Server. Yet, if I've turned off Windows Firewall on the host my application worked even on guest OS - this fact deceived me to thinking that the problem is firewall related (perhaps it was in a way) - after half an hour testing with any possible firewall configuration I gave up. Since I knew it has something to do with VMWare Server I then started searching their forums. And soon enough I've found a solution (at the bottom of the thread):

Disable TCP Offload on the host

While the solution talks about disabling TCP Offload Engine on Windows, I've disabled TCP Offload LargeSend (it sounded enough similar to me and a good candidate for my problem) and it worked like a charm. This is how it looks on my computer:

image

Perhaps networking now consumes 0.00000000001% more of my CPU but at least it works fine. I am not sure whether this is a bug or not, I'll contact VMWare anyway. Funny, the building and installing my server took less than troubleshooting this problem.

Post CodeCamp impressions

On saturday I held a speech and co-host a "round table" at CodeCamp in Zagreb in front of a crowd of ~60. My presentation was well received and understood (at least I was told so) even though my Croatian is not perfect - English and hand gestures helped. The entire event was at high level with world-class speakers (not counting me in this category) worth attending.

And I have to tell that community in Croatia is amazing. They are passionate, communicative, skilled, some of them came even from Dubrovnik (500km) or Slavonija region, and  lastly, they are willing to sacrifice entire warm sunny Saturday for an indoor event. OK, they got plenty of very useful swag, but still, they were there for the content. It is a pleasure speaking for such crowd and among great speakers.

SQL Compare 6 beta

Red Gate is preparing SQL Compare 6 product. The interesting thing about this version is that they are getting ready for supporting source control for storing database schemas. Unfortunatelly the source control support won't be a part of version 6 but they are laying the foundations for it. And now is the perfect time to speak up about your idea of source control support. Read more about SQL Compare 6 beta, the challenges of database and source control and post your opinions and suggestions in SQL Compare forum.