| Author |
Topic |
|
Jeepaholic
Starting Member
36 Posts |
 Posted - 2002-03-22 : 17:00:22
|
| Hi there. I'm relatively new to the DTS world, but I'm trying to accomplish the following and would appreciate any help you folks can provide:I have a large database in SQL server, used and updated by a separate application. I need to read from this database only TWO tables, in their entirety, and store them in my database for warehousing and mining purposes.Essentially, these two tables need to identical and should be updated as close to real-time as possible. Copying them in their entirety is obviously not a scalable solution.So, there are probably a number of solutions out there that might fit my needs. I'm looking for not only suggestions on what the best solution might be, but also some details on how I might go about making this happen (or links to good articles regarding them.)EDIT: I should add that it's also important to "store and forward" if a particular process in this solution fails, and then is reactivated.Thanks much for your time, and recommendations!Al BsharahEdited by - Jeepaholic on 03/22/2002 17:46:52 |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-03-22 : 18:44:28
|
| Have you tried a transactional or merge replication on those TWO tables? |
 |
|
|
Jeepaholic
Starting Member
36 Posts |
Posted - 2002-03-22 : 19:51:08
|
| Thanks for the direction. I just read up, and started following a tutorial on Transactional Replication (seems like the best solution for me.)HOWEVER, it appears that you cannot replicate if the publisher's database tables don't have primary keys? Is there a way around this? For whatever reason, the application database (a relatively popular application) has no (none, nada, zip) primary keys in ANY of it's tables.Thoughts?Thanks again!Al Bsharah |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-23 : 09:16:56
|
| Add primary keys to the tables! |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-23 : 09:25:41
|
| the most simple way of adding would be adding a identity (auto increment ) field to your tables , if you dont have any field which can be converted to primary key. --------------------------------------------------------------Edited by - Nazim on 03/23/2002 10:06:07 |
|
|
|
Jeepaholic
Starting Member
36 Posts |
Posted - 2002-03-23 : 13:04:10
|
| Ok, cool. I considered that as an option - I just hate modifying things in such a way if it's not necessary...you know, for when someone decides to upgrate the existing application or DB and doesn't let anyone know.Thanks for the responses!Al |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-23 : 13:17:40
|
quote:
Ok, cool. I considered that as an option - I just hate modifying things in such a way if it's not necessary...you know, for when someone decides to upgrate the existing application or DB and doesn't let anyone know.
<well-intentioned rant>Well, in this case, it IS necessary. Primary keys are part of good database design. They're NEVER something to "word around" or consider optional. If the database designer didn't include them, it's not a well-designed database. Sorry. It's impossible to make a logical argument AGAINST having a primary key.If you don't have a key, you don't have a table. -Joe Celko</well-intentioned rant>Add them to your tables now, make sure you inform everyone WHAT you're doing and WHY you're doing it. For any future tables you create, the very first column(s) should be the primary key. If the data doesn't suggest a primary key, add one...even if it's an identity column (PLEEEEEEEEEEEEEAAAAAAAAAAAAASSSSSSSSSSSSSSE do whatever you can to avoid identity columns) You won't regret it. |
|
|
|
Jeepaholic
Starting Member
36 Posts |
Posted - 2002-03-23 : 14:45:55
|
| Hey there. I agree 100%. I don't claim to be anything near a database design expert, but I couldn't believe it myself when I saw no primary keys. To be more clear, the database I'm subscribing to is a fairly well-known trouble ticketing system. So it's not as if we had any decision in the design process. My fear is when we upgrade the product and someone doesn't look through the appropriate change-control information to make sure the primary keys are again implemented in the upgrade.Unfortunately, my primary key will have no change on the functionality of the original application...and without knowing the intricacies of the system and how it uses the database, I'm forced to create a new "dummy" column for the primary key. Therefore, I see no other way around making it an identity column.For my own curiosity, what are the con's of using an identity column you speak of? Your well intentioned rant was appreciated, by the way. Thanks for your feedback!Al |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-23 : 17:50:09
|
| Well, let me ask this: is there anything in the data table that needs to be unique? It may not be a single column, but 2 or even 3. If it's a trouble ticket program, I imagine it generates a ticket number. Does the ticket number appear only once in the table? That would be a perfect primary key.If the entire ticket history is stored in the table, perhaps you could use a combination of ticket number and date/time stamp column as primary key (or another combination that is unique in the table). That way you're not changing the structure of the tables except to indicate which columns are your primary key.I personally avoid identity only because I consider it a crutch. That's just my opinion. I had an article on it:http://www.sqlteam.com/item.asp?ItemID=2599It's not a good article, but maybe it might explain better. Certainly read the comments too, they're not positive but at least you'll get a balanced view! If you really can't find a key candidate in your data, and you absolutely have to, then identity may be your only choice. |
|
|
|
|
SQL2k -> SQL2k, copying ONLY changed data?