November 17, 2010 at 12:41 pm
We currently have an application that we use to track trouble calls and use Sql Server 2008 Enterprize edition 64 bit as the backend database. We track calls based on groups and then members within that group. We have been asked by another office to make a copy of the current database and then they want to use the same application pointing to their database to make entries in their own database. We want to be able share a number of tables because we have some groups that work out of both offices and therefore want to keep keep them in sync. These tables are used by several other applications within our organization. The two databases will be on the same server identified by different names. I have read that Merge Replication can be difficult to manage so I was wondering if there is another alternative that might work better for our situation and be easier to manage??? I have had a little experience with Snapshot replication but Merge is new to me and not sure if I have enough insight to make the best choice so I was looking for a little guidance...
November 17, 2010 at 1:55 pm
If they're on the same server, only keep one copy of the tables. The other database should have synonyms that point at the master copy of the table, not real tables.
Here's an example:
use MyFirstDatabase;
GO
create table dbo.MyMainTable (columns);
GO
use MySecondDatabase;
GO
create synonym dbo.MyMainTable for MyFirstDatabase.dbo.MyMainTable;
If you do that, then stored procedures, etc., in both databases can have the same object names in them. Both will access the same table.
That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 17, 2010 at 2:23 pm
Thanks for the response. I went and did a little reading on SYNONYMS and I think I understand the idea. Since the application we are using is a COTS product I cannot change the SQL within the application so when it updates a table it will refer to the table by name. So lets say I have a table called EmpContact in both databases...I would create a Synonym called EmpContact on Database2 that would point to the table EmpContact on database1, is that how it works??? Would it be a good idea to delete the actual table EmpContact in database2 so that no one makes changes to it via Management Studio ???
Thanks again in advance...
November 17, 2010 at 2:25 pm
I'm Sorry, I just re-read your first post and it clearly states only keep one copy of the tables...Brain freeze...
November 17, 2010 at 2:39 pm
You'd have run into an error message if you tried to create a synonym with the same name and schema as a table anyway, so it would have sorted out quickly enough.
And yes, the whole idea here is that the SQL statements, whether procs or in the application, don't care whether an object is a view, table, or a synonym for a view or table, just so long as they can reference it by name in a From clause. "select * from MyTable" will work whether that's a real table, a view, or a synonym for one of those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply