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 .
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.
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.
+1, @sjimmo. Looks like we were both typing at the same time but your answer is more helpful.
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.
Data is huge.. around millions of record in a table. We are setting up DW.
@sjimmo please provide free ebook link. Link you provided is redirecting to some paid version. :(
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.
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.
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.
- Connect to the publisher in SQL Server Management Studio, and then expand the server node.
- Right-click SQL Server Agent and select Start.
- Expand the Replication folder, right-click the Local Publications folder, and select New Publication.
Regards,
Will
22 People are following this question.
