I'm curious as to how other ISVs deploy/update their application's SQL Server database and reports (for SQL Server Reporting Services) via an installer. I'd like to be able to have this part of the install automated so the user doesn't need to touch SQL Server, however at the same time I need a process on my end that's relatively simple. I'm not interested in manually maintaining a database upgrade script. If you already have something like this implemented, I have some questions for you:

1) There's some install creators like InstallShield that support creating a SQL Server database and running arbitrary SQL scripts without much effort on the development end. This is great for a v1 application, where you're just worried about setting up the database. However, it really doesn't touch on upgrading the database (e.g. v1 to v2 where some tables and lookup data may differ). How do you approach this?

If there was a tool out there that could look at two different different databases (e.g. v1 and v2), take the diff, and create the upgrade script with all the fun ALTER, UPDATE (for lookup data), etc. statements, I think this would work in conjunction with something like InstallShield. I wasn't able to find a tool that does this.

2) I'm interested in creating some reports for my application and have decided to use SQL Server Reporting Services. It'll get the job done and customers can get it for free in SQL Express. The only problem I'm facing is figuring out how my customers can deploy these reports with the least manual intervention possible.

You need to push the report up to the server running SQL Server Reporting Services. I haven't seen any installers that can do this in an automated fashion. Any ideas would be greatly appreciated.

If there was a tool out there that could look at two different different databases (e.g. v1 and v2), take the diff, and create the upgrade script with all the fun ALTER, UPDATE (for lookup data), etc.

Check SQL Deltahttp://www.sqldelta.com

I use http://www.red-gate.com/ and I am happy There is also Visual Studio Team System Database comming soon (CTP is a free download)

mihies wrote:

I use http://www.red-gate.com/ and I am happy There is also Visual Studio Team System Database comming soon (CTP is a free download)

I use red-gate too, and I am extremely happy and impressed with the results.

Red-gate SqlCompare is indeed a great tool. I use it to generate the update scripts for our test and production databases.

We are still working on the installer packages. Out approach is to include only the (generated) update scripts in the installer package. The database includes tables with data about the current version and the installed updates. We let the application check whether the application version is in sync with the database version. If not, the application can execute the update scripts from the application. This way, clients with different application versions can use the same installer package.