April 1, 2011 at 2:30 am
Hi All,
I have set up snapshot replication to take a copy of our live database for us to use as a daily 'pre-release' database.
Throughout the course of each day our developers make modifications to the replicated, pre-release database; this includes adding new database objects as well as modifying existing ones.
I need to "reset" these changes during the next sync and whilst modifications to existing database objects are "overwritten"; all new objects are persisted.
Does anyone have any advice as to how i can achieve this?
Thanks in advance.
April 1, 2011 at 5:15 am
I'm not sure that this is the best way to achieve what i set out in my original post but i have created a script to compare the database objects contained in the "sysarticles" and "sysschemaarticles" tables on the publisher with the database objects in the sysobjects table on the subscriber.
I then deleted any objects which were on the subscriber which are not on the publisher.
All this is done as an agent job which runs each morning after the snapshot replication has completed.
April 1, 2011 at 7:11 am
I'm a little confused here.
In your first post, you said you wanted to persist all new objects, but in your second post you say you've written a script to delete objects on the subscriber that aren't on the publisher (i.e. delete all new objects)!!
The whole methodology doesn't sound particularly robust to me.
If I was developing changes that would eventually be applied to an existing production database, I would be building an upgrade script that makes those changes, not making the changes directly to the database.
Each time the snapshot is refreshed, all you need to do is run the upgrade script.
Each time more changes are made, you need to modify your upgrade script... which can eventually be used to make those same changes to your production database when the time comes.
Edit. Removed erroneous "If" at the bottom of the post.
April 1, 2011 at 7:58 am
Apologies, i thought i had provided enough info in my original post but i have now included a more thorough breakdown of my specific problem.
I wasn't saying that i wanted new objects to be persisted, i was saying that with the current set-up any new objects created on the subscriber are persisted by default (they are not deleted during the sync).
Our developers work on a test copy of the database and they generate scripts to appy any changes made during testing to a pre-release copy of the the live database once initial testing is complete.
This allows us to test in a "live" environment and spot any bugs which may be specific to that environment before putting those changes live.
If there are no bugs, the script is run again on the live database and that is the end of it. If bugs are found, testing is started again to resolve them.
Our pre-release database has to be an exact replica of our live database at the start of each day so that the update scripts can be applied. We have therefore set up snapshot replication to facilitate this need.
The problem is as follows:
Day 1
Pre-release snapshot is generated from live
Developers apply test script which creates a new table (NewTable1) on the Pre-release database.
Bugs are found and the changes do not go live
Day 2
Pre-release snapshot is generated from live
Developers run test script again
Error occrus because the script tries to create NewTable1 again when it is still in the database from yesterdays testing.
As i posted earlier, i am now deleting any objects that exist on pre-release but not on live in order to get a true copy but if anyone has any better suggestions of how i can achieve this i would be very greatful.
Hope this clears up any confusion.
April 1, 2011 at 8:05 am
OK, I get it now.
Why not just backup the live database and restore to development as and when required?
April 1, 2011 at 8:08 am
That is how we have been doing it up to this point; i just wanted to see if using replication as an alternative offerred any benefits.
April 3, 2011 at 3:27 am
You "could" use a third party tool.
Apexsql Diff can create Database object snapshots. You apply the replication snapshot then use apexsql diff to compare the created Apex snapshot to the resulting database. Generate a difference script and apply.
Or you could just get your developers to use source control and reapply their development work in the morning. 😀
April 3, 2011 at 7:59 am
If I understand the requirement correctly, please take a look at the below steps that may help you:
1. Use the post snapshot script option to delete any unwanted object from the subscriber. This would automate the object correction problem at the subscriber end.
2. Use the option @pre_creation_cmd = 'Drop'/'Truncate' for the replicating tables. This would ensure the replicating tables have the similar data as that of live.
Cheers
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply