| Author |
Topic |
|
hedxtm
Starting Member
5 Posts |
 Posted - 2007-07-10 : 11:30:14
|
| Hello, coming back to SQL Server after working with Oracle for a while.I am currently using SQL Server 2005, and have an issue I am not exactly sure how to solve. Let's say I have two instances of a database (A & B), the schema of both instances is exactly the same. If I perform an insert on instance A table A how can I automatically have that insert reflected on instance B table A? Both instance are on the same machine. Also I do not want to reflect all the data just one table across multiple instances. I thought replication might be the answer but typically that is used across multiple machines, no?Any help would be greatly appreciated. Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-10 : 11:38:08
|
| how soon do you need to "see" the data in the other instance? I think replication may be an overkill for just one table? how much is the data in the table and what is the growth rate?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
hedxtm
Starting Member
5 Posts |
Posted - 2007-07-10 : 11:55:53
|
quote:
Originally posted by dinakar
how soon do you need to "see" the data in the other instance? I think replication may be an overkill for just one table? how much is the data in the table and what is the growth rate?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Quickly, obviously instantly would be nice.  I should have stated that it may need to be more than one table. The one table example just seemed like the simplest way to explain my issue. So lets say a maximum of 10 tables, each with 1-20k rows. Growth would be very fast in the beginning then taper off to almost nothing. To clarify, initially a bulk import of data would be performed programmatically, then manual intervention would take over. Maybe 8 thousand in the bulk process then manually 15 to 30 inserts per table daily. Basically these tables will hold data that is valuable to each instance, so when data is inserted I would like all instances to see it. Also, aside from the architectural issues with the system, keep in mind I inherited it. I would have designed differently if I had the chance.Let me know if I did not explain clearly or if additional information is necessary. Thanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-10 : 12:15:20
|
| Looks like we might have to get back to replication. You might want to check out how much overhead it creates on the server. You already have 2 instances of SQL server on the box + the replication. You might want to test the scenario before you implement it.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
hedxtm
Starting Member
5 Posts |
Posted - 2007-07-10 : 12:20:59
|
quote:
Originally posted by dinakar
Looks like we might have to get back to replication. You might want to check out how much overhead it creates on the server. You already have 2 instances of SQL server on the box + the replication. You might want to test the scenario before you implement it.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Thanks for the insight. So can I replicate with one installation of SQLServer with multiple instances of a database on the same machine? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-10 : 12:39:19
|
| I have setup replication across instances on different servers not on the same box though. Perhaps worth a try? postback if you see any issues... Also check out books on line for a quick reading to see if they metion about any limitations with setting up replication from/to same server.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
hedxtm
Starting Member
5 Posts |
Posted - 2007-07-10 : 12:40:34
|
quote:
Originally posted by dinakar
I have setup replication across instances on different servers not on the same box though. Perhaps worth a try? postback if you see any issues... Also check out books on line for a quick reading to see if they metion about any limitations with setting up replication from/to same server.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Already into it. Looks like it is possible. Thanks again. Will let you know what I find. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-10 : 12:55:19
|
| We replicate within the same instance even, from one database to another. Replication does not add much overhead at all, so having two instances + replication on the same server should not be an issue.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
hedxtm
Starting Member
5 Posts |
Posted - 2007-07-10 : 12:58:22
|
quote:
Originally posted by tkizer
We replicate within the same instance even, from one database to another. Replication does not add much overhead at all, so having two instances + replication on the same server should not be an issue.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
Yup, just successfully got it to work. I just need to dig deeper into the specifics, about the agent and the various replication methods (transaction/snapshot). Thanks again. |
|
|
|
|
Replication, is that the correct tool for the job?