I’ve spent two weeks in a row working on this part of the project. We have an old database that is internal to our intranet and want to publish part of its data online, the old database is still in use. For publishing the data we should feed an online MSSQL DB. Due to the fact that the old database is offline and on an intranet the sync process has to run on a machine inside the intranet. There were three possible paths for this task:
- Use a DTS package running on the MSSQL every certain time, probably midnight, SQLAgent would deal with scheduling/running the job. The main drawbacks for this option was the offline old database, that we still had to convert/translate some datatypes between databases and the internal staff had no access to run the DTS.
- Use a DTS package and make it run from a client machine with a dtsrun utility (worse case scenario SQL-DMO). Still the DTS package would have to translate datatypes, the server would have to have access to the old DB datasource, still the DTS would have to be invoked from/wrapped into an application of some sort (windows app/service/console app)…
- Use some client application (probably windows service) that would do the sync process at midnight and also on demand using ADO.NET.
We chose the third option, it’s probably not the most efficient way, to use ADO.NET over DTS, but the fact that the internal staff needed to run the sync process on demand and to receive notifications of success or failure plus the location of the old database, made us think this was a way to go.
There was actually a huge thread on DOTNET-WINFORMS dicussion list at Develop.com regarding architectural issues, I was almost convinced to give up on the windows service implementation and make a DTS package, I might give it a try later with the help of the sysadmin. Creating the DTS is not the issue here but giving a client machine/user access to run the DTS package on demand. You can see the thread Changing the DataRow.RowState Property
Anyway, I found a good article on how to create and debug a windows service at OnDotNet.com
Developing Windows Services
and got the chance to code a windows form wrapper for the service. The service is showing a notification Icon on the systray, trying to emulate the MSSQL Server Service Manager’s behavior.
Right now I’m having issues accessing the configuration file from the dll that has the service. The rest of the code works :). I just googled and found a discussion thread about this
Config Files for DLLs
and I’ll give it a try on Monday.
TGIF, let’s go party!