1

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.

dingdangdowney
  • 501
  • 1
  • 8
  • 22

4 Answers4

1

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
Kevin Cunnane
  • 8,020
  • 1
  • 32
  • 35
  • I want to make sure I understand. I have the base sql database which contains all static data and has the correct schema as the SQL Project. I have a customer who has a database on their server which needs the updated schema and static data. What would my process be for using SQLPackage? I build my SQL Project, and get a dacpac file. Then, on the client machine, I would run SqlPackage with some sort of parameters I assume, some pointing to the dacpac and others to the database/server? If you could clarify/correct this overall process, you would be even more of a life-saver than you are already – dingdangdowney Jan 21 '15 at 19:12
  • Correct. Here's a blog explaining deployment with SqlPackage.exe: http://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-sqlpackage. For Static Data, the recommendation is to write a post-deployment script that merges that data into the tables (e.g. make it idempotent so multiple deploys would not mess up data ). Jamie Thomson has examples of this in this blog post: http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/01/implementing-sql-server-solutions-using-visual-studio-2010-database-projects-a-compendium-of-project-experiences.aspx. – Kevin Cunnane Jan 21 '15 at 19:25
  • Also here is the SqlPackage.exe doc page - helpful since it explains the various parameters needed for publish/script https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx – Kevin Cunnane Jan 21 '15 at 19:28
  • Actually, http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx is probably the best reference for including data in the project, shows how to write merges clearly. Hope this helps – Kevin Cunnane Jan 21 '15 at 19:32
  • Thank you so much! This all looks great, and I will hopefully be able to test it out soon! – dingdangdowney Jan 21 '15 at 19:34
0

Try SQL server data tools (SSDT) + TFS + schema compare. It automatically creates schema update scripts. It has some problems with linked servers and cross database, but so far I tried everything can be solved in some ways. Another thing is Redgate Source control. Very easy and intuitive. However it costs extra money.

Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
  • How would we use anything like schema compare against a database we may only have RDP access to at best? Currently, the application I mentioned is downloaded by the customer via our website or FTP, and they then run it, where it will run various SQL scripts. All customers may not have run the previous updates, so unfortunately we cannot just base this on our current, in-office database. – dingdangdowney Jan 21 '15 at 14:23
  • use version control TFS, Git, CVS, SVN. Any one. Then take it via RDP to their server and do the compare. – Dmitrij Kultasev Jan 22 '15 at 07:40
0

Generally you keep a version of the previous version's database, and do a schema compare to that version and then you save that as a .SQL file. The client then can run the combination of several .sql files to get them to the latest version (but they can't downgrade this way).

As an alternative, have a look into Entity Framework Migrations https://msdn.microsoft.com/en-gb/data/jj591621.aspx they give you control to both update schema and insert records but also let you rollback/downgrade if needed.

Seph
  • 8,472
  • 10
  • 63
  • 94
0

Here is a method if you choose to use the Redgate tools.

  1. Run Snapper on your customer database. This is a free tool that takes a schema snapshot of your customer database. This creates a binary file that only contains schema information.

  2. Copy this schema file to your local environment. Use SQL Compare to compare your development database (assuming this is the source of your upgrade) to the schema snapshot (the target). This will produce a SQL deployment file that is tailored for your customer.

  3. Copy the SQL deployment file to your customer environment and execute it.

If you need any further help, post a comment to this answer or post a question on our forum. We'd be delighted to help.

David Atkinson
  • 5,759
  • 2
  • 28
  • 35