Monday, November 23, 2009

Transactional Replication vs Log Shipping

I plan on writing a bit more on this in the future.
Perhaps I am way behind the curve here (as I have been using Merge Replication on rather 'smallish' databases for the past several years) But I have been tasked with replacing our poorly cobbled together reporting database structures with something that doesn't take 9+ hours a day to process.
No small task.

Right now we 'roll' a set of tables that serves as a make-shift data warehouse that loosely supports a rather large group of reports. At this time all the reports are scheduled (not on demand) and the entire burden of our reporting structures is just too much to bear.

First task is to lighten the load on the OLTP server and allow a fuller access to the actual data for reporting while taking MUCH LESS than 9 hours to do any pre-report processing of data so that we can have on-demand reports pretty much 24-7.

Great, I'm thinking lets do the simplest thing in the SQL World.. T-Log shipping here we come. I setup our Redgate SQL Backup to do a test of a small DB that I have on our development servers. It works like a champ. The data flows over all looks good... no, wait, I'm getting errors about not being able to restore the log...because RedGate's software couldn't get an exclusive lock on the DB.... so I close my connections and bam, 4 logs ship.
Great.
I had read, in passing, that this might be a problem. And that the other problem is that the DB's must remain Read Only. I'm thinking.. "I can deal with that, read only is fine for a reporting DB anyways"... well that is a no-go.

Let us keep in mind that my past life dealt with Merge Replication on SQL 2000 servers, and we are talking from 2 to 200 nodes. No small production. Making a change to the schema (remember pre DDL Trigger days) was also a HUGE deal. Something as small as adding a simple column would destroy the publication. I was looking forward to seeing if I could side step that using another Redgate tool that allowed me to push a script to multiple servers at the same time. oh well.

So I go to test SQL 2008's Transactional Replication. First I was surprised to find there are 2 types of Transactional replication.... one is the standard one way only sync, the other looks like traditional Merge Replication. Great. Then Merge Replication sates that it can only merge with SQL Compact?!? Since I wasn't going to do merge I skipped by this happily.
I then blindly followed the wizard and had my first little transactional replication publication setup.
I then decided to try a push subscription. Easiest way to manage many subscriptions, and I am all for easy... Even when I was only planning on one subscriber.
That went without a hitch.
Then I tried to break it. I added a new column and then populated it. The new column replicated, and the data changes went over without skipping a beat. Hmmm, I knew I could add a table and nothing should happen, that article isn't even known to the publication. I did it anyway. nothing changed on the subscriber.
I then ran an update statement on the subscriber. The data changed at the subscriber but didn't replicate (expected). This is fine, I can deal with that by only handing out read-only rights to this server.

So my first impression is that it is pretty easy to setup. I have, right now, four publications and subscriptions, I need my team to review 4 other databases for some tables that don't have PKs, and are looking to be cluttering things up. Seeing that this is our dev box these things may not even be issues.
So, I need to do more testing of fault tolerance with the push subscription, and see what happens when some real world things happen (like the publisher, distributor or subscriber were to have any down time).

I'll post my findings even though they may not be news to anybody.

Labels: ,

0 Comments:

Post a Comment

<< Home