March 19, 2016 at 6:17 pm
Hi All: for several reasons i need to create a (what i call a reporting) table that holds synchronized records from other tables.
This is a very sparse, but very-complicated and extensive third-party db with low utilization - i am sysadmin
example
[Table1].[Column1], [Table1].[Column2],[Table1].[Column3]
[Table2].[Column1], [Table2].[Column2], [Table2].[Column3]
[Reporting].[Table1.Column1], [Reporting].[Table1.Column3], [Reporting].[Table2.Column2], [Reporting].[Table2.Column3]
I want only the column values replicated - keys, constraints, error-checking are not necessary.
The replication could be real-time transactional, or even 'every n minutes'
I suppose you could call this a form of minimalist backup - i just don't know how to proceed efficiently.
many tia
March 21, 2016 at 6:34 am
Normally to set up a reporting system you'd look to something like Availability Groups (where you can set up a read_only secondary), log shipping, mirroring, or even replication. These are standard tools and methods to get a second database that can be read from.
What you're trying to do is extremely customized. You'll have to build it yourself. None of the standard tools works that way. The closest is likely to be replication where you can customize the procedure that does the replication process. That might allow you to do this, but I don't think so. Instead, you're probably going to have to build an SSIS package or a script that you call from SQL Agent so that it runs every X amount of time.
It's going to be a ton of work. Are you sure that AG or one of the other technologies couldn't be made to work for you?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 23, 2016 at 4:40 am
many thanks for your reply, Grant!
i figured, at least, i could trigger a sp on record insert in the subject table, but i guess i'll look into paring down a mirroring script to the bare minimum.
The SSIS packaging certainly seems a bit overkill.
March 23, 2016 at 5:46 am
You can do a trigger, but that comes with the possibility of considerable overhead depending on how it's done. You'll also have to sweat the log on the secondary database to ensure it doesn't get overwhelmed. Plus, depending on the reports and how they're written, you might see significant blocking on the secondary system that would impact the primary pretty severely. I'd probably look to using read committed snapshot isolation on the secondary in order to help with this.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2016 at 4:39 pm
Looks like transactional replication to me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply