Database Replication

Question

Database Replication

J39L4753

SSCrazy

Points: 2803
More actions
September 26, 2011 at 4:55 am

#245409

I am using a Database on a Server that I don't have much access through (Its held via an external company and they are reluctant to change security levels)
From this its difficult to create big queries as I'm unable to create StoredProc's, Tables etc. This makes it slow on running queries especially large queries that use TempTables. So what I have done is setup a machine locally and Installed SQL Server on and enables me to have far more access. What I am wanting to do is replicate the DB held externally on my local machine.
I have created a Database locally that utilises tables I use on the external DB. I started to create an SSIS package that will copy data sets into my DB. This works well with it being just a Truncate of all tables and then a straight Insert Into for all the data. With the number of rows held within each table (50 tables with around 14million records) it takes around half hour for it to import them all.
How would it be possible for me to create an Update/Insert query that can Update any records changed in each table and Insert any new records. Ideally I am looking for this whole procedure to take a few mins, so that this will limit the downtime for my front end reporting that sits on web pages?
Thanks,
Jez

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you could setup your SSIS packages to do an incremental load based upon a create/modified date in the source, and then from there use either an update transformation or even better use the TSQL merge statement from a staging table.

Though this method means that there needs to be something in the source to indicate whether a record is new or changed.

Alternatively you could look at one of the forms of replication, either transactional or snap-shop and use these for your reporting db, though given your restricted access at the moment this may not be possible.

J39L4753 SSCrazy Points: 2803 More actions · Answer 2

steveb. (9/26/2011)
you could setup your SSIS packages to do an incremental load based upon a create/modified date in the source, and then from there use either an update transformation or even better use the TSQL merge statement from a staging table.
Though this method means that there needs to be something in the source to indicate whether a record is new or changed.
Alternatively you could look at one of the forms of replication, either transactional or snap-shop and use these for your reporting db, though given your restricted access at the moment this may not be possible.

Apologies, I've had to look at another urgent issue and put this on the back burner

I'm back looking at it now...

Could you build on what you said about incremental loads and staging table, what do you mean by this?

steveb. SSC-Forever Points: 46733 More actions · Answer 3

It a standard way of loading a data warehouse.

you first need to have something on the source table to let you know when a record has been inserted/modified. Usually this is in the form of a modified date column, or it could be a hash match column or even a change data capture system.

Either way you need a way of knowing, if you can't determine if a record is new or updated then incremental loads will not be possible.

If we assume that you have a change data column in your table, then when you load the data this goes into a staging table. You then need to record what the max change date is. So when you do the load for the next day you only select records that have a changed since the last load.

This means that you can merge the data from the staging into the presentation (final table in your DW) table. So the idea is that you are only actually moving a small amount of rows each load which should cut processing time and resources.

I hope i have explained this clearly enough but if you google for 'incremental data loads with SSIS' you will get a lot more information as it is standard pratice

J39L4753 SSCrazy Points: 2803 More actions · Answer 4

Thanks for the reply, I've googled it and found a couple of tutorials which have been very helpful. Testing it on test data seems to be what I am looking for. Real test is to give it a go on my actual database.

Thanks again 🙂