How to Setup SQL Server Replication

I have been asked to set up a process that will make data available for users to run report daily on a QA box.  Options are:

SnapShot Replication,
Transactional Replication or
Merge?

Which of these three will server the purpose?

What are the troubles with you've encountered with snapshot replication?

How do you threat securities and permissions after the snapshot daily?

What is the best way to sync security here?
FavorableAsked:
Who is Participating?
 
Giorgi_KhurtsilavaConnect With a Mentor Commented:
Hi

if you need data to be replication once a day, you should go with log shipping it is more easy to setup and more easy to manage. you can run log shipping jobs at night thus there will be no downtime for users

http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

as for instance level security objects it should be manually transferred, database will take all it security objects with log shipping
0
 
sqlxlConnect With a Mentor Commented:
Not merge since only the primary database is getting changes.

How big is the database?

If it is very large then snapshot might consume a lot of resources when it takes a snapshot of the database for replication. Transaction replicate would be more efficient in this scenario.
0
 
FavorableAuthor Commented:
This need to be replicated once daily to enable user run report.  
Not necessarily needing the regular changes in the database to run the report.

With this kind of scenario, don't you think snapshot would be more appropriate?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sqlxlCommented:
How big is the database?
0
 
sqlxlConnect With a Mentor Commented:
Snapshot replication moves the entire database each sync so it can consume lots of resources if the database is big.

Transactional replication will move only the update/changes since the last sync. This is more efficient.

If the database is small then snapshot is fine because moving the whole database is not a big deal. If the database has any real size to it, I would use transactional.
0
 
FavorableAuthor Commented:
But i thought transactional will not replicate if there are no primary keys in the object columns?
0
 
FavorableAuthor Commented:
Also, issue with LOB on transactional?
0
 
sqlxlCommented:
You have tables without primary keys?
0
 
FavorableAuthor Commented:
yes we do
0
 
sqlxlCommented:
I'd hate to loose transaction replication as an option over the lack of primary keys on some tables.

Generally speaking, most data sets have some column or set of columns which can be unique. If it makes sense to do so, create the primary key on these column(s). If no columns in the table make sense as a primary key, add GUID column or something and use that.
0
 
FavorableAuthor Commented:
Still research for the best option.  Thank you all!
0
 
marrowyungSenior Technical architecture (Data)Commented:
it seems that we can't replicate DB2 V 8.02 database to MS SQL, right? I am using SQL server 2008 R2 but I don't see I can select Db2 subscription but MS SQL and Oracle subscription only, right?

how can it be done ?
0
 
Anthony PerkinsCommented:
how can it be done ?
You post your own question, rather than tag on a question that is now nearly 3 years old.
0
 
marrowyungSenior Technical architecture (Data)Commented:
yeah, you again !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.