June 9, 2010 at 10:16 am
I need to create a complete replica of my database that exists on Server-A to Server-B. And when Server-A is updated, those changes need to be applied to Server-B. So that both Servers are always in SYNC.
I understand that I can NOT replicate a Table on Server-A if it does NOT have a Primary Key. So that Table will never be copied to Server-B.
Do I have any options?
...thanks in advance
June 9, 2010 at 10:19 am
Why don't the tables have a primary key?
The best option for replication and for a relational database would be to add a primary key,
Failing this you could try and build your own replication using SSIS,
but you are likely to run in the same issue as replication by having no primary key. how do you know what row is to be updated..
June 9, 2010 at 10:23 am
The database I'm supporting is a VENDOR Package database. I can not touch it (e.g., adding keys).
But there's a request to get a real-time copy of the database onto another server for reporting purposes and they'd like it to be in SYNC with our production database.
I'm looking for ways to satisfy their request.
June 9, 2010 at 10:33 am
have a look at log shipping, this should work for you.
June 9, 2010 at 10:57 am
Do they not have a unique index on tables? If so, adding a primary key would merely be changing that unique one to double duty as a PK. Not sure that's the best idea, but it's an option.
In terms of the second server, is this for DR? If so, I'd log ship or use database mirroring. If this is for reporting, I'd look at SSIS to move stuff, see if you can detect changes somehow and incrementally move data.
June 9, 2010 at 11:00 am
Thanks, Steve & Steve. I'm looking into Log Shipping and Database Mirroring now.
June 9, 2010 at 11:10 am
For as close to real-time as you can really get you can look at database mirroring. For near real-time you really have to go with transactional replication or log-shipping. The other options will have more latency.
You specified exact copy, often times you don't truly need EVERYTHING, are there any of these tables that you don't really need. I also want to point out that replication WILL affect the application of any patches that the vendor supplies in the future to these tables.
In my view, database mirroring or log-shipping are probably the lowest latency, least intrusive methods. Given this is a vendor database I wold steer clear of replication.
I also checked: http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx
Just to be sure you could do these in Standard and Enterprise an dyou can.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply