Nov
26
Replicayshun Part II
November 26, 2008 | Leave a Comment
Well, after quite a bit of effort, last week I finally managed to develop a replication solution for my web database that works.
It took a pretty long time to develop because I would get 95-98% of the way to a solution and would then run into some problem that I could not solve which meant that I had to rethink my design and come up with a work around.
For example, I ran into a very strange problem when I called a .NET program from a TSQL stored procedure to query the database and write a message to an MSMQ Queue. The problem was that for some reason, SQL Server would refuse to accept a connection from the .NET program which had been called from the stored procedure. It worked fine when I ran the .NET program from the command line but not when I used the SQL extended stored procedure xp_cmdshell.
This meant that I had to modify the .NET program so that all it did was write a message to the Queue that contained all the relevant information passed as parameters from the stored procedure and not retrieved from the database via a ADO.NET connection. I shifted the actual query of the database to generate the XML for the message to a different .NET program that was not called by TSQL.
In the end, I used MSMQ as a queueing technology and all messages written to the queue were generated by a .NET program called by xp_cmdshell from the Save and Delete stored procedures after the commit had occurred. I had to treat deletes separately as they were not included in the XML generated using the GetXML .NET function. I also made the decision to generate all sequence numbers in the local database and apply them to the server database so as to keep all sequence numbers in sync between the two databases, rather than apply updates in the same order to both databases and expect the same numbers to be assigned. I tried that and it did not work because I could not control the order in which new records for child tables were saved to the database, it often was not the same order in which they were written to the XML.
I used FTP to transport the files generated from MSMQ between my local machine and my server in Canada. As the FTP classes in .NET were rather low-level, I was lucky to find a suitable .NET library that was much more useful on the web.
The end result in a one way interface that allows me to input data on my laptop and transport the inputs to my server in the same order that they were saved to the laptop database. At the far end, the files are written back to MSMQ on the server and written to the database in the same order.
It is not glamorous, but it works.
¼/p>
Nov
5
Visual Studio 2010 and NET Framework 4.0 CTP now available
November 5, 2008 | Leave a Comment
How time flies, it seems like only yesterday that we got Visual Studio 2008 and NET Framework 3.5 and already the next version is on the horizon.
The CTP of Visual Studio 2010 is available for download here. A feedback page at Microsoft Connect is available here.
It should be installed on a Virtual PC.
According to Redmond Developer News, the new release also adds tools to help make parallel computing a lot easier as well.
I must confess that at this point I am still on Visual Studio 2005 and NET Framework 2.0 which is pretty slack really. The reason for this is that my MSDN subscription expired a while back and I have been waiting until economic conditions (i.e. a new contract) allow me to take out a new subscription which should be pretty soon as I start a new contract on Monday. I have played around Visual Basic 2008 Express but I have been very wary of migrating my code to Visual Studio 2008 until I had access to the Professional Edition.
I am also quite keen on migrating to Windows Server 2008 and SQL Server 2008 in due course as well.
