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.
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: ,

Friday, November 13, 2009

Dorid Does!

So, I sit here aching, burning and freezing with chills and hotflashes... no Becky, I'm not menopausal....

I figured I would write down my thoughts on my first week with my new friend R2 the Droid. Yeah I named a phone. I normally wouldn't do that, but my kids (starwars geeks that they are) insisted. I even have a R2D2 ring tone for it... yeah.. I'm a major geek.

Anywho, onto the phone.

This thing oozes quality. The metal chassis is solid. It has a weightiness to it that just says “I'm solid and won't fail you”. The screen is second to none. 3.7” WVGA, 32 bit color... did I say BRIGHT. Yeah, this thing is a major step up from anything I've seen before.

The slideout keyboard is very disappointing. VERY disappointing. My samsung i760's keyboard rocked... I'm thinking Motorola should have just left it off instead of having it this bad. With the bigger screen and improved Android 2.0 soft keyboard I'm not really using it (much).

I'm actually used to the soft-buttons on the front (instead of hardware buttons) the Heptic feedback (buzzing) on touch lets me know that the phone knows I hit the button, and they work very well.

I was pleasently surprised by the sound quality of the speaker. I was testing the Youtube app (built in) and picked a fairly complex classical guitar piece to listen too... and I was blown away. It was much better than I thought it could be. Not nearly as tinny as most phones sound. The screen was also very clear and refreshed fast. The dedicated GPU really helps this sucker process video.

I've only taken 3 still shots and one short video with the camera. It won't be replacing a dedicated camera (my Cannon 2 MP camera is much better). It is ok, and the 'flash' which is really more of a small LED Flashlight, does a little.... but this is a phone first, and the camera seems to be thrown in because all phones have em. That said, when it takes a pic of a UPC barcode for ShopSavvy it does the trick.

So how is the phone? It is a phone first isn't it? Sound quality of the phone is great. Reception on the Verizon network is also very good. Using the native phone or Google Voice it sounds good, is easy to dial and surprisngly easy to hold. Even the speaker phone is pretty good. I placed a call while the phone was in my car mount (really cool motorola accessory that access' a built in GPS Navigation mode). The mount is on my windshield fairly far from where I sit, well within arms reach. I could hear the caller, but it was a little muted because the speaker is well pointing to the mount. I suppose I could use my FM Modulator and send the audio to the car speakers (I've done it with Pandora and the Nav app and it sounds good) and that may do the trick. The caller had no complaints about being able to hear me... however I was using the Nav app and well the computer lady (I need to name her by the way) blurted out “in one quarter mile turn on Louse Burg Road state Highway 401.. and the caller thought that rude, and I have to agree. There is a nav mute button, next time I call and navigate at the same time I'll have to try that.

Battery life is yet to be discovered. I thought it died really early Tuesday night/wednesday morning.. however it has been better since then. Jury is still out. Because it multitasks I'm sure the battery life could be very short... but if I watch it... it may be pretty good.

Now for the “Smart” part of the phone. What is so smart about this phone? First, lets start with the fact that this phone replaces several things for me.
2)MP3 Player
3)GPS Navigation unit
6)Web Browser
7)Radio (Pandora rocks)
9)Alarm clock
10) IM Client
12)Star Chart
13)Guitar Tuner
14)Bike Computer (oh yeah)
15)Weather Alerts
16)note taker (Google Docs anybody)
18)RSS Reader
19)Pod Caster

I could go on and on and on.. however I'll spare you.

Lets start from a business perspective. Out of the box the 'Droid comes with MS Exchange integration. I was able to get my emails, calender and contacts synched up with no problem. It doesn't appear to use the 'to do' list, but neither do I so it doesn't bother me. The email client can get into my folders, and has quite a bit of options as to how much email to keep on board. I have the ability to set a different tone for work emails than from gmail so that is really nice. My last phone all emails sounded the same... so it was tough to know where the email was coming from.

The Calendar works well. It has enough detail, and isn't annoying with the alerts (like my WM6.1 phone was). It syncs well both to and from the server. Contacts to as well. Setup was a breeze took only a minute and bam I was in business.

Personal, and Social networking. Being a google experience device, this thing does all things google well. I am working on becoming more google integrated. I use Gmail, Gtalk and Google Voice now, and am even thinking about forgoing my beloved Open Office for Google Docs... I'm gonna give that a try and see how it goes. Gmail is awesome on the phone, as is Youtube. Not only am I able to view videos but I'm able to upload them as well. I've done it, easy, but required WiFi because the file was large.

I also am able to Facebook with it. Yup, built in. The app is wonderful, and allows me to keep up with FB during the day. Problem is that makes it possible for me to do other stuff at night. Thats good as I'm about to be doing some major programming at night....

I've discovered Pandora Internet Radio for the Android, and I'm in love. I have a few 'channels' where I have defined an artist, and then BAM I get to hear their music AS WELL AS music from others that are just like that artist. I've heard more 'new' stuff this past week than I've heard in a while. AND the best part is that it is free. Plus I get to choose to purchase the MP3 if I like it. Great stuff.

I now also Gtalk on the phone. It is great, the wife can IM me anywhere at anytime an I get it. No more being tied to a desktop. No more annoying texts... see I only really get texts from the office... and it is usually only bad stuff... so when Cheryl started texting me... well... my heart would sink until I saw it was from her.. Now she can IM me..

Google Voice also rocks. I have a new phone number and that number has the ability to send calls to my mobile or my home or my office... or where ever. I also have visual voice mail, which means I get an email as soon as you leave the voice mail and it is transcribed. Granted it isn't always perfect, but I can get the gist of what you wanted to tell me and I can decide to call you back quickly if required without having to listen to the voice mail.

The BEST feature is the Navigation. Bar none. It uses Google Maps and the built in GPS receiver to get you where you need to go. It comes with the Droid free. It has the reliability of routing that Google Maps (the online version) has, the ability to re-route the map as long as you have wireless internet connection, and caches the maps and route in case you drop connection. It even does Turn by Turn vocal directions. The “Lady” who gives you the directions mispronounced many roads, and includes the State Road names (yeah I live in the sticks), and she doesn't pick local routes that I would choose (because of my experience with the roads and the times I think I know the area better) However I think that if Google were 'smart' they would use the people who navigate and choose a different route to better their software.

Yeah, I'm over the moon for this phone. It is my new right hand man, I don't know how I lived without it. It gives me local weather updates and alerts, lets me know where things are and how to get to them, lets me communicate with my co-workers, family and friends, entertains me with music, videos and games, and it beeps like R2D2 for my phone ringer and gmail alerts! Totally worth the 150 bucks I paid for it!

Labels: , , ,