question

nileshamruskarsqlserver avatar image
nileshamruskarsqlserver asked

Replication Setup for DW process

We are working on DW project. one of the phase of this process is to setup a replication from transactional db to staging db. I would like to know which type of replication we should implement and our requirement is to replicate only specific columns from a particular tables not entire table. We are using Sql 2008+. Please recommend and also replication setup steps .
replicationetldatawarehouse
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
sjimmo avatar image
sjimmo answered
Determination of what type of replication to use is really an art, based upon many factors. With what you have provided, it is hard to determine whether to use snapshot or transactional. With either, you can pick the fields that you want of a table with one exception, a primary key. Each table replicated must have a unique primary key and it must be replicated. In addition, I would want to know how much data are we talking about. How busy are the servers. The use of snapshots places additional strain on servers and network bandwidth. To help you with your endeavors, here is a link to a free e-book which has a lot of information, and should be able to answer all of your questions. http://www.sqlservercentral.com/articles/books/101780/ Each type of replication has a purpose, and a place.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1, @sjimmo. Looks like we were both typing at the same time but your answer is more helpful.
1 Like 1 ·
I have an extremely large replication system with 2 central distributors pumping millions of records, in some cases per hr. Each publication has up to 80 subscribers in a variety of locations. I also have replication going to our BI system. In the case that you are describing, I would probably go with Transactional. The reason is because of the way snapshot works, the number of records you are talking about and the length of time it takes to bcp out and in all of these records. But again, this is a very superficial answer based on a very broad question. I don't know how many tales we are talking about. What the timeframe is where the server(s) are available to create the snapshot, nor the total number of records being dumped for the snapshot. Network bandwidth for loading the data. Where the snapshot will be physically located. etc... I urge you to spend some time with the ebook I sent the link for. It will help you to do this correct.
1 Like 1 ·
Data is huge.. around millions of record in a table. We are setting up DW.
0 Likes 0 ·
@sjimmo please provide free ebook link. Link you provided is redirecting to some paid version. :(
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Replication is very flexible. You can certainly replicate just a subset of the database. Replication is not necessarily the best approach. It depends on how big that subset of the data is, how fast the data changes, and whether you want it to be constantly changing in your data warehouse or updated periodically.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
I know you asked about replication and I think that both @DavidWimbush and @sjimmo have very valid answers however I'll throw this out there. Have you looked at using SSIS? Do you need the data real-time like? If you don't, SSIS is an excellent tool to load a data warehouse and/or move data. Not only can you load the data, you would be able to transform it in flight to meet your DW design thus potentially not landing the data twice. Once to staging and then again to the warehouse. Depending on your OLTP data design, after an initial load you could then just load the delta's on a schedule. Just a thought I'd throw out there as a potential alternative.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Our expectation is to replicate the data real time but only included columns. Our architecture goes like this Transaction DB->Replication DB-> ETL-> DW -> Reporting-> App SSIS we will using at ETL -> DW level. First we need to setup replication and as per @sjimmo he has answered by question. I will refer the ebook and will see it helps me in that case. Appreciated all your prompt responses.
0 Likes 0 ·
Thank you very much :)
0 Likes 0 ·
Glad that I was able to help. If my solution solved your problem please make sure to mark it as the answer so that others know the question was answered. Thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.