DTS or Replication?

I need help deciding whether to use DTS or Replication:

We have four SQL Server databases on four separate servers with the same data schema, one for the north east, south west, mid atlantic, and mid west. We also have one "global" database on a separate server. First, and only this one time, we need to copy data from the four regional databases to the global database. Then, at least once a day we will need to copy changed and new data from the four regional SQL databases to the global database.

I have researched DTS and Replication and am still unsure how to proceed. DTS doesn't seem to have a mechanism to copy over only the changed data. Can somebody please advise me as to which method to use?

Thank you.

This sounds like a candidate for transactional replication to me.

Transactional replication bulk copies the data on first use but then only updates are applied, exactly what you are asking for. You will have to be careful about handling updates on the "global" database if that is a requirement, your key must be unique within all of the publication tables. Also, when setting up the subscriptions ensure replication does not try to drop the table first or replicate to seperate databases and join it together with a view. Using this view approach would be slightly less efficient but less prone to people pushing new subscriptions that drop all target tables.

DTS has no mechanism for doing this, you would have to write the logic yourself based on some sort of changed data marker/date flag. Why go to the hassle when SQL Server has a well tested system in place already?

Hope that helps

Nick
DBA
www.comoni.co.uk

|||

Do you know of any texts or resources I can find to teach me how to do this? I am not familiar with how to run replication or create a replication job, or whatever you call it. Thanks

Karen

|||

Merge replication is what you need. It is designed for the occasionally connected scenarios.

The following are some resources to get you started:

Planning Merge Replication

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_2ipa.asp

How merge replication works

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_30z7.asp

|||

Merge replication would not be my choice here. Merge is best suited to 2 way data changes, not one way as in this case. The management overhead is higher with merge than transactional too. It is fine to run the distribution agent for transactional replication only once a day, providing there is space in the distribution database to store that day's changes.

As for documentation on this, books online is not particularly helpful on the basics. I found a quick Google for transactional replication brought up plenty of how-to articles though. It is worth spending the time reading the onlie resources as replication is a reasonably complex process and has a habit of causing problems if not planned correctly.

Nick

|||

The main difference between merge and transactional replication is not two-way or one-way. There is a "download-only" option in SQL Server 2005 merge replication to allow you to setup one-way replication and you can setup bi-directional transactional replication. The main difference is how soon you will see the change. Giving you only want to sync once a day, I would go with merge replication.

Here is an overview of different replication types and how to choose between them:

http://msdn2.microsoft.com/en-us/library/ms152531(SQL.90).aspx

Here is an example of using merge reaplication to integrate data from multiple branch offices, which is similar to the original scenario in this post:

http://msdn2.microsoft.com/en-us/library/ms151790.aspx

|||Thank you all for your help! I have enough to move me in the right direction now. I'm sure I'll have problems along the way.....

DTS help - using look ups

Hi
I'm migrating data from the old schema to the new schema using DTS. The
old and new schema are different. In the new schema we have identity
columns as Primary keys and referred as foreign keys in the child
tables. In the old schema Primary keys are not identity columns. So when
I migrate old data, identity columns are newly generated for parent
tables. Now in order to establish the foreign key relationship in the
child table i.e, to map the identity value generated, I'm planning to
make use of lookups and active scripts something like this.
Old Schema:
Categories table:
Category name varchar(100) Primary key
Products table:
ProductID int PK
Category Name varchar(100)FK
New Schema:
Categories table:
CategoryID int identity Primary key,
Category Name varchar(100)
Products table:
ProductID int PK
CategoryID int FK
When migrating Categories there is no issue as the identity column
CategoryID is newly generated. When migrating Products table, I should
get the CategoryID value for corresponding Category Name. Shall I use
look ups for this?
SELECT CategoryID FROM Categories
WHERE (CategoryName = ?)
In the active-x script, I'll pass the old CategoryName value
DTSDestination("CategoryID")
=DTSLookups("GetCatID").Execute(DTSSource("CategoryName"))
Is this OK? or is there a better way
Regards
RJN
*** Sent via Developersdex http://www.examnotes.net ***Hi
You could allow identity inserts and keep the old primary keys? This may
help with the products table. If you have gaps in the old Primary keys you
can remove then by allowing cascading updates on the FKs you can then re-ran
k
them.
If you don't want to use lookups, then you could store the transfer the
category name into the products table and then do your own updates to get th
e
categoryid once all the data is imported. The categoryname can then be
dropped from the products table using an ALTER TABLE statement. If the FKS
are in place during the data import you may have to default the Categoryid t
o
a known value that will not violate the FK. This may be quicker than using
lookups.
A tutorial on lookups can be found at:
http://www.sqldts.com/default.aspx?277,1
John
"RJN" wrote:

> Hi
> I'm migrating data from the old schema to the new schema using DTS. The
> old and new schema are different. In the new schema we have identity
> columns as Primary keys and referred as foreign keys in the child
> tables. In the old schema Primary keys are not identity columns. So when
> I migrate old data, identity columns are newly generated for parent
> tables. Now in order to establish the foreign key relationship in the
> child table i.e, to map the identity value generated, I'm planning to
> make use of lookups and active scripts something like this.
> Old Schema:
> Categories table:
> Category name varchar(100) Primary key
> Products table:
> ProductID int PK
> Category Name varchar(100)FK
> New Schema:
> Categories table:
> CategoryID int identity Primary key,
> Category Name varchar(100)
> Products table:
> ProductID int PK
> CategoryID int FK
> When migrating Categories there is no issue as the identity column
> CategoryID is newly generated. When migrating Products table, I should
> get the CategoryID value for corresponding Category Name. Shall I use
> look ups for this?
> SELECT CategoryID FROM Categories
> WHERE (CategoryName = ?)
> In the active-x script, I'll pass the old CategoryName value
> DTSDestination("CategoryID")
> =DTSLookups("GetCatID").Execute(DTSSource("CategoryName"))
> Is this OK? or is there a better way
> Regards
> RJN
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>