Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
noamg
Posting Yak Master
215 Posts |
 Posted - 2003-12-11 : 02:20:39
|
| hi,I have an application with 1 connection (to ms-sql 2000).The connection is always opening and working with the database, include modify statement. There are many FK !Now a second connection is open once per day and had to read about 50-40 tables meanwhile, (takes about 10 minutes).It must keeps on the tables relationship.Is the 'isolation level' the solution and how ?thanksNoamNoam Graizer |
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-12-11 : 02:25:08
|
| Noam,Can you try to clarify the question? I'm having trouble understanding you. |
 |
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2003-12-11 : 02:51:19
|
| one connection is always open and running different transaction.second connection needs to copy the specific tables (and not all rows) into other database. it needs like "snapshot" of the database.Noam Graizer |
|
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-12-11 : 03:20:50
|
| I think I understand the problem now, but correct me if I am wrong.1) You have a process that inserts and updates data into multiple related tables as part of ongoing transactions.2) You have a second process that needs to select a substantial amount of data from these tables once per day.3) You are having some sort of problem or concern with the fact that the data you are selecting (per item 2 above) contains data from partially committed transactions. Or you want to make sure that the data selected does not somehow violate your foriegn key constraints.If this is correct, then yes, you could use the transaction isolation level to ensure the referential integrity of the exported data. However, it probably would not be my recommended solution. |
|
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2003-12-11 : 03:32:20
|
| yes. you understand right. the reading process takes few minutes meanwhile the first connection may change data.what is the best solution ?Noam Graizer |
|
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-12-11 : 03:54:39
|
| Unfortunately, it is not that simple. It will depend upon many factors.This is the sort of problem that requires a thorough understanding of the system and its requirements. I would recommend either hiring an experienced SQL Server consultant, or studying up on locking and transaction isolation levels. There is plenty of good information in SQL Books Online.If these are not valid options, then you will want to provide as much detail as you can about the following:- Is it acceptable to prevent modification to the data for the entire duration of the data extract?- How, specifically, is data updated and inserted?- What are the rules for extracting data?- What will you be doing with the extracted data?- Do you maintain a last modified date or timestamp on each row of each table?- Is it possible for data to be deleted by the first process?etc... |
|
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2003-12-11 : 04:10:43
|
| answers:1 - you asked: " .. to prevent modification .. " I say: no. the application must be 24/7 2 - you asked: " How, specifically, is data updated and inserted?" I say: The app is calling to store-procedures. 3 - you asked: "rules for extracting data" I say: some tables have to be full-copy and some tables not. means that the store-procedure has 'where' clause.4 - you asked: "What will you be doing with the extracted data?" I say: insert them as is into other db.5 - you asked: "maintain a last modified date or timestamp" I say: no.6 - "possible for data to be deleted " i say: yes, of course.thanksNoam Graizer |
|
|
|
JCamburn
Starting Member
31 Posts |
Posted - 2003-12-11 : 09:00:50
|
| Given all of these factors, and assuming that you have a normalized database with proper constraints to ensure data integrity, I would recommend using replication.Am I missing something? |
|
|
|
|
|
|
|
|
Data integrity