change to all databases

Question

This is probably the most classic database problem.

I have an E-commerce software solution hosted on a SQL server for data, and a web server for the frontend. Every instance/customer has its own database on SQL Server 2008.

During development of the next version, I might change or add tables, views, stored procedures etc.

How do I publish this change to all databases, without losing data? It should be done via a script or something similar. Centralized management is the key...

Answer 1

here are few options

http://stackoverflow.com/questions/1948220/what-is-an-easy-way-to-deploy-database-changes-using-sql-server

• if you have update scripts that you create manually, and are just looking for a way to easily apply those to various servers, check out the SSW SQL Deploy by SSW Consulting. It can handle that scenario very nicely

• if you tend to do more of a database diff approach, then Red Gate's SQL Compare (already mentioned) and SQL Packager make a great combo. You can diff the database between old and new and then apply the changes in a nice package - as an EXE or a C# project

• if you want a real, end-to-end, well thought out approach (with a bit of a learning curve), check outInnovartis' DBGhost approach. It's a entire methodology / technique how to handle database development and incremental updates. It's very powerful and look very promising - but it's a bit of an all-or-nothing approach: either you buy into it and use it end-to-end, or you don't

---

Regards
Satheesh

Answer 2

Hi Marry,

This problem is essentially one of automating the manual process of logging on to a SQL Server, and running a script against one or more databases, that does the modifications you need.

It's made worse, of course, if the instances of SQL Server that you need to update are remote from you, and therefore not directly accessible.

It's also vital to ensure that the scripts are applied in sequence - it would be no point running the "add index" script before the "create table" script.

The way we've solved this is with a web service that packages script files as datasets, and delivers them in the correct sequence to the remote systems when they call home.

On the remote SQL Server, we have a .NET application which calls the web service, downloads the script files, unpacks them and applies them to the database.

When the remote system calls in, it supplies the ID of the most recent upgrade it has. When the web service completes, it knows the last one it delivered. It's therefore trivial to know what level the remote systems are at.

The only manual intervention required is to create the scripts in the first place, and upload them to the central server.

---

Iric Wen
TechNet Community Support