How to Create an Upload Subscription with MS SQL Server Replication

I ran across this problem recently with a mobile application I am writing. I wanted to do some activity and system logs on the device and have it sync to the server with the publication I had setup, but I did not want each devices log data being synced to all the other devices. I wanted it simply to sync and upload the data only.

Sadly, this is where the problem comes in. There are generally only two options when using MS SQL Server replication: bi-directional and download only. Neither of these are really helpful for my problem because I need an upload only option. So how did I solve my problem?

I first thought I could have a job that ran often that would archive the tables and therefore, clear them out. That would reduce the amount of records being synced to all the devices. The problem with this is there is still the overhead of processing all the inserts and deletes. Another problem is the devices sync very often due to them needing the data as near to real time as possible, so there is still a possibility that unneeded data is getting pushed to multiple devices. Also, having a job do this just seemed like a really hacked up way of solving this problem, although I am not sure if my solution is much better.

Along with my production database, I also have an archive database where I want these logs to go anyway. So to get my upload only replication working, I did the a trick with an INSTEAD OF trigger. On my tables, I setup the trigger where it would write the data to my archive table instead of the table I had setup for replication. This allows the data to bypass the production table, and not worry with getting replicated to all the other devices. Since an INSTEAD OF trigger will always execute first, and I am not actually inserting anything into my production table, the replication triggers are never called for insert.

To keep the tables clean on the devices, I cleanup (clear out) the tables on login after the initial sync.

I realize this is probably a really hacked up solution, but I searched around and could not find anything better and this works. So if you have a better solution, please let me know.

More

Create an Object Without a Constructor

Do you ever get into a situation where a constructor just does not seem like the best way to create your object? Maybe you are using the constructor with the id of the object as the parameter, then creating the object from the results from the database. I will use a very simple House object as an example:

    public class House
    {
        public int HouseID { get; set; }
        public Address HouseAddress { get; set; }

        public House(int id)
        {
            this.HouseID = id;
            this.HouseAddress = //get data from database
        }

What happens when there is nothing found in the database? A constructor cannot return null. What happens when you want to also get the house by the owner ID, which is also an int. This will cause two methods with the same signature. The second one maybe a bad example, but you get the idea.

In any case, using a constructor for actions like this can be limiting, confusing, and just bad design. An alternative approach is to take a bit from the factory pattern. Instead of using a constructor, we can create a static method on our class to return the object. This gives us more flexibility, and also the ability to return a null object. So, instead of the above, we can do this:

    public class House
    {
        public int HouseID { get; set; }
        public Address HouseAddress { get; set; }

        private House() {}

        public static House GetHouseByID(int id)
        {
            //Get house by the ID
        }

        public static House GetHouseByOwner(int ownerID)
        {
            //Get house by the owner id
        }

Beyond allowing us to return a null, you will notice that I named these methods according to what they are actually doing. This is another benefit so you don’t have to purely rely on comments to know how you are getting the object.

Yes, the title may be a little misleading because a constructor will be used, just not a public constructor.

More