August 2, 2013 at 5:35 pm
For a busy OLTP application, what is the most efficient SQL method/process/tool/technique to map two dissimilar databases in real time? Queries from a new application need a homogenous interface whether connected to an old, inefficient legacy DB or to a new one with different structure. Is this what SCHEMABINDING does?
See attached diagram.
August 3, 2013 at 3:50 pm
It is impossible to say whether what you want to achieve is possible or not. That depends on how different the databases are.
But SCHEMABINDING is not your guy. SCHEMABINDING gives you a guarantee that the underlying objects that a view or a function refers to are not dropped or altered. And it cannot be applied for cross-database references.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 8, 2013 at 2:09 am
Thank you Erland for your reply. Here is a more articulate expression of what we are trying to do:
Using the the data samples below, is it possible to implement transactional replication between DB1 and DB2 only for selected tables and columns?? Assumptions are:
•DB1 is a legacy production database
•DB2 is a new development database
•Both DBs are SQL 2012 Express with advanced tools
•All data columns in DB2 exist in DB1 with the same data specification but in different tables
•DB!.CLIENTS.CODE has 1:1 mapping to DB2.PEOPLE.ID
•DB2 tables ADDRESS, MESSAGES and PHONES may contain multiple records for the same PEOPLE.ID
DB1DB2 TABLES LINKED ON PEOPLE.ID
DB1.CLIENTS.CODE varchar(8)DB2.PEOPLE.ID[ 1:1]
DB1.CLIENTS.FORENAMEvarchar (32)DB2.PEOPLE.NAME1
DB1.CLIENTS.SURNAME varchar (32) DB2.PEOPLE.NAME2
DB1.CLIENTS.SEX varchar (1) DB2.PEOPLE.GENDER
DB1.CLIENTS.ADDR1varchar (32) DB2.ADDRESS.ADDRESS1 [1:Many]
DB1.CLIENTS.ADDR2 varchar (32) DB2.ADDRESS.ADDRESS2
DB1.CLIENTS.SUBURB varchar (32) DB2.ADDRESS.TOWN
DB1.CLIENTS.PCODEvarchar (16) DB2.ADDRESS.ZIPCODE
DB1.CLIENTS.EMAIL varchar (128) DB2.MESSAGES.EM [1:Many]
DB1.CLIENTS.FON1 varchar (32) DB2.PHONES.NUMBER [1:Many]
DB1.CLIENTS.FON2 varchar (32) DB2.PHONES.NUMBER
August 8, 2013 at 9:30 am
I don't think you can do that with replication, but you could do it with stored procedures that read and write to both databases. A view could read from both. I'm assuming you have to keep the old database, otherwise it seems the data should be migrated from legacy to new.
August 8, 2013 at 2:07 pm
I think that doing that with Transactional Replication would be very difficult, as you would have to write a lot of custom code. But I am not an expert in replication.
My gut reaction is that you would have triggers on the tables that writes to Service Broker queues, and the activation procedures in the legacy database would transform the data. This would not mean less custom code than in the Replication case, but you would be wading in everything around replication.
Another option is to use Change Tracking and pick up the changes, maybe in an SSIS package.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 15, 2013 at 6:03 pm
Hi thanks for your reply to my questions. What do you think about his article for the job:
http://www.sqlservercentral.com/articles/Change+Tracking/74397/
Our customer tables to be synched contain max 100,000 rows
August 15, 2013 at 6:18 pm
Related to this topic, i notice that many DBs, including MS samples, have table columns like modifieddate, modifiedby & Co --
Is this really necessary?
Can this information be obtained from metadata and used for synching?
August 16, 2013 at 3:33 pm
Yes, it is common to have such columns, but you need to take precuations to have them filled in. Commonly, a column called modifytime would have a default of getdate() or sysdatetime(), and so this value is filled in when you add a row. However, if you update a row, such a column is not automagically updated, unless, you include
modifydate = DEFAULT
in your set clause, or have a trigger to fill it in.
The best option to track changes is a feature called Change Tracking, which you can read more about in Books Online.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply