August 7, 2007 at 9:37 am
We are trying to implement Transactional replliation but our propery management system has some tables which they use for loggin and some tables dont have a uniqueId colum. This triggers an error in the replication process.
Can I get around that somehow? Again we are using Transactional replication. Is this the best solutionf or up to date reporting?
thanks
August 8, 2007 at 1:01 pm
You might consider maintaining these tables outside of the transactional publication, handling these few tables by some other mechanism.
August 9, 2007 at 9:10 am
If the data is truly NOT unique in any way, then this is a case to create an identity column. The only reason for the data would be for replication purposes, but it is a valid reason.
August 9, 2007 at 9:16 am
Transactional requires not just a unique column but a defined primary key. The only replication option without it is snapshot.
August 9, 2007 at 10:49 am
Is there another kind of replication that I can work with besides transactional? The objective is to move our reporting off our live database. and onto its own server this is where replication comes into play for us.
Andy - Can I still have replication withouth a snapshot? Moving forward after the initial replication will not having defined primary key and unique column be a challenge if we went without a snapshot?
What we did was take a backup of the db and restored it onto the replicated new server. Do we still need a snapshot if we are doing this?
thanks
August 9, 2007 at 11:43 am
Snapshot replication is different than transactional, just throws over a complete copy of the data, after that no changes are sent unless you send over the entire snapshot again.
I'd be worried about a table with no primary key. Worth trying to fix regardless of other plans.
You initate replication from a backup, but you still can only do transactional if you have primary keys on the tables involved. Think about why - if you modify a row on the publisher how will it identity the row to change on the subscriber unless there is a primary key? To answer the other part, no, you don't need a snapshot if you init from backup.
August 9, 2007 at 12:57 pm
Sorry, I should have stated what I thought was obvious. Create a Pseudo ID using an identity column and use that column to create a primary key that allows for row level uniqueness of the data for data that is actually not unique.
That will allow you to continue to replicate.
We actually use are own homegrown log shipping to replicate our systems.
We backup the transaction logs to disk. Back up disk to tape. Share that filesystem. Use an automated job to recover those logs to other systems. Those systems can be either left in recovery, or read only.
August 22, 2007 at 6:46 am
It is common to use log shipping for this kind of scenario where you want to keep a recent version of the DB off of the live server for reporting purposes. This is however not available with the free Express sku. We wrote our own log shipping tool like many others have, but it was a pain to keep up. It was a stop gap measure until we purchased another Standard sku so it wasn't worth the time to make it robust.
If you have two instances that are not Express, you can just move these reporting tables into their own DB, then log ship them to the reporting server. Or could just log ship your entire DB, it is not necessarily a bad idea to have a 'warm' server at the standby just in case.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply