Category name:Uncategorized

Repair a broken Replication Sql Server 2005 vs Sql Server 2008

BEFORE YOU READ THIS:  In the end we decided to turn off the continue on conflict option, because too many conflicts were ignored and the tables got too far out of sync… So the continue on conflict is not such a great option afterall…

This is actually a personal note that I wrote down in june 2007.

That day we broke the peer to peer replication across our three servers. But I fixed it without having to restore databases and rebuild the replication.

 

The bad news: Today we broke replication again. This time the cause was a unique index on 2 columns that was being filled from 2 nodes simultaniously. That is BAD.

The good news: I fixed it.

Last time the replication broke I noticed a list of stored procedures that are responsible for theC(R)UD operations of replication. There are insert, update and delete procedures for all tables in replication and for every subscribing node. So for 1 table there are 2 insert, 2 update and 2 delete procedures.

To fix replication you can tweak these stored procedures to return success even in case of a failure. But be careful! If the databases get even more out of sync that’s even worse!

If a delete or update fails you can skip the error message that the row does not exist on the subscribing node.

However if an insert fails, you will have to write your own check to see if the insert will fail and than skip that insert.

 

Last friday the support team had a problem with peer-to-peer replication after installing SP 3 for Sql Server 2005. Even the update for SP3 did not resolve the issue. The issue was that every 30 seconds replication claimed that the process cannot allocate memory, although it was still replicating.

So with replication in a fault state and a wish to upgrade to Sql Server 2008 in the near future the support team decided to upgrade now and hope that that would fix the issue. Well… needless to say… it didn’t.

Non of the support people know how to set up replication, so I ended up doing it. The first time it all seemed to work, tracers worked fine and arrived after about 3 seconds. But the moment I started some of the processes replication failed.  That was a set back, because now the databases were out of sync…  I decided to ignore the fact that the databases were out of sync and set up replication all over again. I could do this, because I was and still am sure that these missing records on two of the nodes aren’t going to give us problems.

With a situation like this I would have had to spend at least a day to get back in sync.

As far as this article – http://technet.microsoft.com/en-us/library/bb934199.aspx – on technet claims: Once I turn on the option to continu on conflict, I do not have to manually change all the stored procedures to continu on conflict. If that is true it will save me a lot of time! First thing on monday I will try this on a test environment before I will use this on the production server.

I will definitely let you know how this went!

DataDude and VS Developer Edition will be merging

I just read the DataDude blog and look and behold in VS 2010 the DB Pro and the Developer edition will be one. And even better news: as of today the developer edition MSDN subscribers will have access to the database edition as well. This really solves a dilemma for us at work.

We do not really have a DBA in place and the developers have to do the database work themselves. This is not ideal, but I think it is a rather common practice in the business. And we were still in doubt whether to buy a database edition license for each developer or have a single database license on a server or shared workstation somewhere. The first option is great, but expensive. The second option a lot cheaper, but far from ideal (read: not workable).

Now we wait untill Microsoft will also merge the Tester edition (although the workstation option works much better for this edition).

 

Read on here:

http://blogs.msdn.com/gertd/archive/2008/09/29/team-developer-team-data-team-developer.aspx

SQL Profiler makes debugging easy…

… if you make it easy on yourself.

 

I was searching for a particular statement hidden in a Stored Procedure, but all I got was tons of statements that were irrelevant to my problem.

The problem with SQL Profiler is that it can give you lots of information, but it is not easy getting the right information. The Standard (default) template for a SQL Profiler trace doesn’t capture the statements inside a stored procedure. To do this, you will have to choose a different template: the TSQL_SPs template.

But even with the right template it could be hard to find a specific statement caused by a specific application.

By default .NET applications show up in the Profiler with the ApplicationName ‘.Net SqlClient Data Provider’. It would be so much easier if you could just filter on ‘My Application’. Well, you can.

Change the connectionstring for your application. Add ‘Application Name=My Application;’ and the ApplicationName will now say: My Application.

You can now filter Sql Profiler to show stored procedure statements of your application only and for your database.

 

For an explanation of all the different Sql Server Profiler templates you can go here: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx

Downsides of Smart Client Software Factory

I like the concept of SCSF but…

 

  1. Model-View-Presenter is a must . This is a good thing, but from a business point of view (fast fast fast) I would like to be able to hack a temporary tool quickly.
  2. SCSF still uses entlib 3.x
  3. Guidance package gives error on VSTS for DBPro projects (see…)
  4. Unable / not sure how to convert existing solutions to SCSF

So I’m looking for another way to create a shell application around all our companies business applications.

VPN and Internet at the same time

Ever had the problem you wanted to use your VPN to work but still wanted to use the internet through your own network. With XP I knew you had to add a route to the routing table. So I tried that in Vista as well.

After an hour of destroying my internet connection and rebooting about 20 times I found out that it is actually really easy to set up in Vista.

Go to the "Connect to" window and choose your VPN connection. Go to the properties window for this connection and to the Networking tab. Then Open the Properties of the TCP/IPv4 protocol. Click the ‘Advanced’ button and uncheck the ‘Use default gateway on the remote network’.

 

defaultGateway

 

After that you still have to add a route to the routing table to get access to the remote network:

route add 192.168.0.0 mask 255.255.255.0 <ip of vpn gateway> if <interface for vpn>

 

And there you have it: VPN and Internet through your own network.

 

Sudden Application pool shutdown / 503 service unavailable

Today I suddenly had our web-site shutting down on us. It gave me a  Http Error 503 while it all worked fine a minute earlier.

Service unavailable

I checked IIS (IIS7 in our case) and I saw the Application pool had shut down. We couldn’t understand how the application pool could just shutdown. This could potentially shutdown hundreds of sites in one go.

Apparently there is an option in IIS that will shutdown an application pool after 5 errors within 5 minutes, called Rapid-Fail Protection.

image

Disable this feature and your application pool will keep on running.

Investigating Smart Client Software Factory Part 1

Investigating Smart Client Software Factory Part 1

At work we are currently developing a new tool to configure our webapplication. The data is contained in a SqlExpress database and is accessed using Linq2Sql. We are going to implement MVC in the website so it seems a good idea to implement a Model View Presenter pattern in the Config tool.

Installation Experience

I had to do 2 installations: One at work and one on my laptop to test it.
At work I run XP Pro and all went just fine with that. Just a matter of running installers for the Guidance Automation Extensions (GAX), the Guidance Automation Toolkit (GAT) and the Smart Client Software Factory – April 2008 (SCSF). And that was it…

On my laptop however, it did not go so great. I ran the GAX self-extracter and the GAT and SCSF msi’s, but to no avail. I could not find the Guidance automation manager in tools menu of VS and no Guidance Automation project types in the Project template list.

The solution seemed simple: reinstall everything. But the remove option of GAX gave me the message: ‘This installer requires Visual Studio which is not present on the computer. Please install it and try again’

I found a post by malcolm.ellis here and that pointed me in the right direction. I ran the self-extracter with elevated mode and than it did finish the uninstall.

After that I decided to not take any chances and ran all the installers in elevated mode. And finally it finished en look and behold: the menu-items and the project types appear as they should.

 

Hello World Application

As starting of without any knowledge of what to do didn’t really work out. I followed the very usefull Hello World example which can be found on CodePlex.

But as I always say: it isn’t fun if it isn’t usefull! So soon I will extend and refactor the Hello World App to a somewhat more usefull School Admin System for my wife.