[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Setup SQL Server Replication

Posted on 2012-08-14
14
Medium Priority
?
468 Views
Last Modified: 2015-04-25
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?
0
Comment
Question by:Favorable
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 7

Assisted Solution

by:sqlxl
sqlxl earned 1332 total points
ID: 38291900
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
 

Author Comment

by:Favorable
ID: 38292024
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
 
LVL 7

Expert Comment

by:sqlxl
ID: 38292080
How big is the database?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 7

Assisted Solution

by:sqlxl
sqlxl earned 1332 total points
ID: 38292090
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
 

Author Comment

by:Favorable
ID: 38292371
But i thought transactional will not replicate if there are no primary keys in the object columns?
0
 

Author Comment

by:Favorable
ID: 38292373
Also, issue with LOB on transactional?
0
 
LVL 7

Expert Comment

by:sqlxl
ID: 38292841
You have tables without primary keys?
0
 

Author Comment

by:Favorable
ID: 38292887
yes we do
0
 
LVL 7

Expert Comment

by:sqlxl
ID: 38293449
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
 
LVL 1

Accepted Solution

by:
Giorgi_Khurtsilava earned 668 total points
ID: 38300272
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
 

Author Closing Comment

by:Favorable
ID: 38308175
Still research for the best option.  Thank you all!
0
 
LVL 1

Expert Comment

by:marrowyung
ID: 40741991
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40743618
how can it be done ?
You post your own question, rather than tag on a question that is now nearly 3 years old.
0
 
LVL 1

Expert Comment

by:marrowyung
ID: 40743844
yeah, you again !
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question