Search code examples
sql-serverdatabasevisual-studioprojects

How to Update/Distribute SQ Server DB Projects?

My company is having me look into switching from our current method of managing the databases that our software uses. We not only need to manage in-office databases, but customer databases across the country. Currently, we have a program which runs various sql scripts to add, remove, and update fields and tables within the databases, but this is starting to get large and cumbersome.

I was tasked with researching SQL Server Database Projects, and as far as creating a new database, it looks great. I cannot, however, find much information on how we might be able to distribute updates to our customers using this (they run their own SQL Servers on site) so that they have the latest tables and schema information to correspond with changes and added functionality in our program. Is there some way built in to do this?

Every time I have looked up updating an existing Database with DB Projects, it says to use schema compare. This is definitely not an option, for various reasons, outside of personal, test databases.

Solution

  • I would suggest you use SqlPackage.exe for deployment on client machines. The general process is: Import existing database into a SQL project. Get this building a .dacpac which contains all the schema changes, plus write pre/post deployment scripts needed to manipulate your data as needed. Then rather than using Schema Compare, use publish for database updates - in VS there's a "Publish" option on the project, while SqlPackage.exe is the command line tool that has matching functionality.

    There are two benefits to this approach:

    1. Full publish ensures that the target database is actually updated to match the project schema. If you generate scripts (using Schema Compare/Publish) against one DB then try to apply to a different DB, you have to hope that they had the exact same schema beforehand. If you actually run Publish/Generate Script against the real target server you will guarantee that what is applied is what you expected.
    2. SqlPackage is useful in situations where you can't install anything on the client machine. This is because you can copy all of the required DLLs to 1 folder (take SqlPackage.exe and the other files in the same folder, plus Microsoft.SqlServer.TransactSql.ScriptDom.dll and Microsoft.SqlServer.Types.dll from C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies) and use that from anywhere. You could put it on a thumb drive or copy it across as a .zip file