3

I am using the Visual Studio Database Project for my database. I have it setup to auto-build twice a week. When it builds it drops the database and recreates it from scripts.

I really like the tear down and re-build system as it prevents junk from accumulating in my Development databases.

I now need to add a second database to the mix and get it receiving some replicated tables from the first database.

I know that replication is a server level thing, but I am hoping to just add some post deploy scripts to my database and have that script setup the replication publisher and subscriber (for my first and second database respectively).

Is this going to work? I don't really know much of the in depth stuff with replication. If there is an easier way, I would love to hear it.

Vaccano
  • 78,325
  • 149
  • 468
  • 850
  • Did you resolve this? I have a very similar situation. – GaTechThomas Jul 27 '12 at 15:26
  • @GaTechThomas - yes I did. When you create the replication you have the option to script it out. I got those scripts and customized my build process to have them run at the correct time. – Vaccano Jul 27 '12 at 18:44
  • That's what I expected. I've done something very similar, but I think I need to reinstall, as I'm having issues with subscription being deleted but appears to have been only partially deleted. – GaTechThomas Jul 27 '12 at 19:17

1 Answers1

2

While there is some replication setup at the server-level, it's mostly a database-level technology. While it's technically feasible to do what you're talking about, it doesn't really seem to fit with the goal of replication (which, in my view is to distribute changes in one database to one or more other databases on a continuous basis). Because you're constantly tearing it down, replication just serves as a fancy copy. Ask yourself if you really need the complexity of if a mere copy of your database when you rebuild it would suffice.

But, if you're hellbent on doing this, take a look at sp_adddistributiondb and sp_adddistpublisher for one-time server-level setup and sp_addpublication, sp_addarticle, sp_addsubscription, and sp_startpublication_snapshot for the database-level setup. Good luck!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68