July 2, 2010 at 10:31 pm
Hello there,
A - SQL server 2005 standard sp2 ( production for system A - preferably left intact)
B - SQL server 2005/2008 R2 standard. ( for replicate /Mirror / .... from A )
I have sql servers as the above.
Here's my situation:
My difficuluty is that I dont want to intefere with A but i need to syn data from A to B in order to
create some dynamic views in B based on A for some reporting. ANd I need to keep A intact as much as possible except configuring for sync.
Would u have any clue as for which means would be best for my scenario ?
Thanks a lot!
Clement
July 3, 2010 at 1:25 am
In my opinion, replication is to be used for scale out, creating reporting databases and the like
Mirroring is to be used for high availability.
Bear in mind that, in mirroring, the mirror database is inaccessible (it's RECOVERING) and hence cannot be queried. If you're using Enterprise edition, you could create a snapshot of the mirror to query it, but it's read only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2010 at 3:12 am
How up to date does B need to be? also sounds like you need to make some changes on B?
If latency is not an issue you could use full backup of A and restore to B or use snapshot replication.
---------------------------------------------------------------------
July 4, 2010 at 3:26 am
I m actually having a business object server 3.1 on B. And I wilL create some dynamic views from A since A is having new tables anytime. I have learnt that mirroring is not a viable solution. And I wonder if I shud use oneway replication A->B since dynamic views only exist in B. I m still googling a way to find an easy way to implement transactional replication. I have some stored procdure for creating these views on B( executed when there is new table in A). There will also be some master data fed from SSIS. Pls hint me on an easy way to achieving it. Time is not a big issue. Can be like 15 minutes lag. And data changes between A and B is just like 10mb per day. Thank you.
July 4, 2010 at 3:30 am
Straightforward transactional replication. Set it up with the wizards in Management Studio (no easier way), create the views on the subscriber afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2010 at 5:29 am
You say you want to keep A as intact as possible. Any tables you replicate with transactional replication will need a primary key.
---------------------------------------------------------------------
July 4, 2010 at 8:03 am
Thank you. But I have one issue here... If i m not allowed to touch A which doesnt have primary key in their tables, that really seem to post me some challenge....
July 4, 2010 at 8:35 am
Then you can't use transactional or merge replication (merge adds columns). Mirroring won't allow you to add views to the destination, nor will log shipping.
You're limited to snapshot replication - copying the entire database over every 15 minutes. If the DB is small, fine, if it's even a GB, that's going to hurt - or writing your own custom data sync - ugly and time consuming.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2010 at 7:37 pm
Thank you for all ur replies and i learnt more on replication and have more ideas on the problems with all expertise input.
Not sure if the following is going to work but it seems like adding a view to A with primary key is the one with least impact. if all options run out. THe DB in question is in unit of GB so it doesnt seem good to copy it.
Came across this one:
http://searchsqlserver.techtarget.com/feature/Replicate-tables-without-primary-keys
cheers for all the insights.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply