September 28, 2016 at 12:10 pm
I would like to replicate a subset of rows & columns from 5-6 Production tables into 1 "Search" table that will be used for searching, in as "real time" as possible
For example, My "Search" Table will have 1 row per Customer containing several columns from various customer related table. Is there a way to use transactional replication to keep my "Search" Table in sync with the 3 production tables ??
Production tables
Customer
Profile
Job
Search Table
Cust_FirstName
Cust_LastName
Cust_City
Cust_State
Cust_Zip
Profile_Email
Profile_Phone
Profile_Contact
Job_Title
Job_Description
Job_StartDate
Job_EndDate
Thought ? Suggestions ?
September 28, 2016 at 12:11 pm
No. The destination schema must match the origin schema (can be hacked, I've done it, caused unending pain).
These tables are on different servers?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2016 at 12:15 pm
They will probably be on the same server, but different databases.
The thinking was a denormalized flat file, with indexed columns would give fast search results.
Insert/Update triggers on the base tables, to update the "search" table ?? Although triggers scare me !
September 28, 2016 at 12:33 pm
homebrew01 (9/28/2016)
The thinking was a denormalized flat file, with indexed columns would give fast search results.
Have you tested that thinking out and confirmed it? If not, do some tests before you put a pile of work in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2016 at 1:13 pm
No testing yet. I have a test server to experiment on.
September 30, 2016 at 4:40 am
How about combine all your data at the source and then replicate it to the subscriber using an indexed view as an alternative?
When replicating an indexed view it will be created at the subscriber as a table.
http://www.informit.com/articles/article.aspx?p=607372&seqNum=3
MCITP SQL 2005, MCSA SQL 2012
September 30, 2016 at 5:20 am
When replicating an indexed view it will be created at the subscriber as a table.
http://www.informit.com/articles/article.aspx?p=607372&seqNum=3
The replication of multiple tables to one would not work. Each article replicated requires it's own primary key.
The way to do this shown above will work giving the data that you wish in the indexed view which can be replicated.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
September 30, 2016 at 8:47 am
RTaylor2208 (9/30/2016)
How about combine all your data at the source and then replicate it to the subscriber using an indexed view as an alternative?When replicating an indexed view it will be created at the subscriber as a table.
http://www.informit.com/articles/article.aspx?p=607372&seqNum=3
The problem is to have the "Combined data" updated close to real time from production, regardless of where the combined data resides.
September 30, 2016 at 9:30 am
edited for accuracy.
homebrew01 (9/30/2016)
RTaylor2208 (9/30/2016)
How about combine all your data at the source and then replicate it to the subscriber using an indexed view as an alternative?When replicating an indexed view it will be created at the subscriber as a table.
http://www.informit.com/articles/article.aspx?p=607372&seqNum=3
The problem is to have the "Combined data" updated close to real time from production, regardless of where the combined data resides.
I don't really see where the problem is here, but I also don't know the architecture of your environment.
If you write a view that combines the data of these 5-6 tables in production providing all tables reside on the same databases and index it, the view is then materialized and when you query this view all the data is real time from the tables queried by the view.
I am assuming your need to replicate the data here is that you need the data available in said view to be available on another SQL server, if so replicating this view using transactional replication will provide near real time data in the subscriber subject to replication latency.
MCITP SQL 2005, MCSA SQL 2012
September 30, 2016 at 10:03 am
Another option if the source tables are indeed split over tables on separate databases is to publish the tables and in the article properties only include the columns you need if applicable.
Then have subscriptions setup to each of these publications to replicate all these tables to 1 database. You can then create a view (indexed or not) on top of these subscriber tables that combines your data for searching.
Providing you use transactional replication this will be near real time depending on replication latency.
MCITP SQL 2005, MCSA SQL 2012
September 30, 2016 at 12:40 pm
RTaylor2208 (9/30/2016)
edited for accuracy.homebrew01 (9/30/2016)
RTaylor2208 (9/30/2016)
How about combine all your data at the source and then replicate it to the subscriber using an indexed view as an alternative?When replicating an indexed view it will be created at the subscriber as a table.
http://www.informit.com/articles/article.aspx?p=607372&seqNum=3
The problem is to have the "Combined data" updated close to real time from production, regardless of where the combined data resides.
I don't really see where the problem is here, but I also don't know the architecture of your environment.
If you write a view that combines the data of these 5-6 tables in production providing all tables reside on the same databases and index it, the view is then materialized and when you query this view all the data is real time from the tables queried by the view.
I am assuming your need to replicate the data here is that you need the data available in said view to be available on another SQL server, if so replicating this view using transactional replication will provide near real time data in the subscriber subject to replication latency.
Thanks for the explanation. I didn't understand at first.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply