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]

There are some third party products that can replicate a database that don't have a PK. The migrate changes at the bit level of the disk. Check out XoSoft and Double-Take.

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

 

[zarkon4]

SQL Server 2005 Std will support database mirroring, it would be one way of replicating the data.
This will get you started

http://msdn2.microsoft.com/en-us/library/ms189047.aspx

 

[zarkon4]

Sorry, this is the one I wanted to send you,

http://msdn2.microsoft.com/en-us/library/ms177412.aspx

 

[ptheriault]

Mirroring is a great option but a mirrored database is not accessable. You would need to create a snap shot to view the data. You can not make any changes to snapshots either. So if you have a requirement to make db changes mirroring won't work for you. That includes database security. So if you have a user on the destination server then he/she must have access on the source server.

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

 

[snootalope]

In my case, the mirrored databases wouldn't need to be accessible to any user. It'd act as nothing more than a "live" backup copy in the event of a disaster for us. (we probably wouldn't even backup the "backup" SQL server)

I just started reading this info, but is it easy to make the mirror server accessible?

 

[zarkon4]

That is exactly the way we use it, we also have it configured to automatically failover. In this type of scenario you would not need to backup the mirror, just the primary.

 

[snootalope]

Hey zar, I'm kind of struggling getting this mirror setup.

I'm still trying to get the mirror created, so I'm not even replicating anything yet. What I'm doing is first, doing a Full Backup of my database on server #1, that finishes, I copy the Full Backup to server #2 and try to restore the database. It restores, but the database it restores to sits at "(Restoring...)" for days and days. I've tried creating a new Full backup from server #1 three times. Doesn't help.

Is this step right? Just a Full Backup and Restore the backup to the second server/database before setting up the mirror?

 

[zarkon4]

The mirror will always be showing "Restoring" when it is the mirror. The database you are mirroring should always show "Principal".

The instructions on the msdn:

http://msdn2.microsoft.com/en-us/library/ms175059.aspx

should be able to walk you through setting it up with autofailover.

 

[snootalope]

Do you use a "Witness" server in your setup?

Have you tested your failover as well?

 

[zarkon4]

I have added a witness, I just installed another instance on the Server that has the mirror. It made more sense that way since we have just two locations. Failover works great!
You can manually failover or test it dangerously like I did and pull the network cable out of the main sql server.

 

[snootalope]

So, when you configured your Witness instance to be on the same as your mirrored machine, did you have to change the port number that the witness was listening on?

I'm guessing I have to use the "Alter Endpoint " command, but not quite sure yet...

 

[snootalope]

ah, nevermind, just figured it out.. deleted the mirroring endpoint on the witeness server and created a new 'witness' endpoint on 5023 - seems to have worked.

 

[zarkon4]

The mirror and principal communicate on the same port and the witness communicates on a different port.

 

[snootalope]

Hey Zarkon4 - one more probably really stuipid question, but when something changes in the Principal Database, it's immediately replicated to the mirror server right? Or does it run on a schedule?

 

[ptheriault]

It is mirrored right away.

- Paul

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

 

[snootalope]

Figured so.. Thanks man!

 

[zarkon4]

Btw, How is it working for you?

 

[snootalope]

well, i haven't got it live yet.. still getting the hardware approved. I'm just getting nervous cause we're spending some bucks on it and I just want it to work the way i'm understanding and explaining it. ya know?

So one more question for you guys. The Principal server in this mirror is going to sit at our home office, it's the production server of course. The Mirror server is going to sit across the WAN at our DR site. I'm planning on installing the Witness instance on the Mirror server. But, if the WAN connection between the two simply drops, our Principal server going to think it needs to become the Mirror server simply because the connection was broken right? Cause even if the connection drops, our home office will still have access to the in-house production server.......so it wouldn't be good if it switched it's role to the Mirror. Hopefully that makes since...

 

[snootalope]

sorry....forgot an important word in the last bit:

So one more question for you guys. The Principal server in this mirror is going to sit at our home office, it's the production server of course. The Mirror server is going to sit across the WAN at our DR site. I'm planning on installing the Witness instance on the Mirror server. But, if the WAN connection between the two simply drops, our Principal server ISN'T going to think it needs to become the Mirror server simply because the connection was broken right? Cause even if the connection drops, our home office will still have access to the in-house production server.......so it wouldn't be good if it switched it's role to the Mirror. Hopefully that makes since...