Options
Transactional Replication Question
Hello. I attended a presentation on SQL Clone at SQL Saturday Phoenix. One of the operations that was not supported by SQL Clone was Transactional Replication, which is used quite heavily in my environment.
Are there plans to include support for Transactional Replication at some point, or is it a feature that can't be supported due to the structure of the cloning setup?
Thank you for any information you can provide on the matter!
Are there plans to include support for Transactional Replication at some point, or is it a feature that can't be supported due to the structure of the cloning setup?
Thank you for any information you can provide on the matter!
Tagged:
Comments
We can't see a reason transactional replication wouldn't work -the SQL Server engine sees a clone database as a normal database - but we haven't tested that yet.
It might be a setup that needs careful management though; transactions posted into the SQL Clone database would be written to the differencing disk which is local to that SQL Server instance (in its default data directory).
So leaving replication running on the same clone database could cause that file to inflate to the extent that much of the benefit of using a clone database is lost.
It might work to have a clone database which is configured to be a replication subscriber, which is then refreshed daily (dropped and recreated from a new, more recent image).
I expect you would need to be able to add and remove the subscription for that database programatically in order to make that manageable.
Sorry for the delay in getting back to you, I was hoping to get a worked example in place - and possibly a blog post - before weighing in on it. I still hope to do so in the near future.
hope this helps
Richard
(SQL Clone Product Manager)
My client also wants to use SQL Data Compare to replace SQL Replication, the scripts that generate insert statements are perfect for this purpose, but the records that are 'different' appear to be problematic. If data is updated in both the production and subscription databases, how does the SQL Data Compare decide which data elements to update? In my experience, the 'differences' script will write a blank on top of a more recently update field with content. Am I missing something?
I was wondering if you had any luck with setting up a lab on this, and if so is there a blog, paper, or anything with your finding. I am looking to use Clone in my development environment, but like 3kernodle my environment is very complicated with MS SQL Transactional replication. Any new direction?
Please accept my apologies, this forum post was first created some five and half years ago and my former colleague Richard is no longer at Redgate.
To my knowledge, there has been no further testing of creating clones or using SQL Clone in a Transactional Replication environment.
In theory it is most likely possible to create a clone database that has MS SQL Transactional replication configured upon it. The biggest problem you will encounter is the inflation of the Clone differencing disk which I am confident will negate the benefit of using a clone database.
There are no plans in regards to SQL Clone to perform any further investigation on Transactional Replication.
I have also sent to you a private message via this forum that maybe of help to you.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com