Replication Issues 2

[snootalope]

Hello

I'm needing to replicate a number of tables out of our primary SQL server to a backup SQL system (all Server 2005). However, most of these tables do NOT have a primary index key so I get the "This table cannot be published because it does not have a primary key column. Primary key columns are required for all tables in transactional publications." when trying to setup a Transactional Publication.

So, I tried Merge replication, and all that does is make the agent sit at "....waiting for a response from server" and it never does anything.

I then tried the Snapshot replication, but that doesn't appear to do anything after the first snapshot is taken and initialized to the subscribers.. Plus, snapshot doesn't sound like the best thing cause it replicates the entire database everytime it'll actually run, and beings it's replicating across a WAN, will probably take up a good junk of bandwidth.

So, my question is, is there anyway to replicate tables without having to have a primary key setup? And, without having to replicate the entire database every night/day?

Thanks for any advice..
-scott

 

[ptheriault]

It does make sense that is why I put my witness server on the same subnet and switch. That way you loose the WAN\LAN connection and you have auto failover set up it won't fail over.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

so you put your witness server on your production server side or the mirror side?

 

[ptheriault]

My Witness is on the production server side.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

So, in the event your production site is damaged and the WAN link is down, the mirror server can't communicate with neither the principal or witness, does that trigger the mirror server to switch roles then?

When the production server and witness come back up though, it looks like I'd have to manually switch the roles back, is that right?

 

[ptheriault]

You can configure it for auto or manual failover.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Hey guys, I got an issue here where the time it takes to backup my to be principal database and copy and restore it to my mirror, it appears the log changes to quickly and I get "...needs to be rolled forwared..." when trying to setup the mirror.

Is there a way i can make a backup log file that covers everything since my last backup of the database?

 

[snootalope]

oh, and when make a new backup of the log file from princiapl and move it to the mirror to catch it up i get this:

"The log in this backup set begins at LSN 4586000001601200001, which is too recent to apply to the database. An earlier log backup that includes LSN 4586000000924300001 can be restored.

 

[snootalope]

ah, forgot to disable the maintenance plan running that was backing up the log file... no worries.

 

[snootalope]

Just a follow up.. The mirror is now in place and working just fine. I can't believe how fast the replication is! The overhead is just a few milliseconds as well.. Impressive.

Hey, got a question though. If you put your Witness right beside your Principal, and say that building gets hit by an asteroid. You go to the mirror server to fail it over, all you have to do is hit the mirror properties and manually fail it over right? Cause if it can't see the principal and the witness, it won't do anything. But, once it loses that connection to the two, is it at least manageable and capable of being brought online as a Principal in a disconnected state?