Best way to extract data from a production database's table into another database

Question

Hi Everyone,

Could someone please guide me towards right direction--i have gone through too many ways and i am bit confused which one would be good?

Scenario is: Database A (production database) contains a table whose data want to move continuosly into anothere database B located onto another server. Database B has same architecture like A except its data. As B is going to be used for an enviornment(to keep the logging information) which required its one table same data as in the database A. As real time data is not a factor, delays of up to 30min or an hour are acceptable.

Questions are: Is it possible to setup this job via Replication?

If yes, would it affect the performance of Database A and create any problem for Log Sequence Numbers(LSN) of transaction logs(we are backing up via Commvault)? Can i setup an alert if it fails?

I know there are other ways to do this as well like SSIS, stored procedure etc. Just want to know which one would be efficient and less troublesome.

Let me know if you need more information.

Thanks in advance for any help and please pardon me for my limited knowledge.

Regards,

---

Dinkar Chalotra

Answer 1

Hi Dinkar,

Let me try to answer your questions:

You may go with replication, SSIS, sproc as well. None of them will impact your transaction log backups. Replication a bit complicated for a table and sometimes not easy to manage. SSIS and sproc can cause blocking if you are not using snapshot/RCSI isolation level.  Some special cases you can go with SAN replication or snapshot.

Personally I'd check SSIS solution first, but it depends on lot of other factors as well.

Yes, you can set up alert for your jobs, event and can be notified by email for example.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Thanks Janos,

NO doubt it helps.

My main concern with the SSIS package is its query which will sort out data in the tempDB database and depends upon how big and how frequent the sorting is--will affect performance. Same could happen in replication as well.

This is where my confusion is.

Thanks,

---

Dinkar Chalotra

Answer 3

Hi Dinkar,

I think that Change Data Capture is perfectly suitable for your task.

So, this combination: CDC + SSIS + SQL Server Agent Job should make lightweight, easy to extend solution.

Of course, CDC doesn't have impact to transaction log backups.

Answer 4

Alex,

Can i use it in 2005?

if yes, would it have any impact on the performance?

Much appreciated,

---

Dinkar Chalotra

Answer 5

Thanks Janos,

NO doubt it helps.

My main concern with the SSIS package is its query which will sort out data in the tempDB database and depends upon how big and how frequent the sorting is--will affect performance. Same could happen in replication as well.

This is where my confusion is.

Thanks,

---

Dinkar Chalotra

Why do you want to sort the data? What is the data volume(number of rows)?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 6

Dinar,

Unfortunately, you cannot use CDC in SQL 2005.

As alternative and quick way to find changed rows, you can use CHECKSUM function, which is available in that version of SQL Server.

As example: Using Checksum in SSIS for Delta Loads

Answer 7

Hi,

Sorting will be in memory if you use SSIS and have enough memory. I'd schedule the export to off-peak hours, so your system may have less impact on performance.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 8

Raunak,

Need to compare whether data exist after that date or not. if it does then export it.

---

Dinkar Chalotra

Answer 9

How huge is the data in the table so that we can understand if it would effect the performance or not.

Answer 10

Its a one of the log tables and we are truncating data older than 6 months. Do have full DB backups for the past though.

Regards,

---

Dinkar Chalotra

Answer 11

Hi,

If we just need to simulate the data from Database A to Database B, We can also go with Object transfer task  in SSIS . The table count is roughly 7 million and I think this will not effect the memory and also should be completed within an hour

Thanks,

Sowjanya

Answer 12

Sowjanya,

My main question is after first export. i need to compare what is already in DB B and what is new in DB A. Then need to compare it and transfer it.

Regards,

---

Dinkar Chalotra

Answer 13

My main question is after first export. i need to compare what is already in DB B and what is new in DB A. Then need to compare it and transfer it.

Dinkar, 

I wonder, what is wrong with CHECKSUM approach, mentioned in the previous answers?

>>As example: Using Checksum in SSIS for Delta Loads

Answer 14

Thanks Janos,

Figured out Replication is bit complicated-- Not saying cant do but yea too much to manage.

i'll better stick with SSIS package-- easier to manage.

Thanks everyone for their helpful suggestions.

---

Dinkar Chalotra