Which type of replication do i need to follow

Question

Which type of replication do i need to follow

mirravi9

SSC Enthusiast

Points: 112
More actions
May 13, 2009 at 3:15 am

#204609

dear all,
Iam new to replication but need to work for it.
i have a test data base on sql server 2005 which to be replicated to the same system.
i tried for transaction replication but here there r some tables dont have PK's which r to be replicated.
For snap shot replication, the system performance becoming very very slow.
to which process shal i go?
is there any other process ?
Thanks in advance

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Take a look at Database snapshots.

Transactional Replication is the best way but since you dont have PK created for all tables, it is out of the question. Snapshots can lock up resources as well

-Roy

mirravi9 SSC Enthusiast Points: 112 More actions · Answer 2

Thanks a lot. is there any other process to replicate data other than snap shot and transactional..

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Roy Ernest

SSC-Dedicated

Points: 38780

May 13, 2009 at 3:55 am

#993530

BCP, Back up.

-Roy

george sibbald SSC Guru Points: 104210 More actions · Answer 4

what is the target database you are currently trying to replicate to being used for?

---------------------------------------------------------------------

noeld SSC Guru Points: 96590 More actions · Answer 5

george's question is "key". What are you going to do on the "replicated" database ?

I could suggest mirroring or log shipping but they may not do what you want.

* Noel

mirravi9 SSC Enthusiast Points: 112 More actions · Answer 6

thanks all

my requirement is to push data for every 2 hrs to the live database.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

mirravi9 (5/14/2009)
thanks all
my requirement is to push data for every 2 hrs to the live database.

sounds perfect for logshipping then.

ship to the same server to a database of a different name to the primary, with log backup and restore every 2 hours, select the option to have the database in read only mode and kill any connections to it on restore.

---------------------------------------------------------------------

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

george sibbald (5/14/2009)
mirravi9 (5/14/2009)
thanks all
my requirement is to push data for every 2 hrs to the live database.
sounds perfect for logshipping then.
ship to the same server to a database of a different name to the primary, with log backup and restore every 2 hours, select the option to have the database in read only mode and kill any connections to it on restore.

With due respect George, I do not think that is a wise idea. He says that he has to push Data TO THE LIVE DB not from Live DB. You cannot kick people out of Live Server and you cannot keep the live Server as read only.

-Roy

george sibbald SSC Guru Points: 104210 More actions · Answer 9

yes I saw that Roy, I took it as a mistype or language barrier thing because it did not tie in with original question.

worth a check though,mirravi19, do we understand your requirements, you want to know the best way to update a COPY of your live database on the same server every 2 hours? In which case log shipping would do it, with the realisation that users of the copy would have to be logged off to bring the database up to date with a log restore

---------------------------------------------------------------------

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

Better to take precaution and ask than give him an advice that could potentially be trouble to him... :hehe:

-Roy

george sibbald SSC Guru Points: 104210 More actions · Answer 11

Roy Ernest (5/14/2009)
Better to take precaution and ask than give him an advice that could potentially be trouble to him... :hehe:

going from the previous posts I believe we are ok (in fact its a test db, previously tried snapshot repl),

---------------------------------------------------------------------

mirravi9 SSC Enthusiast Points: 112 More actions · Answer 12

thanks all for replies.

here my requirements changed and finalized.

we have a live DB and need to push data for every 2 hrs to test DB(which is on same server) and test DB is used as an back up and also for reporting.

Here tables of DB wont have PK and suitable for transactional replication.

There is limited space available on system so snap shot is not feasible as it locks up much resources.

so whats my option to go for?

george sibbald SSC Guru Points: 104210 More actions · Answer 13

yep, logshippng is only HA solution that fits all your present requirements.

If you are on enterprise edition and are prepared to move test db to a different server you have the option of mirroring with snapshots.

logshipping sounds best to me though.

---------------------------------------------------------------------