How to deploy SQL and attach DB: With ClickOnce?

Question

I need to deploy a winform app that uses multiple db's. I need to deploy them with my app as they interact locally.

Can I use ClickOnce or should I be considering a different approach? I found a couple of examples for ClickOnce but they all assumed that the DB is included in the Solution, which isn't the case here.

THis app used to be a webapp and the DB's and DAL reflect that. I am also using Merge Replication with these local db's to the Central one.

Is my best approach to script the creation of the DB's and somehow run those during the install and then fire off merge replication to fill the tables? The DB's also contain over a 100 sproc's that don't alway's play nice with Merge Repl so I need to be able to include those in the initial install as well.

Otherwise, I thought I might figure out how to Attach the DB's during the install which would include the sprocs in them but I do not know how to do that either.

I am open to ALL suggestion's on how to deploy this app with it's 4 seperate SQL DB's.

Thanks!

Answer 1

If you are using SQL Express Locally, you can attach the database as a part of the connection string, but I don't know that this will work out good for you with replication. You might consider having the database attached locally, and put a revision table that holds the version number of the database in it. Your app could then be written to check the revision of the database and run a SQL Script to upgrade the database to the latest revision. This is how DotNetNuke performs rolling upgrades. You just maintain a version specific .SQL file for each release and include them in the click-once deployment files. This way you can roll forward through any missed revisions to get to the final revision of the database.

---

In psuedo code it would be something like:

 Check the database version in Revision Table
     If less than current version
        Load SQL file list from path
            Foreach file with revision > current Revision
               Execute TSQL Scripts

Each TSQL Script will update the Revision number in the database as it applies. Granted this takes a significant amount of time to implement but when it is rolling it works great to solve your problem.